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.
错误被触发。这只发生在我正在使用的特定的webserver上。如果我下载了数据库，并在本地主机和其他服务器上尝试相同的操作，就可以运行良好。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 chokes在这篇博文中描述了。我仍然不确定解决方案是什么，尽管……
11 个解决方案
#1
93
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 /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.
这可能是因为/tmp文件夹没有空间了。在一些Linux安装上，/tmp在它自己的分区中，并且没有太多的空间——大的MySQL查询将会填充它。
You can use df -h to check whether \tmp is 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 folder on a different partition, e.g. /var/tmp
(a)修改/tmp，使其具有更大的空间(可以通过重新分配或将其移动到主分区，例如此处查看)(b)更改MySql配置，以便在不同分区上使用不同的临时文件夹，例如/var/tmp。
#2
20
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
#3
5
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
#4
3
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正在创建一个内部临时表，其中包含来自巨大表的所有记录来应用订单。
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.
我绕过这个的方法是在一个条件下应用一个附加的条件，将这个巨大的表的记录限制到一个更小的集合中。
I hope that helps someone.
我希望能帮助别人。
#5
3
You may find running "ANALYZE TABLE " helps.
您可能会发现运行“分析表”有帮助。
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表的统计数据相当陈旧。运行“分析表…”，统计数据被更新，问题得到解决。使用更精确的统计数据，MySQL能够正确地优化查询，并且不再需要大型的tmp文件。
We now run "mysqlcheck -Aa" periodically to keep all table statistics fresh.
我们现在定期运行“mysqlcheck -Aa”，以保持所有表统计信息的新鲜。
#6
2
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.
如果包含临时文件目录的文件系统太小，您可以使用-tmpdir选项到mysqld来指定文件系统中有足够空间的目录。
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选项可以设置为几种循环使用的路径的列表。在Windows、NetWare和OS/2上，在Unix和分号上的冒号(“:”)应该分隔路径。
#7
1
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服务器上。这只是应用程序开发人员(比如我)没有特权的事情。
#8
0
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用户，这就解决了问题。
#9
0
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
一般错误:表“/tmp/#sql_254c_0.MYI”中有126个不正确的密钥文件;尝试修复它
[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分区大小?
#10
-1
the index keys for one of the 3 tables might be bad, try running a repair command on all 3 tables.
其中一个表的索引键可能很糟糕，尝试在所有3个表上运行修复命令。
#11
-1
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()行作为子查询。
#1
93
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 /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.
这可能是因为/tmp文件夹没有空间了。在一些Linux安装上，/tmp在它自己的分区中，并且没有太多的空间——大的MySQL查询将会填充它。
You can use df -h to check whether \tmp is 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 folder on a different partition, e.g. /var/tmp
(a)修改/tmp，使其具有更大的空间(可以通过重新分配或将其移动到主分区，例如此处查看)(b)更改MySql配置，以便在不同分区上使用不同的临时文件夹，例如/var/tmp。
#2
20
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
#3
5
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
#4
3
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正在创建一个内部临时表，其中包含来自巨大表的所有记录来应用订单。
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.
我绕过这个的方法是在一个条件下应用一个附加的条件，将这个巨大的表的记录限制到一个更小的集合中。
I hope that helps someone.
我希望能帮助别人。
#5
3
You may find running "ANALYZE TABLE " helps.
您可能会发现运行“分析表”有帮助。
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表的统计数据相当陈旧。运行“分析表…”，统计数据被更新，问题得到解决。使用更精确的统计数据，MySQL能够正确地优化查询，并且不再需要大型的tmp文件。
We now run "mysqlcheck -Aa" periodically to keep all table statistics fresh.
我们现在定期运行“mysqlcheck -Aa”，以保持所有表统计信息的新鲜。
#6
2
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.
如果包含临时文件目录的文件系统太小，您可以使用-tmpdir选项到mysqld来指定文件系统中有足够空间的目录。
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选项可以设置为几种循环使用的路径的列表。在Windows、NetWare和OS/2上，在Unix和分号上的冒号(“:”)应该分隔路径。
#7
1
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服务器上。这只是应用程序开发人员(比如我)没有特权的事情。
#8
0
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用户，这就解决了问题。
#9
0
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
一般错误:表“/tmp/#sql_254c_0.MYI”中有126个不正确的密钥文件;尝试修复它
[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分区大小?
#10
-1
the index keys for one of the 3 tables might be bad, try running a repair command on all 3 tables.
其中一个表的索引键可能很糟糕，尝试在所有3个表上运行修复命令。
#11
-1
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()行作为子查询。