TL;DR
TempTableストレージエンジンがDisk落ちした時とInnoDB Temporary tableで「同じストレージあふれ」でも微妙にエラー内容が違う
前者は
ERROR 14 (HY000): Can't change size of file (OS errno 28 - No space left on device), エラーログ出力なし後者は
ERROR 1114 (HY000): The table '#sqlXXXXX' is fullでエラーログ出力もありちなみにMyISAMは
ERROR 126 (HY000): Incorrect key file for table '/mytmp/#sqlXXXXX.MYI'; try to repair it、これ少なくとも5.7からは変わってない
実験用に100MBくらいのファイルにxfsを作ってマウントする。
$ dd if=/dev/zero of=./mytmp bs=1M count=100
$ mkfs -t xfs ./mytmp
$ sudo mkdir /mytmp
$ sudo mount /home/yoku0825/mytmp /mytmp
$ sudo chown -R yoku0825. /mytmp ### mysqldをyoku0825ユーザーで動かすので
$ mkdir /mytmp/innodb_temp ### innodb_temp_tablespaces_dirはあらかじめディレクトリがないと転ける
my.cnfで tmpdir (MyISAM, TempTableストレージエンジンが使う)とinnodb_temp_tablespaces_dir に /mytmp を割り当てて再起動。
$ vim /etc/my.cnf
[mysqld]
tmpdir= /mytmp
innodb_temp_tablespaces_dir=/mytmp/innodb_temp ### このディレクトリは自動で作ってくれないので、自分でmkdirしないと起動が転ける
さっさと溢れさせたいのでダミーデータを入れて少しでもテンポラリー領域を使ったらあふれるようにセット。
$ dd if=/dev/zero of=/mytmp/dummy bs=1M count=90 ### さっさと溢れさせたいのでさらにダミー
$ df -h /mytmp
Filesystem Size Used Avail Use% Mounted on
/dev/loop0 97M 96M 676K 100% /mytmp
$ du -sh /mytmp/*
90M /mytmp/dummy
800K /mytmp/innodb_temp
実験1、TempTable on Disk。Diskに落とすために temptable_max_ram を小さくする。
mysql80 8> SHOW VARIABLES LIKE '%temp%';
+-----------------------------+-----------------------+
| Variable_name | Value |
+-----------------------------+-----------------------+
| avoid_temporal_upgrade | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | /mytmp/innodb_temp |
| show_old_temporals | OFF |
| temptable_max_mmap | 1073741824 |
| temptable_max_ram | 1073741824 |
| temptable_use_mmap | ON |
+-----------------------------+-----------------------+
7 rows in set (0.01 sec)
mysql80 8> SHOW VARIABLES LIKE '%tmp%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_tmp_storage_engine | InnoDB |
| innodb_tmpdir | |
| internal_tmp_mem_storage_engine | TempTable |
| replica_load_tmpdir | /mytmp |
| slave_load_tmpdir | /mytmp |
| tmp_table_size | 16777216 |
| tmpdir | /mytmp |
+---------------------------------+-----------+
7 rows in set (0.01 sec)
mysql80 9> SET GLOBAL temptable_max_ram = 2 * 1024 * 1024;
Query OK, 0 rows affected (0.01 sec)
mysql80 9> SELECT @@temptable_max_ram, @@tmp_table_size; -- tmp_table_sizeの方が小さいとInnoDBに落ちてしまう
+---------------------+------------------+
| @@temptable_max_ram | @@tmp_table_size |
+---------------------+------------------+
| 2097152 | 16777216 |
+---------------------+------------------+
1 row in set (0.00 sec)
TempTableストレージエンジンはユーザー定義テンポラリーテーブル ( CREATE TEMPORARY TABLE ) としては使えないので、 WITH RECURSIVE (必ずテンポラリーテーブルを作る) で代用。
mysql80 9> SET SESSION cte_max_recursion_depth = 10000000;
Query OK, 0 rows affected (0.00 sec)
mysql80 9> WITH RECURSIVE v AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM v WHERE n <= 1999999), v2 AS (SELECT n, MD5(n) AS m FROM v) SELECT n, m, md5(m) AS o FROM v2 ORDER BY RAND() LIMIT 1;
ERROR 14 (HY000): Can't change size of file (OS errno 28 - No space left on device)
### エラーログには出力なし
実験2、InnoDB Temporary。暗黙の一時テーブル( SELECT がバックグラウンドで作るやつ ) も ユーザー定義テンポラリーテーブル ( CREATE TEMPORARY TABLE ) もどちらも同じ .ibt ファイルを使う。
はずなんだけど、なんかエラーが違った。
### 暗黙のテンポラリーテーブル版
mysql80 7> SET SESSION tmp_table_size = 1024;
Query OK, 0 rows affected (0.00 sec)
mysql80 7> SELECT @@temptable_max_ram, @@tmp_table_size; -- tmp_table_sizeを超えるとTempTableからInnoDBに落ちる
+---------------------+------------------+
| @@temptable_max_ram | @@tmp_table_size |
+---------------------+------------------+
| 2097152 | 1024 |
+---------------------+------------------+
1 row in set (0.00 sec)
mysql80 7> WITH RECURSIVE v AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM v WHERE n <= 1999999), v2 AS (SELECT n, MD5(n) AS m FROM v) SELECT n, m, md5(m) AS o FROM v2 ORDER BY RAND() LIMIT 1;
ERROR 1146 (42S02): Table './mytmp/#sql45cf_8_0' doesn't exist
### doesn't existって言われた…
### エラーログには出力なし
↑ちなみにdummyファイルを消して、一度実行してからもう一度dummyファイルを作って、もっかいクエリすると↓と同じ table is full になった。テンポラリーテーブルを作る前の下処理っぽいところで失敗してストレージエンジンまで落ちてなかったっぽい。
2023-02-20T14:48:05.879945+09:00 9 [Warning] [MY-012145] [InnoDB] Error while writing 1048576 zeroes to /mytmp/innodb_temp/temp_10.ibt starting at offset 10485760
2023-02-20T14:48:05.880278+09:00 9 [ERROR] [MY-013132] [Server] The table '/mytmp/#sql54e5_9_3' is full! <--- ここがibtファイルのパスじゃなくて tmpdir/内部名 になるの罠い
### ユーザー定義テンポラリーテーブル版
mysql80 8> CREATE TEMPORARY TABLE d1.tt1 (num int, val varchar(32)) Engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql80 8> LOAD DATA INFILE '/home/yoku0825/md5' INTO TABLE d1.tt1;
ERROR 1114 (HY000): The table 'tt1' is full
### エラーログ
2023-02-20T14:32:23.733251+09:00 8 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file /mytmp/innodb_temp/temp_9.ibt, desired size 425984 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-02-20T14:32:23.733494+09:00 8 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2023-02-20T14:32:23.733538+09:00 8 [ERROR] [MY-012639] [InnoDB] Write to file /mytmp/innodb_temp/temp_9.ibt failed at offset 622592, 425984 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.
2023-02-20T14:32:23.733572+09:00 8 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2023-02-20T14:32:23.733589+09:00 8 [Note] [MY-012641] [InnoDB] Refer to your operating system documentation for operating system error code information.
2023-02-20T14:32:23.733603+09:00 8 [Warning] [MY-012145] [InnoDB] Error while writing 425984 zeroes to /mytmp/innodb_temp/temp_9.ibt starting at offset 622592
..
2023-02-20T14:32:28.431025+09:00 8 [ERROR] [MY-013132] [Server] The table 'tt1' is full!
実験3、MyISAM版 (8.0.32では既に internal_tmp_disk_storage_engine が選べないから単に実験)
mysql80 9> CREATE TEMPORARY TABLE d1.tt1 (num int, val varchar(32)) Engine = MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql80 9> LOAD DATA INFILE '/home/yoku0825/md5' INTO TABLE d1.tt1;
ERROR 126 (HY000): Incorrect key file for table '/mytmp/#sql45cf_9_0.MYI'; try to repair it
### エラーログ
2023-02-20T14:36:08.272683+09:00 9 [ERROR] [MY-013134] [Server] Incorrect key file for table '/mytmp/#sql45cf_9_0.MYI'; try to repair it
2023-02-20T14:36:08.272746+09:00 9 [ERROR] [MY-010239] [Server] Got an error from unknown thread, /home/yoku0825/mysql-8.0.32/storage/myisam/mi_write.cc:194
見慣れたやつが出てきた。
