mysqldump: Got error: 1016: Can't open file: 'some_file' (errno: 24 - Too many open files) when using LOCK TABLES

[Jeroen Baten]

This is a report and also the fix. Please patch this so I don't have to any more.

Error:
During run of automysqlbackup I get the following email:
mysqldump: Got error: 1016: Can't open file: 'some_file.frm' (errno: 24 - Too many open files) when using LOCK TABLES
mysqldump: Got error: 1016: Can't open file: 'some_file.frm' (errno: 24 - Too many open files) when using LOCK TABLES
mysqldump: Couldn't execute 'show events': Out of resources when opening file './mysql/event.MYD' (Errcode: 24 - Too many open files) (23)

Solution:
There is an error in the maximum number of open files. The source is not in the kernel configuration!

This solves this problem:
Error: mysql: too much open files error

Changing /lib/systemd/system/mysql.service and adding in the Service section 2 extra lines:

[Service]
... ... ...
LimitNOFILE=infinity
LimitMEMLOCK=infinity

After that:

systemctl daemon-reload
service mysql restart

Problem goes away (at least until I run an apt update again :-) )

ProblemType: Bug
DistroRelease: Ubuntu 16.04
Package: mysql-server 5.7.22-0ubuntu0.16.04.1
ProcVersionSignature: Ubuntu 4.4.0-119.143-generic 4.4.114
Uname: Linux 4.4.0-119-generic x86_64
ApportVersion: 2.20.1-0ubuntu2.18
Architecture: amd64
Date: Wed Jun 13 10:06:48 2018
InstallationDate: Installed on 2012-07-01 (2172 days ago)
InstallationMedia: Ubuntu 12.04 LTS "Precise Pangolin" - Release amd64 (20120425)
Logs.var.log.daemon.log:

MySQLConf.etc.mysql.conf.d.mysql.cnf: [mysql]
MySQLConf.etc.mysql.conf.d.mysqldump.cnf:
 [mysqldump]
 quick
 quote-names
 max_allowed_packet = 16M
MySQLConf.etc.mysql.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
MySQLVarLibDirListing: False
PackageArchitecture: all
ProcEnviron:
 TERM=xterm-256color
 PATH=(custom, no user)
 XDG_RUNTIME_DIR=<set>
 LANG=nl_NL.UTF-8
 SHELL=/bin/bash
SourcePackage: mysql-5.7
UpgradeStatus: Upgraded to xenial on 2016-09-24 (627 days ago)

[Robie Basak (racb)]

Thank you for taking the time to report this bug and helping to make Ubuntu better.

We'll look into whether it's appropriate to make this change by default for all users.

> Problem goes away (at least until I run an apt update again

Rather than edit /lib/systemd/system/mysql.service, you should override behaviour by creating a /etc/systemd/system/mysql.service with the directives you want to override. This is the provided mechanism to make changes to your system that won't get overwritten by future updates.

[Lars Tangvald (lars-tangvald)]

I'm not opposed to this; The server has its own settings to limit memory and file count, so it shouldn't suddenly go wild if we remove the systemd limit.

The error message also doesn't really explain that the block is from systemd, making it difficult to users to know how to increase it.