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

Question

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?

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, Nov 02 '12 at 16:45

score 41 · Answer 1 · edited Apr 13 '14 at 06:25

41

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.

check mysql config : my.cnf
```
 cat /etc/my.cnf | grep tmpdir
```
I can't see anything in my my.cnf
add tmpdir=/tmp to my.cnf under [mysqld]
restart web/app and mysql server

/etc/init.d/mysqld restart

edited Apr 13 '14 at 06:25

Seunghoon

5,632
5
35
41

answered Nov 02 '12 at 16:15

Rivsen

421
4
8

1

Weird that this works because it looks like it's already using /tmp from the error message, but lo and behold this DID fix it for me. – Octopus Aug 28 '15 at 18:22
I had this problem with a rails app in a docker container that was linked to a mariadb container. I restarted the DB Container as explained here and explicitly linked the conf.d folder with the .cnf file from my file system. https://hub.docker.com/_/mariadb/#using-a-custom-mysql-configuration-file – cessor Oct 11 '15 at 19:12
2

Here I am two months later with the exact same problem. Actually, the only important part of this answer is #3: restart mysql server. See Svenn Dhert's answer as well. – Octopus Oct 27 '15 at 03:53
Thanks. I checked the config and appears it's already in the /tmp, but putting this extra piece of config it fixed the problem. This is how solution should be written not an essay. – MaXi32 Aug 21 '19 at 15:33
Actually this one did solve the problem for my front end web to write sql in temp. but then, I got problem when rebuilding mariadb sql, the system cannot dump existing database to the same temp location that has problem. – MaXi32 Aug 22 '19 at 12:29

score 22 · Accepted Answer · answered Aug 17 '12 at 00:18

22

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.

answered Aug 17 '12 at 00:18

tadman

208,517
23
234
262

that was my first thought too. The `/tmp` had tons of space. For some reason it started working fine. I created a non-hashed tmp file with the above name and another user (root) deleted it and it was back to normal. This was very strange and a bit irksome since we still didn't figure out what the problem was? Sigh... – PhD Aug 18 '12 at 18:22
what's meaning for "non-hashed tmp file with the above name"? – brucenan Mar 12 '13 at 10:00
Probably referring to the `#` in the filename, which is commonly called "hash". – tadman Mar 12 '13 at 14:48
1

Who runs out of disk space on /tmp in 2012? If you're on Fedora definitely read the answer a couple of PgDn's below on mounted private directories overlaying /tmp -- this is exactly what I ran into. – Eric Apr 07 '13 at 06:23
Also, errno 2 is "ENOENT", not "ENOSPC". In other words, the system can't find /tmp, which makes no sense. – Eric Apr 07 '13 at 06:24
7

Disk space can run out on any system if you let it. – tadman Apr 07 '13 at 08:17
Using root privileges, search /tmp for a file named like #sql_d5_0.MYD If found, just move or delete is. That way you release the lock that keeps mysql from accesing your data. It´s sudo rm /tmp/\#sql* – Zsolt Szilagyi Dec 02 '13 at 18:59
1

