MySQL, Error 126: Incorrect key file for table
Solution 1
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;
Solution 2
If your /tmp
mount on a linux filesystem is mounted as overflow, often sized at 1MB, ie
$ df -h
Filesystem Size Used Avail Use% Mounted on
udev 7.9G 12K 7.9G 1% /dev
tmpfs 1.6G 348K 1.6G 1% /run
/dev/xvda1 493G 6.9G 466G 2% /
none 4.0K 0 4.0K 0% /sys/fs/cgroup
none 5.0M 0 5.0M 0% /run/lock
none 7.9G 0 7.9G 0% /run/shm
none 100M 0 100M 0% /run/user
overflow 1.0M 4.0K 1020K 1% /tmp <------
this is likely due to you not specifying /tmp
as its own partition and your root filesystem filled up and /tmp
was remounted as a fallback.
I ran into this issue after running out of space on an EC2 volume. Once I resized the volume, I ran into the /tmp
overflow partition filling up while executing a complicated view.
To fix this after you've cleared space/resized, just unmount the fallback and it should remount at its original point (generally your root partition):
sudo umount -l /tmp
Note: -l
will lazily unmount the disk.
Solution 3
In my case I just cleared temp files form temp location:
my.ini
tmpdir = "D:/xampp/tmp"
And it worked for me.
Solution 4
Splitting a complex query into multiple ones would be faster without needing to increase the temp table size
superhero
oɹǝɥɹǝdns ǝɥʇ - ןןɐʌpuɐן ʞıɹǝ npm &Star; github &Star; linkedin &Star; twitter
Updated on November 10, 2021Comments
-
superhero over 2 years
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