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

Background

Operations reported a device missing in the backend, suspecting data issues causing service errors. Logs showed no errors; the logic caches a unique ID in Redis, inserting if absent, updating otherwise.

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

Problem Analysis

The table holds about 5 × 10⁹ rows; the auto‑increment ID had reached 2,147,483,647, the maximum for a signed INT. The column was defined as INT(50) unsigned, which does not increase the range, so inserts failed.

Changing the column type to BIGINT would avoid the overflow but would lock the table.

Other options include redesigning with distributed IDs or sharding, each requiring data migration and additional infrastructure.

https://juejin.cn/post/7444014749321461811

Solution Implementation

A stored procedure was written to reassign IDs for the last 1 000 000 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;

Then reset the auto‑increment value to MAX(id)+1.

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

Performance Observations

Testing on a 100 million‑row table took about an hour; on production the migration took over two days. Monitoring with

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

showed the operation progressing slowly, partly due to index tree growth and CPU contention from other threads.

Final Takeaways

Designing the schema with appropriate data types is critical; changing a column on billions of rows can take days.

High‑performance auto‑increment strategies can create large gaps under heavy concurrency.

Multithreaded batch processing improves throughput, but excessive threads increase context‑switch overhead.