MySQL ERROR 1878 (HY000)：临时文件写入失败

ERROR 1878 (HY000): Temporary file write failure

提问by dadait

I am executing a query

我正在执行查询

ALTER TABLE messageADD COLUMN syncidint(10) NOT NULL DEFAULT 0;

ALTER TABLE messageADD COLUMN syncidint(10) NOT NULL DEFAULT 0;

MySQL returned error:

MySQL 返回错误：

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

消息表信息：

引擎类型：InnoDB

行数：15786772

索引长度：1006.89 MB

数据长度：11.25 GB

How to fix it?

如何解决？

回答by RandomSeed

MySQL implements ALTER TABLEas 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.

MySQL 实现ALTER TABLE为表重新创建，因此在此过程中的某个阶段，系统上存在表的两个副本。此操作将需要超过 12 GB 的可用空间。

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

释放一些空间。或者，将您的服务器设置为使用其他有足够空间的临时目录。

---

---

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

替代方案的替代方案（WHILE可能需要包装在存储过程中）：

• create a new table (temp_table) with the new structure
• transfer data in small batches from original_tableinto temp_table
• drop original_tableand rename temp_table

• temp_table使用新结构创建一个新表 ( )
• 将小批量数据从小批量传输original_table到temp_table
• 删除original_table并重命名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.

如果您的表使用 InnoDB，则可以使用SELECT ... FOR UPDATE;而不是表锁来提供更复杂的解决方案，但我相信您明白这一点。