ERROR 1878 (HY000): Temporary file write failure in mysql

Question

When i am running Alter command in mysql to add a new column to my existing table. I am facing this error.

ERROR 1878 (HY000): Temporary file write failure.

Any idea regarding this? Please guide.

Many Thanks.

Answer 1

This error is most likely resulting from the temporary directory MySQL is using being too small (as others mention in the comments). In many cases altering a table in MySQL will result in the table being duplicated and you will need at least as much free space in the temporary directory as is used by the table being altered.

The destination for the temporary directory is specified in the my.cnf file under the variable tmpdir. The my.cnf is usually stored in /etc/mysql/my.cnf on Ubuntu for example (not sure what OS you are using). You can either change the location the tmpdir variable points toward (to another larger partition), or alter the size available to the directory specified.

On Ubuntu, for example, you can use df -h to see the sizes of the various filesystems available.