Errori MySQL Comuni e Come Risolverli Rapidamente

Gli errori MySQL di solito richiedono una prima lettura rapida: controlla il log degli errori, identifica la query o il thread che fallisce ed evita di fare supposizioni basandoti solo sul sintomo dell'applicazione. Capire come diagnosticare e risolvere rapidamente gli errori comuni—che spaziano da colli di bottiglia delle prestazioni a guasti critici del servizio—è essenziale per mantenere un'alta disponibilità.

Questa guida copre i guasti MySQL più comuni che puoi triage rapidamente: query lente, deadlock, ritardo di replica e avvisi di corruzione.

Identificare e Diagnosticare gli Errori MySQL

Prima di applicare le correzioni, un'identificazione accurata è fondamentale. Le fonti principali per le informazioni diagnostiche di MySQL sono il Log degli Errori di MySQL e il Log delle Query Lente. Controllare prima questi è il modo più efficace per individuare la causa principale di un problema.

Controllare il Log degli Errori di MySQL

Il log degli errori registra eventi critici del server, informazioni di avvio/arresto ed errori gravi. La sua posizione varia in base al sistema operativo e alla configurazione, ma si trova spesso nella directory dei dati.

Suggerimento: Usa comandi come SHOW VARIABLES LIKE 'log_error'; per trovare il percorso esatto se non sei sicuro.

Utilizzare il Log delle Query Lente

Se le prestazioni degradano senza messaggi di errore espliciti, il Log delle Query Lente è il tuo prossimo passo. Cattura le query che superano un tempo di esecuzione predefinito.

Per abilitarlo (se non è già attivo), devi impostare queste variabili nel tuo file di configurazione (my.cnf o my.ini) e riavviare il server:

[mysqld]
slow_query_log = 1
long_query_time = 2  # Registra le query che richiedono più di 2 secondi
slow_query_log_file = /var/log/mysql/mysql-slow.log

Scenari di Errore Comuni e Correzioni Immediate

Ecco quattro delle sfide operative più frequenti negli ambienti MySQL e i passaggi attuabili per risolverle.

1. Prestazioni delle Query Lente

Le query lente sono il drenaggio di prestazioni più comune. Spesso derivano da indici mancanti, strutture di query inefficienti o una progettazione del database scadente.

Diagnosi

Analizza il log delle query lente. Per una query lenta specifica, usa il comando EXPLAIN per vedere come MySQL la esegue:

EXPLAIN SELECT * FROM tabella_grande WHERE colonna_a = 'valore';

Cerca type: ALL (scansione completa della tabella) o un numero eccessivo di righe esaminate.

Correzioni Rapide

Aggiungi Indici Mancanti: Se EXPLAIN mostra una scansione completa su una colonna frequentemente filtrata, crea un indice su quella colonna: CREATE INDEX idx_colonna_a ON tabella_grande (colonna_a);
Riscrivi le Query: Evita SELECT * nel codice di produzione. Usa i JOIN con giudizio e assicurati che le clausole WHERE utilizzino colonne indicizzate.
Analizza le Statistiche della Tabella: A volte, statistiche obsolete confondono l'ottimizzatore. Esegui ANALYZE TABLE nome_tabella;.

2. Deadlock delle Transazioni

Un deadlock si verifica quando due o più transazioni sono in attesa di blocchi detenuti dall'altra, causando un punto morto. MySQL (usando InnoDB) di solito lo rileva e lo risolve automaticamente annullando una transazione.

Diagnosi

Controlla il log degli errori per messaggi che fanno riferimento a LATEST DETECTED DEADLOCK. Puoi anche controllare lo stato di InnoDB:

SHOW ENGINE INNODB STATUS;

Cerca nella sezione TRANSACTIONS il grafico dettagliato del deadlock, che mostra quali transazioni erano coinvolte e quali dichiarazioni hanno causato l'attesa.

Correzioni Rapide

Accorcia le Transazioni: Mantieni le transazioni il più brevi possibile. Esegui il commit o il rollback rapidamente.
Ordine di Accesso Coerente: Assicurati che tutto il codice dell'applicazione acceda a tabelle e righe nello stesso ordine definito. Se la Transazione A blocca la Tabella X e poi la Tabella Y, anche la Transazione B dovrebbe bloccare X e poi Y.
Usa il Blocco a Livello di Riga: Assicurati di utilizzare clausole WHERE appropriate nelle dichiarazioni UPDATE e DELETE in modo che InnoDB possa bloccare solo le righe necessarie, non intere tabelle (anche se InnoDB per impostazione predefinita utilizza il blocco a livello di riga per le tabelle transazionali).

