Why MySQL 5.7 Auto‑Increment Duplicates After Upgrade and How to Diagnose It

Problem Description

During an online upgrade from MySQL 5.6 to 5.7, a critical MySQL client reported "Duplicate key" errors on inserts across master, replica, and read‑only instances. One table’s auto_increment value dropped from 1758609 (pre‑migration) to 1758598 (post‑migration) even though the maximum id remained 1758609.

Kernel Investigation

Because the issue appeared after the upgrade, the first hypothesis was a kernel bug. Official MySQL bug list contains a related bug (ID 76872/88321) where InnoDB produces the same auto_increment value twice when innodb_autoinc_lock_mode > 0 and auto_increment_increment > 1.

Key background knowledge:

InnoDB auto_increment parameters: innodb_autoinc_lock_mode, auto_increment_increment, auto_increment_offset.

Relevant structures: dict_table_t (stores current auto_increment), transaction structures, and handler structures.

Auto‑increment handling flow:

When a table dictionary is cached, its auto_increment value is saved to a global map ( dict_table_add_to_cache, dict_table_remove_from_cache_low).

During row_import or TRUNCATE, the auto_increment is updated.

On first handler open, the maximum auto_increment column value is read and +1 is used to initialise data_dict_t.

During INSERT, the handler calls update_auto_increment which obtains a new value from InnoDB and adjusts it according to global parameters.

ha_innobase::write_row: ...
handler::update_auto_increment: call InnoDB to get a new auto_increment value and adjust it;
ha_innobase::get_auto_increment: fetch current auto_increment from dict_table and compute next value;
ha_innobase::dict_table_autoinc_initialize: update if the supplied value is larger;
handler::set_next_insert_id: set the next auto_increment for the transaction.

The error only involves the INSERT and UPDATE paths.

On‑site Analysis & Reproduction

Since the official bug did not match the observed behaviour, a manual analysis was performed. The max id, auto_increment, and recent rows were collected; the pattern showed that the last batch of transactions updated the auto_increment column without advancing the global counter.

Further investigation revealed that the problematic tables used REPLACE INTO statements, while a table without errors used plain UPDATE. A known bug (ID 87861) states that REPLACE INTO is implemented as DELETE + INSERT on the master, but in ROW‑format binlog it is logged as an UPDATE event, which does not advance the auto_increment on the replica. When the replica is promoted to master, subsequent INSERTs encounter duplicate‑key errors.

Solution

Business‑side mitigations:

Change binlog format to MIXED or STATEMENT so that REPLACE INTO is logged as a statement.

Replace REPLACE INTO with INSERT ... ON DUPLICATE KEY UPDATE.

Kernel‑side improvements (proposed):

In ROW format, record REPLACE INTO as a statement event, preserving the original SQL.

Alternatively, split the REPLACE INTO into separate DELETE and INSERT events.

Takeaways

1. Changing innodb_autoinc_lock_mode or auto_increment_increment at runtime can easily cause duplicate‑key issues; keep these parameters stable in production.

2. When troubleshooting production incidents, gather the exact SQL, instance configuration, binlog format, and relevant data snapshots before searching for matching bugs, as this enables precise root‑cause analysis even when no official bug exists.