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?

18 pensieri riguardo “Migrazione di un database MySQL e caratteri speciali

  1. Credo che la cosa migliore sia utilizzare l’interfaccia di PHPMyAdmin in caso di importazione. Anche se importate un file UTF8 e la tabella mysql ha i campi con la codifica U8, spesso semplicemente ignora i caratteri accentati. Con l’inserimento delle insert direttamente nella textarea di PHPMYadmin a me va benone…
    Ciao

  2. Il mio problema non è nell’esportazione perché sono andato a controllare
    in codice nel TXT che myAdmin mi ha creato e sono corretti.
    Ma nell’importazione mi mette ? al posto di ‘èéàò… e compagnia bella.

    Alla fine ho trovato una soluzione che non so se è una soluzione
    ma al momento sembra funzionare, è rozza all’ennesima potenza
    ho fatto copia incolla dei 4MB di contenuti nella casella delle
    query SQL di myAdmin e gli ho detto esegui (ci ha messo un po’ però
    mi pare che tutti i caratteri siano corretti)

  3. @Gabriele: Mysql Migration Tool è solo per le versioni 5, il che, poi non esclude, come già avviene per tutti gli altri DBMS commerciali, che tu possa collegarti ad altri demoni mysql.

    Personalmente uso quotidianamente phpmyadmin per la gestione di molti database, ed anche interagendo con sql server tramite odbc non ha mai dato grossi problemi. :k:

  4. Quando il database è già stato esportato credo che l’unica sia andare a fare una sostituzione su ciascuna tabella colonna per colonna dei caratteri accentati che sono “andati persi”.

    mmm per le foto dello ZenaCamp mi ero dimenticato… adesso ci dò un’aggiustata e le pubblico nella galleria ftografica

  5. Ottimo articolo, ma come si fa quando l’esportazione è già avvenuta e nel nuovo db si ritrovano caratteri strani?
    A proposito che fine hanno fatto le tue foto dello zenacamp?

  6. @Gabriele: no non l’ho provato semplicemente perchè ne ignoravo l’esistenza

    @Jtheo: si il problema il più delle volte è phpMyAdmin che fa casini durante l’esportazione

    @Max: si anch’io ho utilizzato fino a poco tempo fa il metodo “rozzo” solo che questo dovrebbe essere più efficace e veloce e dovrebbe evitarti di far casini (ho sempre paura che le funzioni “trova&sostituisci” vadano a sostituire qualcosa che non devono)

  7. La soluzione che ho adottato io è sicuramente la più rozza:

    una volta esportato il database con PhpMyAdmin, ho notato i caratteri errati, e allora con un semplice editori di testo ho fatto un semplice SOSTITUISCI TUTTO dei vari caratteri incriminati: à è é ì ò ù.

    L’altro metodo che invece prediligo è utilizzare wp-db-backup che non crea problemi di esportazione dei database e una volta reimportati con PhpMyAdmin non danno alcun problema con Latin1!

    L’unico inconveniente rilevato con wp-db-backup l’ho incontrato con il plugin wp-Mygallery (risolto mettendo mano al file.sql) :mrgreen:

  8. Uno dei problemi è quando devi passare da phpmyadmin che scazza, avevo trovato un trip per risolvere in questo caso ma non so dove l’ho salvato 🙁

Rispondi

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.