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

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

 * **[MySQL 8.0でInnoDBのAUTO_INCREMENT問題解消を確認してみる](https://qiita.com/hmatsu47/items/4429171c1bbaba564774)**

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

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

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

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

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

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

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

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

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

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

[先の記事](https://qiita.com/hmatsu47/items/4429171c1bbaba564774)と同様の手順で試してみます（文字コードだけ`utf8mb4`指定となっていますが、テストの本題には関係ありません）。

```sql: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`を見てみます。

```sql: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 を一時停止状態にします。

```sql:Serverless（その3）
mysql> QUIT;
```

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

```sql: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`してみます。

```sql: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 互換のほうでやってみました。

```sql:フェイルオーバ（その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`を見てみます。

```sql:フェイルオーバ（その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関連記事](https://hmatsu47.hatenablog.com/aurora)**