MySQL Error / File Write (Errcode 28)

Question

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 a problem on the hard drive on my server?

Answer 1

Use the perror command:

 $ perror 28 OS error code 28: No space left on device 

If your system does not have error codes, your file system is full.

Answer 2

We had a similar problem, and the problem was that MySQL used the / tmp directory for its needs (by default). And / tmp was located on its own partition, which had too little space for large MySQL queries.

See this answer for more details: https://stackoverflow.com/a/316618/

Answer 3

I had the same problem, but the disk space was ok (only 40% full). The problem was inodes, I had too many small files, and my inodes were full.

You can check inode status with df -i

Answer 4

The error means that you do not have enough space to create the temporary files needed by MySQL.

The first thing you can try is to increase the size of your /tmp/ partition. If you are under LVM, check the lvextend command.

If you cannot increase the size of the /tmp/ partition, you can work in the MySQL configuration, edit the my.cnf file (usually in the /etc/mysql/my.cnf file) and find this line:

tmpdir =/tmp/

Change it to whatever you want (example /var/tmp/ ). Just make sure you have a place and assign write permission for the mysql user in the new directory.

Hope this helps!

Answer 5

Run the following code:

du -sh / var / log / mysql

Perhaps mysql binary logs have filled the memory. If so, delete the old logs and restart the server. Also add to my.cnf:

expire_logs_days = 3

Answer 6

As indicated above, Error Code 28 means that there is not enough disk space. Please note that this is a server error message, not a client. So make sure you check the correct server.

Answer 7

You can also try using this line if the other does not work:

du -sh / var / lib / mysql / database_Name

You can also check your host and see how large they allow your databases.

Answer 8

For xampp users: in my experience, the problem was caused by a file named "0" and is located in the "mysql" folder. The size was tooooo huge (mine exploded to about 256 GB). Fixing it fixes the problem.

Answer 9

Let's move on to the interesting case of this - we have a Java application that uses C3PO (for combining) and the Mysql Java Connector trying to connect to a remote server. I'm sure you got the same error (Errcode: 28), but confirmed things like disk space, iNodes, and permissions. Could touch / create files in the directory and there was a place to work. It turned out that the server (mysqld) had a problem with space at its end. You can still connect to the server, but if you ask about something, you will see errors on the server. In the case of the Java Mysql Connector, the inability of the server to send back information seemed to manifest itself as a "disk" error, but in fact it was a completely different problem. If you keep looking for the local server / unix machine, you will never find a problem.

Answer 10

This error occurs when you run out of space in a section. Normally MYSQL uses / tmp on Linux servers. This can happen with some queries because the search was either returning a large amount of data, or perhaps just sifting a large amount of data creating large temporary files.

Change /etc/mysql/my.cnf

tmpdir = / your / new / dir

eg

tmpdir = / var / tmp

More space should be allocated than / tmp, which is usually located in its own partition.

Answer 11

Today. I have the 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 started looking at which folders use the maximum number of inodes:

  for i in /*; do echo $i; find $i |wc -l; done 

I soon found the folder / home / tomnolane / tmp, which contained a huge number of files.

3) I deleted the / home / tomnolane / tmp PROFIT folder.

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 

this is normal.

5) restart mysql service is ok !!!!