MySQL：#126 - 表的密钥文件不正确

MySQL: #126 - Incorrect key file for table

提问by Brian

I got the following error from a MySQL query.

我从 MySQL 查询中得到以下错误。

#126 - Incorrect key file for table

#126 - Incorrect key file for table

I have not even declared a key for this table, but I do have indices. Does anyone know what could be the problem?

我什至没有为这个表声明一个键，但我确实有索引。有谁知道可能是什么问题？

回答by Monsters X

Every Time this has happened, it's been a full disk in my experience.

每次发生这种情况时，根据我的经验，它都是一个完整的磁盘。

EDIT

编辑

It is also worth noting that this can be caused by a full ramdisk when doing things like altering a large table if you have a ramdisk configured. You can temporarily comment out the ramdisk line to allow such operations if you can't increase the size of it.

还值得注意的是，如果您配置了 ramdisk，则在执行诸如更改大表之类的操作时，这可能是由于 ramdisk 已满造成的。如果您不能增加它的大小，您可以暂时注释掉 ramdisk 行以允许此类操作。

回答by snooze92

First of all, you should know that keys and indices are synonyms in MySQL. If you look at the documentation about the CREATE TABLE Syntax, you can read:

首先，您应该知道键和索引在 MySQL 中是同义词。如果您查看有关CREATE TABLE Syntax的文档，您可以阅读：

KEYis normally a synonym for INDEX. The key attribute PRIMARY KEYcan also be specified as just KEYwhen given in a column definition. This was implemented for compatibility with other database systems.

KEY通常是 的同义词INDEX。key 属性PRIMARY KEY也可以KEY在列定义中指定时指定。这是为了与其他数据库系统兼容而实施的。

---

---

Now, the kind of error you are getting can be due to two things:

现在，您遇到的这种错误可能是由于两件事：

• Disk issues on the MySQL server
• Corrupted keys/tables

• MySQL 服务器上的磁盘问题
• 损坏的键/表

In the first case, you will see that adding a limit to your query might solve the problem temporarily. If that does it for you, you probably have a tmpfolder that is too small for the size of the queries you are trying to do. You can then decide or to make tmpbigger, or to make your queries smaller! ;)

在第一种情况下，您将看到向查询添加限制可能会暂时解决问题。如果这样做对您有用，则您的tmp文件夹可能对于您尝试执行的查询的大小来说太小了。然后您可以决定或tmp放大，或缩小您的查询！;)

Sometimes, tmpis big enough but still gets full, you'll need to do some manual cleanup in these situations.

有时，tmp足够大但仍会变满，您需要在这些情况下进行一些手动清理。

In the second case, there are actual issues with MySQL's data. If you can re-insert the data easily, I would advice to just drop/re-create the table, and re-insert the data. If you can't you can try repairing the table in place with REPAIR table. It is a generally lengthy process which might very well fail.

在第二种情况下，MySQL 的数据存在实际问题。如果您可以轻松地重新插入数据，我建议您只需删除/重新创建表，然后重新插入数据。如果你不能，你可以尝试用REPAIR table 修理桌子。这是一个通常很长的过程，很可能会失败。

---

---

Look at the complete error messageyou get:

查看您收到的完整错误消息：

Incorrect key file for table 'FILEPATH.MYI'; try to repair it

表 'FILEPATH.MYI' 的密钥文件不正确；尝试修复它

It mentions in the message that you can try to repair it. Also, if you look at the actual FILEPATH you get, you can find out more:

它在消息中提到您可以尝试修复它。此外，如果您查看实际获得的 FILEPATH，您可以找到更多信息：

• if it is something like /tmp/#sql_ab34_23fit means that MySQL needs to create a temporary table because of the query size. It stores it in /tmp, and that there is not enough space in your /tmp for that temporary table.

• if it contains the name of an actual table instead, it means that this table is very likely corrupted and you should repair it.

• 如果它是类似的，/tmp/#sql_ab34_23f则意味着由于查询大小，MySQL 需要创建一个临时表。它将它存储在 /tmp 中，并且 /tmp 中没有足够的空间用于该临时表。

