MySQL：无法创建/写入文件 '/tmp/#sql_3c6_0.MYI' (Errcode: 2) - 这到底是什么意思？

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

提问by PhD

For some reason my production DB decided to spew out this message. All application calls fail to the DB with the error:

出于某种原因，我的生产数据库决定发出这条消息。所有应用程序调用都失败并显示错误：

PreparedStatementCallback; SQL [ /*long sql statement here*/ ]; 
Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2); 
nested exception is java.sql.SQLException: Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2)

I have no idea, what this even means. There is no file #sql_3c6_0.MYIin /tmpand I can't create one with a #character for some reason. Has anyone heard about it or seen this error? What could be wrong and some possible things to look at?

我不知道，这甚至意味着什么。没有文件#sql_3c6_0.MYI，由于某种原因/tmp我无法创建一个带有#角色的文件。有没有人听说过或看到过这个错误？可能有什么问题以及需要注意的一些事项？

The MySQL DB seems to be up and running and can be queried via the console but the application can't seem to get through to it. There was no change to the application code/files. It just happened out the blue. So I'm not even sure where to start look or what resolution tactics to apply. Any ideas?

MySQL 数据库似乎已启动并正在运行，可以通过控制台进行查询，但应用程序似乎无法访问它。应用程序代码/文件没有变化。它只是突然发生。所以我什至不确定从哪里开始寻找或应用什么解决策略。有任何想法吗？

采纳答案by tadman

Often this means your /tmppartition has run out of space and the file can't be created, or for whatever reason the mysqldprocess cannot write to that directory because of permission problems. Sometimes this is the case when selinuxrains on your parade.

这通常意味着您的/tmp分区空间不足并且无法创建文件，或者mysqld由于权限问题进程无法写入该目录。有时，当selinux您的游行队伍下雨时就是这种情况。

Any operation that requites a "temp file" will go into the /tmpdirectory by default. The name you're seeing is just some internal random name.

/tmp默认情况下，任何需要“临时文件”的操作都将进入该目录。您看到的名称只是一些内部随机名称。

回答by Rivsen

I meet this error too when I run a wordpress on my Fedora system.

当我在 Fedora 系统上运行 wordpress 时，我也遇到了这个错误。

I googled it, and find a way to fix this.

我用谷歌搜索了一下，找到了解决这个问题的方法。

Maybe this will help you too.

也许这也会对你有所帮助。

1. check mysql config : my.cnf

    cat /etc/my.cnf | grep tmpdir
   

   I can't see anything in my my.cnf

2. add tmpdir=/tmpto my.cnfunder [mysqld]

3. restart web/app and mysql server

   /etc/init.d/mysqld restart

1. 检查 mysql 配置：my.cnf

    cat /etc/my.cnf | grep tmpdir
   

   我看不到任何东西 my.cnf

2. 添加tmpdir=/tmp到my.cnf下面[mysqld]

3. 重启 web/app 和 mysql 服务器

   /etc/init.d/mysqld restart

回答by ArturZ

On Fedora with systemd MySQL gets private /tmp directory. In /proc/PID_of_MySQL/mountinfo you will find the line like:

在带有 systemd 的 Fedora 上，MySQL 获得私有的 /tmp 目录。在 /proc/PID_of_MySQL/mountinfo 中，您会发现以下行：

156 129 8:1 /tmp/systemd-namespace-AN7vo9/private /tmp rw,relatime - ext4 /dev/sda1 rw,seclabel,data=ordered

156 129 8:1 /tmp/systemd-namespace-AN7vo9/private /tmp rw,relatime - ext4 /dev/sda1 rw,seclabel,data=ordered

This means a temporary folder /tmp/systemd-namespace-AN7vo9/private is mounted as /tmp in private namespace of MySQL process. Unfortunately this folder is deleted by tmpwatch if not used frequently.

这意味着临时文件夹 /tmp/systemd-namespace-AN7vo9/private 作为 /tmp 挂载在 MySQL 进程的私有命名空间中。不幸的是，如果不经常使用，tmpwatch 会删除此文件夹。

I modified /etc/cron.daily/tmpwatch and inserted the exclude pattern -X '/tmp/systemd-namespace*'like this:

我修改了 /etc/cron.daily/tmpwatch 并插入了这样的排除模式-X '/tmp/systemd-namespace*'：

/usr/sbin/tmpwatch "$flags" -x /tmp/.X11-unix -x /tmp/.XIM-unix \
        -x /tmp/.font-unix -x /tmp/.ICE-unix -x /tmp/.Test-unix \
        -X '/tmp/systemd-namespace*' \
        -X '/tmp/hsperfdata_*' 10d /tmp

The side effect is that unused private namespace folders will not be deleted automatically.

副作用是未使用的私有命名空间文件夹不会被自动删除。

