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!


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

查看所有标签

猜你喜欢:

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

遗传算法

遗传算法

王小平 / 西安交通大学出版社 / 2002-1 / 40.00元

《遗传算法:理论应用与软件实现》全面系统地介绍了遗传算法的基本理论,重点介绍了遗传算法的经典应用和国内外的新发展。全书共分11章。第1章概述了遗传算法的产生与发展、基本思想、基本操作以及应用情况;第2章介绍了基本遗传算法;第3章论述了遗传算法的数学基础;第4章分析了遗传算法的多种改进方法;第5章初步介绍了进化计算理论体系;第6章介绍了遗传算法应用于数值优化问题;第7章介绍了遗传算法应用于组合优化问......一起来看看 《遗传算法》 这本书的介绍吧!

SHA 加密
SHA 加密

SHA 加密工具

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具