[ERROR] InnoDB: Write to file (merge)failed at offset 4249878528, 1048576 bytes should have been wri

最新推荐文章于 2024-04-19 11:08:54 发布

原创最新推荐文章于 2024-04-19 11:08:54 发布 · 1.6k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #db

mysql 专栏收录该内容

20 篇文章

订阅专栏

在尝试为数据库添加索引时遇到错误1878，日志显示磁盘空间不足，导致临时文件写入失败。分析发现是由于[tmp]目录空间不足。尽管尝试通过设置全局变量tmpdir改变临时文件存储位置失败，但由于生产环境无法停机，解决方案是直接在操作系统层面扩大[/tmp]分区的空间。

在给数据库添加索引时，提示失败“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分区的空间了。