DNSstuff.com
Jul
5th

mysqldump: Got error: 1017: Can’t find file: (errno: 24) when using LOCK TABLES

I was recently moving a site from one WHM/CPanel server to another WHM/CPanel server and found that this error was causing some problems. This was particularly interesting as I remember trying to move a CPanel account in the past (using /scripts/pkgacct) and we found that everything was fine except that none of the database tables had been backed up. On that occassion we were able to just recreate the empty tables as there wasn’t a whole lot of data but we never did get to the root cause of the problem.

This recent move was similar. It was actually a site that hosts around 2100 blogs with the WordPress WPMU version. Given that there were a lot of blogs with a lot of data, failure wasn’t an option so I decided to do a manual dump of the database. That’s when I saw the error from mysqldump. A quick Google of the error message turned up a similar issue and it wasn’t until after I read carefully that I found this person had the solution. It was in a bug report though and not all that descriptive so I thought it was worth blogging.

The cause of the problem is the default limit that MySQL has on open files. If you fire up your favourite SSH client and login to your server and issue the command:

mysqladmin variables

…it’s going to tell you this:

open_files_limit   | 1024

So, we now know that MySQL has a default limit of 1024 open files. That’s a lot of files…in most cases… but consider my situation where I have over 2000 blogs and each blog has multiple files. Even with 1 file per blog we’d be over that limit. Note that this issue should only happen with MyISAM databases as they use separate files for each table. Knowing that this open files limit was the issue my next step was to find out exactly how many files were in the database directory. To do that you can issue a command like:

ls /var/lib/mysql/my_database/ | wc -l

The /var/lib/mysql part is the default directory for MySQL MyISAM files and the my_database part would be the directory that is named the same as the database you are trying to back up. The command line above will list (ls) the files in the directory and the pipe to ‘wc -l’ will count how many words are displayed in the resulting ‘ls’ listing. In my case the number was:

60061

That’s a heck of a lot of files! The fix was to edit /etc/my.cnf and add the following line to the [mysqld] section:

open-files-limit=100000

I then restarted the MySQL server and it completed the backup of the database and all of its tables.