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:

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). 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 8.8.3, “How MySQL Opens and Closes Tables”. Reducing the value of
max_connections also reduces
the number of open files (the default value is 100).

--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 :