When MySQL Auto‑Increment Hits INT Limit: Diagnosis & Fixes

Story Background

Operations reported that a device could not be found in the backend, leading the author to suspect data issues causing service errors and missing inserts.

Log inspection showed no program errors; the logic first stores a unique ID in Redis, inserting into MySQL if the key is absent and updating otherwise, using Redis as a cache to speed up bulk inserts/updates.

Redis contained the key, so the author deleted it and retried. An INSERT statement was printed but no row appeared, indicating a deeper problem.

Problem Analysis

The table holds over 5 × 10⁵ 000 rows, prompting suspicion that the MySQL table size might be exhausted, though no obvious limits were found.

The auto‑increment column displayed the value 2147483647, which is the maximum for a signed 32‑bit INT. The column was defined as INT, so inserts failed once the value exceeded this limit. Although the column length was set to 50, it had no effect.

DeepSeek suggested three possible solutions:

Change the column type to BIGINT (simple but locks the table).

Introduce a distributed ID scheme, requiring table redesign and data migration.

Shard the database, which adds framework overhead and may require Elasticsearch and logstash for synchronization.

Reference: Related article

Solution Approach

The author first attempted a stored procedure to clean up the last million IDs, then experimented with consumer concurrency. Reducing the number of Kafka consumers from 30 to 15 decreased the ID gaps.

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's 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, the author reset the auto‑increment value:

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

Changing the column to BIGINT was performed with:

ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Running the alteration on a test dataset of 100 million rows took about one hour; on production it was estimated to take 6–7 hours. To monitor progress, the following query was used:

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

The progress query showed that after many hours the operation was still under 50 % and slowing down, likely due to index tree growth and CPU contention from other threads.

Set BIGINT

Eventually the column type was changed from INT to BIGINT, adding UNSIGNED to double the positive range and ensuring NOT NULL and AUTO_INCREMENT attributes.

Conclusion

The incident highlighted several lessons:

Designing databases for future scale is crucial; changing a column type on billions of rows can take days.

High‑performance auto‑increment strategies can create large ID gaps under heavy concurrency; reducing consumer threads narrowed the gaps.

Increasing consumer count beyond a point yields diminishing returns due to context‑switch overhead.

Switching to batch consumption and multi‑threaded asynchronous processing dramatically improved throughput.

Overall, careful schema design, appropriate ID generation strategy, and balanced concurrency are essential for large‑scale MySQL workloads.