Invalid key file with MySQL

Question

I have a problem with InnoDB (the table was initialized by MyISAM, but converted it to InndoB some time 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 can not start the repair in the corresponding tables; however, I checked CHECK on both tables and they look great. I also did OPTIMIZE for both tables and ALSO rebuilt the tables by doing below.

 INSERT INTO new_table SELECT * FROM old_table; 

Then I renamed the new table to the name of the old table ..... but I still have this problem.

To try to figure out which table calls it, I deleted the code in the query that references 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; 

It worked.

So the problem is with the rss_feeds table.

So, I decided that I would return the table back to MyISAM and start the repair, and then convert it back to InnoDB ..... it worked temporarily, it returned to normal ... then it broke again ..... repaired it again, again broke .... now the repair does not seem to work at all.

Now, I know, I know ... I already looked for this problem on Google ...... I noticed that the BASIS of the time when the problem is that we are running out of space in the MySQL temp directory .... but I already have a host to change temp dir to something with LOT more space, and the problem still remains.

I think HOST is to blame, and this is STILL - a problem with temp dir; What for? Since after I started working again, I started adding data to the rss_posts table again, and therefore JOIN will get LARGER, and MySQL will finish the space again ... what do you think?

Answer 1

What happens here, MySQL executes ORDER BY, creating a temporary table from the union of the two tables. The temporary table is too large to fit in memory, so MySQL creates a temporary file.

There are several things that would prevent this from working correctly. The raw disk space is one. ulimit is another. If this is a placement, they may have a quota for the use of your disk (in addition to ulimit).

I would suggest adding a sentence restriction to your request. You are currently loading all rss_posts and rss_feeds into a temporary table for sorting. If you need only the very last 10, you have much 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; 

Answer 2

It seems that the disk quota for temporary tables is too low.

BTW: There is no need to run REPAIR on InnoDB tables, since all maintenance is performed by the storage engine itself. They also do not have a key file that will be damaged.

Answer 3

Please note that the .MYI file with which the problem is related refers to the temporary table. When you execute queries related to joins, MySql must use a temporary space to merge the data internally. Most likely you are losing your place in your tmp directory.

Try increasing the amount of space allocated for tmpdir, or edit the my.cnf file so that tmpdir points to a place with enough space (remember to give it permission).