2

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:

enter image description here

/tmp is far from full:

enter image description here

I'm totally stuck. Thoughts?

  • Also, df -i reveals that /tmp is only using 1% of its inodes. – rinogo Feb 2 '17 at 0:06
  • how You check the table size? how much space it use on disk - ls -l mysql_data/db_folder? – a_vlad Feb 2 '17 at 0:43
6

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:

enter image description here

  • 2
    Upvote for the photo. – Rick James Feb 2 '17 at 22:38
0

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.

  • Yes, that's exactly the problem - the table already uses InnoDB... :/ – rinogo Feb 2 '17 at 19:32
  • Anything in mysqld.err? – Rick James Feb 2 '17 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.