SQLSTATE[HY000]: General error: 23 Out of resources when opening file ‘/tmp/#sql_203f_0.MYD’ (Errcode: 24 – Too many open files)

Table of Content

I solved the problem. This is how I did it:

I edited /etc/security/limits.conf and added this at the bottom of the file:

*                soft    nofile          65535
*                hard    nofile          65535

Then I edited /etc/my.cnf and added/changed this line:

open_files_limit=65535

Then I restarted MySQL. And now my backup/dump of databases runs and complete without errors. I wonder if 65535 in my.cnf is to extreme high? Even this is a very powerfull server.

But on CentOS 7.0 and MySQL 5.6.0 above suggestions not fix my problem finally.

I check open_files_limit and nothing change

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

Edit /etc/security/limits.conf and add the following lines

mysql soft nofile 65535
mysql hard nofile 65535

Then reboot.

Then edit /usr/lib/systemd/system/mysqld.service or /usr/lib/systemd/system/mariadb.service and add

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then run

systemctl daemon-reload

Then restart the db service:

systemctl reload mysql.service

Already check open_files_limit

mysql> show global variables like "%open_files_limit%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0.00 sec)

So, it seems MySQL 5.6.x needs more open limits files then MySQL 5.5.x, because I never had this problem with MySQL 5.5.x

Also I am confused why one of my CentOS 6.4 64bit server has open files limit set to 65535, but the other server has default to 1024. The server that has 65535 limit, does not have any mention of limit 65535 in /etc/security/limits.conf – maybe this is a default that is changed in recent CentOS 6.x versions, because the server with the higer limit, is a newer server.

Some useful links:
http://unix.stackexchange.com/a/157910
http://dba.stackexchange.com/a/86988
http://dev.mysql.com/doc/refman/5.6/…en-files-limit
http://www.centos.org/modules/newbb/…topic_id=13144
http://duntuk.com/how-raise-ulimit-o…openfileslimit

Leave a Reply

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