Bug #92949 add auto_increment column as PK cause RBR replication inconsistent
Submitted: 25 Oct 2018 15:38 Modified: 26 Oct 2018 12:47
Reporter: Fungo Wang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.6.42, 5.7.24, 8.0.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment, pk, RBR

[25 Oct 2018 15:38] Fungo Wang
Description:
This issue comes from one of our customers.
Under RBR replication, If a table without PK is concurrently updated, and an auto_increment column is added to this table as PK after a while of updating. The table on master and slave will be inconsistent with each other.

How to repeat:
A simple repeatable testcase as follow:

```
--source include/master-slave.inc

## 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

## do some delete
## we delete 'fun' record on master
connection master;
delete from t1 where name = 'fun';

select * from t1;

--sync_slave_with_master
## but on slave 'go' record is deleted
connection slave;
select * from t1;

## cleanup
connection master;
drop table t1;
--sync_slave_with_master

disconnect conn1;
disconnect conn2;
```

both master and slave with this conf:

```
--gtid-mode=on --enforce-gtid-consistency --log-bin --log-slave-updates --binlog_format=row
```

Suggested fix:
I think there are 2 points that cause this inconsistency.

1. For table without PK, the internal cluster index is ordered as insert order. In the above scenario, master and slave have different order, so the data is organized in different order, which is not a big deal in normal case. 
But when the auto_incremental column is added, and PK is create on this column, the whole table is rebuild. The auto_increment value is assigned monotonically increasing, corresponding to the internal cluster index order. So the same auto_increment PK value mapped to different business data.

2. For RBR replication, if PK exists, Update/Delete row lookup is based on PK values, they other columns are not considered, as long as we can fetch data from engine according PK.

I'm not sure whether this is a bug or incorrect usage of MySQL.
But I think we can do some improvements to the RBR row lookup logic, if we found match PK, but they other fields are mismatching, an error or warning should be emitted in error log to notify user about such inconsistency.
[26 Oct 2018 12:47] Umesh Shastry
Hello Fungo Wang,

Thank you for the report and test case.
Observed with 5.7.24/8.0.13 build.

regards,
Umesh
[5 Nov 2018 11:52] Sven Sandberg
Posted by developer:
 
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;

The update will generate row events, so slave will apply this deterministically.