Why MySQL 5.7 Upgrade Triggers Duplicate Key Errors on Auto‑Increment?

Problem Description

After migrating a major MySQL client from 5.6 to 5.7, inserts on the master began failing with "Duplicate key" errors, and the same issue was observed on replica and read‑only instances.

For a representative table, the auto‑increment value shown by SHOW CREATE TABLE changed from 1758609 before migration to 1758598 after migration, while the maximum id in the new table remained 1758609.

The table uses the InnoDB engine; operations staff noted that a similar issue had previously been resolved by simply restarting the server.

Kernel Investigation

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

Background 1 – InnoDB auto‑increment parameters and structures

Key parameters: innodb_autoinc_lock_mode – controls locking when obtaining auto‑increment values. auto_increment_increment and auto_increment_offset – define the step size and starting offset.

Important structures:

Data‑dictionary structure that stores the current auto‑increment value and its lock.

Transaction structure that records rows processed within a transaction.

Handler structure that keeps iteration information for multi‑row operations.

Reference: cnblogs.com/zengkefu

Background 2 – InnoDB auto‑increment workflow

The process of reading and updating the auto‑increment value includes:

Saving and restoring the value in dict_table_t when the data‑dictionary entry is evicted or reloaded (functions dict_table_add_to_cache and dict_table_remove_from_cache_low).

Updating the value during ROW_IMPORT and TRUNCATE operations.

When a handler first opens a table, it queries the maximum auto‑increment column value and initializes the handler’s internal autoinc with max+1.

During an INSERT, the handler calls update_auto_increment which ultimately invokes InnoDB to fetch a new value and adjust it according to global parameters.

ha_innobase::write_row: call handler::update_auto_increment to adjust auto‑increment
handler::update_auto_increment: fetch a value from InnoDB and apply global parameters
ha_innobase::get_auto_increment: read dict_table's current auto‑increment
ha_innobase::dict_table_autoinc_initialize: update if the supplied value is larger
handler::set_next_insert_id: set the next auto‑increment value for the transaction

For INSERT … ON DUPLICATE KEY UPDATE, the engine still advances the auto‑increment counter even if the row already exists.

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 76872 / 88321 – InnoDB AUTO_INCREMENT Produces Same Value Twice

Bug summary: When autoinc_lock_mode > 0 and auto_increment_increment > 1, a server restart followed by concurrent inserts can generate duplicate‑key errors.

Root cause: After a restart, InnoDB sets the auto‑increment value to MAX(id)+1. The first insert calls handler::update_auto_increment, which fetches this value, then recomputes the next value using the global parameters. Because the handler updates the next value before the lock is released, another thread may obtain the same next_id, causing duplication.

The bug only manifests when both autoinc_lock_mode and auto_increment_increment are greater than 1. In the production environment both parameters are set to 1, so the bug can be ruled out.

On‑Site Analysis & Reproduction

Since the official bug did not match the observed behavior, the investigation started from the symptoms.

1. Collected MAX(id), auto‑increment values, and recent rows for the failing tables. The pattern suggested that the last batch of transactions updated the auto‑increment column without advancing the global counter.

2. Noted that the tables have an ON UPDATE CURRENT_TIMESTAMP column, and that the failing tables used REPLACE INTO while a non‑failing table used plain UPDATE.

3. Discovered bug #87861 – “Replace into causes master/slave have different auto_increment offset values”.

Cause: REPLACE INTO is implemented as DELETE + INSERT. In ROW‑format binlog the operation is logged as an UPDATE, which does not adjust the auto‑increment counter on the replica. Consequently, after replication the replica’s MAX(id) can exceed its stored auto‑increment value, leading to duplicate‑key errors when the replica is promoted to master.

Solution

Application‑side mitigations:

Change binlog format to MIXED or STATEMENT.

Replace REPLACE INTO with INSERT … ON DUPLICATE KEY UPDATE.

Engine‑side mitigations (potential patches):

For ROW‑format binlog, record REPLACE INTO as a statement‑format event.

Alternatively, log the REPLACE INTO as separate DELETE and INSERT events.

Takeaways

(1) Changing autoinc_lock_mode or auto_increment_increment can easily trigger duplicate‑key problems; avoid dynamic modifications in production.

(2) When troubleshooting live incidents, gather complete context: affected SQL, configuration, binlog format, and data snapshots. This enables precise matching with known bugs or independent root‑cause analysis.

Author: Tencent Database Technology Source: cloud.tencent.com/developer/article/1367681