This did not seem to work, and the only solution seems to be to upgrade to MySQL 5.5 11/27/2011


Possible errors you might be having:

mysqldump: Couldn't execute 'show fields from `tablename`': Out of resources when opening file './databasename/tablename#P#p125.MYD' (Errcode: 24) (23)

I started having problems with this when I upgraded to Ubuntu 10.10 with MySQL 5.1 installed. I am running a large database for the Open Dental Software in my office and was having the hardest time figuring out how to fix the problem. Finally I figured out it was not a MySQL problem, but a limit put on the number of files MySQL or my user could open by Ubuntu “security.”

View the problem

Here is how to test to see if this might be an issue:

sudo -u mysql bash

mysql@ubuntu:~$ ulimit -a

You will get a readout something like this:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 71680
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 71680
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Look at the line:

open files                      (-n) 1024

This tells you that your system is limiting you to 1024 files open and that is what cause the problem on large databases.

Fix the problem


sudo nano /etc/security/limits.conf

Add the following lines:

mysql soft nofile 24000
mysql hard nofile 32000

Reboot the system and then follow the steps above to see if the table is changed the number of files is very large now to match the settings you just inserted