I had this issue when my filesystem ran out of usable inodes – A.B. Carroll Sep 09 '15 at 18:25
My temp files is empty including hidden files. – MaXi32 Aug 21 '19 at 15:35
@MaXi32 Newer [`tmpfs` type systems](https://en.wikipedia.org/wiki/Tmpfs) don't actually have a normal `/tmp` partition, but instead a per-process view of it. This avoids conflict between processes. – tadman Aug 21 '19 at 17:40
1

With a VPS if everything looks correct - enough free space, enough free inodes, correct permissions, it's possible that the problem is outside your VPS, it's the host machine that's full and you need to contact the hosting company. You could test writing a larger file to /tmp (1GB) with: `dd if=/dev/zero of=/tmp/file.txt count=1024 bs=1048576` to test. That's what I ran into. – Andrew Jan 22 '20 at 12:44

score 17 · Answer 3 · answered Jan 22 '13 at 09:58

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.

Thanks for this information! Changing just the tmpwatch cron job did not fix it for me, because systemd is also deleting temporary folders. I've now gone and added exclusions to /usr/lib/tmpfiles.d/tmp.conf too, but I'll only be able to know if it worked in 10 days from now. — Pada, Jul 15 '13 at 10:23

score 15 · Answer 4 · answered Mar 07 '13 at 07:14

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:

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.
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.
systemd then invokes mysqld in this private namespace.
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.
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.
/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.

Thanks to you and ArturZ. Perhaps obvious, but another simple solution would be to specify a different tmp directory for mysql, ie `tmpdir=/var/lib/mysqltmp` in /etc/my.cnf (if skipping the private tmp 'feature' is acceptable) — Jonathan MacDonald, Mar 08 '13 at 18:50
I tried doing that, but was still running into this problem after restarting mysqld, and ps auxww wasn't showing --tmpdir . The solution, at least on my fedora box, was to modify the call to /usr/sbin/tmpwatch — Eric, Apr 07 '13 at 06:25
Thanks for your thorough explanation. I've been running into this on a Fedora server intermittently as well (just 2-3 times over a couple of months), but have always restarted `mysqld` to see the problem go away, without being able to error search more closely. I hope this solution will keep these issues at bay. — Daniel Andersson, Apr 07 '13 at 19:45

score 14 · Answer 5 · answered Nov 02 '12 at 16:55

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.

Bill's Answer is the most appropriate, It's not a permissions issue especially that I got the same error on Windows! — Yazid Erman, Nov 17 '15 at 08:45

SvennD · Answer 6 · 2014-12-19T13:34:00.440

7

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.

edited Dec 19 '14 at 13:34

answered Jul 19 '13 at 12:33

SvennD

371
5
12

1

Yep. Definitely weird. Was using the mysql tool to alter tables and reset my passwords and usernames and everything, but simply restarting mysqld made wordpress work again. – Octopus Oct 27 '15 at 03:51
1

This worked for me, but is extra weird because I actually did run out of space on `/tmp` yesterday. But I've freed the space since then... and yet the error persisted. – rr- Mar 15 '16 at 07:33

Ranjith Siji · Answer 7 · 2013-04-18T10:50:31.720

5

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://smashingweb.info/solved-mysql-tmp-error-cant-createwrite-to-file-tmpmykbo3bl-errcode-13/

edited Apr 18 '13 at 10:50

answered Apr 17 '13 at 17:54

Ranjith Siji

1,125
3
12
19

This solution worked for me, as I had plenty of disk space, and my other config was set correctly. Evidently the sticky bit needed setting. – bbbco May 10 '13 at 20:24
Not good if its a production system on a public IP. /tmp hacks via apache are way too common. – Srihari Karanth Oct 25 '14 at 03:57

Ken Yang · Answer 8 · 2015-07-15T08:26:54.250

3

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

chmod -R 777 /tmp

edited Jul 15 '15 at 08:26

answered Jul 15 '15 at 08:14

Ken Yang

39
2

you should not do this unless you know what it means http://www.cyberciti.biz/faq/unix-linux-bsd-chmod-numeric-permissions-notation-command/ – Zach Smith Oct 28 '15 at 18:14
2

If you do this, at least use `1777`. The `1` enabled the sticky bit. For directories, it prevents unprivileged users from removing or renaming a file in the directory unless they own the file or the directory and is a very good idea for /tmp. – Martijn Heemels May 16 '17 at 12:04

score 2 · Answer 9 · answered Jan 06 '15 at 05:21

2

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,

answered Jan 06 '15 at 05:21

krishnakumarp

8,967
3
49
55

1

Thanks! that works even without mysqld restart . cat <> /etc/apparmor.d/abstractions/user-tmp . owner /mnt/tmp/** rwkl, /mnt/tmp/ rw, EOF . sudo /etc/init.d/apparmor reload . – Ryszard Perkowski May 18 '17 at 11:47

score 2 · Answer 10 · edited Mar 28 '15 at 14:35

2

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.

edited Mar 28 '15 at 14:35

Waldir Leoncio

10,853
19
77
107

answered Mar 28 '15 at 13:33

Abner

31
3

MaXi32 · Answer 11 · 2019-11-28T04:29:33.620

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)

score 1 · Answer 12 · answered May 19 '14 at 20:23

1

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.

answered May 19 '14 at 20:23

Shoaib Khan

899
14
26

Sometimes this is enough to restore everything. Remember to restart mysqld – gdm Jan 11 '17 at 11:58

score 0 · Answer 13 · answered Jul 17 '14 at 10:18

0

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.

answered Jul 17 '14 at 10:18

takeshin

49,108
32
120
164

Andrew · Answer 14 · 2020-01-22T12:53:52.327

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.

Alexander Suraphel · Answer 15 · 2022-05-25T14:57:35.893

-1

I had the same issue and it was caused because our DB server run out of space. Clearing up some disk space solved the issue.

edited May 25 '22 at 14:57

answered May 23 '22 at 11:30

Alexander Suraphel

10,103
10
55
90

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

15 Answers15

Linked

Related