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