MySQL on a server stopped working with out any reason. This was a new server.

Notice: Error: Can't open file: './DB_NAME/TABLE_NAME.frm' (errno: 24)
Error No: 1016


I tried to fix the DB with myisamchk, still same error.

For some reason, MySQL can't change to DB folder, same for other web sites with DB problem.


# mysql DB_NAME -e 'show tables;'
ERROR 1018 (HY000) at line 1: Can't read dir of './DB_NAME/' (errno: 24)

I tried to change ownership of DB folder thinning it was some permission/ownership related error.

Even after chown, still getting same error.

# chown -R mysql:mysql /var/lib/mysql/DB_NAME
# ls -l  /var/lib/mysql | grep DB_NAME
drwx------  2 mysql mysql    20480 Apr 21 16:26 DB_NAME/
# mysql DB_NAME -e 'show tables;'
ERROR 1018 (HY000) at line 1: Can't read dir of './DB_NAME/' (errno: 24)
#

Solution

Problem was caused due to low open_files_limit limit on MySQL server.

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

Fixed by increasing the limit to

mysql>  show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 30000 |
+------------------+-------+
1 row in set (0.01 sec)

mysql>

This is done by editing /etc/my.cnf and adding

set-variable = open_files_limit=3000

under [mysqld]

[mysqld]
set-variable = open_files_limit=30000