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

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

When I run

repair table mytablename

I get this message:

The storage engine for the table doesn't support repair

Optimize won't work. What to do?

Answer1:

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

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.

now the query runs succesfull

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Incorrect key file for table MYSQL