Incorrect key file problem with MySQL - InnoDB Table

Question

I REALLY hope somebody can help me with this as it's been hard to get help for it elsewhere, including mysql.com

Anyway... 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.

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

Any help would be MUCH appreciated!

Thanks very much!

Answer 1

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.

Answer 2

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;