ALTER TABLE .. ADD KEY .. がERROR 1878 (HY000): Temporary file write failure. で失敗する

TL;DR

• なんか見慣れない感じがするのは ALTER TABLE .. ADD INDEX .., ALGORITHM = INPLACE (Fast Index Creation)の時に起こるエラーだから
  • コード上は ALTER TABLE 以外にもHash Joinとかでも出るっぽい
    • https://github.com/search?q=repo%3Amysql%2Fmysql-server+ER_TEMP_FILE_WRITE_FAILURE&type=code
• innodb_tmpdir の容量が足りないと起こる
• なので他に容量があるなら innodb_tmpdir の場所を変えるか、 tmpdir の場所を変える ( innodb_tmpdir に値を設定しなかった場合は tmpdir の値がコピーされるから)
• ストレージ容量の問題なので、 innodb_sort_buffer_size の値を変えたところで意味はなさそう

---

datadirに /tmp/datadir , tmpdirに /tmp/tmpdir を指定してmysqldを起動する。この時、 /tmp/tmpdir が100MBしか使えない状態にしておいた。

$ dd if=/dev/zero of=~/100mb bs=1M count=100
$ mkfs -t xfs ~/100mb
$ mkdir -p /tmp/tmpdir
$ sudo mount ~/100mb /tmp/tmpdir
$ sudo chown yoku0825. /tmp/tmpdir

$ /usr/mysql/8.4.8/bin/mysqld --no-defaults --datadir=/tmp/datadir --initialize-insecure
$ /usr/mysql/8.4.8/bin/mysqld --no-defaults --datadir=/tmp/datadir --tmpdir=/tmp/tmpdir --daemonize --innodb-buffer_pool_size=10G --local-infile --log-error-verbosity=3

$ df -h /tmp/datadir /tmp/tmpdir/
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/ocivolume-root   36G   24G   12G  67% /
/dev/loop0                   95M  6.0M   89M   7% /tmp/tmpdir

ダミーデータを突っ込む。

mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE d1.t1 (num SERIAL, val VARCHAR(32));
Query OK, 0 rows affected (0.02 sec)

mysql> SET SESSION sql_log_bin = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA LOCAL INFILE '/usr/mysql/md5_10000000' INTO TABLE d1.t1;
Query OK, 10000000 rows affected (29.93 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
Query OK, 0 rows affected (4.32 sec)

mysql> SET SESSION sql_log_bin = ON;
Query OK, 0 rows affected (0.00 sec)

ALTER TABLE ( CREATE INDEX でもいい ) でFast Index Creationを起こして ERROR 1878 (HY000): Temporary file write failure. を起こす。

mysql> SELECT @@tmpdir, @@innodb_tmpdir;
+-------------+-----------------+
| @@tmpdir    | @@innodb_tmpdir |
+-------------+-----------------+
| /tmp/tmpdir | NULL            |
+-------------+-----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE d1.t1 ADD KEY (val);
ERROR 1878 (HY000): Temporary file write failure.

$ cat /tmp/datadir/yoku0825-sandbox.err
..
2026-03-01T09:47:53.068413Z 0 [Warning] [MY-012637] [InnoDB] 61440 bytes should have been written. Only 16384 bytes written. Retrying for the remaining bytes.
2026-03-01T09:47:54.401197Z 0 [Warning] [MY-012637] [InnoDB] 61440 bytes should have been written. Only 16384 bytes written. Retrying for the remaining bytes.
2026-03-01T09:47:54.403201Z 0 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2026-03-01T09:47:54.403226Z 0 [ERROR] [MY-012639] [InnoDB] Write to file (ddl) failed at offset 25559040, 61440 bytes should have been written, only 0 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.
2026-03-01T09:47:54.403245Z 0 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2026-03-01T09:47:54.403252Z 0 [Note] [MY-012641] [InnoDB] Refer to your operating system documentation for operating system error code information.
2026-03-01T09:47:54.404041Z 0 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.

innodb_tmpdir を別の場所（今回は100MB制限をしてない /tmp ）に移す。

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

$ /usr/mysql/8.4.8/bin/mysqld --no-defaults --datadir=/tmp/datadir --tmpdir=/tmp/tmpdir --daemonize --innodb-buffer_pool_size=10G --local-infile --log-error-verbosity=3 --innodb_tmpdir=/tmp

mysql> SELECT @@tmpdir, @@innodb_tmpdir;
+-------------+-----------------+
| @@tmpdir    | @@innodb_tmpdir |
+-------------+-----------------+
| /tmp/tmpdir | /tmp            |
+-------------+-----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE d1.t1 ADD KEY (val);
Query OK, 0 rows affected (2 min 22.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

成功した。
innodb_tmpdir を未指定にして、 tmpdir を /tmp に移す。

mysql> ALTER TABLE d1.t1 DROP KEY val;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

$ /usr/mysql/8.4.8/bin/mysqld --no-defaults --datadir=/tmp/datadir --tmpdir=/tmp/tmpdir --daemonize --innodb-buffer_pool_size=10G --local-infile --log-error-verbosity=3 --tmpdir=/tmp

mysql> SELECT @@tmpdir, @@innodb_tmpdir;
+----------+-----------------+
| @@tmpdir | @@innodb_tmpdir |
+----------+-----------------+
| /tmp     | NULL            |
+----------+-----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE d1.t1 ADD KEY (val);
Query OK, 0 rows affected (2 min 18.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

成功する。
最後に tmpdir も innodb_tmpdir もサイズ制限がある状態に戻して、オプションを足してテーブルリビルドにすればいけるか試した。

mysql> ALTER TABLE d1.t1 DROP KEY val;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

$ /usr/mysql/8.4.8/bin/mysqld --no-defaults --datadir=/tmp/datadir --tmpdir=/tmp/tmpdir --daemonize --innodb-buffer_pool_size=10G --local-infile --log-error-verbosity=3

mysql> SELECT @@tmpdir, @@innodb_tmpdir;
+-------------+-----------------+
| @@tmpdir    | @@innodb_tmpdir |
+-------------+-----------------+
| /tmp/tmpdir | NULL            |
+-------------+-----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE d1.t1 ADD KEY (val);  -- 何もしなければ失敗する
ERROR 1878 (HY000): Temporary file write failure.

mysql> ALTER TABLE d1.t1 ADD KEY (val), Engine = InnoDB;  -- Engine = InnoDBを付けても実オペレーションのADD KEYがあるからテーブルリビルドになってくれない
ERROR 1878 (HY000): Temporary file write failure.

mysql> ALTER TABLE d1.t1 ADD KEY (val), FORCE;  -- FORCEをつけても同上
ERROR 1878 (HY000): Temporary file write failure.

mysql> ALTER TABLE d1.t1 ADD KEY (val), ALGORITHM = COPY;  -- ALGORITHM=COPYならさすがにテーブルコピーなので通った

Query OK, 10000000 rows affected (7 min 53.70 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

↓確かにHash Join周りでも出そう（まだ遭遇したことはない）

$ perror 1878
MySQL error code MY-001878 (ER_TEMP_FILE_WRITE_FAILURE): Temporary file write failure.

$ global -g ER_TEMP_FILE_WRITE_FAILURE
include/mysqld_ername.h
include/mysqld_errmsg.h
include/mysqld_error.h
sql/handler.cc
sql/iterators/composite_iterators.cc
sql/iterators/hash_join_chunk.cc
sql/iterators/hash_join_iterator.cc
storage/innobase/handler/handler0alter.cc

​