• 如果它包含实际表的名称，则表示该表很可能已损坏，您应该修复它。

---

---

If you identify that your issue is with the size of /tmp, just read this answer to a similar question for the fix: MySQL, Error 126: Incorrect key file for table.

如果您确定您的问题与 /tmp 的大小有关，请阅读此修复类似问题的答案：MySQL, Error 126: Incorrect key file for table。

回答by user387049

Following these instructions allowed me to recreate my tmp directory and fix the issue:

按照这些说明，我可以重新创建我的 tmp 目录并解决问题：

Display all file systems and their disk usage in human readable form:

以人类可读的形式显示所有文件系统及其磁盘使用情况：

df -h

Find the processes that have files open in /tmp

找到打开文件的进程 /tmp

sudo lsof /tmp/**/*

Then umount /tmpand /var/tmp:

然后卸载/tmp和/var/tmp：

umount -l /tmp
umount -l /var/tmp

Then remove the corrupt partition file:

然后删除损坏的分区文件：

rm -fv /usr/tmpDSK

Then create a nice new one:

然后创建一个不错的新的：

/scripts/securetmp

Note that by editing the securetmp Perl script you can manually set the size of the tmp directory yourself, however just running the script increased the size of the tmp directory on our server from roughly 450MB to 4.0GB.

请注意，通过编辑 securetmp Perl 脚本，您可以自己手动设置 tmp 目录的大小，但是仅运行该脚本会将我们服务器上 tmp 目录的大小从大约 450MB 增加到 4.0GB。

回答by junmats

Error #126 usually occurs when you got a corrupt table. The best way to solve this is to perform repair. This article might help:

错误 #126 通常发生在表损坏时。解决此问题的最佳方法是执行修复。这篇文章可能会有所帮助：

http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

回答by jcampbell1

I got this error when I set ft_min_word_len = 2in my.cnf, which lowers the minimum word length in a full text index to 2, from the default of 4.

设置ft_min_word_len = 2in时出现此错误my.cnf，它将全文索引中的最小字长从默认值 4 降低到 2。

Repairing the table fixed the problem.

修理桌子解决了这个问题。

回答by Hyder B.

I know that this is an old topic but none of the solution mentioned worked for me. I have done something else that worked:

我知道这是一个老话题，但提到的解决方案都不适合我。我做了一些其他有效的事情：

You need to:

你需要：

1. stop the MySQL service:
2. Open mysql\data
3. Remove both ib_logfile0 and ib_logfile1.
4. Restart the service

1. 停止 MySQL 服务：
2. 打开 mysql\data
3. 删除 ib_logfile0 和 ib_logfile1。
4. 重启服务

回答by ThreaT

repair table myschema.mytable;

回答by Sean

I fixed this issue with:

我用以下方法解决了这个问题：

ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;

May helps

可能有帮助

• Refs: MySQL: ALTER IGNORE TABLE gives "Integrity constraint violation"

• 参考：MySQL：ALTER IGNORE TABLE 给出“违反完整性约束”

回答by BenD

Go to /etc/my.cnfand comment out tmpfs

转到/etc/my.cnf并注释掉tmpfs

#tmpdir=/var/tmpfs

This fixes the problem.

这解决了问题。

I ran the command suggested in another answer and while the directory is small, it was empty, so space was not the issue.

我运行了另一个答案中建议的命令，虽然目录很小，但它是空的，所以空间不是问题。

/var/tmp$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/vzfs              60G   51G  9.5G  85% /
none                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 200M     0  200M   0% /var/tmpfs
/var/tmpfs$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/vzfs              60G   51G  9.5G  85% /
none                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 200M     0  200M   0% /var/tmpfs

回答by Bajrang

Try to use limit in your query. It's because of full disk as said by @Monsters X.

尝试在您的查询中使用限制。这是因为@Monsters X 所说的磁盘已满。

I have also faced this problem and solved by limit in query, because the thousands of records were there. Now working good :)

我也遇到过这个问题并通过查询限制解决了，因为那里有数千条记录。现在工作良好:)