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

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

論理削除にする

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

しかし、物理削除前提で実装してしまっている場合は使用できません。

採番テーブルを使用する

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

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

Web
この記事を書いた人
まっしろ

お家とインターネット大好きな渋谷区在住20代社会人です。
平均睡眠時間は10時間です。

まっしろをフォローする
スポンサーリンク
まっしろブログ

コメント