Ample disk space with error: "1034 Incorrect key file for table 'table_name'; try to repair it"

Question

I'm attempting to alter a 1.6 GB InnoDB table with the following query:

ALTER TABLE `table_name` ADD `field_id` int(11) unsigned DEFAULT NULL

The query fails with the following error:

1034 Incorrect key file for table 'table_name'; try to repair it

Nearly every thread on this topic (SO and elsewhere) suggests that this is due to the tmpdir running out of space.

While this seems to be the problem for many, this doesn't seem to be the case on our system.

tmpdir is /tmp:

/tmp is far from full:

I'm totally stuck. Thoughts?

Answer 1

Well, I'm a big dummy. When I went to check the size of the database via the method requested by @a_vlad, I realized that previously I was checking the free space on /tmp on the wrong server.

Gah. Executing the command again while tracking the free space on /tmp on the correct server reveals that lack of free space is, indeed, the problem.

...and here's the slice of humble pie I'm eating right now:

Answer 2

I'll be different. When using MyISAM, almost any non-graceful shutdown leads to some tables needing "repair". This is an important reason to switch to InnoDB.

CHECK TABLE `table_name`;

That will probably say that it needs repairing, then do

REPAIR TABLE `table_name`;

After that, you can proceed with your ALTER.