Why Does MySQL 5.7 Throw Duplicate‑Key Errors After Auto‑Increment Migration?

A major MySQL client upgraded from 5.6 to 5.7 and started seeing "Duplicate key" errors during inserts on the master, standby, and read‑only instances. The table’s auto‑increment value after migration (1758598) was lower than the original max id (1758609), causing the conflict.

Kernel Investigation

The issue was first suspected to be a MySQL 5.7 kernel bug, so the official bug list was searched. One relevant bug (BUG 76872 / 88321) describes a situation where InnoDB produces the same auto‑increment value twice when innodb_autoinc_lock_mode > 0 and auto_increment_increment > 1.

Key points of the bug:

After a restart, multiple threads inserting concurrently can obtain the same auto‑increment value.

The handler updates the next auto‑increment value based on the engine’s returned value, then locks it, preventing duplication.

The bug only manifests when both parameters are greater than their defaults.

Related Bug #87861 (Replace‑Into Offset Mismatch)

Another bug shows that REPLACE INTO is implemented as DELETE + INSERT. In ROW‑format binlogs the delete is logged as an UPDATE, which does not advance the auto‑increment counter on the slave, leading to mismatched max(id) and auto‑increment values and eventual duplicate‑key errors when the slave is promoted.

On‑Site Analysis and Reproduction

Field analysis revealed that the problematic tables used REPLACE INTO, while a table without errors used plain UPDATE. Binlog inspection confirmed updates to the auto‑increment column without corresponding counter advancement.

Reproduction steps:

Collect max id, auto‑increment, and recent rows from affected tables.

Observe that the last batch of transactions only updated the auto‑increment column.

Execute a REPLACE INTO on a test table and verify duplicate‑key errors after promotion.

Technical Details of InnoDB Auto‑Increment Handling

The write‑row path involves several internal calls:

ha_innobase::write_row → handler::update_auto_increment → ha_innobase::get_auto_increment → ha_innobase::dict_table_autoinc_initialize → handler::set_next_insert_id

During an INSERT … ON DUPLICATE KEY UPDATE, the engine still advances the auto‑increment value:

if (error == DB_SUCCESS && table->next_number_field && new_row == table->record[0] && thd_sql_command(m_user_thd) == SQLCOM_INSERT && trx->duplicates) {
    ulonglong auto_inc;
    ...
    auto_inc = table->next_number_field->val_int();
    auto_inc = innobase_next_autoinc(auto_inc, 1, increment, offset, col_max_value);
    error = innobase_set_max_autoinc(auto_inc);
    ...
}

Solutions

Business‑side mitigations:

Change binlog format to MIXED or STATEMENT to avoid ROW‑format issues with REPLACE INTO.

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

Kernel‑side fixes (proposed):

Log REPLACE INTO as a statement‑format event when using ROW binlog.

Record REPLACE INTO as separate DELETE and INSERT events in ROW format.

Takeaways

Changing innodb_autoinc_lock_mode or auto_increment_increment in production can easily trigger duplicate‑key problems; keep these parameters stable.

When troubleshooting, gather full context: affected SQL, configuration, binlog format, and data snapshots.

Accurate bug matching requires precise scenario replication; if no official bug exists, independent analysis is essential.