TL;DR
- なんか見慣れない感じがするのは
ALTER TABLE .. ADD INDEX .., ALGORITHM = INPLACE(Fast Index Creation)の時に起こるエラーだから- コード上は
ALTER TABLE以外にもHash Joinとかでも出るっぽい
- コード上は
- 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
