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上就变成一致的了，后面在设置成主键。