Incorrect key file with MySQL

mysql innodb mysql-error-126

11,686

Solution 1

What's going on here is MySQL is doing the ORDER BY by building a temporary table from the join of the two tables. The temporary table is too large to fit into memory so MySQL creates a temporary file.

There are a few thing that would prevent this from working correctly. Raw disk space is one. ulimit is another. If this is being hosted, they may have a quota on your disk usage (in addition to ulimit).

I would suggest adding a limiting clause to your query. Currently you load the entire of both the rss_posts and rss_feeds into the temporary table for sorting. If you only want the most recent 10 that's a lot more data than you really need.

SELECT posts.id, posts.post_title 
FROM rss_posts AS posts INNER JOIN rss_feeds AS feeds ON posts.blog_id=feeds.id 
WHERE feeds.blog_language=1 
AND posts.post_data_db > (now - interval 30 day);
ORDER BY posts.post_date_db DESC LIMIT 10;

Solution 2

It sure looks like the disk quota you have for temporary tables is too small.

BTW: There's no need to run REPAIR on InnoDB tables, since all the maintenance is done by the storage engine itself. They also do not have a key file to be corrupted.

Solution 3

Notice that the .MYI file its having a problem with is for a temp table. When you're running queries involving joins, MySql needs to use temp space to merge the data internally. You are most likely running out of space in your tmp directory.

Try increasing the amount of space allocated to tmpdir or edit the my.cnf file to have tmpdir point to a place with enough space (don't forget to give it permissions).

11,686

Brett

Updated on May 05, 2022

Comments

Brett almost 2 years
I'm having a problem with a InnoDB (table was initally MyISAM, but converted it to InndoB awhile ago) table; I am trying to run this query:
```
SELECT 
   posts.id,
   posts.post_title
FROM
   rss_posts AS posts
   INNER JOIN rss_feeds AS feeds ON posts.blog_id=feeds.id
WHERE
   feeds.blog_language=1
ORDER BY
   posts.post_date_db DESC
LIMIT
   10;
```
I get this error:
```
Query : SELECT   posts.id,posts.post_title  FROM   rss_posts AS posts   INNER JOIN vw_rss_feeds AS feeds ON posts.blog_id=feeds.id  WHER...
Error Code : 126
Incorrect key file for table '/tmp/#sql_7375_0.MYI'; try to repair it
```
I cannot run a repair on the tables involved; however I have ran a CHECK on both tables & they appear fine. I have also done an OPTIMIZE on both tables & ALSO rebuilt the tables by doing the below..
```
INSERT INTO new_table SELECT * FROM old_table;
```
I then renamed the new table to the old table name..... but I am STILL having that problem.

To try & figure out what table was causing it I removed the code in the query referencing the "rss_feeds" table.... so now the query looks like this..
```
SELECT 
   posts.id,
   posts.post_title
FROM
   rss_posts AS posts
ORDER BY
   posts.post_date_db DESC
LIMIT
   10;
```
That worked.

So the problem is something with the rss_feeds table.

So then I figured I would convert the table back to MyISAM & run a repair & then convert back to InnoDB..... this worked temporarily, it was back to normal.... then it broke again..... repaired it again, broke again.... now the repair doesn't seem to work at all.

Now, I know, I know...... I have searched for this problem on Google already...... I noticed that the MAJORITY of the time the problem is there us not enough space in the MySQL temp directory.... but I already got the host to change the temp dir to something with a LOT more space & the problem still remains.

I'm thinking the HOST is to blame & it STILL is a problem with the temp dir; why? Because after I got it working again I started adding data to the rss_posts table again & hence the JOIN would get LARGER & MySQL would again run out of space.... what do you think?
Brett over 13 years

Thank you - there definitely is enough disk space available; basically the host said this.. "The '/var/tmp' partition uses the same space as '/', and its usage information is currently as follows; /dev/sda3 442G 130G 289G 32% / So you should not be seeing any issues with the space." Though I'm wondering if perhaps the /var/tmp/ dir is restricted itself? If that's even possible. So that query you have there - that basically only gets the last 10 posts from the last 30 days?
Thomas Jones-Low over 13 years

Correct. You can parametrize the "30" to make that a little broader. But regardless of disk space available if you are constantly sorting the whole of two tables for 10 rows, you are going to run into scaling and performance issues.
Brett over 13 years

Ok..... well I have changed the query to what you suggested (even tried 1 day) but the error remains... it actually did work for a little while, but then the error came back. :(
Thomas Jones-Low over 13 years

How many rows go into the RSS_POSTS table every day? What is the data type for the rss_posts.post_date_db column? I may have gotten my date arithmetic wrong and need a better value. Read up on the ulimit command which can impose limits disk space used per process or per user and specifically ask your host about the results.
Brett over 13 years

At the moment about 500k... but that number should increase significantly later on. The rss_posts.post_date_db column is a bigint. I couldn't find much good info on the ulimit before when I did a search..... I'm assuming it's a server thing & not MySQL correct?
Thomas Jones-Low over 13 years

Correct. ulimit is a unix/linuc command to limit various resources on the machine, including memory allocation, disk space use and so on. To prevent DOS attacks by having one process eat all disk space or memory or some such thing
anonymous-one almost 11 years

quick note, in our case we were experiencing this... it was a simple case of our mysqltmp location being too small.