MySQL で、 AUTO INCREMENT の値が時々リセットされる原因

はじめに

MySQL 5.6 の InnoDB データベースで AUTO INCREMENT のカウンター値が時々リセットされ、物理削除後の値が再度使用されてしまう可能性がある原因と対処法を紹介します。

現象

私が関わっていたプロジェクトで、AUTO INCREMENT のカラムなのに物理削除後の値が再利用されるという不具合が発生しました。
ユーザーテーブルとして、会員 ID をプライマリキーかつ AUTO INCREMENT で設定しており、削除したらテーブルから物理削除する仕様になっていました。
ある日、削除済みの会員 ID と、登録されていた会員 ID が重複するという不具合が発生しました。

原因

MySQL5 系の InnoDB データベースでは、再起動されると AUTO INCREMENT カウンターがリセットされ、下記の SQL 結果の値に設定されると公式ドキュメントに記載されています。
SELECT MAX(ai_col) FROM t FOR UPDATE;`
MySQL :: MySQL 5.7 Reference Manual :: 14.6.1.6 AUTO_INCREMENT Handling in InnoDB
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
MySQL が再起動されると連番の一番大きい値にカウンターがリセットされるという実装になっています。
つまり、最大値のレコードが物理削除された後に再起動されると、その値が再度使用されてしまうということです。AUTO INCREMENT カウンターをシーケンスであると完全に信頼して設計していたので、拍子抜けしましたし、実装を理解せずに使用して恥ずかしいです。

対処方法

MySQL のバージョンアップ

MySQL8 系ではこの実装が変更されています。
公式ドキュメントによると、カウンター値をメモリでなくディスクに保存するように変更されたので、MySQL の再起動後もカウンター値が維持されるとのことです。
バージョンアップできれば、バージョンアップで対処できます。
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 is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.
MySQL :: MySQL 8.0 Reference Manual :: 15.6.1.6 AUTO_INCREMENT Handling in InnoDB
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

論理削除にする

論理削除にすれば、レコードが削除されないのでリセットされても影響はなくなります。

採番テーブルを使用する

今回のプロジェクトでは、急遽古き良き採番テーブルを使用する方法を取りました。

まとめ

特定の RDBMS の機能に依存するのは手軽ですが、仕様を確認して見極めて使っていきたいです。