MySQL/编写文件错误(Errcode 28)

小开

最佳答案

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.

小开

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

小开

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

小开

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!

小开

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.

小开

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

小开

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.

小开

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.

小开

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

小开

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% /
...

小开

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!

小开

For those like me who don't have a full disk or full inodes and that are using MySQL 8, you should take a look at your /var/lib/mysql directory to check if there is some large binlog.XXXXXX files.

It was the case for me and it prevented a query to be executed until the end because it seemed like this query was generating a large binlog file.

I just had to turn off the logbin generation by adding this parameter in /etc/mysql/mysql.conf.d/mysqld.cnf :

disable_log_bin

Then restart mysql service :

service mysql restart

After that i didn't have the "No space left on device" error anymore!