How to solve "Incorrect key file for table
'/tmp/#sql_9315.MYI'; try to repair it" mysql start up error
When a server hard disk scape gets filled its common that Apache
returns empty (no content) pages...
This just happened in one server I administer. To restore the
normal server operation I freed some space by deleting old obsolete
backups.
Actually the whole reasons for this mess was an enormous backup
files, which on the last monthly backup overfilled the disk empty
space.
Though, I freed about 400GB of space on the the root filesystem and
on a first glimpse the system had plenty of free hard drive space,
still restarting the MySQL server refused to start up properly and
spit error:
Incorrect key file for table '/tmp/#sql_9315.MYI'; try to
repair it" mysql start up error
Besides that there have been corrupted (crashed) tables, which
reported next to above error.
Checking in
/tmp/#sql_9315.MYI, I couldn't see any MYI -
(MyISAM) format file. A quick google look up revealed that this
error is caused by not enough disk space. This was puzzling as I
can see both
/var and
/ partitions had plenty of
space so this shouldn't be a problem. Also manally creating the
file
/tmp/#sql_9315.MYI with:
server:~# touch /tmp/#sql_9315.MYI
Didn't help it, though the file created fine. Anyways a bit of a
closer examination I've noticed a
/tmp filesystem mounted
besides with the other file system mounts
????
You can guess my great amazement to find this 1 Megabyte only
/tmp filesystem hanging on the server mounted on the
server.
I didn't mounted this 1 Megabyte filesystem, so it was either an
intruder or some kind of "weird" bug...
I digged in Googling to see, if I can find more on the error and
found actually the whole mess with this 1 mb mounted /tmp partition
is caused by, just recently introduced Debian init script
/etc/init.d/mountoverflowtmp.
It seems this script was introduced in Debian newer releases.
mountoverflowtmp is some kind of emergency script, which is
triggered in case if the root filesystem
/</> space gets
filled.
The script has only two options:
# /etc/init.d/mountoverflowtmp
Usage: mountoverflowtmp [start|stop]
Once started what it does it remounts the /tmp to be 1
megabyte in size and stops its execution like it never run. Well
maybe, the developers had something in mind with introducing this
script I will not argue. What I should complain though is the
script design is completely broken. Once the script gets
"activated" and does its job. This 1MB mount stays like this, even
if hard disk space is freed on the root partition - /
....
Hence to cope with this unhandy situation, once I had freed disk
space on the root partition for some reason mountoverflowtmp stop
option was not working,
So I had to initiate "hard" unmount:
server:~# mount -l /tmp
Also as I had a bunch of crashed tables I had to also issue on each
of the broken tables reported on /etc/init.d/mysql start
start-up.
server:~# mysql -u root -p
mysql> use Database_Name;
mysql> repair table Table_Name extended;
....
Then to finally solve the stupid Incorrect key file for table
'/tmp/#sql_XXYYZZ33444.MYI'; try to repair it error, I had to
restart once again the SQL server:
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing
tables..
root@server:/etc/init.d#
Tadadadadam!, SQL now loads and works back as
before!