进行多个连接时，tmp 表的 MySQL 密钥文件不正确

MySQL incorrect key file for tmp table when making multiple joins

提问by Paolo Bergantino

I don't come here for help often but I am pretty frustrated by this and I am hoping someone has encountered it before.

我不经常来这里寻求帮助，但我对此感到非常沮丧，我希望有人以前遇到过。

Whenever I try to fetch records from a table using more than one join I get this error:

每当我尝试使用多个连接从表中获取记录时，我都会收到此错误：

#126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it

So this query will produce the error:

所以这个查询会产生错误：

SELECT * FROM `core_username`
INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
ORDER BY `core_username`.`name` ASC LIMIT 1

But this one won't:

但这个不会：

SELECT * FROM `core_username`
INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
ORDER BY `core_username`.`name` ASC LIMIT 1

And neither will this one:

这个也不会：

SELECT * FROM `core_username`
INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
ORDER BY `core_username`.`name` ASC LIMIT 1

What could be causing this? I don't really know how to go about repairing a tmp table but I don't really think that's the problem as it is a new tmp table every time. The username table is fairly large (233,718 records right now) but I doubt that has anything to do with it.

什么可能导致这种情况？我真的不知道如何修复 tmp 表，但我真的不认为这是问题，因为它每次都是一个新的 tmp 表。用户名表相当大（现在有 233,718 条记录），但我怀疑这与它有什么关系。

Any help would be much appreciated.

任何帮助将非常感激。

UPDATE: After some further testing, it appears that the error only happens when I try to order the results. That is, this query will give me what I expect:

更新：经过一些进一步的测试，似乎只有当我尝试对结果进行排序时才会发生错误。也就是说，这个查询会给我我所期望的：

SELECT * FROM `core_username`
INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
LIMIT 1

But if I add the:

但如果我添加：

ORDER BY `core_username`.`name` ASC

The error is triggered. This is only happening on the specific webserver I am currently using. If I download the database and try the same thing on my localhost as well as other servers it runs fine. The MySQL version is 5.0.77.

错误被触发。这仅发生在我当前使用的特定网络服务器上。如果我下载数据库并在我的本地主机以及其他服务器上尝试相同的操作，它运行良好。MySQL 版本是 5.0.77。

Knowing this I am fairly confident that what is happening is that the tmp table being created is way too big and MySQL chokes as described in this blog post. I am still not sure what the solution would be, though...

知道这一点后，我相当有信心正在发生的事情是正在创建的 tmp 表太大，并且 MySQL 阻塞，如本博客文章中所述。我仍然不确定解决方案是什么，但......

回答by codeulike

Sometimes when this error happens with temp tables:

有时，当临时表发生此错误时：

#126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it

It can be because the /tmpfolder is running out of space. On some Linux installations, /tmpis in its own partition and does not have much space - big MySQL queries will fill it up.

可能是因为/tmp文件夹空间不足。在某些 Linux 安装中，/tmp它位于自己的分区中并且没有太多空间 - 大型 MySQL 查询将填满它。

You can use df -hto check whether \tmpis in its own partition, and how much space is allocated to it.

您可以使用它df -h来检查是否\tmp在它自己的分区中，以及分配了多少空间给它。

If it is in its own partition and short of space, you can either:

如果它在自己的分区中并且空间不足，您可以：

(a) modify /tmp so that its parition has more space (either by reallocating or moving it to the main partition - e.g. see here)
(b) changing MySql config so that it uses a different temp folderon a different partition, e.g. /var/tmp

(a) 修改 /tmp 使其分区有更多空间（通过重新分配或将其移动到主分区 - 例如见这里）
(b) 更改 MySql 配置，以便它在不同的分区上使用不同的临时文件夹，例如/var/tmp

回答by Francesc Rosàs

Check your MySQL tmpdir available space (/tmp in your case) while running the queries as it can eat hundreds of MBs when working with big tables. Something like this worked for me:

在运行查询时检查您的 MySQL tmpdir 可用空间（在您的情况下为 /tmp），因为在处理大表时它可能会消耗数百 MB。这样的事情对我有用：

$ while true; do df -h /tmp; sleep .5; done

回答by Pentium10

run this

运行这个

REPAIR TABLE `core_username`,`core_site`,`core_person`;

or do this:

或这样做：

select * from (
 SELECT * FROM `core_username`
 INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
 INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
 LIMIT 1)
ORDER BY `name` ASC

回答by Roger Lucas

You may find running "ANALYZE TABLE " helps.

您可能会发现运行“ANALYZE TABLE”有帮助。

We had this problem suddenly appear on a large table (~100M rows) and MySQL tried to use /tmp to write a temporary table of over 1GB, which failed as /tmp was limited to ~600M.

我们在一个大表（~100M 行）上突然出现了这个问题，MySQL 尝试使用 /tmp 写一个超过 1GB 的临时表，但由于 /tmp 被限制在~600M 而失败。

It turned out that the statistics for the InnoDB table were rather stale. After running "ANALYZE TABLE ...", the statistics were updated and the problem cleared. With the more accurate statistics, MySQL was able to optimize the query correctly and the large tmp file was no longer required.

结果证明 InnoDB 表的统计数据相当陈旧。运行“ANALYZE TABLE ...”后，更新了统计信息并清除了问题。有了更准确的统计信息，MySQL 能够正确优化查询，不再需要大的 tmp 文件。

We now run "mysqlcheck -Aa" periodically to keep all table statistics fresh.

我们现在定期运行“mysqlcheck -Aa”以保持所有表统计信息新鲜。

