Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

B.5.4.3 How MySQL Handles a Full Disk

This section describes how MySQL responds to disk-full errors (such as no space left on device), and to quota-exceeded errors (such as write failed or user block limit reached).

This section is relevant for writes to MyISAM tables. It also applies for writes to binary log files and binary log index file, except that references to row and record should be understood to mean event.

When a disk-full condition occurs, MySQL does the following:

  • It checks once every minute to see whether there is enough space to write the current row. If there is enough space, it continues as if nothing had happened.

  • Every 10 minutes it writes an entry to the log file, warning about the disk-full condition.

To alleviate the problem, you can take the following actions:

  • To continue, you only have to free enough disk space to insert all records.

  • To abort the thread, you must use mysqladmin kill. The thread is aborted the next time it checks the disk (in one minute).

  • Other threads might be waiting for the table that caused the disk-full condition. If you have several locked threads, killing the one thread that is waiting on the disk-full condition enables the other threads to continue.

Exceptions to the preceding behavior are when you use REPAIR TABLE or OPTIMIZE TABLE or when the indexes are created in a batch after LOAD DATA INFILE or after an ALTER TABLE statement. All of these statements may create large temporary files that, if left to themselves, would cause big problems for the rest of the system. If the disk becomes full while MySQL is doing any of these operations, it removes the big temporary files and mark the table as crashed. The exception is that for ALTER TABLE, the old table is left unchanged.

Download this Manual
User Comments
  Posted by Yurii Zborovs'kyi on May 5, 2003
Another exception is a SELECT with GROUP at several joins, where each of the table has a couple of millions rows and each of them is actually full-scanned.

Example: linguistic task to find (some top of) spreads of combinations of 2, 3, 4, 5 patterns each of 'A%' type (all words with 1st letter same).

When I was calculated comb of 4 patterns, the query was almost 5 hours long (@1.1GHz Athlon) and required about 1.6 Gb of free memory at tmpdir disk (measured after first get my query aborted due to full disk)!

Do you fill power? ;-)


  Posted by J Jorgenson on July 17, 2006
Be aware that the Database does NOT return until there is disk space, or the thread is *manually* killed.

It would be nice to have the option to (programatically) 'abort' a SQL query, sooner than later, when there is not sufficient disk space.

We've had processes 'hang' for hours until the DBA noticed a full disk situation. Once the disk space was freed, THEN the thread generated an error (go figure?).

  Posted by Galt Barber on August 24, 2006
On our linux servers, it is defaulting to /tmp which is not a large volume on our servers. When making indexes on big tables (80 million rows 3GB data, 1.5GB index), show processlist starts out saying "Repair with sorting" but soon switches to the dreaded "Repair by keycache", which means it does not fail with an error message but continues, albeit very very slowly (12 hours). To fix the problem we just restarted the mysqld server using a larger temp directory with --tmpdir=/plenty-of-space-here and that is so much better! Just 28 minutes now!
You can also set $TMPDIR instead. See the documentation.

See also

  Posted by Bas Meijer on September 17, 2007
There is no way to symlink to a new SAN mount, there is an open filehandle there. If only myisamchk would open the $TMPDIR paths in order as suggested by the man page, or if it would try to create the tmpfile anew with the path in the error then I could create a big enough disk without interupting the repair
(with a 100Gb table you get tired of waiting for mysql)
Sign Up Login You must be logged in to post a comment.