UTF-8 Latin1 and the £ that looked like a ?

On moving a Database from an older version of PHP and MySQL 5.old to a newer version of PHP and MariaDB 10 I found that the contents had been changed - or rather - it was not rendering correctly. The primary issue I was seeing was that the £ was rendering as question mark in a black diamond. This is kind of vital when this is a billing platform that takes payments, and displays them - like WHMCS for example.

This was the equivalent of hitting £ and getting a # for those who have ever had to use a non UK keyboard : /

However - where did the issue lay? Was this the Database configuration, something to do with PHP or something to do with Apache.

In this case the issue was with the export of the Database.

Despite both of them announcing the DB was Latin1, and the tables of various types, it is apparent that something was awry somewhere.

The process involved dumping the data filtering the content, and then re importing it.

You may find this useful if you are seeing question marks in diamonds where you are expecting pounds stirling.

Anyway - I digress

time mysqldump -u root -p -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B MY-DATABASE-NAME > /home/username/MY-DATABASE-NAME-OLD.SQL

Which squirts it out in a format that is a little more flexible.... and then the magic happens.....

time sed 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/' < /home/username/MY-DATABASE-NAME-OLD.SQL | sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' > /home/username/MY-DATABASE-NAME-NEW.SQL

On the basis of the shining example above - the file you can now work with is the newly shiny and free of confused symbols /home/username/MY-DATABASE-NAME-NEW.SQL - feel free to import that as you see fit.

That was a pain to figure out for a Bear of Little Brain such as myself.... especially when it formed a small part in a long chain of events that needed to occur to migrate of dump, change, import, upgrade, dump, import, success.

Great success.

Leave a Reply

Your email address will not be published. Required fields are marked *