回答by ClickWhisperer

I had this problem with a query on a table that had 500K+ records. It was giving me the same exact type of error, pointing to a .MYI file in the /tmp directory that was rarely there upon checking. I had already increased the heap and temp file sizes in the /etc/my.cnf file.

我在对具有 500K+ 记录的表进行查询时遇到了这个问题。它给了我相同类型的错误，指向 /tmp 目录中的一个 .MYI 文件，该文件在检查时很少出现。我已经在 /etc/my.cnf 文件中增加了堆和临时文件的大小。

The problem with the query was the it did indeed contain a ORDER clause at the end, omitting it made the query work without error. It also had a LIMIT. I was trying to look at the most recent 5 records in the table. With the ORDER clause included it choked and gave the error.

查询的问题是它确实在末尾包含一个 ORDER 子句，省略它使查询工作没有错误。它也有一个限制。我试图查看表中最近的 5 条记录。包含 ORDER 子句后，它会窒息并给出错误。

What was happening was the mysqld was creating an internal temp table with ALL the records from the giant table to apply the ORDER.

发生的事情是 mysqld 正在创建一个内部临时表，其中包含来自巨型表的所有记录以应用 ORDER。

The way that I got around this is to apply an additional WHERE condition, limiting the records from the giant table to some smaller set. I conveniently had a datetime field to do the filtering from.

我解决这个问题的方法是应用一个额外的 WHERE 条件，将大表中的记录限制在一些较小的集合中。我很方便地有一个日期时间字段来进行过滤。

I hope that helps someone.

我希望能帮助某人。

回答by Jaydeep Dave

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

在 Unix 上，MySQL 使用 TMPDIR 环境变量的值作为存储临时文件的目录的路径名。如果未设置 TMPDIR，MySQL 使用系统默认值，通常是 /tmp、/var/tmp 或 /usr/tmp。

On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp.

在 Windows、Netware 和 OS2 上，MySQL 按顺序检查 TMPDIR、TEMP 和 TMP 环境变量的值。对于发现的第一个设置，MySQL 使用它并且不检查剩余的那些。如果 TMPDIR、TEMP 或 TMP 均未设置，则 MySQL 使用 Windows 系统默认值，通常为 C:\windows\temp。

If the file system containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a file system where you have enough space.

如果包含临时文件目录的文件系统太小，可以使用 mysqld 的 --tmpdir 选项指定文件系统中具有足够空间的目录。

In MySQL 5.0, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (“:”) on Unix and semicolon characters (“;”) on Windows, NetWare, and OS/2.

在 MySQL 5.0 中，--tmpdir 选项可以设置为以循环方式使用的多个路径的列表。在 Unix 上，路径应该用冒号字符 (“:”) 分隔，在 Windows、NetWare 和 OS/2 上用分号字符 (“;”) 分隔。

回答by Ahmad

I experience the same problem.

我遇到了同样的问题。

Here is my solution : 1. Dont use "select *". Just select field that you need. 2. Split the query. If the field you select is too much, splitting it to some query can be a result. You can "array_merge()" the result later if you want the variable that contain the result not changed.

这是我的解决方案： 1. 不要使用“select *”。只需选择您需要的字段。2. 拆分查询。如果您选择的字段太多，将其拆分为某个查询可能是一个结果。如果您希望包含结果的变量不被更改，您可以稍后“array_merge()”结果。

On my case, I split the query to 5 queries, then array merge it using PHP.

就我而言，我将查询拆分为 5 个查询，然后使用 PHP 进行数组合并。

The problem is lies on the mysql server. It is just a thing that application developer (such us me) don't has a previlege.

问题出在mysql服务器上。这只是应用程序开发人员（例如我们我）没有特权的事情。

回答by Lateef

I had similar problem. In my own case, the problem occurred due to incorrect owner/permission. I just had to change the owner on my data directory to mysql user and this resolved the problem.

我有类似的问题。在我自己的情况下，问题是由于所有者/权限不正确而发生的。我只需要将我的数据目录中的所有者更改为 mysql 用户，这解决了问题。

回答by DarckBlezzer

Only increase the file tmp, because mysql doesn't have space in it, for queries...

只增加文件tmp，因为mysql里面没有空间，查询...

mount -o remount,size=[NEW MAX SIZE HERE] tmpfs /tmp

Links reference:

链接参考：

General error: 126 Incorrect key file for table ‘/tmp/#sql_254c_0.MYI'; try to repair it

一般错误：126 表 '/tmp/#sql_254c_0.MYI' 的密钥文件不正确；尝试修复它

[ERROR] /usr/sbin/mysqld: Incorrect key file for table '/mysqltmp/#sql_ca1a_0.MYI'; try to repair it

[错误] /usr/sbin/mysqld: 表 '/mysqltmp/#sql_ca1a_0.MYI' 的密钥文件不正确；尝试修复它

How to increase /tmp partition size

如何增加 /tmp 分区大小

回答by Jon

Using the EXPLAIN keyword may help find out how to best optimize this query. Essentially, what you need to do is get the result set as small as possible as quickly as possible. If you have a result set of every row in core_username until the end, when you order it you run the risk of... this.

使用 EXPLAIN 关键字可能有助于找出如何最好地优化此查询。本质上，您需要做的是尽快获得尽可能小的结果集。如果您在 core_username 中的每一行都有一个结果集，直到最后，当您订购它时，您将面临...的风险。

If you can do the ordering on core_username alone without a problem, you may want to get the min() row as a subquery.

如果您可以单独对 core_username 进行排序而没有问题，您可能希望将 min() 行作为子查询。