回答by Thomas Dwyer III

Tremendous thanks to ArturZ for pointing me in the right direction on this. I don't have tmpwatch installed on my system so that isn't the cause of the problem in my case. But the end result is the same: The private /tmp that systemd creates is getting removed. Here's what happens:

非常感谢 ArturZ 为我指明了正确的方向。我的系统上没有安装 tmpwatch，所以这不是我的问题的原因。但最终结果是一样的：systemd 创建的私有 /tmp 被删除了。这是发生的事情：

1. systemd creates a new process via clone() with the CLONE_NEWNS flag to obtain a private namespace. Or maybe it calls unshare() with CLONE_NEWNS. Same thing.

2. systemd creates a subdirectory in /tmp (e.g. /tmp/systemd-namespace-XRiWad/private) and mounts it on /tmp. Because CLONE_NEWNS was set in #1, this mountpoint is invisible to all other processes.

3. systemd then invokes mysqld in this private namespace.

4. Some specific database operations (e.g. "describe ;") create & remove temporary files, which has the side effect of updating the timestamp on /tmp/systemd-namespace-XRiWad/private. Other database operations execute without using /tmp at all.

5. Eventually 10 days go by where even though the database itself remains active, no operations occur that update the timestamp on /tmp/systemd-namespace-XRiWad/private.

6. /bin/systemd-tmpfiles comes along and removes the "old" /tmp/systemd-namespace-XRiWad/private directory, effectively rendering the private /tmp unusable for mysqld while the public /tmp remains available for everything else on the system.

1. systemd 通过带有 CLONE_NEWNS 标志的 clone() 创建一个新进程以获得私有命名空间。或者它可能使用 CLONE_NEWNS 调用 unshare()。一样。

2. systemd 在 /tmp 中创建一个子目录（例如 /tmp/systemd-namespace-XRiWad/private）并将其挂载在 /tmp 上。因为 CLONE_NEWNS 是在 #1 中设置的，所以这个挂载点对所有其他进程都是不可见的。

3. systemd 然后在这个私有命名空间中调用 mysqld。

4. 一些特定的数据库操作（例如“描述；”）创建和删除临时文件，这具有更新 /tmp/systemd-namespace-XRiWad/private 上的时间戳的副作用。其他数据库操作完全不使用 /tmp 执行。

5. 最终 10 天过去了，即使数据库本身保持活动状态，也不会发生更新 /tmp/systemd-namespace-XRiWad/private 上的时间戳的操作。

6. /bin/systemd-tmpfiles 出现并删除“旧”/tmp/systemd-namespace-XRiWad/private 目录，有效地使私有 /tmp 无法用于 mysqld，而公共 /tmp 仍然可用于系统上的其他所有内容。

Restarting mysqld works because this starts everything over again at step #1, with a brand new private /tmp directory. However, the problem eventually comes back again. And again.

重新启动 mysqld 有效，因为这会在步骤 1 中重新开始所有内容，并使用全新的私有 /tmp 目录。然而，问题最终又卷土重来。然后再次。

The simple solution is to configure /bin/systemd-tmpfiles so that it preserves anything in /tmp with the name /tmp/systemd-namespace-*. I did this by creating /etc/tmpfiles.d/privatetmp.conf with the following contents:

简单的解决方案是配置 /bin/systemd-tmpfiles 以便它保留 /tmp 中名为 /tmp/systemd-namespace-* 的任何内容。我通过使用以下内容创建 /etc/tmpfiles.d/privatetmp.conf 来做到这一点：

x   /tmp/systemd-namespace-*
x   /tmp/systemd-namespace-*/private

Problem solved.

问题解决了。

回答by Bill Karwin

The filename looks like a temporary table created by a query in MySQL. These files are often very short-lived, they're created during one specific query and cleaned up immediately afterwards.

文件名看起来像是由 MySQL 中的查询创建的临时表。这些文件通常非常短暂，它们是在一次特定查询期间创建的，之后会立即清理。

Yet they can get very large, depending on the amount of data the query needs to process in a temp table. Or you may have multiple concurrent queries creating temp tables, and if enough of these queries run at the same time, they can exhaust disk space.

然而，它们可能会变得非常大，具体取决于查询需要在临时表中处理的数据量。或者您可能有多个并发查询创建临时表，如果同时运行足够多的这些查询，它们可能会耗尽磁盘空间。

I do MySQL consulting, and I helped a customer who had intermittent disk full errors on his root partition, even though every time he looked, he had about 6GB free. After we examined his query logs, we discovered that he sometimes had four or more queries running concurrently, each creating a 1.5GB temp table in /tmp, which was on his root partition. Boom!

