Why MySQL Auto‑Increment IDs Hit Their Limit and How to Fix It

Story Background

Operations reported a device that could not be found in the backend. The initial suspicion was data corruption causing service errors. Logs showed no errors; the logic placed a unique ID in Redis, inserting when the key was absent and updating otherwise. Redis held a value, suggesting a possible inconsistency between Redis and the database.

Problem Analysis

The table contained over 5 × 10¹⁸ rows, raising concerns about MySQL table size limits. Examination of the auto‑increment column revealed the value 2147483647, the maximum for a signed INT, indicating the INT overflow caused insert failures despite the column being defined with length 50, which had no effect.

Solution Handling

DeepSeek suggested three options: (1) change the column to BIGINT without data migration but with full table lock; (2) redesign using distributed IDs, requiring data migration and Redis support; (3) implement sharding, which would need a framework, Elasticsearch, and middleware such as Logstash.

Because locking the table for a BIGINT conversion was too disruptive, an emergency approach was sought. Although INT’s theoretical limit is ~2.1 × 10⁹, the actual auto‑increment value was non‑sequential due to a high‑performance ID strategy.

The non‑sequential IDs stem from the database’s high‑performance auto‑increment algorithm, which does not guarantee continuity.

A stored procedure was written to clean up the last one million IDs, providing a temporary 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 IDs
  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

The procedure reset the auto‑increment value; if the new value already existed, MySQL would jump to max(id)+1.

-- Reset auto‑increment
ALTER TABLE your_table AUTO_INCREMENT = max(id) + 1;

Testing showed that reducing consumer threads from 30 to 15 decreased the ID gaps, indicating high concurrency caused large ID jumps.

Set BIGINT

After the holiday, the remaining gap was about 2 million IDs, and the column type was finally changed from INT to BIGINT.

ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

UNSIGNED doubles the positive range; NOT NULL enforces presence; AUTO_INCREMENT keeps the auto‑increment behavior.

In a production environment with 100 million rows, the type change took roughly 6–7 hours; monitoring the progress with the following query revealed that after many hours the operation was still under 50% complete and slowing down.

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

The slowdown was attributed to the growing index tree and CPU contention from other threads.

Summary

The incident highlighted three key lessons: (1) massive data volume and high concurrency can exhaust an INT auto‑increment, so proper data type planning is essential; (2) high‑performance auto‑increment strategies may produce large gaps under heavy load, requiring careful thread management; (3) multi‑threaded batch consumption dramatically improves throughput, but excessive threads increase context‑switch overhead.

Author: 玛奇玛丶 Source: juejin.cn/post/7494167764917305379