MySQL ERROR 1034: Incorrect Key File on InnoDB Table

Sometimes, you may experience “ERROR 1034: Incorrect key file” while running the ALTER TABLE or CREATE INDEX command:

mysql> alter table ontime add key(FlightDate);
ERROR 1034 (HY000): Incorrect key file for table 'ontime'; try to repair it

As the error message mentions key file, it is reasonable to assume we’re dealing with the MyISAM storage engine (the legacy storage engine which used to have such a thing), but no, we can clearly see this table is InnoDB!

When the error message in MySQL is confusing or otherwise unhelpful, it is a good idea to check the MySQL error log:

2019-02-24T02:02:26.100600Z 9 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 696320 bytes written. Retrying for the remaining bytes.
2019-02-24T02:02:26.100884Z 9 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2019-02-24T02:02:26.100894Z 9 [ERROR] [MY-012639] [InnoDB] Write to file (merge) failed at offset 625999872, 1048576 bytes should have been written, only 696320 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2019-02-24T02:02:26.100907Z 9 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'

The most important part of this message is “Error number 28 means ‘No space left on device’” – so, we’re simply running out of disk space. You may wonder, though, what file is it being written to and where is it located?  “Write to file (merge) failed” is your (albeit, not fully helpful) indication; “merge” here corresponds to the temporary file which is used to perform a Merge Sort operation when building Indexes through Sort (AKA Innodb Fast Index Creation).

This file is created in the directory set by innodb_tmpdir server variable if it is not set by the setting of tmpdir variable or OS default, such as /tmp on Linux.  In many cases, such a tmpdir may be located on a filesystem that has little space, making this error occur quite frequently.

The amount of disk space required can be significant, sometimes exceeding the total size of the final table. When adding indexes on CHAR/VARCHAR columns, especially with multibyte character sets (utf8, utf8mb3, utf8mb4), the space allocated for each index entry will be roughly a multiple of the number of bytes per character in charset to the maximum length of the string.  So adding an index on utf8 VARCHAR(100) column will require roughly 400 bytes for every row in the table. 

Summary:

Are you getting the “ERROR 1034: Incorrect key file” message for InnoDB table?  Check your error log and the tmpdir server variable!

by Peter Zaitsev via Percona Database Performance Blog