MySQL Geekery du jour. Cannot open file error.

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)

About

A wandering geek. Toys, shiny things, pursuits and distractions.

View all posts by

3 thoughts on “MySQL Geekery du jour. Cannot open file error.

Leave a Reply

Your email address will not be published. Required fields are marked *