如何停止“无法创建/写入文件" (Errcode:2)在MySQL中? [英] How do I stop "Can't create/write to file" (Errcode: 2) in MySQL?

问题描述

我现在经常收到该错误:

I now frequently receive the error:

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

从我的MySQL服务器.它将锁定所有查询或对数据的更改，直到我重新启动守护程序.然后，它会自行修复一段时间，然后再次发生.我尝试使用具有777权限的tmp目录中的文件使用touch，并且还尝试将tmp目录移至MySQL目录(/var/lib/mysql/tmp)内:

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错误1(HY000)无法创建文件Errcode 2

均未奏效.我的tmp目录几乎无法满载90％以上的可用空间，因此该问题的最佳答案并不适用:

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:无法创建/写入文件'/tmp/#sql_3c6_0.MYI'(错误代码:2)-这甚至意味着什么?

推荐答案

我已经看到这是由在具有多个联接的复杂查询期间创建的巨大临时表引起的.考虑一下这样的查询在您的应用程序中的何处存在，并在监视磁盘活动/可用空间时尝试触发该查询.这就是我们发现正在发生的事情的方式. 作为解决方案，您可以为临时表(默认情况下为/tmp)提供更多空间，或尝试重构查询./tmp为空并且在您查看时有足够的空间这一事实，并不意味着在查询执行期间并不会全部消耗掉它.在我们的例子中，/tmp可能会占用磁盘上的所有可用空间，并且实际上是在执行这个特别复杂的查询的过程中这样做的，并且由于与您类似的错误而中断，因为这还不够.

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.

这篇关于如何停止“无法创建/写入文件" (Errcode:2)在MySQL中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！