MySQL には、5.7 まで「テーブルの最後のほうの行を削除した後にサーバを再起動すると、AUTO_INCREMENT値が巻き戻る」という問題がありました。

MySQL 8.0でInnoDBのAUTO_INCREMENT問題解消を確認してみる

この仕様は AWS の Aurora（MySQL 互換）でも引き継がれていますが、

Aurora Serverless でしばらくアクセスせずに一時停止（pause）状態になった後に、再度アクセスして復帰（resume）した場合
Aurora Provisioned（通常版）MySQL 互換でフェイルオーバを行った場合

にどうなるかを確認してみます。

※おそらく、この時点で予想は付いていると思いますが。

確認の前に／`AUTO_INCREMENT`値が巻き戻るとなぜマズいのか

マズい理由が分かっている方は飛ばして先へ進んでください。

例えば、以下のようなケースが考えられます。

会員memberと注文ログorder_logの 2 つのテーブルの間で、order_logがmemberの主キーmember_id（AUTO_INCREMENT）を参照している
memberテーブルにmember_id=10で「鈴木さん」のデータが登録される
「鈴木さん」に関する注文ログがorder_logにmember_id=10で登録される
「鈴木さん」がmemberテーブルから削除される（order_logテーブルは放置）
ここでサーバ再起動などでmember_idのAUTO_INCREMENT値が巻き戻る
「佐藤さん」のデータがmemberテーブルに登録される→このとき、member_id=10として登録されてしまう
order_logにある「鈴木さん」の注文ログが「佐藤さん」と紐付いてしまう

明らかに設計や処理手順がマズいのですが、このような処理をしてしまうコードは意外とよく見かけたりします…。

Aurora Serverless で一時停止（pause）→復帰（resume）した場合

先の記事と同様の手順で試してみます（文字コードだけutf8mb4指定となっていますが、テストの本題には関係ありません）。

Serverless（その1）

mysql> CREATE DATABASE test_ai;
Query OK, 1 row affected (0.01 sec)

mysql> USE test_ai;
Database changed
mysql> CREATE TABLE test_ai (id int(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100)) ENGINE innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test_ai SET name = 'first';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_ai SET name = 'second';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_ai SET name = 'third';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | third  |
+----+--------+
3 rows in set (0.01 sec)

mysql> DELETE FROM test_ai WHERE id = 3;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO test_ai SET name = 'fourth';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  4 | fourth |
+----+--------+
3 rows in set (0.00 sec)

ここでid=4の行をDELETEしてSHOW CREATE TABLEを見てみます。

Serverless（その2）

mysql> DELETE FROM test_ai WHERE id = 4;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

次のAUTO_INCREMENT値は5であることが分かります。

ここで一旦接続を切ってしばらく放置し、Aurora Serverless を一時停止状態にします。

Serverless（その3）

mysql> QUIT;

一時停止状態になったら、再度接続してSHOW CREATE TABLEを見てみます。

Serverless（その4）

mysql> USE test_ai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

次のAUTO_INCREMENT値が3に巻き戻ってしまいました。

実際にINSERTしてみます。

Serverless（その5）

mysql> INSERT INTO test_ai SET name = 'fifth';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | fifth  |
+----+--------+
3 rows in set (0.01 sec)

やっぱりid=3になってしまいました。

通常版 Aurora（MySQL 互換）でフェイルオーバを行った場合

今回は 5.6 互換のほうでやってみました。

フェイルオーバ（その1）

mysql> CREATE DATABASE test_ai;
Query OK, 1 row affected (0.02 sec)

mysql> USE test_ai;
Database changed
mysql> CREATE TABLE test_ai (id int(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100)) ENGINE innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test_ai SET name = 'first';
Query OK, 1 row affected (0.00 sec)
（中略）
mysql> INSERT INTO test_ai SET name = 'fourth';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  4 | fourth |
+----+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM test_ai WHERE id = 4;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

フェイルオーバし、再度SHOW CREATE TABLEを見てみます。

フェイルオーバ（その2）

mysql> SHOW CREATE TABLE test_ai\G
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SHOW CREATE TABLE test_ai\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: test_ai

*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.11 sec)

mysql> INSERT INTO test_ai SET name = 'fifth';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | fifth  |
+----+--------+
3 rows in set (0.01 sec)

やはり巻き戻ってしまいました。

というわけで

AUTO_INCREMENT値が巻き戻っても良いように、

不要になった行は確実に削除する（親テーブルの行だけ削除して子テーブルは放置、のようなことはしない）
外部キー制約を正しく使う（必要ならCASCADE）
そもそも物理削除をやめる
AUTO_INCREMENT値を使うのをやめて、別途採番テーブル等を用意する

などの方法でトラブルを防ぎましょう。

Qiitaに投稿したAmazon Aurora関連記事

小ネタ／Aurora Serverlessの一時停止、およびAurora MySQLのフェイルオーバとAUTO_INCREMENT巻き戻り問題

確認の前に／AUTO_INCREMENT値が巻き戻るとなぜマズいのか

Aurora Serverless で一時停止（pause）→復帰（resume）した場合

通常版 Aurora（MySQL 互換）でフェイルオーバを行った場合

というわけで

確認の前に／`AUTO_INCREMENT`値が巻き戻るとなぜマズいのか