MySQL can't open files after updating server: errno: 24

12.04 server mysql

33,716

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:

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.

33,716

Van

Updated on September 18, 2022

Comments

Van over 1 year
Ubuntu: 12.04 LTS (Linux mysql02 3.2.0-40-generic #64-Ubuntu SMP Mon Mar 25 21:22:10 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux)

MySQL: Ubuntu distro 5.5.31

Apparmor: REMOVED!

Server has been running rock solid for over a year. Then this Monday MySQL began failing. An update has caused the problem and we cannot figure it out what it is. We have even tried to roll back to MySQL 5.5.30 but with no luck. We returned at 5.5.31.

MySQL error log entries:
```
130430  7:55:46 [ERROR] Error in accept: Too many open files
130430  7:55:46 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/fclvod.frm' (errno: 24)
130430  7:55:46 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/fcnote.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/ffcont.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/ffcontv.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/ffnote.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/frcfcl.frm' (errno: 24)
```
It appears we are running into ulimit problem. We have removed APPARMOR completely. We have increased the /etc/security/limits.conf and still no luck:
```
# Out of desperation....
* soft  nofile  49152
* hard  nofile  65536

# No effect!?!!?
#mysql  soft  nofile  49152
#mysql  hard  nofile  65536
```
And to show the limits.conf is working:
```
root@mysql02:/etc/security# ulimit -Sa | grep "open files"
open files                      (-n) 49152

root@mysql02:/etc/security# ulimit -Ha | grep "open files"
open files                      (-n) 65536
```
And here are the important entries in my.cnf
```
[mysqld_safe]
open_files_limit = 16384

[mysqld]
open_files_limit = 16384
```
However:
```
root@mysql02:/etc/mysql# mysqladmin -u root -pThePassword variables| grep open_files_limit
open_files_limit                                  | 1024
```
We are totally stumped and down. Any assistance would be greatly appreciated.
- Bert about 11 years
  
  What error messages are there in the logs BEFORE the one about Too Many Open Files? You have restarted mysqld since changing open_files_limit, right?
- Van about 11 years
  
  yes, we have restarted MySQL every time we make changes. We have one table that is being reported as missing (and it is for some reason): 30430 8:36:39 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './oti_lw_prod/apinvoice_charges.ibd'!
- Van about 11 years
  
  FYI, we moved our users to our other master (duel master setup) sever (01) and it is now exhibiting the exact same symptoms. It (01) had the same exact configuration as this failing server (02) and is our fail over master should this one (02) die. Well, so much for that plan. We are pretty sure this is an OS problem.
- Kzqai almost 9 years
  
  I'm sure this didn't work for the Original Poster, but for me this happened after a security update, and restarting mysql was enough.
Van about 11 years

Disappointing this isn't documented clearly somewhere. :( We just happened upon David's post on serverfault.
Van about 11 years

And this isn't a bug, according to this: bugs.launchpad.net/mysql-server/+bug/938669
markdwhite over 8 years

This can become suddenly, and alarmingly, apparent after adding partitions to tables which can cause an increase in open files.
Emmanuel about 8 years

This worked for me on Ubuntu 15.10. After upgrading packages got tons of 'Can't open file' error messages, which broke all my sites :(... Thanks a million for saving me a lot of headache and time.
Eaten by a Grue almost 8 years

can anyone explain what is the "pre-start" block? I'm running Ubuntu 16 and having this issue but the config file looks different than it did previously
thesmart over 7 years

I was confused by this also, but it's not a bug. Linux PAM is a system for authenticating user sessions, and PAM uses the /etc/security/limits.conf file. Starting processes using upstart, or forking processes as different users from a non-user session will not use PAM, and thus will not consider limits.conf. This is by design.
Richard Frank almost 7 years

this worked for me on Ubuntu 16.04, thanks :)