Risoluzione dei Problemi Comuni di Migrazione MySQL e degli Errori di Trasferimento Dati

Le migrazioni MySQL falliscono in alcuni modi familiari. L'importazione si blocca su una chiave esterna. I caratteri si trasformano in punti interrogativi. Un dump da MySQL 5.7 non viene caricato correttamente in MySQL 8.0. I dati vengono caricati, ma l'applicazione si rompe perché una routine memorizzata, un trigger, un utente o una modalità SQL non sono stati trasferiti come previsto. Nessuno di questi problemi è insolito, ma sono molto più facili da gestire quando tratti la migrazione come un processo ripetibile invece di una copia una tantum.

La migliore abitudine di migrazione è provare. Fai un vero backup, ripristinalo in un target di staging, esegui gli stessi comandi di importazione che intendi usare in produzione e annota ogni avviso. Una prova ti dice se il tuo dump è completo, se la configurazione del target è compatibile e quanto tempo richiede effettivamente il caricamento. Ti dà anche un piano di rollback più realistico di "lo risolveremo durante la finestra di manutenzione".

Inizia Identificando il Tipo di Errore

Quando una migrazione si rompe, non iniziare a cambiare variabili del server a caso. Inquadra l'errore in una di queste categorie:

Compatibilità: differenze di versione, parole riservate, funzionalità rimosse, impostazioni predefinite modificate.
Codifica: disallineamenti del set di caratteri e della collation.
Vincoli: chiavi esterne, chiavi univoche, vincoli di controllo, colonne generate.
Copertura degli oggetti: trigger, routine, eventi, viste, utenti o grant mancanti.
Prestazioni: importazione troppo lenta, disco pieno, log binari in crescita, indici che richiedono troppo tempo.
Comportamento dell'applicazione: dati importati, ma query o scritture si comportano diversamente.

Questa classificazione ti dà il prossimo comando da eseguire. Un errore di chiave duplicata e una emoji corrotta sono entrambi "problemi di migrazione", ma hanno cause completamente diverse.

Disallineamenti di Versione: MySQL 5.7 a 8.0 e Salti Simili

Gli aggiornamenti di versione principale sono dove compaiono molte sorprese. MySQL 8.0 ha modificato le impostazioni predefinite, le parole riservate, il comportamento di autenticazione, i dettagli interni del dizionario dati e il comportamento dell'ottimizzatore rispetto a 5.7. Parte della vecchia sintassi funziona ancora; parte no. MariaDB aggiunge un altro livello di compatibilità perché non è un sostituto drop-in per ogni funzionalità di MySQL.

Prima della migrazione, cattura le impostazioni di origine:

SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Sul target, esegui gli stessi controlli e confronta. sql_mode merita un'attenzione speciale. Un dump che viene caricato su un'origine permissiva può fallire su un target più restrittivo con errori come date non valide, valori predefiniti mancanti per colonne NOT NULL o date zero che non sono più accettate nella modalità target.

Se incontri un errore come:

ERROR 1067 (42000): Invalid default value for 'created_at'

non rilassare immediatamente sql_mode per sempre. Prima ispeziona la definizione della tabella e i dati. Potrebbe essere necessario correggere i valori predefiniti, convertire le date zero o aggiornare le ipotesi dell'applicazione. Abbinare temporaneamente la sql_mode di origine durante l'importazione può aiutarti a completare un ripristino graduale, ma la produzione dovrebbe muoversi verso una modalità nota ed esplicita su cui la tua applicazione è stata testata.

Le parole riservate possono anche rompere vecchi schemi. Una colonna o tabella denominata rank, groups o un'altra parola riservata più recente potrebbe necessitare di quoting o ridenominazione. Se l'errore appare in DDL, ispeziona l'istruzione esatta dal dump e testa una versione corretta sul target.

Problemi del Plugin di Autenticazione

Una migrazione che include un cutover dell'applicazione spesso fallisce prima che qualsiasi query venga eseguita perché i client non possono autenticarsi. MySQL 8.0 utilizza comunemente caching_sha2_password per impostazione predefinita, mentre i client più vecchi potrebbero aspettarsi mysql_native_password.

Controlla gli utenti target:

SELECT user, host, plugin FROM mysql.user;

La soluzione migliore è solitamente aggiornare la libreria client o il driver. Se non è possibile prima del cutover, potresti aver bisogno di un account di compatibilità temporaneo:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';

Tratta questo come una decisione di compatibilità, non come una best practice generica. Le impostazioni di autenticazione influenzano la sicurezza e la risposta giusta dipende dalle versioni del client e dal modello di rischio.

