• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1769
  • Last Modified:

Incorrect key file for table '/tmp/#sql_6ae0_0.MYI'; try to repair it

I created an HEAP table and a traditional MyISAM table with the same fields on both of them.
One software writes into the HEAP table, then periodically transfers its content to the MyISAM table.

It works pretty good for a while then I get one error while trying to do a select over both of them using an sql view.

The error is: Incorrect key file for table '/tmp/#sql_6ae0_0.MYI'; try to repair it

To fix I have to drop the table and create it again.

Any idea?

Mysql 5 on Linux.
0
phoenix128
Asked:
phoenix128
  • 5
  • 3
1 Solution
 
ai_ja_naiCommented:
why dropping it? use

REPAIR TABLE tablename;

even if it's a temporary table. Check if the /tmp folder and drive have no quota/space restrictions, and that your mysql process has sufficient rights to create its temporary files there without limitations
0
 
phoenix128Author Commented:
Unfortunately REPAIR does not always work, but maybe you gave me an idea.
My /tmp is on tmpfs and maybe the problem is there... but I see it is 98% free.

Am I missing something here?
0
 
ai_ja_naiCommented:
is there any particular error code? like, 126?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ai_ja_naiCommented:
ok, here's an idea: keep an eye on /tmp while the program normally works; and especially when you do the select. If the space fills up all in one it means we know the problem
0
 
phoenix128Author Commented:
Yes, it givers error 28 ... I googled it and it seems it is related to free space.
What I did not understand is why it needs to write into /tmp folder during a select. Can you explain me the mechanism?
0
 
ai_ja_naiCommented:
Well, any operation outputs garbage data during the execution, data that must be deleted after the opearation has terminated.
These data are the intermediate values that have to be computed some more. Normally it resides into RAM, but to prevent accidents, this data is stored on disk, so that if something goes wrong it can be checked for debugging purposes.
0
 
ai_ja_naiCommented:
If you told me earlier that it was Error 28, we could have solved it much earlier and quicker
http://www.mysql.com/news-and-events/newsletter/2003-10/a0000000249.html
0
 
phoenix128Author Commented:
Thank you very much for your help :)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now