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

Story Background

Operations reported a device missing in the backend, suspecting a data issue causing service errors. Logs showed no error; the logic caches unique IDs in Redis before inserting or updating the database.

Redis contained values, so the key was deleted, but insert statements printed without actual insertion, indicating a deeper problem.

Problem Analysis

The table holds billions of rows, and the auto‑increment column reached the maximum value of a signed 32‑bit int (2147483647). Because the column type was INT, further inserts failed.

Thus the ID overflow caused the failure.

Solution Options

DeepSeek suggested three approaches:

Change the column type to BIGINT (no data migration needed) – simple but locks the table.

Adopt a distributed ID scheme – requires redesign and data migration, plus Redis support.

Sharding the database – introduces a framework, Elasticsearch, and logstash for sync.

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

Implementation

Created a stored procedure to reassign IDs for the last 1 million rows.

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

Reset the auto‑increment value to max(id)+1:

ALTER TABLE your_table AUTO_INCREMENT = max(id)+1;

Adjusted consumer concurrency: reduced threads from 30 to 15, which decreased ID gaps.

Switch to BIGINT

Altered the column:

ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Monitoring progress with:

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

Observed that the operation progressed slowly due to large index trees and CPU contention.

Conclusion

Key takeaways: design the schema with a sufficient ID range (use BIGINT), be cautious with high‑concurrency auto‑increment strategies, and prefer batch processing with appropriate thread counts.