MySQL/Writing file error (Errcode 28)

MysqlLogging

Mysql 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:

  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.

  1. 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!

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionZabsView Question on Stackoverflow
Solution 1 - MysqlArnaud Le BlancView Answer on Stackoverflow
Solution 2 - MysqlXSeryogaView Answer on Stackoverflow
Solution 3 - MysqlTurshijaView Answer on Stackoverflow
Solution 4 - MysqlSlayerXView Answer on Stackoverflow
Solution 5 - MysqlAlexView Answer on Stackoverflow
Solution 6 - MysqlSamuel FrostView Answer on Stackoverflow
Solution 7 - MysqlWyattView Answer on Stackoverflow
Solution 8 - MysqlSandro RosaView Answer on Stackoverflow
Solution 9 - MysqlDaniel BernardezView Answer on Stackoverflow
Solution 10 - MysqlМедведев АлександрView Answer on Stackoverflow
Solution 11 - MysqlAashina VyasView Answer on Stackoverflow