When working with MySQL on Debian systems (particularly older versions like Lenny), you might encounter the frustrating error:

Can't create/write to file '/tmp/#sql_xxxx.MYI' (Errcode: 13)
OS error code 13: Permission denied

MySQL needs to create temporary files during operations like complex queries or sorting. The default location (/tmp) often has restrictive permissions for security reasons. The key components involved are:

• MySQL's tmpdir configuration (shown via mysqladmin variables)
• System-level permissions on the /tmp directory
• MySQL daemon's user privileges (typically mysql:mysql)

While changing /tmp permissions to 777 works, it's a security risk. Here's why you shouldn't do it:

# THIS IS NOT RECOMMENDED!
chmod 777 /tmp

Option 1: Dedicated MySQL Temp Directory

Create a directory specifically for MySQL temp files:

sudo mkdir /var/lib/mysql/tmp
sudo chown mysql:mysql /var/lib/mysql/tmp
sudo chmod 700 /var/lib/mysql/tmp

Then edit /etc/mysql/my.cnf:

[mysqld]
tmpdir = /var/lib/mysql/tmp

Option 2: SELinux/AppArmor Context

If using security modules, ensure proper context:

# For SELinux:
semanage fcontext -a -t mysqld_t "/tmp/mysql.*"
restorecon -Rv /tmp

# For AppArmor:
echo "/tmp/mysql* rwk," >> /etc/apparmor.d/local/mysql
systemctl restart apparmor

Option 3: Systemd PrivateTmp

Modern systems using systemd can leverage PrivateTmp:

sudo systemctl edit mysql.service

[Service]
PrivateTmp=true

Verify your configuration with these commands:

# Check current tmpdir
mysql -e "SHOW VARIABLES LIKE 'tmpdir';"

# Verify directory ownership
ls -ld /tmp /var/lib/mysql/tmp

# Check security context (SELinux)
ls -Z /tmp

For production servers, consider these additional measures:

• Mount a separate tmpfs partition for MySQL temp files
• Set appropriate limits in /etc/security/limits.conf
• Monitor space usage in your temp directory

# Example tmpfs mount in /etc/fstab
tmpfs /var/lib/mysql/tmp tmpfs rw,size=1G,nr_inodes=10k,noexec,nodev,nosuid,uid=mysql,gid=mysql 0 0

---

When working with MySQL on Linux systems (particularly Debian/Ubuntu), you might encounter this error while accessing tables through phpMyAdmin or performing certain operations:

Can't create/write to file '/tmp/#sql_xxxx.MYI' (Errcode: 13)
OS error code 13: Permission denied

This occurs because MySQL needs temporary file access in /tmp but lacks proper permissions. While setting chmod 777 /tmp works, it's a security risk we should avoid.

The correct approach involves setting appropriate permissions for the MySQL user:

sudo chown mysql:mysql /tmp
sudo chmod 1777 /tmp

The sticky bit (1 in 1777) ensures only file owners can delete their own files in /tmp while allowing all users to create temporary files.

For better security isolation, consider creating a dedicated temporary directory for MySQL:

sudo mkdir /var/lib/mysql/tmp
sudo chown mysql:mysql /var/lib/mysql/tmp
sudo chmod 700 /var/lib/mysql/tmp

Then edit /etc/mysql/my.cnf:

[mysqld]
tmpdir = /var/lib/mysql/tmp

Restart MySQL to apply changes:

sudo service mysql restart

Check if MySQL can now access the temporary directory:

mysqladmin variables | grep tmpdir

You should see either the default /tmp with correct permissions or your custom directory.

On modern systems using systemd, you might need to configure PrivateTmp:

sudo systemctl edit mysql.service

[Service]
PrivateTmp=false

Then reload and restart:

sudo systemctl daemon-reload
sudo systemctl restart mysql

If you're using SELinux or AppArmor, additional steps might be needed:

# For SELinux
sudo semanage fcontext -a -t mysqld_tmp_t "/var/lib/mysql/tmp(/.*)?"
sudo restorecon -Rv /var/lib/mysql/tmp

# For AppArmor
echo "/var/lib/mysql/tmp/** rwk," | sudo tee -a /etc/apparmor.d/local/usr.sbin.mysqld
sudo systemctl reload apparmor