MySQL Bugs: #103084: Add auto_increment primary key causes data inconsistent in replication

Bug #103084	Add auto_increment primary key causes data inconsistent in replication
Submitted:	24 Mar 2021 7:43	Modified:	24 Mar 2021 9:38
Reporter:	Yun Liu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.7.18, 5.7.33, 8.0.23	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
For table which has no auto_increment primary key, data will be inconsistent between master and slave after adding auto_increment primary key.

How to repeat:
1.[master]create table t1 with unique key but without auto_increment primary key at first.
2.[master]in session1, begin a transaction, insert into t1 values(A); but not commit
3.[master]in session2, insert into t1 values(B);
4.[master]in session1, commit transaction;
5.select * from t1, then we can see:
[master]: A，B
[slave]: B，A

6.[master]alter table t1 add auto_increment primary key, and then we can see data in master and slave have been already inconsistent:
[master]:(1,A),(2,B)
[slave]:(1,B),(2,A)

*Of course you can run test case which is added to this bug.

Suggested fix:
Maybe just order by the first column in adding auto_increment primary key.

Here is the test case.

Attachment: add_auto_increment.test (application/octet-stream, text), 769 bytes.

Hello Yun Liu,

Thank you for the report and test case.

regards,
Umesh

A duplicate of #92949 ?

Of course, it's always recommended to have a primary key and you can enable GIPK mode (https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html) using sql_generate_invisible_primary_key.

But in the case you described, usually when defining a unique key, in your example key idx_2(b,c), maybe you don't want to have NULL values in the columns used in that index.

If this is the case, I would suggest to define it like this:

  create table t1 (a int, b int not null, c int not null, d int, unique key idx_2(b,c));

If you define the table like that, even without a primary key defined, InnoDB will use the unique key (as not null) as PK and the order will be preserved. Otherwise, the internal hidden InnoDB key of 6 bytes will be used. That key is not externalized for replication and has guarantees to be the same on each members of the replication (which is the case in your example).

Cheers,