3. Ritardo o Guasto della Replica

Nelle configurazioni source-replica, il ritardo di replica si verifica quando la replica resta indietro rispetto alla sorgente, portando a letture obsolete. I comandi e i campi MySQL più vecchi usano ancora la terminologia master e slave, quindi potresti vedere entrambi i nomi in produzione.

Diagnosi

Controlla lo stato della replica utilizzando i thread IO e SQL:

SHOW REPLICA STATUS\G
-- Sulle versioni MySQL più vecchie: SHOW SLAVE STATUS\G

Campi chiave da esaminare:

Replica_IO_Running o Slave_IO_Running: Dovrebbe essere Yes.
Replica_SQL_Running o Slave_SQL_Running: Dovrebbe essere Yes.
Seconds_Behind_Source o Seconds_Behind_Master: Indica il ritardo in secondi. Se questo valore aumenta, la replica sta rimanendo indietro.

Correzioni Rapide

Risolvi gli Errori del Thread SQL: Se l'applicatore SQL è fermo, rivedi l'ultimo errore SQL. Saltare un evento con sql_slave_skip_counter o comandi di replica più recenti può causare una deriva dei dati, quindi usalo solo dopo aver compreso la transazione fallita e avere un piano per riconciliare i dati.
Aumenta le Risorse della Replica: Se il ritardo è costante sotto carico di scrittura pesante, la replica potrebbe aver bisogno di più CPU o I/O del disco più veloce per elaborare gli eventi del log binario abbastanza rapidamente.
Risincronizza: Se il ritardo è grave o la replica è danneggiata, ferma la replica, assicurati che la replica punti alla corretta posizione del log binario della sorgente e riavvia.

4. Errori di Corruzione dei Dati

La corruzione dei dati, sebbene rara con le moderne configurazioni InnoDB, può manifestarsi come impossibilità di avviare il server, errori di checksum o risultati di query strani. La corruzione spesso indica un guasto hardware (disco/memoria) o arresti impropri.

Diagnosi

La corruzione è solitamente immediatamente evidente tramite messaggi di errore all'avvio nel log degli errori, che spesso fanno riferimento a tablespace o pagine specifiche che falliscono un test di checksum.

Correzioni Rapide

Esegui Controllo/Riparazione Tabella (MyISAM): Per le tabelle MyISAM, usa CHECK TABLE nome_tabella; seguito da REPAIR TABLE nome_tabella;.
Modalità di Recupero InnoDB: Se InnoDB non si avvia, puoi avviarlo temporaneamente in modalità di recupero per esportare i dati:
```
[mysqld]
innodb_force_recovery = 1
```
Avvia il server, esporta immediatamente tutti i dati critici usando mysqldump, spegni, rimuovi i file di dati corrotti e riavvia senza il flag di recupero.

Avvertenza: innodb_force_recovery non dovrebbe mai essere usato in modo permanente. Ignora i controlli critici e può portare a un ulteriore degrado dei dati se si tenta di scrivere.
Ripristina dal Backup: La soluzione più sicura per una corruzione grave è ripristinare l'intero database dall'ultimo backup valido noto.

Punto Chiave

Risolvi i problemi di MySQL basandoti sulle prove, non sulle supposizioni. Il log degli errori, il log delle query lente, EXPLAIN, lo stato di InnoDB e lo stato della replica di solito mostrano il passo successivo. Mantieni i backup testati prima di toccare il recupero dalla corruzione o i comandi di salto della replica.

Miglior Pratica: Monitoraggio Proattivo

La correzione più rapida è spesso la prevenzione. Implementa strumenti di monitoraggio completi (come Prometheus/Grafana, Percona Monitoring and Management (PMM) o strumenti del provider cloud) per monitorare le metriche chiave:

Numero di connessioni e tasso di hit della cache dei thread.
Utilizzo e tasso di hit del buffer pool InnoDB.
Ritardo di replica (Seconds_Behind_Master).
Utilizzo I/O del disco.

Gli avvisi basati su queste metriche ti consentono di affrontare query lente o problemi di replica prima che si trasformino in guasti critici.