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

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

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.

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.