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

Problem Description

An important MySQL client table upgraded from 5.6 to 5.7 started producing "Duplicate key" errors during inserts on the master, its replicas, and read‑only instances. For example, the table's AUTO_INCREMENT value changed from 1758609 (pre‑migration) to 1758598 (post‑migration), while the maximum existing ID remained 1758609.

Kernel Issue Investigation

Because the issue appeared only after moving to MySQL 5.7, the first hypothesis was a kernel bug. A search of the official MySQL bug list revealed a related bug, which is summarized below.

Background Knowledge 1 – InnoDB Auto‑Increment Parameters and Structures

Key parameters controlling auto‑increment behavior include innodb_autoinc_lock_mode, auto_increment_increment, and auto_increment_offset. Important structures are the data‑dictionary object that stores the current auto‑increment value and its lock, the transaction object that tracks rows processed, and the handler object that manages multi‑row iteration.

Background Knowledge 2 – InnoDB Auto‑Increment Access Flow

When a dict_table_t is cached or evicted, its auto‑increment value is saved to or restored from a global map (functions dict_table_add_to_cache and dict_table_remove_from_cache_low).

During row_import or table truncation, the auto‑increment value is updated.

On the first handler::open, the engine reads the maximum existing auto‑increment column value and initializes the in‑memory structure with max+1.

During an INSERT, the handler calls update_auto_increment to obtain a new value from InnoDB and set the next value for the transaction.

For INSERT … ON DUPLICATE KEY UPDATE, the auto‑increment is advanced regardless of whether the row already exists.

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

The relevant code for handling duplicate‑key updates is:

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);
    ...
}

Bug Analysis

Bug 76872 / 88321 – "InnoDB AUTO_INCREMENT produces same value twice" – occurs when innodb_autoinc_lock_mode > 0 and auto_increment_increment > 1. After a server restart, multiple threads inserting concurrently can obtain identical auto‑increment values, leading to duplicate‑key errors.

The root cause is that after restart InnoDB sets the auto‑increment to max(id) + 1. The first insert updates the auto‑increment via handler::update_auto_increment. Because the lock mode forces serialization but the increment step is larger than 1, the next thread may still see the same next_id, causing a collision.

In the examined production environment both parameters are set to 1, so this bug cannot explain the observed failures.

On‑Site Analysis & Reproduction

Field investigation showed a pattern: tables with ON UPDATE CURRENT_TIMESTAMP columns exhibited mismatched max(id) and auto‑increment values. The author reproduced the issue by executing REPLACE INTO statements on a test cluster, observing that the binlog recorded an UPDATE event that did not advance the auto‑increment on the replica.

Further analysis revealed three sharding tables, all with auto‑increment primary keys. Two of them ( hz_notice_stat_sharding and hz_notice_group_stat_sharding) experienced the error, while hz_freeze_balance_sharding did not. The failing tables used REPLACE INTO, whereas the successful one used UPDATE.

Official bug #87861 – "Replace into causes master/slave have different auto_increment offset values" – explains this behavior:

MySQL implements REPLACE INTO as DELETE + INSERT. In ROW‑format binlog, this appears as an UPDATE event, which does not adjust the auto‑increment counter on the replica.

Consequently, the replica’s max(id) can exceed its stored auto‑increment value. When the replica is promoted to master, subsequent inserts trigger 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 mitigations :

In ROW mode, log REPLACE INTO as a statement‑format event, preserving the original SQL.

Alternatively, record the operation as a DELETE event followed by an INSERT event.

Takeaways

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

When troubleshooting live incidents, gather precise information about the failure scenario, SQL statements, affected instances, configuration, binlog format, and data snapshots, and back them up before proceeding.

Accurate matching of observed symptoms to official bugs is essential, but when no bug exists, a systematic analysis can still lead to a solution.