Problemi di Set di Caratteri e Collation

I problemi di set di caratteri sono dolorosi perché l'importazione può essere completata con successo mentre i dati sono già danneggiati. Il sintomo classico è ?, mojibake, caratteri accentati rotti o insert falliti che coinvolgono emoji.

Controlla le definizioni del database e delle tabelle di origine:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';

Controlla anche le colonne:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
  AND character_set_name IS NOT NULL;

Per la maggior parte delle applicazioni moderne, utf8mb4 è il set di caratteri target corretto perché supporta l'intero intervallo Unicode, incluse le emoji. Il vecchio nome utf8 di MySQL non è uguale al UTF-8 completo nelle versioni precedenti; è comunemente un set di caratteri a 3 byte.

Quando si esegue il dump e l'importazione, sii esplicito:

mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql

Se i dati di origine sono effettivamente latin1, non dichiararli ciecamente utf8mb4 e sperare. Prima determina se i byte sono validi nella codifica di origine. Alcuni vecchi sistemi contengono dati "doppiamente codificati" in cui la colonna dichiara un set di caratteri ma l'applicazione ha memorizzato byte da un altro. Ciò richiede una conversione testata, non una ricerca e sostituzione globale.

Le differenze di collation possono anche cambiare il comportamento. L'ordine di ordinamento, i confronti di unicità e la sensibilità alle maiuscole/minuscole possono differire tra le collation. Se un indice univoco fallisce durante la migrazione, controlla se la collation target tratta due stringhe come uguali quando l'origine non lo faceva.

Errori di Chiave Esterna

Gli errori di chiave esterna di solito significano una di quattro cose:

Le tabelle figlie sono state importate prima delle tabelle padre.
Il dump è parziale e mancano righe referenziate.
I dati di origine avevano già riferimenti incoerenti.
Lo schema target differisce dall'origine.

La soluzione comune per il caricamento bulk è:

SET FOREIGN_KEY_CHECKS = 0;
-- importa dati
SET FOREIGN_KEY_CHECKS = 1;

Questo può essere appropriato per un ripristino logico completo da un dump affidabile. Non è uno strumento di pulizia. Riattivare FOREIGN_KEY_CHECKS non convalida completamente ogni riga esistente nel modo in cui molti pensano, quindi puoi importare relazioni errate e non accorgertene fino a dopo.

Se stai unendo dati o importando solo una parte di uno schema, mantieni i controlli abilitati dove possibile e carica prima le tabelle padre. Se devi disabilitare i controlli, esegui query di convalida in seguito. Ad esempio:

SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
  AND p.id IS NULL
LIMIT 20;

Usa query come queste per le tue relazioni reali, specialmente per tabelle di alto valore come ordini, pagamenti, account e permessi.

Errori di Chiave Duplicata

Un errore di chiave duplicata significa che il target ha già un valore che i dati in arrivo vogliono inserire:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

Se il target dovrebbe essere una copia esatta, la soluzione pulita è solitamente eliminare e ricreare il database target, quindi importare di nuovo. Un target caricato a metà non è un buon punto di partenza per un secondo tentativo a meno che il tuo processo non sia stato progettato per riprendere.

Se stai unendo dati, decidi la politica di conflitto prima dell'importazione. INSERT IGNORE nasconde i duplicati saltando le righe. REPLACE INTO elimina la riga esistente e inserisce quella nuova, il che può attivare cascate e modificare colonne ad aggiornamento automatico. ON DUPLICATE KEY UPDATE è più esplicito, ma richiede comunque regole attente.

Per le migrazioni, preferisco le tabelle di staging per le unioni. Carica i dati in arrivo in tabelle staging_*, ispeziona i conflitti, quindi scrivi istruzioni deliberate INSERT ... SELECT o UPDATE ... JOIN. È più lento da progettare, ma evita di buttare via dati silenziosamente.

Trigger, Routine, Eventi e Viste Mancanti

Una migrazione può sembrare riuscita perché esistono tabelle e righe, mentre manca una logica importante del database. Le opzioni di mysqldump sono importanti:

mysqldump -u user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  appdb > appdb.sql

Viste e routine possono fallire durante l'importazione a causa di account definitori. Una vista può fare riferimento a:

DEFINER=`old_user`@`old_host`

Se quell'account non esiste sul target, l'oggetto potrebbe non essere creato o fallire quando utilizzato. Puoi creare l'account definitor necessario con privilegi appropriati o regolare i definitori durante un processo di migrazione controllato. Non rimuovere i definitori ciecamente senza comprendere il modello di sicurezza dell'applicazione.

