Why do I keep getting MYSQL error writing file errcode 28 when I execute SQL query or creating a database?

Most of the hosting providers use MySQL because it is easy to use.

Here at Bobcares, we resolve MySQL related errors as part of our Server Management Services.

Recently, we came across this error ‘MYSQL error writing file errcode 28’ which created problems in running SQL commands.

We found that this error happens when there is an insufficient space for MySQL query to create temp files.

Today, let’s see how our Support Engineers fix this mysql error 28 for our customers.

What is MYSQL error writing file errcode 28?

The error occurs when there is not enough disk space to run the database on the server. Also, this error was causing the SQL commands not to work properly.

Again, one of the top reasons for this mysql error writing file errcode 28 is insufficient space for MySQL to create temp files.

Often, if no space left in the partition in which /tmp folder is located, users may get the following error when trying to run a web application.

How we fix MYSQL error writing file errcode 28?

So far we discuss the reason for the mysql errcode 28. Now let’s see how our Support Engineers fix this mysql error for our customers in an effective way.

1. Create a new directory

The most effective way to fix this mysql error is to create a new directory on other bigger partition for mysqltmp.

Therefore, we follow the below steps to create a new directory for temporary mysql information.

Initially, we choose the /home directory because in 99% of the servers it is located in the biggest partition.

1. Initially, we log into the server via SSH.

2. Then, we enter the below commands to create a new directory.

mkdir /home/mysqltmp

3. Next, we set up the correct permission to the new directory /mysqltmp with the following command.

chown mysql:mysql /home/mysqltmp

4. Then we open /etc/my.cnf using a text editor and add following under [mysqld] section.

tmpdir=/home/mysqltmp

5. At last, we restart the MYSQL service.

service mysql restart

After this, we confirm that the error has been solved. For that, we enter this command.

mysqladmin variables|grep tmpdir

If the result have the new directory path for tmpdir, we can confirm a new directory for mysqltmp is created.

mysqladmin variables|grep tmpdir
| tmpdir | /home/mysqltmp |

That’s it, the MYSQL error writing file errcode 28 has been solved.

2. Empty the /tmp folder to fix MYSQL error writing file errcode 28

Recently, one of our customers had a problem with his website. Some of the URLs stopped working and started throwing 500 internal server error.

On checking the /var/logs/mysqld.log, our Support Engineers found that no space left in the partition in which /tmp folder is located.

Therefore, we first stopped the MYSQL service using the below command.

service mysql stop

Then, we cleared /tmp folder. After this, we restarted the MYSQL service.

service mysql restart

That’s it.

3. Change the tmpdir location for mysql

Similarly, the other solution for this error is to change the tmpdir location for mysql. Therefore, we fixed this mysql error by changing the tmpdir location for mysql to another location.

For that, we took the following steps.

1. Firstly, we opened the /etc/my.cnf file.

2. Then, we set tmpdir location to a new folder in a different partition.

tmpdir=/mnt/logs/tmpdir

That’s it. Thus we fixed this error for our customers.

4. Re-arrange the server space

In this case, we’ll try to re-arrange the customer’s server space or we ask them to create larger /tmp partition and rearrange the server space.

But, it can cause server downtime. Also, it takes one more day to complete this process.

[Need more assistance to fix the MYSQL error writing file errcode 28? We’ll help you.]

Conclusion

In short, the MYSQL error errcode 28 means that there is insufficient space for MySQL to create temp files. Also, this error was causing the SQL commands not to work properly. Today, we saw how our Support Engineers fixed this error for our customers.