我做 MySQL 咨询，我帮助了一个客户，他的根分区上有间歇性的磁盘已满错误，即使他每次查看时，他都有大约 6GB 的可用空间。在我们检查了他的查询日志后，我们发现他有时同时运行四个或更多查询，每个查询在 /tmp 中创建一个 1.5GB 的临时表，该表位于他的根分区上。繁荣！

Solutions I gave him:

我给他的解决方案：

• Increase the MySQL config variables tmp_table_sizeand max_heap_table_sizeso MySQL can create really large temp tables in memory. But it's not a good idea to allow MySQL to create 1.5GB temp tables in memory, because there's no way to limit how many of these are created concurrently. You can exhaust your memory pretty quickly this way.

• Set the MySQL config variable tmpdirto a directory on another disk partition with more space.

• Figure out which of your queries is creating such big temp tables, and optimize the query. For example, use indexes to help that query reduce its scan to a smaller slice of the table. Or else archive some of the data in the tale so the query doesn't have so many rows to scan.

• 增加 MySQL 配置变量tmp_table_size，max_heap_table_size因此 MySQL 可以在内存中创建非常大的临时表。但是让 MySQL 在内存中创建 1.5GB 的临时表并不是一个好主意，因为没有办法限制同时创建的临时表的数量。通过这种方式，您可以很快耗尽您的记忆力。

• 将 MySQL 配置变量设置为tmpdir另一个具有更多空间的磁盘分区上的目录。

• 找出您的哪个查询正在创建如此大的临时表，并优化查询。例如，使用索引来帮助该查询减少对表的较小部分的扫描。或者归档一些故事中的数据，这样查询就没有太多的行要扫描。

回答by SvennD

For me this issue came after a long period of not using mysql nor the webserver. So I was sure that my settings where correct; Simply restarting the service fixes this issue; The weird part about the issue is that one can still connect to the database, and even query/add tables using the mysql tool. for example :

对我来说，这个问题是在长时间不使用 mysql 和网络服务器之后出现的。所以我确信我的设置是正确的；只需重新启动服务即可解决此问题；关于这个问题的奇怪部分是仍然可以连接到数据库，甚至可以使用 mysql 工具查询/添加表。例如 ：

mysql -u root -p

I restarted using :

我重新启动使用：

systemctl start mysqld.service

or service mysqld restart or /etc/init.d/mysqld restart

或 service mysqld restart 或 /etc/init.d/mysqld restart

Note : depending on the machine/environment on of these commands should restart the service.

注意：根据这些命令的机器/环境，应该重新启动服务。

回答by Ranjith Siji

A better way worked for me.

更好的方法对我有用。

    chown root:root /tmp
  chmod 1777 /tmp
  /etc/init.d/mysqld restart

That is it.

这就对了。

See here :http://nixcraft.com/databases-servers/14260-error-1-hy000-cant-create-write-file-tmp-sql_9f3_0-myi-errcode-13-a.html

见这里：http: //nixcraft.com/databases-servers/14260-error-1-hy000-cant-create-write-file-tmp-sql_9f3_0-myi-errcode-13-a.html

http://smashingweb.info/solved-mysql-tmp-error-cant-createwrite-to-file-tmpmykbo3bl-errcode-13/

http://smashingweb.info/solved-mysql-tmp-error-cant-createwrite-to-file-tmpmykbo3bl-errcode-13/

回答by Ken Yang

it's very easy, you just grant the /tmp folder as 777 permission. just type:

这很容易，您只需将 /tmp 文件夹授予 777 权限即可。只需输入：

chmod -R 777 /tmp

回答by krishnakumarp

On an Ubuntu box, I started getting this error after moving /tmp to a different volume (symlink). Even after setting the required permission 1777, the issue was not resolved.

在 Ubuntu 机器上，将 /tmp 移动到不同的卷（符号链接）后，我开始收到此错误。即使设置了所需的权限 1777，问题也没有解决。

MySQL is protected by AppArmor, which was disallowing writes to the new tmp location /mnt/tmp. I had to add the following lines to /etc/apparmor.d/abstractions/user-tmp to fix this

MySQL 受 AppArmor 保护，它禁止写入新的 tmp 位置 /mnt/tmp。我必须将以下行添加到 /etc/apparmor.d/abstractions/user-tmp 来解决这个问题

owner /mnt/tmp/** rwkl,

/mnt/tmp/ rw,

所有者 /mnt/tmp/** rwkl,

/mnt/tmp/ rw,

回答by Abner

On debian 7.5 I got the same error. I realized the /tmpfolder owner and permissions were off. As another answer suggested I did as follows (must be root):

在 debian 7.5 上，我遇到了同样的错误。我意识到/tmp文件夹所有者和权限已关闭。正如另一个答案所建议的那样，我做了如下（必须是 root）：

chown root:root /tmp && chmod 1777 /tmp

I did not even have to restart mysql daemon.

我什至不必重新启动 mysql 守护进程。