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:

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:

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!

Share this post

Comment (1)

  • Diego Reply

    Hi Peter, after upgrading from MySQL 8.0.16 to MySQL 8.0.20 (also going from 8.0.16 to 8.0.17) I am having the following message:

    [ERROR] [MY-010725] [Server] Couldn’t repair table: mysql.general_log
    [ERROR] [MY-013178] [Server] Execution of server-side SQL statement ‘CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMBLOB NOT NULL) engine=CSV CHARACTER SET utf8 comment=”General log”;
    ‘ failed with error code = 1034, error message = ‘Incorrect key file for table ‘general_log’; try to repair it’.
    [ERROR] [MY-013380] [Server] Failed to upgrade server.

    Have you ever seen this and do you know how could I fix it?

    Thanks in advance!

    July 3, 2020 at 6:59 pm

Leave a Reply