PKの無いテーブルに、後からAUTO_INCREMENT PRIMARY KEYなカラムを足すときの罠

TL;DR

• InnoDBの暗黙の行IDはテーブルにINSERTした順番で払い出される

• ソースでは並列で走ると、INSERTした順番がCOMMITした順番とは限らない

• レプリカではMulti Thread Applierを使っていない限り、COMMITした順番にINSERTがされる

• よってソースとレプリカでは暗黙の行IDがズレることがある

• 【2022/08/25 12:01】書き込みロックをとっても(テーブルを読み書き一切できない時間があって)良いなら多少安全にやる方法はある

• https://gist.github.com/yoku0825/9482455b42cb2cdfaf52288792441b12

---

百聞は一見に如かず。再現方法。

### on Source, connection 1

master [localhost] {msandbox} ((none)) > CREATE DATABASE d1;

Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} ((none)) > CREATE TABLE d1.t1(num int);

Query OK, 0 rows affected (0.02 sec)

master [localhost] {msandbox} ((none)) > BEGIN;

Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (1);

Query OK, 1 row affected (0.03 sec)

### まだCOMMITしない

connection1でINSERTをした後に、connection2で先にCOMMIT

### on Source, connection 2
master [localhost] {msandbox} ((none)) > BEGIN;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (2);
Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} ((none)) > COMMIT;
Query OK, 0 rows affected (0.00 sec)

で、connection1でCOMMIT

master [localhost] {msandbox} ((none)) > COMMIT;
Query OK, 0 rows affected (0.00 sec)

---

こうすると、ソースではINSERTした順番が num = 1, num = 2の順番だけど、コミットされた順番が num = 2, num = 1の順番なのでレプリカでINSERTが走るのは num = 2, num = 1の順になる。

よって、PKもないので暗黙の行IDがズレて、ORDER BY無しのSELECTで比較するとこんな感じになる。

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num
1
2
# server: 1:
num
2
1

ここで、ALTER TABLEでAUTO_INCREMENTなPKを足すじゃろ？

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

AUTO_INCREMENTの値はその時の暗黙の行IDの順番に依存して振られていくので、ソースとレプリカでズレる。

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
2       2
# server: 1:
num     id
2       1
1       2

当然、この状態で DELETE FROM d1.t1 WHERE id = 2 なんてやるともう取り返しがつかない。numが数字だからわかりにくいけど、emailとかだともう致命的にダメだってことが想像できる。

master [localhost] {msandbox} ((none)) > DELETE FROM d1.t1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
# server: 1:
num     id
2       1

レプリケーションとしては正常(ALTER TABLEはステートメント形式で伝達されてそれぞれのノードで再計算されるし、 id = 2は行ベースレプリケーションでもPKしか比較しないのでそれぞれでid = 2の行が消える)なので、気を付けないとこのデータ不整合に気が付けない。

$ ./check_slaves
master
port: 25038
             File: mysql-bin.000001
         Position: 6184
Executed_Gtid_Set:
slave # 1
port: 25039
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 6184
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 6184
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:

俺も同僚から教えてもらったんですが、 MySQL Bugs: #108229: Exists gaps when add a auto_increment column の原因ってこれじゃないかなと思います。まる。

【2022/08/25 12:01】

限りなくユニークに近いカラムがあって、LOCK TABLE WRITE (= ほかのスレッドは読み書きできない）を取っても良いなら、多少安全にやる方法はある。

master [localhost] {msandbox} ((none)) > LOCK TABLE d1.t1 WRITE;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ORDER BY num;  ### numが限りなくユニークに近いことが必要だけれども
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
2       2
# server: 1:
num     id
1       1
2       2

• https://gist.github.com/yoku0825/9482455b42cb2cdfaf52288792441b12