実装中に奇妙なことが起きました。
ローカルの開発環境内で構築した MySQL5.7での出来事です。

AUTO_INCREMENTを設定したidを持つテーブルに、INSERT処理を実行しました。
すると、100近くまでカウントされていたidが突然1に戻っていたんです。
そのテーブルは他のテーブルとの関係を多く持っており、これ本番（AWS RDS）もバグっちゃうのでは！？と焦り散らかしましたが、過去そのような事例は起きてなかったみたいなんです。

ガクブルの僕は恐る恐る調査を始めることにしました。

■発生条件の調査

どのタイミングで発生するのだろう。全く予想できない。
約20個近くあるテーブル全てにAUTO_INCREMENTを設定したidが存在するのですが、毎度 1にリセットされてしまうテーブルは決まって1つのテーブルのみ。

何が他のテーブルと違うかなーと考えた時に、1点大きな違いがありました。
それは、 「データ削除の方法」 です。

基本的には「論理削除」を採用しているのですが、とても流動性が高いテーブルだったので、どうやらこのテーブルだけは 「物理削除」 を採用しています。

僕「まさか、、、こいつが関係しているのか？」

いやでも待てよと。何のための AUTO_INCREMENTなんだいと。
その辺を自動で採番してくれるから便利なんだろ AUTO_INCREMENTさんよぉ　と。

正直この疑惑は信じたくなかったです。一気にAUTO_INCREMENTの信用が下がってしまうじゃないかと。
でも違いがこれしかないんです。そこで僕はようやく Google先生に力を借り始めました。

■原因究明

検索ワード：mysql autoincrement id リセット

ほとんどが 「AUTO_INCREMENTをリセットする方法」 という検索結果の中、輝く一件を見つけました。

僕「ピンポイントすぎるうぅぅぅぅ！！」

真っ白に輝くこちらの記事。僕が求めていた内容が載っていました。

こちらの記事を見てみると、

MySQLが再起動されると連番の一番大きい値にカウンターがリセットされるという実装になっています。
つまり、最大値のレコードが物理削除された後に再起動されると、その値が再度使用されてしまうということです。AUTO INCREMENTカウンターをシーケンスであると完全に信頼して設計していたので、拍子抜けしましたし、実装を理解せずに使用して恥ずかしいです。

、、、嘘でしょ？
信じたくなかったことが起きました。やはり物理削除がAUTO_INCREMENTに大きく関係していました。

再起動をするたびに、AUTO_INCREMENTは連番の最大値にカウンターがリセットされていたんです。
ということは、もしも物理削除を実施してすっからかんになったテーブルのAUTO_INCREMENTカウンターが「100」だったとしても、MySQLを再起動するとそのテーブルには1件レコードは入っていないので、「1」からスタートしてしまうということです。

■対策

こちらの記事に掲載されている対策として、3つ紹介がありました。

MySQL8へのバージョンアップ
論理削除にする
採番用のテーブルを作る

この3つの中でどれを採用しようかというところなのですが、私が関わっているサービスは、つい先日ローンチしたばかりです。
この初期段階でMySQLのバージョンアップは提案しづらいというのと、まだまだissueが残っているなかで採番用のテーブルを用意して、約20近くのテーブル群の採番を一つにまとめるのも重たいかなと感じました。

そのため、今回はそのテーブルを物理削除から論理削除に切り替えて、その周辺の処理を修正することになりました。（バグも少なそうだし）

■まとめ

新規開発するなら、MySQL8を採用しようね。
もしもMySQL5系を採用した新規開発をするなら、AUTO_INCREMENTには十分気をつけて、設計段階でシーケンステーブルを用意しようね。
とはいえやっぱり物理削除って本当に怖いから、論理削除を採用しようね。
「まっしろブログ」さんの記事が的確すぎて本当に感謝だね。（以下の参考文献にリンク貼ってますので、そちらも是非）

■追記

書き洩れましたが、なぜ本番環境（AWS RDS）で起きていないかというと、可能性が2点あります。

常に最新の一件が存在した時に再起動が実行されていた。
再起動が実施されなかった。

１は、神的すぎるタイミングなので、おそらく可能性は薄いかと。
2 は、可能性高いです。
https://go-journey.club/archives/9869
こちらの記事によると、数ヶ月に一回セキュリティパッチの適用があって、自動アップデートが走っちゃうこともあるみたいなのですが、まだそれに当たっていないだけかと。。。
遅かれ早かれ起きていたことなので、ローンチしてすぐ発覚してよかった。。。

■参考文献

https://masshiro.blog/mysql-auto_increment-reset/
https://go-journey.club/archives/9869