错误1878(HY000):临时文件写入失败 [英] ERROR 1878 (HY000): Temporary file write failure

问题描述

我正在执行查询

ALTER TABLE message添加列syncid int(10)NOT NULL DEFAULT 0;

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

MySQL返回错误:

MySQL returned error:

ERROR 1878 (HY000): Temporary file write failure.

---

消息表信息:

message table info:

引擎类型:InnoDB

engine type:InnoDB

行:15786772

rows:15786772

索引长度:1006.89 MB

index length:1006.89 MB

数据长度:11.25 GB

data length:11.25 GB

如何解决?

推荐答案

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

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.

替代方法(WHILE可能需要包装在存储过程中):

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

• 使用新结构创建新表(temp_table)
• 将数据从original_table小批量传输到temp_table
• 放下original_table并重命名temp_table

• 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;

如果您的表使用InnoDB，则可以使用SELECT ... FOR UPDATE;(而不是表锁)更精细的解决方案，但是我相信您会明白的.

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.

这篇关于错误1878(HY000):临时文件写入失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！