MySQL には、5.7 まで**「テーブルの最後のほうの行を削除した後にサーバを再起動すると、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値を使うのをやめて、別途採番テーブル等を用意する
などの方法でトラブルを防ぎましょう。