MySQL: Can’t create/write to file ‘/tmp/#sql_3c6_0.MYI’ (Errcode: 2)

This error have multiple shapes and forms, this can come on cPanel or other normal servers when you have no space left on “/tmp” directory of your server, and mysql can not write to it.

There is another possible reason, your disk space might not be full. But you are still getting this error, it is because your INODES are full. Today we will see that in both these cases how we can get around this error, some shapes and error messages that you might get in this situation are :

exception – MySQL: Can’t create/write to file ‘/tmp/#sql_3c6_0.MYI’ (Errcode: 2)
Can’t create/write to file ‘/tmp/MYRqo1OW’ (Errcode: 28 – No space left on device)

Or something like this:

First we’ve to  diagose weather the error came due to disk space or inodes. Let see how we can get around this problem.

Step 1: Get the root cause!

Check for disk space usage

Let see what is causing this issue, run the following command to see your disk space usage:

df -h

This command will print out all your mount points along with their disk usage. Let see how the output looks like and what it means to us in this situation:

Now you can see that disk space usage of /tmp here is only “69MB” out of 2.7 GB, which is roughly 3% in my case, however, when you are reciving this error your disk space might be full, and if it is, that means you have to deal with your disk space issue.

Check or inodes usage!

Now if your disk space usage is fine and you are still getting this error, you need to run another check, please run the command below to see if your inodes usage is not full:

df -i

The output of this command will look something like this:

Do not confuse this command with the disk usage command, because disk usage command is “df -h” and inodes usage command is “df -i” , there is only very slight difference.

And the output looks mostly the same as well, but with inodes usage command your heading looks “Iuse%”, so please make sure you do not confuse these two commands.

Uptill now you should have figured that from two of these issue which category you fall into, I am going to explain seprately for each problem, so you can skip the part if it does not belongs to you.

Step 2: Fix the issue

Disk Usage Issue

There can be two possible scenarios.

1. You are using LVM.
2. Not using LVM.

If you are not using LVM, you have got no option but to delete things from your /tmp directory.

Without LVM

If you are not using LVM, you will have to delete things from your /tmp directory. If nothing important is going on you can run following to delete everything:

rm -rf /tmp

This will delete everything inside /tmp, you do not need to worry because normally /tmp directory also resets itself upon each reboot.

If you are not interested to delete everything from this directory you can find large files and folder and delete them one by one to free some space:

du -a /tmp | sort -n -r | head -n 10

This command will output large files and folders inside your /tmp, you can than delete them as necessary to free some space.

With LVM

If you are using LVM, you have some room here. What you can do is reduce space from one logical volume (If that logical volume have some free space), and give the freed space to /tmp directory. Here is a comprehensive guide on how you can do this:

https://www.tecmint.com/extend-and-reduce-lvms-in-linux/

Thats all with the disk usage issue. However, if you are not interested to go through all this trouble even if you have LVM, you can simply follow the first method and delete things from your /tmp directory and you will be fine.

Inode Usage Issue

If you disk space usage is fine, than that means your Inodes usage on /tmp is 100%. To fix this issue, run the following commands:

rm -rf /tmp
shutdown -r now

After the reboot you should be able to write to /tmp directory.

Step 3: Check for permission Issue

There can be one more possibility that your disk and inodes usage is fine. But mysql process can not write to  /tmp directory due to permission issues.

You can check under which user mysql is running using:

ps aux | grep mysql

First column of this command will tell you under which user mysql is running, if it is running under something other than root, that means the user is not allowed to write to /tmp directory, for time being you can change the permissions of /tmp directory so that every user can write to it.

chmod -R 777 /tmp

After running this command check if your mysql is running fine now, however, this is not the parmenent solution. You should further investigate that why the specified user was not able to write to /tmp directory and try to fix the situation.