MySQL ERROR 1034: Incorrect Key File on InnoDB Table

栏目: IT技术 · 发布时间: 4年前

内容简介: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

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!


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

设计沟通十器

设计沟通十器

Daniel M. Brown / 樊旺斌 / 机械工业出版社 / 2008-12 / 49.00元

本书提供了网站设计时所需的可交付文档资料包括:概念模型,站点地图,可用性报告等,这些文档资料是设计人员和客户进行交流的主要工具。本书深入讨论了文档推介和风险规避技巧,向你展示了如何将文档资料按要求制作成有效的交流工具。 本书内容全面,结构清晰,讲解详细。可作为网站设计人员的参考用书。 关于网站设计的多数讨论好像都着眼于流程的创建,然而,要想把概念变为现实,需要一整套强大的可交付文档资料......一起来看看 《设计沟通十器》 这本书的介绍吧!

URL 编码/解码
URL 编码/解码

URL 编码/解码

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具