mysqld fills /tmp with file /tmp/#sql_*****.MAD

Symptoms

You will receive a notification that the /tmp partition is full. In the MySQL error log, the following entries will be found:

[Warning] mysqld: Disk is full writing '/tmp/#sql_*****.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

[ERROR] mysqld: Disk full (/tmp/#sql_*****.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

Description

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. When you are loading the data, /tmp is being filled with files named similar to this:

#sql_****.MAD
#sql_****.MAI

Workaround

There are a few different ways this can be addressed:

• You can use tmpwatch to reduce disk usage in /tmp:
  How-to clean your /tmp directory with tmpwatch
• You can move the /tmp directory to a different, larger partition:
  How-To move the MySQL tmpdir.
• If allowed by your hosting provider, you may also raise the size of the /tmp partition:
  How to increase the size of the cPanel-generated /tmp filesystem