mysql错误+126,MySQL，错误126：表的密钥文件不正确

I read the following question that has relevance, but the replies didn't satify me: MySQL: #126 - Incorrect key file for table

The problem

When running a query I get this error

ERROR 126 (HY000): Incorrect key file for table`

The question

When I'm trying to find the problem I cant't find one, so I don't know how to fix it with the repair command.

Is there any pointers to how I can find the problem causing this issue in any other way then I already have tried?

The query

mysql> SELECT

-> Process.processId,

-> Domain.id AS domainId,

-> Domain.host,

-> Process.started,

-> COUNT(DISTINCT Joppli.id) AS countedObjects,

-> COUNT(DISTINCT Page.id) AS countedPages,

-> COUNT(DISTINCT Rule.id) AS countedRules

-> FROM Domain

-> JOIN CustomScrapingRule

-> AS Rule

-> ON Rule.Domain_id = Domain.id

-> LEFT JOIN StructuredData_Joppli

-> AS Joppli

-> ON Joppli.CustomScrapingRule_id = Rule.id

-> LEFT JOIN Domain_Page

-> AS Page

-> ON Page.Domain_id = Domain.id

-> LEFT JOIN Domain_Process

-> AS Process

-> ON Process.Domain_id = Domain.id

-> WHERE Rule.CustomScrapingRule_id IS NULL

-> GROUP BY Domain.id

-> ORDER BY Domain.host;

ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_2b5_4.MYI'; try to repair it

mysqlcheck

root@scraper:~# mysqlcheck -p scraper

Enter password:

scraper.CustomScrapingRule OK

scraper.Domain OK

scraper.Domain_Page OK

scraper.Domain_Page_Rank OK

scraper.Domain_Process OK

scraper.Log OK

scraper.StructuredData_Joppli OK

scraper.StructuredData_Joppli_Product OK

counted rows

mysql> select count(*) from CustomScrapingRule;

+----------+

| count(*) |

+----------+

| 26 |

+----------+

1 row in set (0.04 sec)

mysql> select count(*) from Domain;

+----------+

| count(*) |

+----------+

| 2 |

+----------+

1 row in set (0.01 sec)

mysql> select count(*) from Domain_Page;

+----------+

| count(*) |

+----------+

| 134288 |

+----------+

1 row in set (0.17 sec)

mysql> select count(*) from Domain_Page_Rank;

+----------+

| count(*) |

+----------+

| 4671111 |

+----------+

1 row in set (11.69 sec)

mysql> select count(*) from Domain_Process;

+----------+

| count(*) |

+----------+

| 2 |

+----------+

1 row in set (0.02 sec)

mysql> select count(*) from Log;

+----------+

| count(*) |

+----------+

| 41 |

+----------+

1 row in set (0.00 sec)

mysql> select count(*) from StructuredData_Joppli;

+----------+

| count(*) |

+----------+

| 11433 |

+----------+

1 row in set (0.16 sec)

mysql> select count(*) from StructuredData_Joppli_Product;

+----------+

| count(*) |

+----------+

| 130784 |

+----------+

1 row in set (0.20 sec)

Update

Disk usage

root@scraper:/tmp# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/xvda1 20G 4.7G 15G 26% /

none 4.0K 0 4.0K 0% /sys/fs/cgroup

udev 237M 4.0K 237M 1% /dev

tmpfs 49M 188K 49M 1% /run

none 5.0M 0 5.0M 0% /run/lock

none 245M 0 245M 0% /run/shm

none 100M 0 100M 0% /run/user

解决方案

It appears that your query is returning a large intermediate result set requiring the

creation of a temporary table and that the configured location for mysql temporary

disk tables (/tmp) is not large enough for the resulting temporary table.

You could try increasing the tmpfs partition size by remounting it:

mount -t tmpfs -o remount,size=1G tmpfs /tmp

You can make this change permanent by editing /etc/fstab

If you are unable to do this you could try changing the location of disk

temporary tables by editing the "tmpdir" entry in your my.cnf file (or add

it if it is not already there). Remember that the directory you

choose should be writable by the mysql user

You could also try preventing the creation of an on disk temporary table by increasing

the values for the mysql configuration options:

tmp_table_size

max_heap_table_size

to larger values. You will need to increase both of the above parameters

Example:

set global tmp_table_size = 1G;

set global max_heap_table_size = 1G;