在给数据库添加索引时,提示失败“ERROR 1878 (HY000) at line 1: Temporary file write failure.”,查看后台日志提示磁盘空间不足。
2022-10-18T15:27:14.349897+08:00 5659887 [Warning] InnoDB: Retry attempts for writing partial data failed.
2022-10-18T15:27:14.394634+08:00 5659887 [ERROR] InnoDB: Write to file (merge)failed at offset 4249878528, 1048576 bytes should have been written, only 684032 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2022-10-18T15:27:14.394738+08:00 5659887 [ERROR] InnoDB: Error number 28 means 'No space left on device'
经过分析后发现是/tmp目录空间不足,登录数据库后进行参数修改:
mysql> show variables like '%tmp%'
-> ;
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| default_tmp_storage_engine | InnoDB |
| innodb_tmpdir | |
| internal_tmp_disk_storage_engine | InnoDB |
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
+----------------------------------+----------+
7 rows in set (0.00 sec)
mysql> set global tmpdir='/data1/tmp';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable
mysql>
这个/tmpdir是个只读参数,只能修改配置文件后重启服务器。但这是生产主从环境,没有停机的时间点,故只能够从操作系统层面增加/tmp分区的空间了。