Planet Geek

MySQL Geekery du jour. Cannot open file error.

Business yammerings , Geekitude

This one is going out to the world in general, because it took me 2 hours of googling to find it, and I want to lessen some poor other slobs pain...

MySQL , in this case on a Debian Etch (stable) install, has a limit set on the number of files that can be opened in the mysqld server. Apparently when you start getting close to this limit, like doing something that opens a LOT of tables at once, you start bumping into it. This article describes how to fix this.

For me this happened when trying to do a 'mysqldump' of the Drupal 5.0 installation we have. Drupal 5 can put all the sites tables into a single database instance. Of course, that means when you do the mysqldump, if you have a large number of sites, you're dumping a large number of tables.

Like, say, 530.

In the default configuration, this causes mysqldump to barf with an unhelpful message like this:

dbs@boomer:~$ mysqldump -uroot -p drupal5 > /tmp/drupal5.dump
Password: 
mysqldump: Got error: 1016: Can't open file: './drupal5/sierraterm_data.frm' (errno: 24) when using LOCK TABLES

The actual file name can vary, depending on at what point the limit was hit.

To fix this, a change to the my.cnf file (on Debian distributions, this is in /etc/mysql) needs to be made. In the [mysqld] section, add:

open-files-limit=15000

You'll need to restart mysqld to make the change active.

Once this change is done, your dump should proceed normally.

(A big nod to the folks on #mysql on Freenode for nudging me in the right direction on this one)

Posted by dbs at June 4, 2007 8:59 PM