When MySQL Auto‑Increment IDs Hit Their Limit: Diagnosis and Fixes

Story Background

Operations reported a device missing in the backend, suspecting data issues causing service errors and missing inserts.

Log inspection showed no errors; the logic stores a unique ID in Redis, inserting when absent and updating otherwise, using Redis as a cache to speed up batch inserts/updates.

Redis contained the key, but after deleting it and re‑running, an INSERT statement was printed yet no row was added, indicating a deeper problem.

Problem Analysis

The table holds over 5 × 10⁹ rows. The auto‑increment column had reached the maximum value of a signed 32‑bit integer (2147483647). The column type was INT, so further inserts failed despite the column length being set to 50 (which has no effect).

Because of the high concurrency, the auto‑increment strategy produced large gaps between IDs, and the ID sequence became non‑continuous.

Solution Options

Deepseek suggested three approaches:

Change the column type to BIGINT without migrating data (locks the table during the operation).

Introduce a distributed ID system, requiring table redesign and data migration, plus Redis support.

Apply sharding (database and table partitioning), which involves additional frameworks, Elasticsearch, and Logstash for synchronization.

Reference: https://juejin.cn/post/7444014749321461811

Implementation Details

A quick fix was to write a stored procedure that extracts the last 1 000 000 IDs and reassigns them, buying a few days of relief.

BEGIN
DECLARE start_id INT DEFAULT 1;
DECLARE end_id INT DEFAULT 100000;
DECLARE current_batch INT DEFAULT 0;

WHILE start_id <= end_id DO
  -- Update temporary table ID
  UPDATE table
  SET id = start_id + 1
  WHERE id = (SELECT original_id FROM (SELECT id AS original_id FROM table ORDER BY id DESC LIMIT 1) as test);
  SET start_id = start_id + 1;
END WHILE;
END

After cleaning about 5 million ID ranges, the team reduced the consumer count from 30 to 15 threads, which decreased the ID gap.

Ultimately the column was altered to BIGINT UNSIGNED:

ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

In production, the alteration on a table with 100 million rows took about an hour; on the live system it required 6‑7 hours and was monitored with:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,
       ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS "Progress (%)"
FROM performance_schema.events_stages_current;

The operation progressed slowly (under 50 % after many hours) due to large index trees and CPU contention from other threads.

Summary

The MySQL auto‑increment overflow was caused by using INT for a massive table and a high‑performance auto‑increment strategy that generated large gaps under heavy concurrency. Converting the column to BIGINT UNSIGNED resolved the issue, but the migration required careful planning, monitoring, and consideration of concurrency and sharding trade‑offs.