Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

B.5.2.18 'File' Not Found and Similar Errors

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 23
OS error code  23:  File table overflow
shell> perror 24
OS error code  24:  Too many open files
shell> perror 11
OS 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_open_cache system variable (the default value is 64). This may not entirely prevent running out of file descriptors because in some circumstances the server may attempt to extend the cache size temporarily, as described in Section, “How MySQL Opens and Closes Tables”. 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.4, “Server System Variables”. The easiest way to set these values is to add an option to your option file. See Section 4.2.6, “Using Option Files”. If you have an old version of mysqld that does not 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.


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.

Download this Manual
User Comments
  Posted by Thierry Coppey on July 23, 2006
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 :


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.
  Posted by Emmanuel KARTMANN on September 28, 2010
On a Windows server we experienced similar problems (running out of file handles) with a slightly different error code (22) and messages:

Can't create/write to file #sql105c_452d_0.MYD' (Errcode: 22) Error_code: 1
Can't create table 'temporary.mytable' (errno: 22) Error_code: 1005

The only workaround we found was to lower some configuration parameters:

SET GLOBAL table_open_cache = 128 /* WAS: 1024 */
SET GLOBAL max_connections = 350 /* WAS: 1000 */

The rationale behing this settings is:
* Less open tables => less opened files.
* Less connections => less temporary tables => less opened files.

Please note that error 22 is really unexpected/awkward it this context - it means:
OS error code 22: Invalid argument



Sign Up Login You must be logged in to post a comment.