Error encountered while fetching data: Disk full (/tmp/#sql-temptable-X.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”)

Error encountered while fetching data: Disk full (/tmp/#sql-temptable-196ae-c7e7a-5e2f5.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”) DBDISKUSED pcx3_wp324 0 DISKUSED 0 DB pcx3_wp324 USER pcx3_wp261 Error encountered while fetching data: (XID 3gdpnw) The system received an error from the “MySQL” database “mysql”: 1021 (Disk full (/tmp/#sql-temptable-196ae-c7e7a-5e2f6.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”))

When you run certain ALTER or CREATE commands on an InnoDB table, a temporary table is created in MySQL’s tmpdir during the operation. The default value in my.cnf is /tmp.

In cPanel, the size of the /tmp is usually between 3-5GB.

To fix the problem simply delete the temporary files and then restart MySQL.

cd /tmp

and use ncdu to view sizes and D to delete files.

ncdu

service mysqld restart

Afterward, to permanently fix the problem you can do one of the following:

• Increase size of the /tmp
• Set maximum size that MySQL can use for temporary tables
• Move MySQL’s tempdir to another partition such as /home

I would suggest you change the value of tmpdir in my.cnf to be on a disk with more space. Stop MySQL first, change the value in my.cnf and start mysql.

Check the tmpdir value is correct by running:

show variables like "tmpdir";

To change the size of temp tables that MariaDB uses use tmp_disk_table_size

To increase the tmpdir (mount a separate volume) follow this guide: LIMITING MYSQL TMPDIR SIZE