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

How do you fix a MySQL "Incorrect key file" error when you can't repair the table?

提问by Wayne Molina

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 itbut the storage engine I'm using (whatever the default is, I guess?) doesn't support repairing tables.

我正在尝试运行一个相当大的查询，该查询应该每晚运行以填充表。我收到一条错误消息，说Incorrect key file for table '/var/tmp/#sql_201e_0.MYI'; try to repair it我正在使用的存储引擎（我猜是什么默认值？）不支持修复表。

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

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

采纳答案by MarkR

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.

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

回答by marknt15

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 所在的磁盘空间不足。

回答by Drew

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 完成。

回答by Martin

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 存储临时文件的地方

回答by Mladen Janjetovic

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

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

1. go to phpmyadmin
2. open problematic table
3. go to Operations tab (in my version of PMA)
4. at the bottom you will find "Repair table" link

1. 去 phpmyadmin
2. 打开有问题的表
3. 转到操作选项卡（在我的 PMA 版本中）
4. 在底部你会找到“维修表”链接

回答by sai kumar

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

回答by Ninad Pingale

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

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

回答by BhavyaSoft

REPAIR TABLE tbl_name USE_FRM;

修理表 tbl_name USE_FRM;

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

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

Hope this helps

希望这可以帮助

回答by Yasin Hassanien

This happenes might be because you ran out of disk storage and the mysql files and starting files got corrupted

发生这种情况可能是因为您的磁盘存储空间不足，并且 mysql 文件和启动文件已损坏

The solution to be tried as below

要尝试的解决方案如下

First we will move the tmp file to somewhere with larger space

首先我们将tmp文件移动到空间更大的地方

Step 1: Copy your existing /etc/my.cnf file to make a backup

步骤 1：复制现有的 /etc/my.cnf 文件以进行备份

cp /etc/my.cnf{,.back-`date +%Y%m%d`}

Step 2: Create your new directory, and set the correct permissions

第 2 步：创建新目录，并设置正确的权限

mkdir /home/mysqltmpdir
chmod 1777 /home/mysqltmpdir

Step 3: Open your /etc/my.cnf file

第 3 步：打开您的 /etc/my.cnf 文件

nano /etc/my.cnf

Step 4: Add below line under the [mysqld] section and save the file

第 4 步：在 [mysqld] 部分下添加以下行并保存文件

tmpdir=/home/mysqltmpdir

Secondly you need to remove or error files and logs from the /var/lib/mysql/ib_*that means to remove anything that starts by "ib"

其次，您需要从 /var/lib/mysql/ib_*中删除或错误文件和日志，这意味着删除以“ib”开头的任何内容

rm /var/lib/mysql/ibdata1and rm /var/lib/mysql/ibda.... and so on

rm /var/lib/mysql/ibdata1和 rm /var/lib/mysql/ibda.... 等等

Thirdly you will need to make sure that there is a pid file available to have the database to write in

第三，您需要确保有一个 pid 文件可用于写入数据库

Step 1 you need to edit /etc/my.cnf

第一步你需要编辑/etc/my.cnf

pid-file= /var/run/mysqld/mysqld.pid 

Step 2 create the directory with the file to point to

步骤 2 使用要指向的文件创建目录

 mkdir /var/run/mysqld
 touch /var/run/mysqld/mysqld.pid
 chown -R mysql:mysql /var/run/mysqld

Last step restart mysql server

最后一步重启mysql服务器

/etc/init.d/mysql restart

回答by MohammedFiroz-Todotcom

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 查询创建数据库和表结构。它创建的数据库结构如下：

1. database with charset of "utf8", collation of "utf8_general_ci"
2. tables with charset of "utf8" and collation of "utf8_bin".
3. table columns / fields have charset "utf8" and collation of "utf8_bin".

1. 具有“utf8”字符集、“utf8_general_ci”排序规则的数据库
2. 具有“utf8”字符集和“utf8_bin”排序规则的表。
3. 表列/字段具有字符集“utf8”和“utf8_bin”的排序规则。

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

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