Understanding MySQL 8.0.13 sql_require_primary_key and Resolving Inconsistent Data After Adding Auto‑Increment Primary Keys

Problem Description

The client added auto‑increment primary keys to several tables without existing primary keys in a test environment (MySQL 8.0.25, 1 primary‑secondary replication). Some tables showed data inconsistency between master and slave after the operation.

Reproduction Steps

1. Create two tables t1 and t2 without primary keys.

mysql> create table t1(n int,m int);
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2(n int,m int);
Query OK, 0 rows affected (0.01 sec)

2. Insert two rows into each table, using different transaction orders.

mysql> insert into t1 values(1,1),(2,2);
Query OK, 2 rows affected (0.01 sec)

mysql> insert into t2 values(1,1),(2,2);
Query OK, 2 rows affected (0.01 sec)

3. Add an auto‑increment primary key to each table.

mysql> alter table t1 add id int primary key auto_increment first;
Query OK, 0 rows affected (0.14 sec)

mysql> alter table t2 add id int primary key auto_increment first;
Query OK, 0 rows affected (0.08 sec)

4. Query both tables on master and slave. t1 remains consistent, while t2 shows different row order on the slave.

Analysis

In InnoDB, when a table lacks a primary key or a non‑null unique key, the engine generates an internal RowID at statement execution time. The session that executes first receives smaller RowIDs. Because the two sessions commit in opposite order, the binlog on the slave records the rows in a different order, leading to mismatched auto‑increment values for t2 .

Thus, the inconsistency is caused by the combination of:

Absence of an explicit primary key.

Different execution/commit order of the sessions.

InnoDB’s reliance on RowID ordering for auto‑increment assignment.

The official MySQL documentation (replication‑features‑auto‑increment) confirms that auto‑increment consistency depends on the order of inserts on master and slave.

Solution

To avoid data loss, the manual recommends recreating the table with the correct primary key ordering:

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 TABLE t1;
RENAME TABLE t2 TO t1;

For large tables this can be time‑consuming, so the best practice is to define a primary key at table creation. MySQL 8.0.13 introduced the sql_require_primary_key parameter to enforce this, and MySQL 8.0.30 added sql_generate_invisible_primary_key (GIPK) to automatically generate an invisible primary key when none is defined.

Summary

Defining primary keys early prevents the replication inconsistency demonstrated. Enabling sql_require_primary_key forces explicit primary key definition, while sql_generate_invisible_primary_key offers a fallback invisible key for legacy tables.