他社が制作したWebサイト( PHP + MySQL )で、新規登録したユーザーの履歴に他人の情報が紐付いているのが見つかったので、修正したいと依頼があって初めて知ったこと。
提供頂いたデータを確認すると新規登録したユーザーレコードの作成日付より古い履歴情報レコードと紐付いているデータが数件あったため、いろいろ調べてみるとシステムメンテナンスを実施した後に発生していることが判明。
テスト環境を構築して、さらに調べるとAUTO_INCREMENT で割り当てられた最新番号(最後)のレコードが削除された後で、MySQLサーバー(MySQLd)を再起動して新しくレコードを追加すると「MySQLd 再起動前に削除した最新番号」が再利用されていた。
MySQL のマニュアルを調べてみると… 「14.6.5.1 従来の InnoDB の自動インクリメントロック」に
InnoDB テーブルに AUTO_INCREMENT カラムを指定すると、InnoDB データディクショナリ内のテーブルハンドルに、カラムに新しい値を割り当てる際に使用される自動インクリメントカウンタと呼ばれる特別なカウンタが含まれます。このカウンタは、ディスク上には格納されず、メインメモリー内にのみ格納されます。
InnoDB では、ai_col という名前の AUTO_INCREMENT カラムを含むテーブル t に自動インクリメントカウンタを初期化するために、次のようなアルゴリズムが使用されます。サーバーの起動のあとで、テーブル t への最初の挿入をするために、InnoDB は次のステートメントと同等なものを実行します。
SELECT MAX(ai_col) FROM t FOR UPDATE;
14.6.5.1 従来の InnoDB の自動インクリメントロック
と記載があり、MySQL の仕様でした。
MySQL の InnoDB テーブルでは、AUTO_INCREMENT の番号って、メモリ上で管理されていて MySQLd を再起動するとデータベース内のデータの最大値から再設定されるようになっていたのですね。
MySQL も PostgreSQL の「シリアル型」と同様にデータベースにAUTO_INCREMENT の値を持っていると考えていたこともあり、AUTO_INCREMENT は、削除しても「欠番」になり再利用されないと思い込んでいたので、意外な落とし穴があることに今更ながら驚いた。