This error occurs when MySQL cannot write temporary files to disk, usually due to insufficient permissions on the tmpdir, a full filesystem, or file descriptor limits being reached.
The EE_CANTCREATEFILE error indicates that MySQL server is unable to create or write temporary files to the configured temporary directory. MySQL uses temporary files for various operations including sorting large result sets, creating temporary tables, handling complex queries with GROUP BY or ORDER BY clauses, and managing query caches. This error typically appears with a file path showing where MySQL attempted to write (commonly /tmp/#sql_xxx_x.MYI or similar). The error may include an additional error code (errcode) that provides more specific details: errcode 13 indicates permission denied, errcode 2 means no such file or directory, and errcode 28 indicates no space left on device. The issue prevents MySQL from completing operations that require temporary file storage, causing queries to fail and potentially disrupting database functionality. This can affect both routine queries and administrative operations like ALTER TABLE statements.
First, verify that the filesystem containing MySQL's temporary directory has available space.
Check disk usage:
df -h /tmpIf 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 -20For 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 -deleteCheck that the temporary directory has correct permissions for MySQL to write files.
Check current permissions:
ls -ld /tmpThe 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 /tmpThe 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-tmpEdit MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf):
[mysqld]
tmpdir = /var/lib/mysql-tmpRestart MySQL to apply changes:
sudo systemctl restart mysql
# or
sudo service mysql restartVerify 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 = 65535Also 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.confAdd:
[Service]
LimitNOFILE=65535Reload systemd and restart MySQL:
sudo systemctl daemon-reload
sudo systemctl restart mysqlSecurity 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 mysqlIf AppArmor is blocking access, edit the MySQL profile:
sudo nano /etc/apparmor.d/local/usr.sbin.mysqldAdd your tmpdir path:
/var/lib/mysql-tmp/** rw,Reload AppArmor:
sudo systemctl reload apparmorFor SELinux (RHEL/CentOS), check context:
ls -Z /tmpSet correct context for custom tmpdir:
sudo semanage fcontext -a -t mysqld_tmp_t "/var/lib/mysql-tmp(/.*)?"
sudo restorecon -Rv /var/lib/mysql-tmpMonitoring Temporary File Usage: You can monitor MySQL's temporary file creation with performance schema tables. Enable file instrumentation and query the file_summary_by_instance table to identify queries creating excessive temporary files.
Temporary Table Configuration: The tmp_table_size and max_heap_table_size variables control when in-memory temporary tables spill to disk. Increasing these values (with caution regarding available RAM) can reduce the need for on-disk temporary files. However, this is a workaround rather than a fix for permission or space issues.
Read-Only Filesystems: In container environments or systems with read-only root filesystems, ensure tmpdir points to a writable volume. Docker containers should mount a volume for MySQL's tmpdir, and Kubernetes deployments should use emptyDir volumes or persistent volume claims.
Multiple tmpdir Paths: MySQL supports specifying multiple tmpdir locations separated by colons (Linux/Unix) or semicolons (Windows). MySQL will use these in round-robin fashion: tmpdir=/tmp:/var/tmp. This can provide redundancy if one filesystem fills up.
Errcode Reference: Common errcodes include: 13 (EACCES - permission denied), 2 (ENOENT - no such file/directory), 28 (ENOSPC - no space left), 30 (EROFS - read-only filesystem), 24 (EMFILE - too many open files). These map to standard POSIX error codes and can help diagnose the specific cause.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL