なんかauto incrementがずれてた
サービス停止中の本番環境のDBにデータを追加したところ、何故かauto incrementの値がずれていた
こんな感じでした↓(idにauto incrementが設定されている)
mysql> SELECT * FROM test_table;
+----+------+
| id | name |
+----+------+
| 1 | aaa1 |
| 2 | aaa2 |
| 4 | aaa3 |
+----+------+
mysql> SELECT auto_increment FROM information_schema.tables WHERE table_schema = "test" AND table_name = "test_table";
+----------------+
| AUTO_INCREMENT |
+----------------+
| 5 |
+----------------+
サービスには影響は無いが直しておくことになった
対応手順
以下の手順で変更しようとした
- idがずれているレコードのアップデート
- トランザクション開始
- ALTER文でauto incrementの値を変更
- insertして確認
- ロールバック
- 確認
- もう一度ALTER文でauto incrementの値を変更
1~6のSQLがこちら↓
-- 1. idがずれているレコードのアップデート
UPDATE test_table SET id = 3 WHERE id = 4 AND name = "aaa3";
-- 2. トランザクション開始
BEGIN;
-- 3. ALTER文でauto incrementの値を変更
ALTER TABLE test_table AUTO_INCREMENT = 4;
-- 4. insertして確認
INSERT INTO test_table VALUES (0, "name4");
SELECT * FROM test_table;
-- 5. ロールバック
ROLLBACK;
-- 6. 確認
SELECT * FROM test_table;
SELECT auto_increment FROM information_schema.tables WHERE table_schema = "test" AND table_name = "test_table";
-- 7. もう一度ALTER文でauto incrementの値を変更
ALTER TABLE test_table AUTO_INCREMENT = 4;
わざわざ変えてからデータを作って戻そうとしている理由はauto incrementの値を変えたことが無かったため、設定した値で本当に正しく動くのか確認したかった
作業確認者の先輩とこれで大丈夫っしょという認識になっていたので実行することになりました
実行結果
順調に進み6を実行した結果↓
mysql> SELECT * FROM test_table;
+----+------+
| id | name |
+----+------+
| 1 | aaa1 |
| 2 | aaa2 |
| 3 | aaa3 |
| 4 | aaa4 |
+----+------+
mysql> SELECT auto_increment FROM information_schema.tables WHERE table_schema = "test" AND table_name = "test_table";
+----------------+
| AUTO_INCREMENT |
+----------------+
| 5 |
+----------------+
なんか思ったんとちゃう、、
かなり焦りました
ロールバックしたのにid=4のレコードが消えていない、、
色々と調べたらalter文を実行した後に暗黙的コミットということが起きていたみたいです
つまり3を実行した時点でコミットが行われていたので、4のinsert文を実行した後にロールバックしても何も戻らないということでした、、
知識不足でお恥ずかしいのですが、暗黙的コミットというものを全く知らなかったです
発生する条件は他にもいっぱいあるみたいです↓
https://dev.mysql.com/doc/refman/8.0/ja/implicit-commit.html
まとめ
そもそも検証環境で試さなかったことが問題ですよね、、