MySQL ERROR 1878 報錯解決辦法
錯誤重現
Part1: 大表修改欄位
mysql> ALTER TABLE `erp`
-> ADD COLUMN `eas_status` tinyint(3) unsigned NOT NULL DEFAULT 0 ‘ AFTER `totalprice`;
ERROR 1878 (HY000): Temporary file write failure.
mysql> q
這裏可以看到，新增欄位的時候爆出了 1878 錯誤。
Part2: 排錯過程
先看資料庫的 error 日誌
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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@dev1 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 時需要的臨時空間要比該表才行。由於筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。