ERROR 1878 (HY000): Temporary file write failure

Question

I am executing a query

ALTER TABLE message ADD COLUMN syncid int(10) NOT NULL DEFAULT 0;

MySQL returned error:

ERROR 1878 (HY000): Temporary file write failure.

---

message table info:

engine type:InnoDB

rows:15786772

index length:1006.89 MB

data length:11.25 GB

How to fix it?

Answer 1

Sorry for the late answer or digging up this old topic, but the following tools can help you with that:

• pt-online-schema-change
• github/gh-ost

Both tools recreate the table in the fashion that @RandomSeed proposed, but in a simpler way.

However please ensure that there is enough space on the file system. Those tools don't need more space in the temporary folder, which is interesting when you're mounting your temporary folder on a separate drive / RAMdisk.

Answer 2

MySQL implements ALTER TABLE as a table re-creation, so two copies of the table exists on the system at some stage during the process. You will need over 12 GB free space for this operation.

Free some space. Alternatively, set your server to use a different temporary directory, where there is enough space.

---

Alternative to the alternative (the WHILE might need to be wrapped in a stored procedure):

• create a new table (temp_table) with the new structure
• transfer data in small batches from original_table into temp_table
• drop original_table and rename temp_table

-- useful only if concurrent access is allowed during migration
LOCK TABLES original_table WRITE, temp_table WRITE;

SELECT COUNT(*) INTO @anythingleft FROM original_table;
WHILE @anythingleft DO
    -- transfer data
    INSERT INTO temp_table
    SELECT
        original_table.old_stuff,
        "new stuff"
        FROM original_table
        ORDER BY any_sortable_column_with_unique_constraint -- very important!
        LIMIT 1000; -- batch size, adjust to your situation

    DELETE FROM original_table
    ORDER BY any_sortable_column_with_unique_constraint
    LIMIT 1000; -- ORDER BY and LIMIT clauses MUST be exactly the same as above

    SELECT COUNT(*) INTO @anythingleft FROM original_table;
END WHILE;

-- delete, rename
DROP TABLE original_table;
UNLOCK TABLES;
RENAME TABLE old_table TO original_table;

If your table uses InnoDB, a more elaborate solution is possible with SELECT ... FOR UPDATE; instead of table locks, but I trust you get the idea.