Vous êtes arrivés au max des id INT 2147483647...

En tant que développeur ou Devops, si cela ne vous est jamais arrivé, vous ne perdez rien pour attendre et vous vous retrouverez dans une panique de l’espace, le fameux 2 147 483 647, le maximum de la valeur INT en SQL !!

Vous allez dire que ce nombre est inatteignable pour le commun des mortels, et des projets, et c’est vrai. Mais cela arrive dans pas mal de projets, à partir du moment où votre projet commence à être bien visible et que vous produisez beaucoup de données.

Par exemple, une table de logs avec les modifications de BDD, les parcours users, les e-mails etc… peut facilement être dans ce cas.

2 147 483 647 c’est le nombre possible de ligne avec un ID positif, et le double si vous aviez placé votre auto_increment à - 2 147 483 647 dès le début, ce que personne ne fait.

Conseils après s’être brulé :

• Tuer un SELECT / INSERT / ALTER ne libère pas immédiatement les ressources. Après avoir essayé de faire de grandes copies et des modifications de table sur place, les performances en ont souffert. Et même en tuant manuellement les threads, la performance ne sera pas complètement rétablie. Car lorsque vous abandonnez une requête comme celle-ci, MySQL effectue une restauration, et il prendra des ressources précieuses. Dans un exemple de confrère, cela a pris plus de 48 heures pour une restauration complète, même avec des ressources MySQL monstrueuses à portée de main.
• Un grand SELECT / INSERT bloque les insertions. En fait MySQL verrouille par défaut les lignes en cours de lecture en fonction du "niveau d'isolement". Voir cet excellent article pour une bien meilleure description. Il y a plus de facteurs, donc même lorsque vous utilisez l'option la moins contraignante (SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;), les performances souffriront toujours des grandes tables ou tables utilisées pendant la copie.

Comment migrer INT vers un BIGINT ?

Solution 1, déplacer les ID, pour les petites tables (< 1Go)

• Régler le niveau d’isolation des transactions de lecture à “Sans Engagement”
• Ignorer les vérifications de clés étrangères SET FOREIGN_KEY_CHECKS = 0;
• Copiez les données de la table afin que nous impactions le moins possible le fonctionnement. ALTER TABLE table_with_id_hitting_max_int CHANGE id`` id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHME = COPIE, VERROUILLAGE = PARTAGE;
• L'ID minimum est maintenant 0 et le maximum est 4 294 967 294
• Prend généralement 1 à 5 minutes.

Solutions 2, Passer en BIGINT, pour les petites tables (< 1Go)

• Régler le niveau d’isolation des transactions de lecture à “Sans Engagement”
• Ignorer les vérifications de clés étrangères SET FOREIGN_KEY_CHECKS = 0;
• Copiez les données de la table afin que nous impactions le moins possible le fonctionnement ALTER TABLE table_with_id_hitting_max_int CHANGE id`` id BIGINT (12) UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHME = COPIE, VERROUILLAGE = PARTAGE;
• Avec ~ 1 To, cela peut prendre environ 48 heures, selon la charge.
• L'ID minimum est maintenant 0 et le maximum est 4 294 967 294

Solution 3, Échanger les tables, (Table 10 Go).

• Régler le niveau d’isolation des transactions de lecture à “Sans Engagement”
• Ignorer les vérifications de clés étrangères SET FOREIGN_KEY_CHECKS = 0;
• Créer une nouvelle_table comme l’ancienne_table CREATE nom_table_v2 LIKE nom_table;
• Modifier la “nouvelle table” ALTER TABLE nouvelle_table CHANGE id`` id BIGINT (12) UNSIGNED NOT NULL AUTO_INCREMENT;
• Modifier l’auto_increment ALTER TABLE nouvelle_table AUTO_INCREMENT = 2147483648;
• RENAME TABLE ancienne_tableTO ancienne_table_v1, nouvelle_table TO table;
• Vous devriez être de nouveau opérationnel, mais bien sûr, vos anciennes données sont manquantes…
• Copier les données de l'ancienne vers la nouvelle table, avec un script en python et en faisant des boucles de 10 000 lignes INSERT INTO table SELECT * FROM ancienne_table limit 10 000;

Solution 4, Échanger les tables via une procédure stockée (Table > 100Go)

Le principe est le même qu’en solution 2, sauf que l’on passe par une procédure stockée plutôt que par un script python, mais le principe est le même, il est sans doute plus puissant qu’avec une surcouche de python.

CREATE TABLE `migrate_tmp` (
   `id` int (11) unsigned NOT NULL AUTO_INCREMENT,
   `key` varchar (255) DEFAULT NULL,
   `val` varchar (255) DEFAULT NULL,
   KEY PRIMARY (`id`),
   UNIQUE KEY `clé` (` clé`)
) MOTEUR = InnoDB AUTO_INCREMENT = 0 DEFAULT CHARSET = latin1;

• Supprimez cette procédure stockée au cas où nous la mettions à jour. Puis recréez.

 DROP PROCEDURE IF EXISTS migrate_v1_to_v2;

 CREATE PROCEDURE migrate_v1_to_v2 (row_count INT (11), sleep_seconds INT (11))
 START
 SET start_id BIGINT;
 DECLARE end_id BIGINT;
 DECLARE max_id BIGINT;
 DECLARE heure_début VARCHAR;
 SET start_id = (SELECT val FROM migrate_tmp WHERE `key` = 'next_id');
 SET max_id = (SELECT id FROM nom_table_v1 ORDER BY id DESC LIMIT 1);
 SET FOREIGN_KEY_CHECKS = 0;
 WHILE start_id <= max_id DO
 SET heure_début = MAINTENANT ();
 SET end_id = start_id + row_count;
   - migrate_v1_to_v2
     INSERT IGNORE INTO nom_table_v2
     SELECT *
     FROM nomtable_v1 INDEX AS FORCE (PRIMAIRE)
     OÙ
     id> = id_démarrage
     AND id <end_id
     LIMIT row_count;

 SET id_début = id_fin;

 INSERT INTO migrate_tmp (`clé`,` val`)
 VALEURS
 ('last_row_count', ROW_COUNT ()),
 ('next_id', start_id),
 ('last_insert_finished', MAINTENANT ()),
 ('last_insert_started', heure_début);
 SELECT 'migrate_v1_to_v2', SLEEP (sleep_seconds);
 END WHILE;
 SET FOREIGN_KEY_CHECKS = 1;

• Ensuite, exécutez quelque chose comme ça pour copier des lots de 10k lignes
• Et faites une pause d'une demi-seconde après chaque lot jusqu'à ce que vous ayez terminé.APPEL migrate_v1_to_v2 (10000, .5);

Contactez Efficience IT pour plus d'informations.