mysql> \! cat /etc/os-release
NAME="CentOS Linux"
VERSION="8 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

メモリ

2048MB

ストレージ

20.00 GB

MySQL

mysql> status;
--------------

mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)

Connection id:          27
Current database:       hobby
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.21 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 11 hours 56 min 31 sec
Threads: 5  Questions: 1817  Slow queries: 0  Opens: 409  Flush tables: 3  Open tables: 312  Queries per second avg: 0.042

謎現象発動

テーブル作成

CREATE TABLE `auto_increment_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

AUTO_INCREMENTの値を確認。

mysql> show create table auto_increment_test \G;
*************************** 1. row ***************************
       Table: auto_increment_test
Create Table: CREATE TABLE `auto_increment_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

AUTO_INCREMENTの値が初期値のときはDEFAULTと表記される。

バルクインサート

insert into auto_increment_test select 0 from information_schema.TABLES  limit 10;

データ確認。

mysql> select * from auto_increment_test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)

連番がそれぞれ付与されている。

AUTO_INCREMENTの挙動は期待している挙動と一致している。

ここでAUTO_INCREMENTの値を再確認。

AUTO_INCREMENT値再確認

mysql> show create table auto_increment_test \G;
*************************** 1. row ***************************
       Table: auto_increment_test
Create Table: CREATE TABLE `auto_increment_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

AUTO_INCREMENT=16←？

auto_increment_testには、10件のデータしか挿入していないので、AUTO_INCREMENTは10の次である11であるはずだったが、実際は16になっている。

これが今回の謎である。

謎解決

普通に調べたらそれらしいものがヒットした。

yoku0825.blogspot.com

innodb_autoinc_lock_mode が原因だったらしい。

確認します。

mysql> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.01 sec)

記事で紹介されていたのは、innodb_autoinc_lock_mode = 1だったが、自分の環境では「2」だった。

調べてみた。

innodb_autoinc_lock_mode = 2

MySQL8.0からデフォルトになったモード。

innodb_autoinc_lock_mode = 2 （「インターリーブ」ロックモード）

このロックモードでは、「- INSERTlike」ステートメントはテーブルレベルのAUTO-INC ロックを使用せず、複数のステートメントを同時に実行できます。これは最速で最もスケーラブルなロックモードですが、SQL ステートメントがバイナリログから再生されるときにステートメントベースのレプリケーションまたはリカバリシナリオを使用する場合は安全ではあり ません。

このロックモードでは、自動インクリメント値は一意であり、同時に実行されるすべての「INSERT-like」ステートメントにわたって単調に増加することが保証されています。ただし、複数のステートメントが同時に数値を生成する可能性があるため（つまり、数値の割り当てがステートメント間でインターリーブされるため）、特定のステートメントによって挿入された行に対して生成される値は連続しない場合があります。

実行されるステートメントが「単純な挿入」のみであり、挿入される行数が事前にわかっている場合、「混合モード挿入」を除いて、単一のステートメントに対して生成される数にギャップはありません。ただし、「一括挿入」を実行すると、特定のステートメントによって割り当てられた自動インクリメント値にギャップが生じる場合があります。

AUTO_INCREMENTにまつわるであろう記述抜粋。

With innodb_autoinc_lock_mode set to 2 (“interleaved”), there may be gaps in the auto-increment values generated by “bulk inserts,” but only if there are concurrently executing “INSERT-like” statements.

For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.

↓翻訳

innodb_autoinc_lock_modeが2("interleaved")に設定されている場合、"一括挿入 "によって生成されるオートインクリメント値にギャップが生じる可能性がありますが、"INSERTのような "文が同時に実行されている場合に限ります。

ロックモード1または2では、バルク挿入では各ステートメントで必要とされるオートインクリメント値の正確な数がわからず、過大評価される可能性があるため、連続するステートメント間にギャップが発生する可能性があります。

今回のようなバルクインサートを行うとギャップが発生するそうですね。

innodb_autoinc_lock_mode 「1」と「2」で挙動は違うがバルクインサートで連番を保証しないのは同じみたいですね。

連番を保証したいのであれば、innodb_autoinc_lock_mode = 0 と指定するのが良さそうですね。

解決までのプロセス

事柄に結論だけ知りたい人がいると思いますが、自分は「なぜそれが気になった」とか、何を考えながら解決したのかがきになるたちなのでそういう人向けに、少し道のりを書こうかと。

謎発覚

この謎に出会ったのは、前回の記事をみていただければよいのですが、少しだけ説明すると。

updraft.hatenadiary.com

TRUCATEの検証しててテストデータをバルクインサートを使用して作成したときに出会った。

発生方法確認

まず、自分が行ったのはどのようなときに発生するのかです。

バルクインサートで参照するテーブルを変更してみたり、普通のINSERTを実行してみたり、Terminalから接続方法を変えてみたりと発生方法の確立を行いました。

挿入するテーブル先の構造を変えてみたりもしました。

原因箇所を限定するためですね。

これの作業を行うことでバルクインサートを行うと発生すると確信が持てました。

AUTO_INCREMENT調べる

次に行ったのが、AUTO_INCREMENT周りの設定について調べることですね。

一番は公式リファレンスを見ることですね。

ただ、闇雲に見てもわからないので、設定周りであたりをつけて調べてみた。

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0049

この記事をみて、innodb_autoinc_lock_modeの存在を知る。

次にinnodb_autoinc_lock_modeを調べてみた。

それっぽいのがあった

innodb_autoinc_lock_modeで調べてみたら、上から2つ目ぐらいにそれっぽいのがあった。

innodb_autoinc_lock_mode <> 0 でINSERT .. SELECT .. をかけた時にauto_incrementなカラムが歯抜けになる理由を調べてみる

記事を読んで、同じ現象だなと思ったので自分の環境でinnodb_autoinc_lock_modeを確認。

あとは、謎解決に書いてあるとおりである。

おまけ

innodb_autoinc_lock_mode = 1 vs 2 でバルクインサートが競合した時のAUTO_INCREMENTの挙動が違うはなし

を試してみたが、自分の環境ではうまく動作しなかった。

今回したいこととは少し違う気がしたのであまり深く追求しなかった。

〆

日々の覚書

感謝です。

今回調べようとした内容がすべて解説されている。

記事の中でソースコードまで、調べていてすごいなと思った。

そこまで、自分は調べたことはないのでいずれはそこまで調べて原因究明を行ってみたい。

個人的に使用するテーブルとかなら連番のほうが見やすいのでinnodb_autoinc_lock_mode=0でいい気がする。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、AUTO_INCREMENTに対してバルクインサートをしたときの日。

目次

とある日

環境

Oracle VM VirtualBox

DBeaver

Tera Term

ホスト PC

ゲスト PC

OS

メモリ