やりたかったこと

行を削除したテーブルで、採番を詰め直したい場面がありました。末尾の行を消したので、次の INSERT に空いた ID を使わせたい。

やることは単純です。

DELETE FROM t WHERE id >= 4;
ALTER TABLE t AUTO_INCREMENT = 4;

ALTER TABLE ... AUTO_INCREMENT = N は、N が列の現在の最大値より大きければそのまま採用されます。削除後の MAX(id) + 1 を渡せば安全です。

症状: ALTER したのに値が変わらない

実行後、確認しました。

SELECT AUTO_INCREMENT FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 't';
-- => 6

6 のままです。4 を指定したのに変わっていません。

ここで疑ったのは「N が小さすぎてクランプされた」でした。ドキュメントには最大値以下の値を指定した場合の挙動が書かれているので、そこに引っかかったのだと考えました。念のため SHOW TABLE STATUS でも見ます。

SHOW TABLE STATUS LIKE 't'\G
-- Auto_increment: 6

こちらも 6。2つの独立した確認手段が同じ答えを返したので、「ALTER は効いていない」とほぼ確信しました。

ここが危ないところでした。 効いていないと思い込むと、次にやることは「もっと強い操作」です。テーブルを再構築する、OPTIMIZE TABLE を打つ、最悪の場合はダンプして作り直す。実データが入っているテーブルに対して、必要のない破壊的な操作をしそうになりました。

実際には ALTER は成功していた

先に結論を書きます。試しに1行 INSERT してみると、こうなります。

INSERT INTO t (v) VALUES (99);
SELECT LAST_INSERT_ID();
-- => 4

4 が採番されました。ALTER は最初から正しく効いていました。 嘘をついていたのは確認に使ったクエリのほうです。

原因: information_schema はキャッシュを返している

MySQL 8.0 以降、information_schema.TABLES や information_schema.STATISTICS の統計系カラムは、ストレージエンジンに問い合わせず、データディクショナリのキャッシュから返されます。有効期限はこの変数で決まります。

SHOW VARIABLES LIKE 'information_schema_stats_expiry';
-- information_schema_stats_expiry    86400

既定値は 86400 秒（24時間）。そして ALTER TABLE ... AUTO_INCREMENT は、このキャッシュを無効化しません。だから古い値が返り続けます。

SHOW TABLE STATUS も同じキャッシュから返します。独立した確認手段ではありません。 2つ試して両方同じだったので確信を強めた、というのが完全に裏目に出ていました。

キャッシュを更新すれば正しい値が見えます。

ANALYZE TABLE t;
SELECT AUTO_INCREMENT FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 't';
-- => 4

あるいは、そのセッションだけキャッシュを使わない設定にします。

SET SESSION information_schema_stats_expiry = 0;
-- 以降、統計系カラムは常にストレージエンジンから取得される

ここからが本題: 「事前に確認した」ことが原因だった

ここで妙なことに気づきました。別の日に同じ手順を試したら、ALTER 直後の SHOW TABLE STATUS が正しく 4 を返したのです。

同じ MySQL、同じ手順で結果が違う。何が違うのか切り分けるために、対照実験をしました。違いは1点だけにします。

CASE X — ALTER の前に information_schema を一度も読まない

CREATE TABLE zz_x (id INT AUTO_INCREMENT PRIMARY KEY, v INT) ENGINE=InnoDB;
INSERT INTO zz_x (v) VALUES (1),(2),(3),(4),(5);
DELETE FROM zz_x WHERE id >= 4;
ALTER TABLE zz_x AUTO_INCREMENT = 4;

結果:

SHOW TABLE STATUS      => Auto_increment: 4   ← 正しい
information_schema     => 4                   ← 正しい

CASE Y — ALTER の前に information_schema を1回だけ読む

CREATE TABLE zz_y (id INT AUTO_INCREMENT PRIMARY KEY, v INT) ENGINE=InnoDB;
INSERT INTO zz_y (v) VALUES (1),(2),(3),(4),(5);
DELETE FROM zz_y WHERE id >= 4;

-- ↓ この1行を足すだけ
SELECT AUTO_INCREMENT FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'zz_y';   -- => 6

ALTER TABLE zz_y AUTO_INCREMENT = 4;

結果:

SHOW TABLE STATUS      => Auto_increment: 6   ← 古い
information_schema     => 6                   ← 古い

足したのは ALTER 前の SELECT 1本だけです。 それだけで、ALTER 後の両方の確認手段が24時間ぶん古い値を返すようになります。

機構はこうです。ALTER 前の information_schema 読み取りが、その時点の値（6）でキャッシュを埋めます。キャッシュにはそこから24時間の寿命が与えられます。ALTER TABLE はキャッシュを無効化しないので、以後の読み取りは——SHOW TABLE STATUS も含めて——期限まで 6 を返し続ける。

つまり、変更前に現状を確認するという最も真っ当な手順が、変更後に確認する能力を壊していました。 確認しなければ正しい値が見えた。観測が対象を変えたのではなく、観測が観測器を汚染したわけです。

（検証環境: MySQL 8.4.8 / InnoDB）

実務でどうするか

3つに絞れます。

ALTER TABLE ... AUTO_INCREMENT の確認に information_schema と SHOW TABLE STATUS を使わない。 どちらも同じキャッシュを見ています。確認したいなら ANALYZE TABLE を先に打つか、SET SESSION information_schema_stats_expiry = 0 にしてから読む。

最も確実な確認は、実際に1行 INSERT して LAST_INSERT_ID() を見ること。 キャッシュ層を一切通りません。使い捨ての行なら後で消せます。

「確認できない」を「失敗した」と読み替えない。 これが一番大事です。確認手段が古い値を返しているとき、対象は正常なのに壊れているように見えます。この状態で破壊的な操作へ進むのが最悪の分岐です。読み取り経路そのものを疑ってから、次の操作を決める。

なお、この統計キャッシュは AUTO_INCREMENT だけの話ではありません。SHOW TABLE STATUS の Rows も同じで、実際の行数が3件のときに 5 を返していました。行数の概算値がずっと合わない、という現象も同じ原因です。

まとめ

  • MySQL 8.0 以降、information_schema.TABLES の統計系カラムはデータディクショナリのキャッシュから返る。 ストレージエンジンには問い合わせない
  • キャッシュの寿命は information_schema_stats_expiry（既定 86400 秒＝24時間）。 ALTER TABLE はこれを無効化しない
  • SHOW TABLE STATUS も同じキャッシュを見る。 独立した裏取りにならない。2つ試して一致しても意味がない
  • ALTER の「前」に information_schema を読むと、その古い値が24時間キャッシュされる。 事前確認をしなければ正しい値が見えた
  • 更新するなら ANALYZE TABLE、常に実値を見たいなら SET SESSION information_schema_stats_expiry = 0
  • 最も確実な確認は実際に1行 INSERT して LAST_INSERT_ID() を見ること。 キャッシュ層を通らない
  • SHOW TABLE STATUS の Rows も同じ理由で概算・古い値になる
  • 「確認できない」を「失敗した」と読み替えて破壊的な操作へ進まない。 まず読み取り経路を疑う

この記事は、事業・Webサイトの売買プラットフォーム RIKKA M&A を開発する中で得た知見です。RIKKA M&A は GitHub リポジトリを解析する技術デューデリジェンスを備え、Drupal 11 / PHP / LLM で構築・運用しています。

サイト・アプリ・AIプロダクトM&Aのプラットフォーム RIKKA M&A