Migrazione di un database MySQL e caratteri speciali

Chi si è cimentato qualche volta nella migrazione di un database MySQL da un macchina ad un’altra si sarà spesso imbattuto in qualche problema con i caratteri speciali. Il problema si manifesta quasi sistematicamente all’interno del testo o delle colonne di tipo varchar contenenti dei dati che hanno al loro interno dei caratteri accentati, virgolette, apostrofi e altri caratteri dalla codifica diversa a seconda dello standard che si utilizza.

Per esempio, un tipico caso prettamente italico è quello che riguarda il monosillabo:

è

che durante le migrazioni rischia di diventare all’interno del database:

â

e di essere successivamente visualizzato nel sito web con i seguenti simboli:

’

Se state trasferendo i vostri dati dalla versione 4.0 alla 4.1 di MySQL è un problema molto comune anche se spesso si può verificare quando si trasferiscono dei dati da un database all’altro sulla stessa installazione di MySQL ed anche se le due versioni dei due database sono entrambi la 4.1 potrebbero esserci dei problemi di questo genere: insomma se vi va bene siete nella vostra giornata fortunata, altrimenti non fatevi prendere dal panico perchè siete semplicemente nella normalità.

A questo punto se si fa una rapida ricerca sul web le diverse soluzioni al problema si basano su complicate e rischiose funzioni che ricercano e sostituiscono i caratteri incriminati per eliminare il problema alla radice. Questi metodi molto poco ortodossi fanno perdere parecchio tempo e non sono una soluzione efficiente al problema che invece a detta di chi ci lavora quotidianamente una soluzione più logica e sensata ce l’ha.

La natura del problema dovrebbe risiedere nel fatto che mysqldump, la funzione che si occupa di esportare i database MySQL, utilizza l’UTF8 per la codifica dei caratteri mentre, nella maggiorparte dei casi, le tabelle MySQL seguono la codifica Latin 1. Basterebbe quindi avere l’accortezza di creare delle tabelle che utilizzino come codifica l’UTF8 per evitare l’inconveniente: chi utilizza una versione pari o inferiore alla 4.0 di MySQL non potrà effettuare questa operazione perchè è supportata solo la codifica Latin 1 e per motivi di retrocompatibilità molto spesso si creano tabelle che utilizzano quest’ultima codifica. Se non vi si è mai presentato il problema fino ad adesso è proprio perchè avete sempre lavorato con tabelle le cui colonne utilizzavano già la codifica UTF8 che non causa alcuna disfunzione, quando però vi troverete a dover esportare tabelle aventi colonne con la vecchia codifica Latin 1 quasi sicuramente vi si presenterà il fastidioso inconveniente.

Quando abbiamo infatti un datatabase di nome example_db ed andiamo ad esportarlo eseguendo direttamente o indirettamente (ovvero via phpMyAdmin) il comando:

mysqldump -u mysql_username -p example_db > example_db.mysql

nel caso in cui le colonne delle tabelle abbiano una codifica di tipo Latin 1 ma al loro interno siano presenti caratteri codificati secondo lo standard UTF8 (ovvero nella stragrande maggioranza delle situazioni) tutto verrà ritradotto nella codifica UTF8: anche quei caratteri speciali che già lo erano e che vengono erroneamente interpretati come se fossero codificati in Latin 1. Quando si andrà ad importare il database da un’altra parte in teoria dovrebbe filare tutto liscio perchè le sue tabelle sono adesso in formato UTF8 e MySQL 4.1 supporta questa codifica ma i caratteri speciali incriminati hanno subito una doppia codifica e pertanto quando eseguirete il comando:

mysql -u mysql_username -p new_db < example_db.mysql

tutto questo farà si che le tabelle che myslqdump aveva codificato in UTF8 verranno ritradotte nella codifica da voi scelta per le vostre tabelle, ma i caratteri al loro interno che erano già in UTF8 ed erano stati mal interpretati e ritraddotti in questa codifica, adesso daranno origine ad i problemi di interpretazione visti sopra.

Per evitare tutto ciò ci sono due metodi. Il primo consiste nel risolvere il problema lavorando sulle singole colonne delle tabelle del database, il secondo invece lavora sul file esportato prima di reimportarlo nuovamente.

Metodo 1 – Riparare un singola colonna

Questa soluzione non farà altro che far funzionare mysqldump come dovrebbe funzionare in teoria e quindi far si che quando andrete ad importare il database tutto avverrà normalmente. I caratteri speciali vengono alterati anche se soltanto apparentemente perchè in realtà sono formattati correttamente in UTF8 però in una colonna che è codificata secondo lo standard Latin 1. Per effettuare tutto questo se avete una colonna di tipo varchar(255) che per comodità identificheremo come “column_name” in una tabella che chiameremo “example_table” bisognerà eseguire le seguenti istruzioni:

ALTER TABLE example_table MODIFY column_name BINARY(255);ALTER TABLE example_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8;

Mentre per le colonne di testo che per comodità identificheremo come “text_column_name” in una tabella che chiameremo “example_table” bisognerà eseguire le seguenti istruzioni:

ALTER TABLE example_table MODIFY text_column_name BLOB;ALTER TABLE example_table MODIFY text_column_name TEXT CHARACTER SET utf8;

I dati a questo punto dovrebbero essere pronti per l’esportazione e quando li andrete ad importare tutti i caratteri speciali dovrebbero venire visualizzati correttamente. In caso contrario molto probabilmente il problema che state riscontrando non è lo stesso che vi ho descritto in precedenza.

Metodo 2 – Risolvere il problema in fase di importazione

Quando avete un numero di colonne improponibile per applicare a ciascuna di esse il Metodo 1 potete optare per una soluzione diversa che vi permetterà di risolvere il problema al volo in fase di importazione e che dovrebbe funzionare nella maggior parte dei casi (ripeto: nella maggior parte dei casi). In sostanza bisogna forzare l’esportazione delle colone in Latin 1 e poi far credere a MySQL in fase di importazione che siano in formato UTF8. Perciò sarà necessario esportare il database, che chiameremo in questo caso “example_db”, con il seguente comando:

mysqldump -u mysql_username -p --default-character-set=latin1 example_db > example_db.mysql

Adesso aprite il file esportato (che si chiamerà example_db.mysql in questo caso) e dovreste avere una linea all’incirca come questa:

/*!40101 SET NAMES latin1 */;

Modificatela sostituendo latin1 con utf8, così:

/*!40101 SET NAMES utf8 */;

Adesso importate il file nel nuovo database:

mysql -u mysql_username -p new_db < example_db.mysql

Nella stragrande maggioranza dei casi dovrebbe terminare tutto con successo e non dovreste riscontrare i famosi problemi con i caratteri accentati nel nuovo database. A volte potrebbero verificarsi dei problemi dovuti a dei conflitti che rendono questa soluzione inutilizzabile: in questi casi sarà necessario utilizzare il Metodo 1 e correggere manualmente tutte le colonne per poi procedere all’esportazione.

Siete capitati anche voi nel problema? Come l’avete risolto? Conoscete altri metodi? C’è qualcosa che non vi torna?