tags:

views:

51

answers:

2

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!

A: 

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.

Mchl
Great - thanks for that useful info! :)
Brett
A: 

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;
Thomas Jones-Low
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?
Brett
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.
Thomas Jones-Low
Yep... you make a good point. Thanks very much!
Brett
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. :(
Brett
No more suggestions? :(
Brett
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.
Thomas Jones-Low
Brett
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
Thomas Jones-Low
Ok thank you!!!
Brett