[MySQL]AUTO_INCREMENTのidが戻る

こんにちは、M-Yamashitaです。

今回の記事は、MySQLのAUTO_INCREMENTのidが戻ってしまう話です。
以前、RailsとMySQLを使うサービスにて、Mysql2::Error: Duplicate entry 'xxx' for keyが発生しました。このエラーの原因を調べたところ、テーブルでAUTO_INCREMENTとなっているカラムのidが戻って採番されており、その影響でエラーが発生していることがわかりました。当時の私の認識では、AUTO_INCREMENTとなっているidは、戻って採番されることはないと思っていたので非常に驚きました。
そのため、このidが戻る現象について調べて記事にしたいと思い、執筆しました。

なお、この記事ではMySQL 5.7を使用しています。

この記事で伝えたいこと

• MySQL再起動によりAUTO_INCREMENTのidが戻って採番される

MySQLの再起動によりidが戻って採番される

実例

上記セクション名が結論となっていますが、MySQLの再起動でidが戻って採番されてしまうようです。

例を示します。以下usersテーブルを使用することを考えます。

mysql> SHOW COLUMNS FROM users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  | MUL | NULL    |                |
| email      | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime(6)  | NO   |     | NULL    |                |
| updated_at | datetime(6)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

このテーブルには、すでに複数データが入っている状態です。

mysql> SELECT * FROM users;
+----+------------+-------------------+----------------------------+----------------------------+
| id | name       | email             | created_at                 | updated_at                 |
+----+------------+-------------------+----------------------------+----------------------------+
|  1 | test-name  | [e-mail]  | 2023-04-01 00:00:00.000000 | 2023-04-01 00:00:00.000000 |
|  2 | test-name2 | [e-mail] | 2023-04-02 00:00:00.000000 | 2023-04-02 00:00:00.000000 |
|  3 | test-name3 | [e-mail] | 2023-04-03 00:00:00.000000 | 2023-04-03 00:00:00.000000 |
+----+------------+-------------------+----------------------------+----------------------------+
3 rows in set (0.01 sec)

SHOW TABLE STATUSでAUTO_INCREMENTの値を確認します。

mysql> SHOW TABLE STATUS \G;
・・・
*************************** 5. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 4
    Create_time: 2023-05-16 23:39:59
    Update_time: 2023-05-16 23:39:52
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
5 rows in set (0.01 sec)

Auto_increment: 4となっており、次のAUTO_INCREMENTの値が4であることがわかります。
ここでid=3をDELETEし、再度usersテーブルを表示します。

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

mysql> SELECT * FROM users;
+----+------------+-------------------+----------------------------+----------------------------+
| id | name       | email             | created_at                 | updated_at                 |
+----+------------+-------------------+----------------------------+----------------------------+
|  1 | test-name  | [e-mail]  | 2023-04-01 00:00:00.000000 | 2023-04-01 00:00:00.000000 |
|  2 | test-name2 | [e-mail] | 2023-04-02 00:00:00.000000 | 2023-04-02 00:00:00.000000 |
+----+------------+-------------------+----------------------------+----------------------------+
2 rows in set (0.00 sec)

このときSHOW TABLE STATUSをしても、まだAUTO_INCREMENTが4のままであることがわかります。

mysql> SHOW TABLE STATUS \G;
・・・
*************************** 5. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 4
    Create_time: 2023-05-16 23:46:26
    Update_time: 2023-05-16 23:47:53
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
5 rows in set (0.00 sec)

ここで、MySQLを再起動し、再度SHOW TABLE STATUSをします。

mysql> SHOW TABLE STATUS \G;
*************************** 5. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 3
    Create_time: 2023-05-16 23:46:26
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
5 rows in set (0.00 sec)

Auto_increment: 3となっており、次のAUTO_INCREMENTの値が3に戻ったことがわかります。
実際にINSERTして、usersテーブルを表示します。

mysql> INSERT INTO users (name, email, created_at, updated_at) VALUES ("test-name4", "[e-mail]", "2023-04-04 00:00:00.000000", "2023-04-04 00:00:00.000000");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM users;
+----+------------+-------------------+----------------------------+----------------------------+
| id | name       | email             | created_at                 | updated_at                 |
+----+------------+-------------------+----------------------------+----------------------------+
|  1 | test-name  | [e-mail]  | 2023-04-01 00:00:00.000000 | 2023-04-01 00:00:00.000000 |
|  2 | test-name2 | [e-mail] | 2023-04-02 00:00:00.000000 | 2023-04-02 00:00:00.000000 |
|  3 | test-name4 | [e-mail] | 2023-04-04 00:00:00.000000 | 2023-04-04 00:00:00.000000 |
+----+------------+-------------------+----------------------------+----------------------------+
3 rows in set (0.00 sec)

idが戻って3を採番し、レコードを作っていることがわかります。
よって、MySQLの再起動により、AUTO_INCREMENTのidが戻ってしまうようです。

なぜ再起動で戻るのか？

公式ドキュメントから引用します。

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

InnoDB uses the in-memory auto-increment counter as long as the server runs.

To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

MySQLでは、AUTO_INCREMENTの値はメモリ上に保持しているようです。MySQLサーバーが起動している間は、InnoDBはメモリ上のauto-incrementカウンターを使用します。
サーバー再起動時、AUTO_INCREMENTを初期化するために、InnoDBはSELECT MAX(ai_col) FROM table_name FOR UPDATE;を実行しているようです。

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

これを踏まえると、さきほどのusersテーブルでは、再起動した際にSELECT MAX(id) FROM users FOR UPDATE;が実行されているようです。再起動時のMAX(id)は3なので、AUTO_INCREMENTの値が3に戻ってしまったということになります。

補足

MySQL 8.0では、この再起動によるAUTO_INCREMENTの値が戻る処理が改善されています。

In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and saved to the data dictionary on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

AUTO_INCREMENTのカウンターの値が変わるごとにredoログに書き込まれ、チェックポイントごとにdata dictionaryに保存されます。つまりディスクに保存することになります。よってサーバー再起動でもAUTO_INCREMENTの最大値が保持できるようになっています。

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

(MySQL 5.5.9未満)OPTIMIZE実施でもAUTO_INCREMENTの値が戻る

2006年に起票された不具合では、DELETEしたあとにOPTIMIZE実行でAUTO_INCREMENTの値が戻っていたようです。

We have InnoDB table with auto_increment field.
This table primary used for inserts and deletes only. In any moment it keeps few records only (typically - 0). After some time "show table status" displays very large Data_length while "select count(*)" shows 0. To reuse space we had tried "OPTIMIZE table X" and found, that auto_increment field was droped to 1.

https://bugs.mysql.com/bug.php?id=18274

この不具合は、5.5.9で解消されたようです。

InnoDB: The OPTIMIZE TABLE statement reset the auto-increment counter for an InnoDB table. Now the auto-increment value is preserved across this operation. (Bug #18274)

https://downloads.mysql.com/docs/mysql-5.5-relnotes-en.pdf

おわりに

今回はAUTO_INCREMENTのidが戻ることについて記事を執筆しました。
idが戻る現象は驚きでしたが、MySQLの仕組みから見ると納得できる仕様とわかりました。

この記事が誰かのお役に立てれば幸いです。

参考記事

• MySQL サーバーを再起動するとAUTO_INCREMENT の値が戻る
• MySQLのInnoDBでは最適化でオートインクリメントが戻るのでしょうか？
• 【MySQL】AUTO_INCREMENTの値が戻る＠InnoDBエンジンのテーブル
• ようこそ…『男の世界』へ…（AUTO_INCREMENTが巻き戻るお話）