进行多个连接时，tmp 表的 MySQL 密钥文件不正确 [英] MySQL incorrect key file for tmp table when making multiple joins

问题描述

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

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

但这个不会:

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

这个也不会:

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

可能是什么原因造成的?我真的不知道如何修复 tmp 表，但我真的不认为这是问题所在，因为它每次都是一个新的 tmp 表.用户名表相当大(现在有 233,718 条记录)，但我怀疑这与它有什么关系.

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.

任何帮助将不胜感激.

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

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

但是如果我添加:

ORDER BY `core_username`.`name` ASC

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

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.

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

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...

推荐答案

有时当这个错误发生在临时表上时:

Sometimes when this error happens with temp tables:

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

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

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

可以使用df -h查看\tmp是否在自己的分区内，以及分配了多少空间.

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

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

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

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

(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 folder on a different partition, e.g. /var/tmp

这篇关于进行多个连接时，tmp 表的 MySQL 密钥文件不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！