MySQL新增自增主鍵的坑

很多不規範的系統，由於各種各樣的原因可能一開始沒有主鍵，後面接手的dba，在推動規範化的時候，可能需要對這些表新增自增主鍵，在新增自增主鍵的時候，很可能會踩到這個坑。

bug: 92949

## Use two concurrent connecitons on master
## Insert in each connection, and make sure the insert order is different
## from commit order.
connect(conn1,localhost,root,,test,$MASTER_MYPORT,);
connect(conn2,localhost,root,,test,$MASTER_MYPORT,);
connection conn1;
create table t1(tid int, name varchar(30), index idx_tid(tid)) engine=InnoDB;
## conn1 insert first
begin;
insert into t1 values (1, 'fun');
## conn2 insert later, but commit first
connection conn2;
begin;
insert into t1 values (2, 'go');
commit;
## but conn1 commit later
connection conn1;
commit;
## check what we have on master and slave
connection master;
select * from t1;
--sync_slave_with_master
connection slave;
select * from t1;
## the above shows records on M and S are in different order
## add a auto_increment column as PK
connection master;
alter table t1 add column id int not null auto_increment primary key;
select * from t1;
checksum table t1;
--sync_slave_with_master
connection slave;
select * from t1;
checksum table t1;
## the above show records on M and S are different now

透過解析資料檔案，在master與slave上，2個記錄在cluster index上的位置不一樣，2個記錄的位置是互換的，在加主鍵的時候，alter 在rebuild的過程中，按cluster上儲存的順序，讀取插入，從而導致主鍵id 對應的記錄在主從庫不匹配。

給出的解決方式是

 Workaround:
    # Instead of ALTER TABLE t ADD COLUMN
    # c INT NOT NULL AUTO_INCREMENT PRIMARY KEY:
    ALTER TABLE t ADD COLUMN c INT NOT NULL;
    SET @c = 0;
    UPDATE t SET c = (@c := @c + 1);
    ALTER TABLE t ADD PRIMARY KEY(c);
    ALTER TABLE t CHANGE c c INT NOT NULL AUTO_INCREMENT;

透過先加一個欄位，然後透過給這個欄位賦值的方式，給正確的值，然後在設定這個欄位成自增主鍵。

檢視程式碼，從庫在執行事件的時候，使用了index_scan的方式

decide_row_lookup_algorithm_and_key: info: decide_row_lookup_algorithm_and_key: decided - INDEX_SCAN

在master上執行的時候更新c的時候是按cluster上存放的順序更新的,透過binlog event傳播到從庫，這樣c的記錄在master，slave上就變成一致的了，後面在設定成主鍵。