Erreur MySQL “Out of range value for column id” : comment éviter la saturation des identifiants INT

🧩Le Contexte du besoin

Il arrive tôt ou tard dans une vie de développeur ou de Devops, d'arriver au fameux maximum d’un identifiant INT dans MySQL

Et là, c’est souvent la panique : plus d’insertion possible, application bloquée, logs en erreur.

Chez Efficience IT, nous rencontrons régulièrement ce cas sur des applications en production à fort trafic. Savoir anticiper ce problème est essentiel pour préserver la continuité de service et éviter une panne critique.

Ce scénario peut sembler rare, mais dès qu’un projet devient visible et commence à produire un grand volume de données (emails, logs, événements, historiques…), la limite des 2 147 483 647 lignes est vite atteinte.

ℹ️ Ce chiffre correspond à la valeur maximale d’un champ INT SIGNED. Avec UNSIGNED, vous doublez la capacité, mais vous restez limité à environ 4,2 milliards d’entrées.Au-delà, il faut migrer votre colonne en BIGINT, capable d’encaisser jusqu’à 2^64 - 1 valeurs.

⚠️ Précautions à prendre après une première erreur

Lorsqu’on se brûle une fois avec MySQL, on apprend vite à respecter sa mécanique interne.

• Tuer un process MySQL (SELECT, INSERT, ALTER) ne libère pas immédiatement les ressources.
  MySQL effectue une restauration interne, très coûteuse en CPU et en I/O.
• Lors d’un test mal planifié, nous avons observé jusqu’à 48 heures de récupération complète, même avec une infrastructure dimensionnée.
• Les grands SELECT ou INSERT peuvent bloquer les insertions concurrentes selon le niveau d’isolation de transaction.

Même avec l’option :

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

… les performances chutent. D’où l’importance de planifier une migration maîtrisée.

⚙️ Comment migrer un champ INT vers un BIGINT

Passer de 2^32 - 1 à 2^64 - 1 peut se faire de plusieurs manières, selon :

• la taille de votre table,
• le volume de données,
• la tolérance à l’interruption.

Dans tous les cas :

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; //Ne verrouille pas les lignes pendant que la copie
SET FOREIGN_KEY_CHECKS = 0; //Réduit la charge, en ignorant la vérification de clés externes

Ces deux lignes éviteront les verrous et réduiront la charge lors des manipulations.

Trois possibilités s'offrent à vous suivant le temps que vous pouvez y consacrer et la taille de votre table en base de donnée. Dans tous les cas, nous vous invitons à bien effectuer un script sur des bases de tests, afin de bien vérifier son fonctionnement et effectuer les tests avec une machine de puissance égale à votre production. Vous en serez d'autant averti si la requête est un peu longue.

Pour ceux qui ne connaissaient pas les niveaux d'isolations de transaction chez mysql, nous vous invitons à bien lire la documentation en amont.

Solution 1 - déplacer les ID (table < 1Go)

Méthode simple pour gagner du temps sans refonte majeure : déplacer l'auto-incrément de la base de donnée. C'est-à-dire qu'on va configurer le int pour qu'il ne commence pas à -2^32-1 et termine à 2^32-1, mais commence à 0 et termine autour de 2^33. On va donc modifier la table qui déborde.

ALTER TABLE table
CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
ALGORITHM=COPY, LOCK=SHARED;

👉 Vous doublez la capacité de votre ID (≈ 4 294 967 294 lignes).

Durée : 1 à 5 minutes selon la taille de la table.

Idéal pour les petites bases avec peu de dépendances.

Solutions 2 - Passer en BIGINT (table < 1Go)

Une autre solution, consiste à modifier la colonne en elle-même, et la passer directement en BIGINT avec la commande :

ALTER TABLE table
CHANGE `id` `id` BIGINT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
ALGORITHM=COPY, LOCK=SHARED;

✅ Simple

❌ Temps d’exécution long pour des tables moyennes (plusieurs heures possibles)

💡 Testez impérativement sur une base de recette avant de passer en production.

Solution 3 - Échanger les colonnes (table quelques Go)

Méthode progressive et sûre :

Ajouter une nouvelle colonne :

ALTER TABLE table ADD `new_id` BIGINT(12) UNSIGNED NOT NULL;

Copier les données par paquets :

UPDATE table SET new_id = id WHERE new_id = 0 LIMIT 10000;

Renommer les colonnes et les clés :

ALTER TABLE table DROP PRIMARY KEY;
ALTER TABLE table CHANGE id old_id INT;
ALTER TABLE table CHANGE new_id id BIGINT UNSIGNED AUTO_INCREMENT, ADD PRIMARY KEY(id);

Supprimer la colonne old_id.

💡 Ce processus, automatisé avec un script batch, minimise la charge et garantit la cohérence des clés étrangères.

Solution 4 - Échanger les tables, (Table 10 Go et +)

Pour les très gros volumes, la création d’une nouvelle table miroir est plus fiable.

CREATE TABLE table_v2 LIKE table;
ALTER TABLE table_v2 CHANGE id id BIGINT(12) UNSIGNED NOT NULL AUTO_INCREMENT;

Définissez l’incrément de départ :

SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_NAME='table' AND TABLE_SCHEMA='database';
ALTER TABLE table_v2 AUTO_INCREMENT = 2147483648;

Puis insérez les données par paquets :

INSERT INTO table_v2 SELECT * FROM table LIMIT 10000;

Renommez ensuite :

RENAME TABLE table TO table_old, table_v2 TO table;

✅ Migration complète

✅ Sécurité maximale

❌ Process plus long, nécessite un suivi rigoureux

🧠 Ce que recommande Efficience IT

Chaque scénario dépend du volume de données, du temps de maintenance disponible et de la tolérance au risque.

Notre approche :

• Audit préalable de la base MySQL et des dépendances applicatives,
• Simulation sur environnement miroir,
• Migration progressive avec rollback planifié,
• Optimisation post-migration (index, performances, monitoring).

Nos ingénieurs DevOps et backend accompagnent les entreprises dans la sécurisation de leurs bases de données MySQL et MariaDB, avec des méthodes testées sur des environnements de production à fort trafic.

🤝 Besoin d’un accompagnement ?

Si votre application commence à dépasser ses limites techniques ou si vous anticipez une croissance rapide des données, nos experts MySQL et DevOps peuvent vous aider à planifier, exécuter et sécuriser votre migration.

👉 Contactez-nous dès maintenant