我在给message表增加字段时报错。
使用的SQL
ALTER TABLE message ADD COLUMN syncid int(10) NOT NULL DEFAULT 0;
MYSQL报错如下：
ERROR 1878 (HY000): Temporary file write failure.
以下是message表的信息：
表类型:InnoDB
行数:15786772
索引长度:1006.89 MB
数据长度:11.25 GB
已经排除了磁盘空间不够的问题。
服务器64G内存还有50%以上的剩余。
服务器操作系统 CentOS 64位。
MySQL Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using EditLine wrapper
附加一部分到出现报错时候的strace日志。
2014-08-26 17:18:29 39108 [ERROR] InnoDB: Could not find a valid tablespace file for 'cfm/#sql-ib1445'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2014-08-26 17:18:29 39108 [ERROR] InnoDB: Tablespace open failed for '"cfm"."#sql-ib1445"', ignored.
2014-08-26 17:18:29 39108 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-26 17:18:30 39108 [Note] InnoDB: Waiting for purge to start
2014-08-26 17:18:30 39108 [Note] InnoDB: 5.6.15 started; log sequence number 919641462089
2014-08-26 17:18:30 39108 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2014-08-26 17:18:30 39108 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2014-08-26 17:18:30 39108 [Note] Server socket created on IP: '0.0.0.0'.
2014-08-26 17:18:30 39108 [Note] Event Scheduler: Loaded 0 events
2014-08-26 17:18:30 39108 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.15' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
2014-08-26 17:22:36 7f99b0686700 InnoDB: Error: Write to file (merge) failed at offset 7486832640.
InnoDB: 1048576 bytes should have been written, only 663552 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
已解决问题了，原因是mysql的tmpdir分区的剩余空间大小没有message大。
mysql在alert table时需要的临时空间要比该表才行。