Dopo l'importazione, confronta i conteggi degli oggetti:

SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;

SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;

Controlla anche gli eventi pianificati se la tua applicazione si basa su di essi:

SHOW EVENTS FROM appdb;

Importazioni Lente e Tabelle Grandi

Le importazioni grandi sono solitamente limitate da I/O del disco, manutenzione degli indici, logging binario, controlli di chiave esterna o dimensione della transazione. Prima di ottimizzare, osserva il target:

iostat -xz 1
df -h
top

Per i dump logici, usa insert estesi. mysqldump lo fa per impostazione predefinita nella maggior parte dei casi, ma conferma di non usare --skip-extended-insert a meno che non abbia bisogno di diff leggibili dall'uomo più che di velocità.

Per le importazioni InnoDB, un innodb_buffer_pool_size più grande può aiutare se il target ha memoria disponibile. Non impostarlo così alto che il sistema operativo inizi a fare swapping. Durante un caricamento una tantum, alcuni team rilassano temporaneamente le impostazioni di durabilità come innodb_flush_log_at_trx_commit o disabilitano il logging binario per la sessione di importazione. Quelle scelte scambiano il ripristino da crash o il point-in-time recovery per la velocità, quindi dovrebbero essere utilizzate solo quando puoi permetterti di riavviare l'importazione da un backup noto.

Se il target è anche una fonte di replica, fai attenzione ai log binari. Disabilitare il logging binario può velocizzare l'importazione, ma i repliche a valle non riceveranno quelle modifiche. In una topologia con repliche, decidi dove dovrebbe avvenire l'importazione e come i cambiamenti dovrebbero fluire prima di disattivare i log.

Per tabelle molto grandi, considera strumenti di backup fisico o utilità di dump e caricamento di MySQL Shell invece del semplice mysqldump. I dump logici sono portatili e facili da ispezionare, ma non sono sempre il percorso più veloce per dataset multi-centinaia di gigabyte.

Errori di Spazio su Disco

Gli errori di disco durante la migrazione sono comuni e evitabili. Hai bisogno di spazio per il file di dump, i dati importati, gli indici, i file temporanei, i log binari e talvolta lo storage doppio mentre le tabelle vengono ricostruite.

Controlla prima dell'importazione:

df -h
du -sh /var/lib/mysql

All'interno di MySQL, controlla le dimensioni delle tabelle:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;

Se l'importazione fallisce perché il disco si riempie, non eliminare semplicemente file casuali dalla directory dei dati. Libera spazio in modo sicuro, ispeziona se il target è parzialmente caricato e decidi se ricominciare da capo.

Convalida Post-Migrazione

Una migrazione non è finita quando il comando di importazione termina. Convalida il risultato.

Inizia con i conteggi delle righe per le tabelle importanti:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;

I soli conteggi delle righe non sono sufficienti. Confronta somme o controlla i dati critici per il business:

SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;

Esegui le stesse query su origine e target durante il periodo di quiete finale. Per le tabelle che continuano a cambiare durante la migrazione, usa un congelamento pianificato, il recupero della replica o la riconciliazione a livello di applicazione.

Testa i flussi di lavoro dell'applicazione sul target prima del cutover:

Login e creazione di sessione.
Creazione e aggiornamento di record principali.
Ricerche e report che si basano su collation o indici.
Job in background, trigger ed eventi pianificati.
Controlli dei permessi e azioni di amministrazione.

Il test dell'applicazione è importante perché il database può essere tecnicamente importato ma comportamentalmente sbagliato.

Una Checklist Pratica di Triage per la Migrazione

Quando appare un errore di migrazione, usa questa sequenza:

Salva il messaggio di errore esatto e l'istruzione SQL fallita, se disponibile.
Identifica la categoria: compatibilità, codifica, vincolo, copertura degli oggetti, prestazioni o comportamento dell'applicazione.
Confronta le versioni di MySQL di origine e target, sql_mode, set di caratteri e collation.
Per errori di vincolo, ispeziona le righe padre e figlio specifiche o le chiavi duplicate.
Per problemi di codifica, ferma l'importazione finché non sai se i byte di origine sono validi e come la connessione client li sta interpretando.
Per importazioni lente, controlla disco, memoria, log binari e manutenzione degli indici prima di cambiare variabili casuali.
Dopo una correzione, riesegui la migrazione in staging prima di applicarla in produzione.

La migrazione MySQL più affidabile è quella che puoi buttare via e ripetere. Tieni i comandi in uno script, mantieni documentate le modifiche alla configurazione e rendi la convalida parte del piano, non un'ultima occhiata piena di speranza.