What can be the solution for the frequently encountered # 126 - Invalid key file for table '/tmp/#sql_4a05_0.MYI'; try to restore it to mysql?

I have a database table named answer, when I insert data into it, the data is inserted, but this leads to the following problem for another table '# 126 - Invalid key file for table' / tmp / # sql_4a05_0.MYI '; try to repair it "Then I solve this problem using the solution given in this link But this problem occurs when I insert the data into the answer table again.

And one more thing, when I export this database and use somewhere (place B) from place A, it worked like this again. I am dropping the database from Place A and importing the database to Place A from place B (where there is some more data that I pasted into place B). But when importing data to place A, it does not import all the data of the "response" table and gives the following error ERROR 1114 (HY000): the table is full I have used the resolution in the following, but I cannot solve this problem. Does anyone have this solution for this problem.


source to share

3 answers

The error could be caused by your table, and instead a temporary table created during filesort or other query operations. These temporary tables are stored in MyISAM format in your tmpdir

. If there is tmpdir

not enough space to store the temporary table, you may receive this error.

Since the problem with the temporary table being too large and not your permanent table doing REPAIR TABLE with your permanent table does not fix the problem.

The solution is to change tmpdir

to a location with more free space. Also note that if you have many requests at the same time, and they all use some of the space in tmpdir

, the free space may still be exhausted.

You can also try to improve query optimization, so fewer queries use filesort or other temporary table usage. Or, you can redesign your queries to handle fewer rows of data.



Have you checked the free space on your server yet? Especially for a temporary directory. You will notice that the answers to your linked question are about disk space.



DBD :: mysql :: st execute failed: invalid key file for table '/tmp/#sql_4091_0.MYI'; try to restore it [for Statement "...

The idea that a repair is proposed on a temporary table should raise an eyebrow. It just doesn't make much sense: it offers it somehow flawed, but since it is transitory and has just been created by MySQL, it will never be needed. Not to mention the complicated implementation.

In my experience, albeit limited, although the main reason for this error message is that your tmpdir ran out of free space. Like me, you will check how much free space you have: 1Gb, 2Gb, 4Gb. This may not be enough. Here's why: MySQL can create temporary tables larger than in seconds, quickly filling up any free space. Depending on the nature of the request and the size of the database, of course.

Take this evening for example: I had a temporary table created about 3.6 GB in size, from a database of only 5.4 GB, and I only had 1.8 GB of free space. Imagine a couple of queries of this kind running in parallel.

The moment the space is full, the above error is thrown, the temporary file on disk will be deleted and you won't be any wiser. Conclusion

Perhaps the MySQL instance could be better configured, but it isn't. At best, the error message will be oblique. Make sure you have enough free space, you may need more than you imagine.



All Articles