ようこそ…『男の世界』へ…（AUTO_INCREMENTが巻き戻るお話）

AUTO_INCREMENTが巻き戻る

今の会社に入るまで知らなかったんですが、結構有名なバグっぽいですね。

AUTO_INCREMENTで採番された番号が、再起動するとMySQL5.7以前は巻き戻る現象が起きる話です。

再現してみる

MySQL5.7と8.0にそれぞれ同じテーブルを作ってデータを入れ、最新の行を削除します。

共通

mysql> use test;
Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t1 where id = 3;
Query OK, 1 row affected (0.00 sec)

MySQL5.7.29の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AUTO_INCREMENTは4ですね。

MySQL8.0.19の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

同じくAUTO_INCREMENTは4ですね。

MySQLを再起動すると・・・

MySQL5.7.29の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AUTO_INCREMENT=3 になってる！（4から3に巻き戻ってる）

でも、information_schema.tablesで確認すると4のまま…。

MySQL8.0.19の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AUTO_INCREMENT=4 のままですね。

ドキュメントやソース

この変更についてはこの8.0のドキュメントに記載されています。

Google先生等を使ってそれっぽく訳したものをあげておきます。

MySQL 5.7 以前のバージョンでは、オートインクリメントカウンタはメインメモリにのみ保存され、ディスクには保存されません。 サーバの再起動後にオートインクリメントカウンタを初期化するために、InnoDB は AUTO_INCREMENT カラムを含むテーブルへの最初の挿入時に以下のステートメントと同等のものを実行します。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

MySQL 8.0では、この動作が変更されています。 現在のオートインクリメントカウンタの最大値は、変更されるたびに REDO ログに書き込まれ、チェックポイントごとにエンジン・プライベート・システム・テーブルに保存されます。 これらの変更により、現在のオートインクリメントカウンタの最大値は、サーバーの再起動時にも持続します。

通常のシャットダウン後のサーバー再起動時に、InnoDB は、データ辞書システムテーブルに保存されている現在の最大オートインクリメント値を使用して、 メモリ内オートインクリメントカウンタを初期化します。

REDOログに書き込んでいると言っているのがソース上だとこの辺

そのdict_table_autoinc_log()のドキュメントはここ

バグだったのが8で直ったという扱い

2003年に見つかって、約20年経って直ったんですねえ…。

5.7以前でも巻き戻らないようにしたいという人は

(5.6以降ならオンラインDDL扱いなので)ストアドプロシージャ作って、そこから値を取ればシーケンスのような挙動はできますね。

• 作るときはinformation_schema.tablesのauto_incrementカラムの値を取得して、
• alter table 文をでauto_incrementの値を変更

5.5以前だとauto increment使わずにプロシージャと専用テーブルかな？（さすがにやりすぎか）

ところでタイトルの意味は？

ようこそ…『男の世界』へ…

ジョジョの奇妙な冒険 Part7 スティール・ボール・ランのキャラクターの一人リンゴォ・ロードアゲインのセリフから。

このキャラがきっかり時を6秒『巻き戻す』能力を持っていることから引用。

ただそれだけｗ

ぶっちゃけ

ソースの場所とかyoku0825さんに教えてもらったんですよね。

本当にいつもありがとうございます。

参考

• https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
• https://lefred.be/content/bye-bye-bug-199/
• https://bugs.mysql.com/bug.php?id=199
• https://www.s-style.co.jp/blog/2018/08/2284/