In VPS and dedicated servers, especially in those running high traffic websites, MySQL server sometimes fails to respond with the error [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/tmp/#XXXXX.MYI’; try to repair it. This often leads to a secondary web server error 500 Internal Server Error.

Resolving the MySQL error

There are several variants of this error, but when a temporary file is mentioned (eg., /tmp/#sql_dc301_1.MYI), the error is caused by insufficient space or inodes in the MySQL temporary directory or drive. The error says that the MySQL server is trying to create a temporary table for executing a query, and is unable to tally the index with the MyISAM table size.

To troubleshoot the issue, you need to verify the size and inode usage of the MySQL temporary directory. The MySQL temporary directory is defined in /etc/my.cnf, and is usually /tmp of Linux servers.

In Linux, you can use the below commands:

# df -h
# df -i

If you see 100% in the temporary drive any of the above commands, you have zeroed in on the problem.

A disk usage command should show you which file is eating up all the space. You can do it using the du command in the temporary directory:

# du -h --max-depth=1

Often it is due to very old cache files, or a crashed program (like backup process) which stored temporary files there. Delete them, and you’ll resolve the immediate issue.

Preventing this MySQL error

If this error has happened more than once, you need to consider the following options to prevent its recurrence:

1. Increase the temporary drive size. In cPanel servers, you can do it using the “securetmp” utility.
2. Increase the temporary drive inode size in VPS servers.
3. Look for large database queries, and optimize those queries for lower resource overhead. You can find out large queries using a combination of MyTop utility, mysqladmin utility and large query logging. The EXPLAIN MySQL query can give you great information on how the queries actually executes in the server.
4. Install a temporary files cleaning utility like tmpwatch to remove old temporary files.
5. Monitor temporary directory usage, and trigger a manual intervention if the size or inode usage goes above 90%.
6. Investigate possibility of abusive connections, and protect the server from them. Configure a Web Application Firewall to keep out spurious connections from bots, configure mod_evasive (in Apache) to kill leeching connections, optimize web server settings, optimize firewall rules and optimize network settings.

Do you need expert assistance managing VPS and Dedicated server clients?

SEE HOW BOBCARES CAN HELP