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?
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.
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.
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% /
...
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.
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:
tmpdir = /tmp/
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!
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
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
Today. I have same problem... my solution:
1) 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
2) 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.
3) I removed /home/tomnolane/tmp folder PROFIT.
4) 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.
5) restart mysql service
- it's ok!!!!
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
Source: Stackoverflow.com