16

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.

4
  • 1
    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?
    – Bert
    Apr 30, 2013 at 12:31
  • 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
    Apr 30, 2013 at 12:45
  • 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.
    – Van
    Apr 30, 2013 at 13:00
  • 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.
    – Kzqai
    Jun 22, 2015 at 15:54

4 Answers 4

20

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:

6
  • Disappointing this isn't documented clearly somewhere. :( We just happened upon David's post on serverfault.
    – Van
    Apr 30, 2013 at 14:47
  • And this isn't a bug, according to this: bugs.launchpad.net/mysql-server/+bug/938669
    – Van
    Apr 30, 2013 at 16:32
  • This can become suddenly, and alarmingly, apparent after adding partitions to tables which can cause an increase in open files.
    – markdwhite
    Feb 17, 2016 at 9:06
  • 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.
    – Emmanuel
    Mar 1, 2016 at 1:43
  • 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 Jun 15, 2016 at 19:11
4

Had the same problem on Ubuntu 15.10.

https://bugs.launchpad.net/ubuntu/+source/mysql-5.6/+bug/1434758 - brought the solution:

  1. check if /lib/systemd/system/mysql.service or /lib/systemd/system/mysqld.service is existing
  2. (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
    
  3. if one or both files existing, check if this two lines are included:

    LimitNOFILE=infinity
    LimitMEMLOCK=infinity
    
  4. execute systemctl daemon-reload

... and everything should be fine.

0
1

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.

1
  • this worked for me on Ubuntu 16.04, thanks :) Jul 21, 2017 at 6:35
0

Thank you for the workaround. But for me, the issue has been overshadowed by the two other facts.

  1. My data directory is different from default installation. For multiple reasons, both historical and technical.
  2. I was upgrading from a very old installation, that gone through a number of back- and forward-ports. On the first start of a newly installed MySQL 5.5, the InnoDB engine was not activated (internal implementation was disabled in configuration file, but plugin that was available in previous versions is not present in 5.5), and the upgrade mark was created without actually upgrading any tables.

After fixing InnoDB issue, it was still spitting

mysql> SHOW DATABASES;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

I had to start mysqld in root console and manually restart

/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf --force

Then the server started to show databases, but unable to access some of the tables. Your workaround with increased limits fixed the rest of the issues, thank you!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .