MySQL: Too Many Open Files

Recently, I experienced an issue with a MySQL instance running on a CentOS 5.4 host whereby applications were unable to contact the daemon. This was despite there being being no network issues and the operating system not reporting any excessive load. Restarting MySQL fixed the issue and allowed the applications to continue working. While investigating the root cause of the problem, I found that the /var/log/mysqld.log file contained the following messages:

100804 4:43:00 [ERROR] /usr/libexec/mysqld: Can't open file: './test_db/test_table.frm' (errno: 24)
100804 4:43:00 [ERROR] /usr/libexec/mysqld: Can't open file: './test_db/test_table.frm' (errno: 24)
100804 4:43:03 [ERROR] Error in accept: Too many open files

In order to prevent the problem occurring again, I increased the maximum number of file descriptors available to MySQL. First, I increased the maximum number of open file descriptors available to the mysql user account by adding the following two lines to the file /etc/security/limits.conf:

mysql soft nofile 8192
mysql hard nofile 8192

Next, I modified the configuration of the MySQL instance to explicitly set the maximum number of open files allowed by simply adding the following line to the /etc/my.cnf file:

open-files-limit=8192

This configuration directive has a default value of 0, which means MySQL will attempt to allocate enough file descriptors itself. However, as I knew exactly how many open files where available to the MySQL user (8192), I could safely use this as the value for open-files-limit.
A restart of MySQL applied the changes and resolved the issue.

Ubuntu MySQL Upgrade Issue

I recently had to assist a friend who is hosting sites on an Ubuntu VPS; he found himself unable to connect to local MySQL instance as root.
Obviously, my first suggestion was to simply reset the password using the --skip-grant-tables trick that's well documented elsewhere online. However, attempting to stop the MySQL instance using the command /etc/init.d/mysql stop was not successful. So, after backing up the /var/lib/mysql directory, I began to research the issue a bit further:

1. Unable to use the init.d script to stop MySQL, I manually stopped the process: killall mysqld


2. Started the MySQL daemon, ignoring the permissions/grant tables: /usr/bin/mysqld_safe --skip-grant-tables &


3. Reset the root user's password: UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='root';


4. Ran mysqlcheck to look for any corrupted tables (especially in the "mysql" database containing the accounts and permissions) and noticed that errors were being thrown in relation to the "information_schema" database: mysqlcheck --all-databases


5. Attempted a repair of the "information_schema" database: mysqlcheck --repair information_schema


6. Ran mysqlcheck again against the "information_schema" database; errors were still produced by the same tables as before.


7. Restarted mysqld and noticed errors being output in relation to the "debian-sys-maint" user.

This particular error was what helped to identify the root cause of the issue. The server had recently been updated using the apt-get tool and this had included an update to MySQL. Trawling the web unearthed a bug filed on the Ubuntu project bug tracker that suggested that the post-upgrade scripts had not been executed. There is a dedicated MySQL account that exists on a Debian-based system specifically for this (and other DB maintenance) tasks: debian-sys-maint
The credentials for this user can be found in the file /etc/mysql/debian.cnf and should already be present in the MySQL database itself; allowing the system to perform maintenance tasks. Realising this user must be missing from the system, I set about recreating the user:

1. Logged into MySQL as root and, using the credentials specified in /etc/mysql/debian.cnf, created the "debian-sys-maint" user: GRANT ALL ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '';


2. Now restarting MySQL generated different error messages: Cannot proceed because system tables used by Event Scheduler were found damaged at server start


3. Knowing that the MySQL package installed on the system had been upgraded recently (and judging by the number of people in similar situations), I forced the execution of the mysql upgrade scripts: mysql_upgrade -u root -p --verbose --force

After these steps were carried out, restarting the MySQL instance occurred without any errors being produced as well as it being possible to authenticate to the system as "root".