29

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
Community
  • 1
  • 1
superhero
  • 6,281
  • 11
  • 59
  • 91
  • 2
    what about the disk space issue? – Sebas Sep 25 '13 at 11:02
  • 500GB hd space, no blobs in db, I don't see how this could be the issue. It really isn't that big av an database for the moment. The project is just in testing so we have a lot more resources then currently needed. Although, I will look it over and update with the information – superhero Sep 25 '13 at 11:07
  • @Sebas Though I was wrong about the current disk size we should still have an ok margin I believe.. – superhero Sep 25 '13 at 11:19

5 Answers5

41

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;
Cillier
  • 1,021
  • 9
  • 8
  • 2
    It should also be mentioned that the query in this case should be formatted in a different way to be able to avoid this issue. It was a lot faster to use multiple queries in this case. – superhero Oct 14 '13 at 11:50
  • This pointed me in the right direction. In my case, I had recently run out of space on my root partition which generated an overflow mount to protect `/tmp`. When i ran out of room, I resized the volume. A few months later, I ran into this error and it was because the overflow mount that I hadn't removed was too small. Thanks for this! – Carrie Kendall May 03 '17 at 17:41
  • Why are there so many answers to this type of question that say "make more tmp space", and none that say "upgrade to mysql version x, where they fixed the product to give an explicit error when temporary space runs out"? Shouldn't mysql just fix the error message to make it more obvious to EVERYONE what the problem is? There are dozens of questions about this error across stack overflow, super user and the dba site. Wouldn't it be nice if they just made a better error message? – Sinc Apr 24 '23 at 18:31
4

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.

Carrie Kendall
  • 11,124
  • 5
  • 61
  • 81
1

Splitting a complex query into multiple ones would be faster without needing to increase the temp table size

John
  • 541
  • 3
  • 6
  • 19
1

In my case I just cleared temp files form temp location:

my.ini

tmpdir = "D:/xampp/tmp"

And it worked for me.

SA Soibal
  • 63
  • 1
  • 8
-8

You just need to repair your table which use in search query. this problem generally occur on search query.

go to "table_name" -> operation- > repair (just one click) effect may take some time to apply

Kaushal Sachan
  • 1,175
  • 11
  • 8
  • He didn't mention using any gui for mysql and you describe steps to be performed on some gui obviously (since you say it's "just one click"). – Dimitris Zorbas Feb 09 '15 at 15:43