{"id":301,"date":"2014-09-16T16:38:42","date_gmt":"2014-09-16T08:38:42","guid":{"rendered":"http:\/\/play.datalude.com\/blog\/?p=301"},"modified":"2014-09-16T16:38:55","modified_gmt":"2014-09-16T08:38:55","slug":"mysql-not-starting-errno-24-too-many-open-files","status":"publish","type":"post","link":"https:\/\/play.datalude.com\/blog\/2014\/09\/mysql-not-starting-errno-24-too-many-open-files\/","title":{"rendered":"mysql not starting – errno: 24 – Too many open files"},"content":{"rendered":"

This was a really strange one and might be peculiar to debian servers. I was trying to change a couple of mysql default settings, so after editing the config file, I restarted mysql on a webserver and it didn't come back up again. There were a bunch of errors in the \/var\/log\/mysql.log saying \"errno: 24 – Too many open files\". And in fact mysql was so busy pumping these errors out, that it didn't have time to do any databasing on the files it was<\/strong> able to open.<\/p>\n

After a bit of experimentation (read 'panic'), I discovered that rebooting the server started mysql up fine, but doing a 'service mysql restart' produced the file errors, so I rebooted and left it running while I investigated. So the problem lies with the way the system and mysql agree on the number of open files they need. In the system its set in \/etc\/security\/limits.conf , whereas in mysql its set in \/etc\/mysql\/my.cnf via the open_files_limit directive. NB, your install of mysql might use\u00a0 a different config file. To check what your current open_files_limit value is with mysqladmin …<\/p>\n

> mysqladmin variables | grep open\r\nopen_files_limit\u00a0 | 1024<\/pre>\n
OK well there's the problem, 1024 is the default value, and a quick count of the files in mysql's data directory gave me about 2500 files. The simplest solution seemed to be to set it higher in my.cnf so I put in 'open_files_limit = 16000', but after a 'service mysql restart'<\/em> and even a reboot, I still got the errors.<\/p>\n
Now I needed to attack the \/etc\/security\/limits.conf file. I put these lines towards the end.<\/p>\n
*\u00a0\u00a0 \u00a0hard\u00a0\u00a0 \u00a0nofile\u00a0\u00a0 \u00a032000\r\n*\u00a0\u00a0 \u00a0soft\u00a0\u00a0 \u00a0nofile\u00a0\u00a0 \u00a032000<\/pre>\n
After a reboot, dammit, mysql is still using open_files_limit of 1024. But ulimit -a shows that the system is now using the 32,000 value.<\/p>\n
So another search around the internet … and we find a bunch of posts telling us that \/etc\/security\/limits.conf gets loaded AFTER mysql starts at boot, so the correct limits for mysql are not set. The solution is, for debian at least, is to find \/etc\/pam.d\/common-auth, and add this line to the bottom.<\/p>\n
session    required      pam_limits.so<\/pre>\n
And that's it, after a 'service mysql restart<\/em>' or a reboot, mysql's open_files value is now set to 32000, which is the value in limits.conf rather than my.cnf, which it still chooses to ignore.<\/p>\n","protected":false},"excerpt":{"rendered":"
This was a really strange one and might be peculiar to debian servers. I was trying to change a couple of mysql default settings, so after editing the config file, I restarted mysql on a webserver and it didn't come back up again. There were a bunch of errors in the \/var\/log\/mysql.log saying \"errno: 24 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[1,4],"tags":[],"class_list":["post-301","post","type-post","status-publish","format-standard","hentry","category-it","category-linux"],"_links":{"self":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts\/301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/comments?post=301"}],"version-history":[{"count":0,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts\/301\/revisions"}],"wp:attachment":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/media?parent=301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/categories?post=301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/tags?post=301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}