Categories
If you get the error, can’t create/write to file, it means that MySQL is unable to write/create temporary file in the temporary directory.
Any operation that requires a temporary file created, will go into the /tmp directory by default. The file that is created is given a random file name. These files are normally short-lived. They are created during specific queries and are removed soon after.
This error could be due to the below reasons:
- It could be because your /tmp partition has run out of space and the file can’t be created
- Or it could be a permission problem in the /tmp directory
Solution for error, can’t create/write to file
1. The error could be due to /tmp partition running out of space. Depending on the amount of temporary data generated by the query and also the number of concurrent queries running, the disk space can get exhausted.
You can run the below command to confirm if there is free space:
df -h
If you identify that the space is full, then clean-up or add more space
Alternatively, you can set the MySQL config variable tmpdir to a directory on another disk partition with more space. Follow the below steps:
Edit your my.cnf
tmpdir = /var/lib/mysql/tmp/
restart MySQL
/etc/init.d/mysql restart
At the end of it, you will need to identify why the disk space is filling up. Is it due to any queries that are creating big temp files and filling up the space? Those queries need to be optimized.
2. From the above, if you identify that the space is not the issue. Then it could be a permission problem in the /tmp directory, that is preventing MySQL query from writing to the directory.
To check the permission of the file, run the below command:
ls -al /tmp
If it does not have the 777 permission. Set it to full permission by the below command:
chmod -R 777 /tmp
Trying all the above troubleshooting should fix the MySQL error ‘can’t create/write to file’. If you still face the issue then have your system administrator check this further.
If you want all your KPI‘s or Business metrics monitored on a single dashboard, join our BETA.
