Disk full error in MySQL logs.

-------------------

81218 14:53:40 [ERROR] /usr/libexec/mysqld: Disk is full writing ‘/tmp/#sql_5047_21.MYI’ (Errcode: 28).

Waiting for someone to free space... Retry in 60 secs
-------------------

This occurs when /tmp runs out of space. Check the size of /tmp partition. It should be atleast 3 times as the size of largest table.

Execute following query to list tables with large size.

-------------------
mysql>SELECT concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M') rows,
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G')idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac FROM 
information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10;
-------------------

Solution

MySQL uses an environment variable “TMPDIR” to store temporary files. If you don’t have TMPDIR set in my.cnf, MySQL uses the system default, which is normally /tmp, /var/tmp, or /usr/tmp.

So set “TMPDIR” as any directory with sufficient size and restart MySQL.

Eg:-

TMPDIR=/backup/mysqltemp