How to fix EE_CANTCREATEFILE (1): Can't create/write to file in MySQL

First, verify that the filesystem containing MySQL's temporary directory has available space.

Check disk usage:

df -h /tmp

If the filesystem is full (100% usage), free up space by removing unnecessary files or expanding the filesystem. You can identify large files with:

du -sh /tmp/* | sort -hr | head -20

For immediate relief on a full disk, clean up old temporary files (be cautious with this command):

# Remove files older than 7 days from /tmp
find /tmp -type f -mtime +7 -delete

Check that the temporary directory has correct permissions for MySQL to write files.

Check current permissions:

ls -ld /tmp

The output should show: drwxrwxrwt (permissions 1777 with sticky bit set)

If permissions are incorrect, fix them as root:

sudo chown root:root /tmp
sudo chmod 1777 /tmp

The sticky bit (1 in 1777) ensures users can only delete their own files, which is essential for /tmp security.

If /tmp is problematic, configure MySQL to use a different temporary directory.

Create a dedicated directory for MySQL temporary files:

sudo mkdir -p /var/lib/mysql-tmp
sudo chown mysql:mysql /var/lib/mysql-tmp
sudo chmod 1777 /var/lib/mysql-tmp

Edit MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf):

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

Restart MySQL to apply changes:

sudo systemctl restart mysql
# or
sudo service mysql restart

Verify the new tmpdir is in use:

SHOW VARIABLES LIKE 'tmpdir';

Verify that MySQL hasn't reached its file descriptor limit.

Check current limits while MySQL is running:

SHOW VARIABLES LIKE 'open_files_limit';

Check system limit for MySQL process:

cat /proc/$(pgrep -f mysqld)/limits | grep 'open files'

If the limit is too low (default is often 1024), increase it in MySQL configuration:

[mysqld]
open_files_limit = 65535

Also update systemd service limits (for systemd-managed MySQL):

sudo mkdir -p /etc/systemd/system/mysql.service.d/
sudo nano /etc/systemd/system/mysql.service.d/limits.conf

Add:

[Service]
LimitNOFILE=65535

Reload systemd and restart MySQL:

sudo systemctl daemon-reload
sudo systemctl restart mysql

Security modules may prevent MySQL from accessing temporary directories.

For AppArmor (Ubuntu/Debian), check if it's blocking MySQL:

sudo aa-status | grep mysql
sudo dmesg | grep -i apparmor | grep mysql

If AppArmor is blocking access, edit the MySQL profile:

sudo nano /etc/apparmor.d/local/usr.sbin.mysqld

Add your tmpdir path:

/var/lib/mysql-tmp/** rw,

Reload AppArmor:

sudo systemctl reload apparmor

For SELinux (RHEL/CentOS), check context:

ls -Z /tmp

Set correct context for custom tmpdir:

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