当你无法修复表时，如何修复MySQL“错误的密钥文件”错误？

I'm trying to run a rather large query that is supposed to run nightly to populate a table. I'm getting an error saying Incorrect key file for table '/var/tmp/#sql_201e_0.MYI'; try to repair it but the storage engine I'm using (whatever the default is, I guess?) doesn't support repairing tables.

我正在尝试运行一个相当大的查询，该查询应该每晚运行以填充表格。我收到错误，指出表'/var/tmp/#sql_201e_0.MYI'的密钥文件不正确;尝试修复它，但我正在使用的存储引擎（无论默认是什么，我猜？）不支持修复表。

how do I fix this so I can run the query?

我该如何解决这个问题，以便运行查询？

9 个解决方案

#1

The storage engine (MyISAM) DOES support repair table. You should be able to repair it.

存储引擎（MyISAM）支持修复表。你应该能够修复它。

If the repair fails then it's a sign that the table is very corrupted, you have no choice but to restore it from backups.

如果修复失败，则表明该表已损坏，您别无选择，只能从备份中恢复。

If you have other systems (e.g. non-production with same software versions and schema) with an identical table then you might be able to fix it with some hackery (copying the frm an MYI files, followed by a repair).

如果您有其他系统（例如，具有相同软件版本和架构的非生产）具有相同的表，那么您可能能够通过一些hackery来修复它（复制frm和MYI文件，然后进行修复）。

In essence, the trick is to avoid getting broken tables in the first place. This means always shutting your db down cleanly, never having it crash and never having hardware or power problems. In practice this isn't very likely, so if durability matters you may want to consider a more crash-safe storage engine.

从本质上讲，诀窍是避免首先破坏表格。这意味着始终干净地关闭数据库，永远不会崩溃，也不会遇到硬件或电源问题。在实践中，这不太可能，因此如果耐久性很重要，您可能需要考虑更安全的存储引擎。

#2

You must change the location of MySQL's temporary folder which is '/tmp' in most cases to a location with a bigger disk space. Change it in MySQL's config file.

在大多数情况下，您必须将MySQL临时文件夹的位置（'/ tmp'）更改为磁盘空间更大的位置。在MySQL的配置文件中更改它。

Basically your server is running out of disk space where /tmp is located.

基本上，您的服务器没有/ tmp所在的磁盘空间。

#3

You'll need to run this command from the MySQL prompt:

您需要从MySQL提示符运行此命令：

REPAIR TABLE tbl_name USE_FRM;

From MySQL's documentation on the Repair command:

从MySQL关于修复命令的文档：

The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.

如果.MYI索引文件丢失或其标头已损坏，则可以使用USE_FRM选项。此选项告诉MySQL不要信任.MYI文件头中的信息，并使用.frm文件中的信息重新创建它。使用myisamchk无法进行此类修复。

#4

Your query is generating a result set so large that it needs to build a temporary table either to hold some of the results or some intermediate product used in generating the result.

您的查询生成的结果集非常大，以至于需要构建临时表以保存某些结果或生成结果时使用的某些中间产品。

The temporary table is being generated in /var/tmp. This temporary table would appear to have been corrupted. Perhaps the device the temporary table was being built on ran out of space. However, usually this would normally result in an "out of space" error. Perhaps something else running on your machine has clobbered the temporary table.

临时表在/ var / tmp中生成。此临时表似乎已损坏。也许正在建立临时表的设备用完了空间。但是，通常这通常会导致“空间不足”错误。也许在你的机器上运行的其他东西已经破坏了临时表。

Try reworking your query to use less space, or try reconfiguring your database so that a larger or safer partition is used for temporary tables.

尝试重新编写查询以使用更少的空间，或尝试重新配置数据库，以便将更大或更安全的分区用于临时表。

MySQL Manual - B.5.4.4. Where MySQL Stores Temporary Files

MySQL手册 - B.5.4.4。 MySQL存储临时文件的位置

#5

Apply proper charset and collation to database, table and columns/fields.

对数据库，表和列/字段应用适当的字符集和排序规则。

I creates database and table structure using sql queries from one server to another. it creates database structure as follows:

我使用从一个服务器到另一个服务器的sql查询创建数据库和表结构。它创建数据库结构如下：

database with charset of "utf8", collation of "utf8_general_ci"
数据库与“utf8”的字符集，“utf8_general_ci”的整理
tables with charset of "utf8" and collation of "utf8_bin".
表格与“utf8”的字符集和“utf8_bin”的整理。
table columns / fields have charset "utf8" and collation of "utf8_bin".
表列/字段具有字符集“utf8”和“utf8_bin”的排序规则。

I change collation of table and column to utf8_general_ci, and it resolves the error.

我将表和列的排序规则更改为utf8_general_ci，它解决了错误。

#6

In my case, there was a disc space issue. I deleted some unwanted war files from my server and it worked after that.

就我而言，存在光盘空间问题。我从我的服务器上删除了一些不需要的war文件，然后它就可以了。

#7

REPAIR TABLE tbl_name USE_FRM;

Command only run when MySQL 'Storage Engine' type should be 'MyISAM'

命令仅在MySQL'存储引擎'类型应为'MyISAM'时运行

Hope this helps

希望这可以帮助

#8

Simple "REPAIR the table" from PHPMYADMIN solved this problem for me.

PHPMYADMIN的简单“REPAIR the table”为我解决了这个问题。

go to phpmyadmin
去phpmyadmin
open problematic table
打开有问题的表
go to Operations tab (in my version of PMA)
转到操作选项卡（在我的PMA版本中）
at the bottom you will find "Repair table" link
在底部你会找到“修复表”链接

#9

this issue is because of low storage space availability of a particular drive(c:\ or d:\ etc.,), release some memory then it will work.

这个问题是因为特定驱动器（c：\或d：\等）的存储空间可用性低，释放一些内存然后就可以了。

Thanks Saikumar.P

谢谢Saikumar.P

#1