表MYSQL的密钥文件不正确 [英] Incorrect key file for table MYSQL

问题描述

当我想按查询分组时，出现以下错误消息:

When i want to GROUP BY a query i get this error message:

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

这是一个具有3.000.000条记录和4,3 GiB大的InnoDB表.

It is an InnoDB table with 3.000.000 records and 4,3 GiB large.

我跑步时

repair table mytablename

我收到此消息:

The storage engine for the table doesn't support repair

优化将不起作用.该怎么办?

Optimize won't work. What to do?

推荐答案

问题不是表已损坏.当您进行分组时，mysql将创建一个临时表.由于该表太大，因此临时表占用了超过10gb的磁盘空间.当磁盘100％充满时，将出现错误消息.

The problem was not that the table was corrupt. When you do a group by, mysql creates a temporary table. Because the table is so large, the temporary table takes more than 10gb of diskspace. The error message appears when the disk got 100% full.

我用突击队检查了磁盘空间:

I used the commando to check the diskspace:

df -h /dev/root

因此，在MySQL配置(my.ini)中，我将临时表的存储位置(tmpdir)更改为具有足够可用磁盘空间的分区.

So in the MySQL config (my.ini) I changed the location (tmpdir) where the temporary tables are stored to a partition with enough free diskspace.

现在查询运行成功

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