How to resolve the "Storage Space Full" error in MySQL

When encountering a “storage space full” error in MySQL, it is likely due to insufficient space to create temporary files needed by the database. This article will guide you through the steps to resolve this issue.

Checking Storage Space

First, you need to check the storage space allocated to the /tmp directory. You can do this by running the following command:

df -h

This command will display the storage space allocated to each partition, including /tmp.

Solutions

If the /tmp directory is in its own partition and is short of space, you can either:

1. Modify /tmp so that its partition has more space (either by reallocating or moving it to the main partition).
2. Modify the MySQL configuration file so that it uses a different temp folder on a different partition, e.g. /var/tmp.

Modifying /tmp Partition

To modify the /tmp partition, you can follow the steps provided in this example: Link to example

Changing MySQL Temp Folder

To change the temp folder used by MySQL, follow these steps:

1. Open the MySQL configuration file (usually located at /etc/my.cnf or /etc/mysql/my.cnf).
2. Locate the [mysqld] section in the configuration file.
3. Add or modify the following line:

   tmpdir = /var/tmp
   

   Replace /var/tmp with the desired directory path.
4. Save the changes and restart the MySQL service.

Additional Resources

• MySQL Writing File Error
• MySQL Documentation: Server System Variables - tmpdir