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

mysql alter
asterisk user picture asterisk user · Jan 23, 2015 · Viewed 7k times · Source

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

wingr picture wingr · May 28, 2016

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.

Related questions

How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is: ALTER TABLE `tbl_magazine_issue` DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users` But I got an error: #1064 - You have an error in your SQL syntax; check the …

Read More
How to add new column to MYSQL table?

I am trying to add a new column to my MYSQL table using PHP. I am unsure how to alter my table so that the new column is created. In my assessment table I have: assessmentid | q1 | q2 | q3 | q4 | …

Read More
How to move columns in a MySQL table?

Currently I am having the following MySQL table: Employees (empID, empName, department); I want to change the table to the following: Employees (empID, department, empName); How can this be done using ALTER statements? Note: I want to change only column …

Read More