MySQL can't open files after updating server: errno: 24
Solution 1:
OS: Ubuntu (Debian) deployments
MySQL Server Option: open-files-limit
It seems that the Debian upstart doesn't use the parameters defined in /etc/security/limits.conf, so when you launch mysql through the service command (and so, under upstart), it overrides those defined limits and uses the default 1024.
The solution is to modify the mysql.conf file that defines the upstart service, it is located in /etc/init/mysql.conf and add the following lines before the pre-start block:
# NB: Upstart scripts do not respect
# /etc/security/limits.conf, so the open-file limits
# settings need to be applied here.
limit nofile 32000 32000
limit nproc 32000 32000
References:
- https://serverfault.com/questions/440878/changing-open-files-limit-in-mysql-5-5
- http://dev.mysql.com/doc/refman/5.5/en/server-options.html#c12634
Solution 2:
Had the same problem on Ubuntu 15.10.
https://bugs.launchpad.net/ubuntu/+source/mysql-5.6/+bug/1434758 - brought the solution:
- check if /lib/systemd/system/mysql.service or /lib/systemd/system/mysqld.service is existing
-
(in my case) if not, create /lib/systemd/system/mysql.service and copy content of to this file https://bugs.launchpad.net/ubuntu/+source/mysql-5.6/+bug/1434758/comments/11 and add the two lines somewhere in the file
LimitNOFILE=infinity LimitMEMLOCK=infinity -
if one or both files existing, check if this two lines are included:
LimitNOFILE=infinity LimitMEMLOCK=infinity - execute
systemctl daemon-reload
... and everything should be fine.
Solution 3:
As none of the above fixed the problem for me (only lead to the system running out of memory), here's the solution I found:
In /etc/mysql/my.conf you need to increase MySQLs internal open_files_limit. So temporarily add this to the configuration and restart MySQL.
[mysqld]
open_files_limit = 100000
sudo /etc/init.d/mysql restart
After running the operation that gives you the too many open files error, you can change your configuration back to its default and restart MySQL again.