- B.5.2 Common Errors When Using MySQL Programs
- B.5.2.1 Access denied
- B.5.2.2 Can't connect to [local] MySQL server
- B.5.2.3 Lost connection to MySQL server
- B.5.2.4 Client does not support authentication protocol
- B.5.2.5 Password Fails When Entered Interactively
- B.5.2.6 Host 'host_name' is blocked
- B.5.2.7 Too many connections
- B.5.2.8 Out of memory
- B.5.2.9 MySQL server has gone away
- B.5.2.10 Packet too large
- B.5.2.11 Communication Errors and Aborted Connections
- B.5.2.12 The table is full
- B.5.2.13 Can't create/write to file
- B.5.2.14 Commands out of sync
- B.5.2.15 Ignoring user
- B.5.2.16 Table 'tbl_name' doesn't exist
- B.5.2.17 Can't initialize character set
- B.5.2.18 'File' Not Found and Similar Errors
- B.5.2.19 Table-Corruption Issues
If you get ERROR '...' not found (errno:
23), Can't open file: ... (errno:
24), or any other error with errno
23 or errno 24 from MySQL, it
means that you haven't allocated enough file descriptors for
the MySQL server. You can use the perror
utility to get a description of what the error number means:
shell>perror 23OS error code 23: File table overflow shell>perror 24OS error code 24: Too many open files shell>perror 11OS error code 11: Resource temporarily unavailable
The problem here is that mysqld is trying to keep open too many files simultaneously. You can either tell mysqld not to open so many files at once or increase the number of file descriptors available to mysqld.
To tell mysqld to keep open fewer files at
a time, you can make the table cache smaller by reducing the
value of the table_cache
system variable (the default value is 64). Reducing the value
of max_connections also
reduces the number of open files (the default value is 100).
To change the number of file descriptors available to
mysqld, you can use the
--open-files-limit option
to mysqld_safe or set the
open_files_limit system
variable. See Section 5.1.3, “Server System Variables”. The
easiest way to set these values is to add an option to your
option file. See Section 4.2.3.3, “Using Option Files”. If you have
an old version of mysqld that doesn't
support setting the open files limit, you can edit the
mysqld_safe script. There is a
commented-out line ulimit -n 256 in the
script. You can remove the “#”
character to uncomment this line, and change the number
256 to set the number of file descriptors
to be made available to mysqld.
--open-files-limit and
ulimit can increase the number of file
descriptors, but only up to the limit imposed by the operating
system. There is also a “hard” limit that can be
overridden only if you start mysqld_safe or
mysqld as root (just
remember that you also need to start the server with the
--user option in this case so
that it does not continue to run as root
after it starts up). If you need to increase the operating
system limit on the number of file descriptors available to
each process, consult the documentation for your system.
Note
If you run the tcsh shell, ulimit does not work! tcsh also reports incorrect values when you ask for the current limits. In this case, you should start mysqld_safe using sh.
User Comments
This hint works as well with "errno: 9" (bad file descriptor) message. I suppose most of filesystem errors (may appear randomly on tables) are solved with this tweak.
I suggest you to edit my.cnf (usually /etc/my.cnf) and add the following line :
open-files=2048
Then launch the mysql (linux/bsd) server by doing (with sh)
# ulimit -n 2048
# [your current mysqld launching command]
Where 2048 is the number of files you allow MySQL to open.
Add your own comment.