After some maintenance was done to the virtual host server that one of my virtual servers was running on that required a host reboot, my websites weren’t running due to the MySQL error "[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24)"
tl;dr
In my case, the issue was due to too many files being open. Increasing the open_files_limit setting and restarting MySQL fixed the issue. It’s quite possible this error message will also occur for other reasons (e.g. file corruption), but in my case it was the file limit issue.
Longer answer
After working out it was not an issue with the web server (Nginx) or PHP, I checked the MySQL error log, which was full of messages like this (where "databasename" was the name of the database, and "tablename" was the name of the table):
[ERROR] /usr/sbin/mysqld: Can't open file: './databasename/tablename.frm' (errno: 24)
I was able to log into the MySQL command line and run a query against a database, and got the same sort of error message:
ERROR 1016 (HY000): Can't open file: './databasename/tablename.frm' (errno: 24)
After some Googling around, I found that the issue might be due to the open files limit setting not being high enough. You can check what the current setting is with this query, and result:
mysql> SHOW VARIABLES LIKE 'open_files_limit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 1024 | +------------------+-------+ 1 row in set (0.00 sec)
And then see how many files are currently open:
mysql> SHOW GLOBAL STATUS LIKE 'Open_files'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_files | 712 | +---------------+-------+ 1 row in set (0.01 sec)
Judging from the above result, I did actually have a high enough open files limit, but it was worth attempting to change the setting to see if it fixed the problem, seeing as the number of open files wasn’t too far off the limit. I also found myself unable to subsequently open up a MySQL command prompt again.
Increasing the open_files_limit setting
The open_files_limit setting cannot be changed from the MySQL command line; it needs to be done in the configuration file and then MySQL restarted. Add the following line to the your my.cnf file:
open_files_limit = <your limit here>
Then restart MySQL.
In my case, it wouldn’t shut down so after about 30 minutes I rebooted the server (it was reporting issues with open files again). When it came back up, the problem was gone, so increasing the open files limit solved my problem.
This is the error message in the log file when I was attempting to shut down MySQL, which confirms it was an issue with the file limit:
[ERROR] Error in accept: Too many open files
Grepping the error log now, I can see this particular message came up a number of times in the three hours the issue occurred for.
You can’t set the open_files_limit dynamically
Note that you can’t set open_files_limit dynamically from the MySQL command line; you have to change it in the configuration and restart.
If you run this:
mysql> SET GLOBAL open_files_limit = <your limit here>;
You will get this error message:
ERROR 1238 (HY000): Variable 'open_files_limit' is a read only variable
