Description:
Adding an auto-increment primary key to an InnoDB table may lead to data inconsistency.
How to repeat:
1. create table t2(n int,m int);
2. session 1: begin; insert into t2 values (1,1);
3. session 2: insert into t2 values (2,2); commit;
4. session 1: commit;
5. master result:
select * from t2;
+----+------+------+
| id | n | m |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+----+------+------+
2 rows in set (0.00 sec)
6. slave result:
select * from t2;
+----+------+------+
| id | n | m |
+----+------+------+
| 1 | 2 | 2 |
| 2 | 1 | 1 |
+----+------+------+
2 rows in set (0.00 sec)
7. master update: update t2 set m=99 where n=2;
8. master result:
select * from t2;
+----+------+------+
| id | n | m |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 99 |
+----+------+------+
2 rows in set (0.00 sec)
9. slave result:
select * from t2;
+----+------+------+
| id | n | m |
+----+------+------+
| 1 | 2 | 2 |
| 2 | 2 | 99 |
+----+------+------+
2 rows in set (0.00 sec)
Suggested fix:
The manual prompts that（https://dev.mysql.com/doc/refman/8.0/en/replication-features-auto-increment.html）: Adding an AUTO_INCREMENT column to a table using ALTER TABLE may not result in the same row ordering on the replica and the source. This situation occurs because the sequence in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is crucial to have the same row order on the source and the replica, the rows must be sorted before assigning an AUTO_INCREMENT value. Supposing that you want to add an AUTO_INCREMENT column to a table named t1 which has columns col1 and col2, the following statements can create a new table t2 that is identical to t1 but has an AUTO_INCREMENT column:
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
DROP t1;
ALTER TABLE t2 RENAME t1;
However, I think this issue will not only lead to differences in the auto-increment primary key values of the same row of data between the master and slave databases, but also cause more serious data inconsistencies between the master and slave databases in subsequent replication. This is because in row-based replication, when the slave database replays a row of data, it uses the primary key or unique key as the filtering condition to determine a row of data. If the auto-increment primary key values of the same row of data are different between the master and slave databases, when the master database modifies row1, the slave database may modify row2 or row3 instead.
It should be ensured that the values of the auto-increment primary keys in the master and slave databases are the same after adding the auto-increment primary key. If this cannot be guaranteed, it is recommended to report an error during the operation of adding the auto-increment primary key, and prompt that it may lead to data inconsistency.
