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

user_609074 0
Viewed 155950

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?

jdbc exception mysql
15 Answers

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)

user_8416770

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.

user_27718760

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.

user_12428420
Related