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

44
votes

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.MYI in /tmp and 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?

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?

14
mysqlexceptionjdbc
The hash character is for comments in most shells, but you can create a file with a hash character by enclosing it in quotes: $ touch '#filename'. – Bill Karwin

14 Answers

21
votes

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

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

38
votes

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

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=/tmp to my.cnf under [mysqld]

  3. restart web/app and mysql server

    /etc/init.d/mysqld restart

16
votes

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

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.

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

/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.

14
votes

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.

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!

Solutions I gave him:

  • Increase the MySQL config variables tmp_table_size and max_heap_table_size so 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 tmpdir to 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.

14
votes

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:

  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.

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.

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:

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

Problem solved.

7
votes

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 -u root -p

I restarted using : 

systemctl start mysqld.service

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

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

3
votes

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

chmod -R 777 /tmp
2
votes

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.

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

owner /mnt/tmp/** rwkl,

/mnt/tmp/ rw,

2
votes

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

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

I did not even have to restart mysql daemon.

2
votes

I'm using mariadb. When I try to put this line at /etc/my.cnf:

[mysqld]
tmpdir=/tmp

It solved the error generated from website frontend related to /tmp. But, it has backend problem with /tmp. Example, when I try to rebuild mariadb from the backend, it couldn't read the /tmp dir, and then generated the similar error.

mysqldump: Couldn't execute 'show fields from `wp_autoupdate`': Can't create/write to file '/tmp/#sql_1680_0.MAI' (Errcode: 2 "No such file or directory") (1)

So this one work for both front end and back end:

1. mkdir /var/lib/mysql/tmp

2. chown mysql:mysql /var/lib/mysql/tmp

3. Add the following line into the [mysqld] section:

    tmpdir = /var/lib/mysql/tmp

4. Restart mysqld (eg. Centos7: systemctl restart mysqld)

1
votes

Its due to access control security policies specifically when SELinux is enabled it won't allow external executables to create temporary files in the system locations.

Disable SELinux by issuing below command:

echo 0 >/selinux/enforce

You can now start mysql it wont give any permission related errror while reading/writing to /tmp or system directories.

In case you wish to enable the SELinux security back change 0 to 1 in above command.

0
votes

Check permission issues, mysql config.

Also check if you haven't reached disk space, quota limits.

Note: Some systems are limiting number of files (not just space), deleting some old session files helped fixed the issue in my case.

0
votes

For those using VPS / virtual hosting.

I was using a VPS, getting errors with MySQL not being able to write to /tmp, and everything looked correct. I had enough free space, enough free inodes, correct permissions. Turned out the problem was outside my VPS, it was the machine hosting the VPS that was full. I only had "virtual space" in my file system, but the machine in the background which hosted the VPS had no "physical space" left. I had to contact the VPS company any they fixed it.

If you think this might be your problem, you could test writing a larger file to /tmp (1GB):

dd if=/dev/zero of=/tmp/file.txt count=1024 bs=1048576

I got a No space left on device error message, which was a giveaway that it was a disk/volume in the background that was full.