Ben's Blog Main Site | All Posts Twitter | RSS

Dealing with MySQL resource shortage

You may in life while working with mysql get the following errors:

ERROR 1016 (HY000) at line 1: Can't open file: './blah/table.frm' (errno: 24)


SQL Error (23): Out of resources when opening file

This happens normally because by default the mysql user on most distro’s has a really short limit of 1024 open files, mysql can freak out if you have a large amount of databases or tables.

I triggered this by activating innodb_file_per_table on my setup and having lots of InnoDB tables.

To increase this you need to add the following to /etc/security/limits.conf

mysql soft nofile 10000
mysql hard nofile 20000

and then add the following to the my.inf mysql config (Normally found in /etc/mysql/my.inf)

open_files_limit = 10000

Reboot the mysql server and it should be fixed!

If this is not fixed then please check if the server itself has not ran out of space.

Like the blogging engine? Click here to see the code