进行多个联接时,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: 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
错误被触发.这仅在我当前正在使用的特定Web服务器上发生.如果我下载数据库并在本地主机和其他服务器上尝试相同的操作,它将运行良好. 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,使其分区具有更大的空间(通过重新分配或将其移动到主分区中,例如不同的临时文件夹在另一个分区上,例如/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屋!