How do I stop "Can't create/write to file" (Errcode: 2) in MySQL?

0
votes

I now frequently receive the error:

#1 - Can't create/write to file '/tmp/#sql_1a2_3.MYI' (Errcode: 2)

From my MySQL server. It locks out any queries or changes to data until I restart the daemon. It then fixes itself for a while and then happens again. I've tried using touch on the file inside the tmp directory with 777 permissions, and I've also tried moving the tmp directory inside of the MySQL directory (/var/lib/mysql/tmp):

MySQL Error 1 (HY000) Trouble creating file Errcode 2

Neither has worked. My tmp directory is nowhere near full with 90%+ free space, so the best answer from this question does not apply:

MySQL: Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2) - What does it even mean?

1
mysqltmp

1 Answers

1
votes

I've seen this being caused by a huge temporary table that was created during a complex query with many joins. Think where in your application could such a query exist and try triggering it while monitoring disk activity/available space. This is how we discovered what was going on. As a solution, you can give more space for use for temporary tables (/tmp by default), or try refactoring the query. The fact that /tmp is empty and has plenty of space when you look at it, doesn't mean it's not all getting consumed during query execution. In our case, /tmp could take all available space on disk, and it actually did during the execution of this particularly complex query, breaking with an error similar to yours because even that wasn't enough.