MySQL ERROR 1878 解决办法

Mike

　　


MySQL ERROR 1878报错解决办法
错误重现
Part1:大表修改字段
mysql>ALTER TABLE `erp`
    -> ADD COLUMN `eas_status`  tinyint(3) unsigned  NOT NULL DEFAULT 0 ' AFTER `totalprice`;
ERROR1878 (HY000): Temporary file write failure.
mysql>\q


这里可以看到，添加字段的时候爆出了1878错误。




Part2:排错过程
先看数据库的error日志

2016-10-27 10:19:22 7f5dca1bf700 InnoDB: Error: Write to file (merge) failed at offset 258998272.
InnoDB: 1048576 bytes should have been written, only 651264 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
2016-10-27 10:35:34 7f5dcb42f700 InnoDB: Error: Write to file (merge) failed at offset 88080384.
InnoDB: 1048576 bytes should have been written, only 782336 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
2016-10-27 10:37:02 7f5dd0076700 InnoDB: Error: Write to file (merge) failed at offset 88080384.
InnoDB: 1048576 bytes should have been written, only 770048 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

查看错误日志可以看到磁盘空间不足




Part3:验证
[root@HE3 helei]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       45G   43G 160M 100% /
tmpfs                 7.9G  1.2M 7.9G   1% /dev/shm
/dev/sda1             477M   90M 358M  21% /boot

可以看到磁盘空间不够了，需要释放磁盘空间，




——总结——
原因是mysql的tmpdir分区的剩余空间大小没有erp大。 mysql在alter table时需要的临时空间要比该表才行。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。