MySQL/Writing file error (Errcode 28)
MysqlLoggingMysql Problem Overview
I have the following error with one of our web applications -
Query3 failed: Error writing file '/tmp/MY1fnqpm' (Errcode: 28) ... INSERT MailList... (removed the rest of the query for security reasons)
Any ideas - is this some hard disk space issue on my server?
Mysql Solutions
Solution 1 - Mysql
Use the perror
command:
$ perror 28
OS error code 28: No space left on device
Unless error codes are different on your system, your file system is full.
Solution 2 - Mysql
We have experienced similar issue, and the problem was MySQL used /tmp directory for its needs (it's default configuration). And /tmp was located on its own partition, that had too few space for big MySQL requests.
For more details take a look for this answer: https://stackoverflow.com/a/3716778/994302
Solution 3 - Mysql
I had same problem but disk space was okay (only 40% full). Problem were inodes, I had too many small files and my inodes were full.
You can check inode status with df -i
Solution 4 - Mysql
The error means that you dont have enough space to create temp files needed by MySQL.
The first thing you can try is to expand the size of your /tmp/
partition. If you are under LVM, check the lvextend
command.
If you are not able to increase the size of your partition /tmp/
you can work in the MySQL configuration, edit the my.cnf
(typically on /etc/mysql/my.cnf
) file and look for this line:
Change it for whatever you want (example /var/tmp/
). Just be sure to have space and assign write permission for the mysql user in the new directory.
Hope this helps!
Solution 5 - Mysql
Run the following code:
du -sh /var/log/mysql
Perhaps mysql binary logs filled the memory, If so, follow the removal of old logs and restart the server. Also add in my.cnf:
expire_logs_days = 3
Solution 6 - Mysql
I had this same error and the problem was simply not enough space on my virtual machine. I deleted some unnecessary files and it started working again.
my memory/disk space allocation looked something like this
df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 37G 37G 127M 100% /
...
Solution 7 - Mysql
You can also try using this line if the other doesn't work:
du -sh /var/lib/mysql/database_Name
You may also want to check with your host and see how big they allow your databases to be.
Solution 8 - Mysql
For xampp users: on my experience, the problem was caused by a file, named '0' and located in the 'mysql' folder. The size was tooooo huge (mine exploded to about 256 Gb). Its removal fixed the problem.
Solution 9 - Mysql
This error occurs when you don't have enough space in the partition. Usually MYSQL uses /tmp on linux servers. This may happen with some queries because the lookup was either returning a lot of data, or possibly even just sifting through a lot of data creating big temp files.
Edit your /etc/mysql/my.cnf
> tmpdir = /your/new/dir
e.g
> tmpdir = /var/tmp
Should be allocated with more space than /tmp that is usually in it's own partition.
Solution 10 - Mysql
Today. I have same problem... my solution:
-
check inode:
df -i
I saw:root@vm22433:/etc/mysql# df -i Filesystem Inodes IUsed IFree IUse% Mounted on udev 124696 304 124392 1% /dev tmpfs 127514 452 127062 1% /run /dev/vda1 1969920 1969920 0 100% / tmpfs 127514 1 127513 1% /dev/shm tmpfs 127514 3 127511 1% /run/lock tmpfs 127514 15 127499 1% /sys/fs/cgroup tmpfs 127514 12 127502 1% /run/user/1002
-
I began to look what folders use the maximum number of inods:
for i in /*; do echo $i; find $i |wc -l; done soon I found in /home/tomnolane/tmp folder, which contained a huge number of files.
-
I removed /home/tomnolane/tmp folder PROFIT.
-
checked:
Filesystem Inodes IUsed IFree IUse% Mounted on udev 124696 304 124392 1% /dev tmpfs 127514 454 127060 1% /run /dev/vda1 1969920 450857 1519063 23% / tmpfs 127514 1 127513 1% /dev/shm tmpfs 127514 3 127511 1% /run/lock tmpfs 127514 15 127499 1% /sys/fs/cgroup tmpfs 127514 12 127502 1% /run/user/1002
it's ok.
restart mysql service
- it's ok!!!!
Solution 11 - Mysql
I had the same problem and after little research found that the snap directory was occupying most of the space. So executed following command to get rid of it:
sudo apt autoremove --purge snapd
After that ran following command to get rid of useless/dev/loop mounts:
`sudo apt purge snapd ubuntu-core-launcher squashfs-tools`
After that ran following command to restart mysql:
sudo service mysql restart
It worked for me!