Exporting a latin1 encoded MySQL database from PhpMyAdmin

Tonight, I helped a veteran Piwigo user to migrate to piwigo.com hosting. The main problem I encountered was PhpMyAdmin.

In his MySQL database, strings were using latin1 character set, he was still using PhpWebGallery 1.7. Unfortunately, there was no access to any mysql command line, so I had to deal with PhpMyAdmin :-/ and whatever option I could check or uncheck in the export tool, I had an utf8 encoded database dump. I import it the following way:

mysql --user=*** --password=*** pwg213 < dump.sql

Imported as is, the dump generated a messy result in the browser. So instead of just importing the dump from the command line, I used the mysql shell and set the client character set first:

mysql --user=*** --password=*** pwg213
mysql> set names utf8;
mysql> source dump.sql

Then all was fine, the upgrade from PhpWebGallery 1.7 to Piwigo 2.1 was as easy as usual with only 664 photos.

Written by plg

October 7, 2010 at 11:37 pm

Posted in Miscellaneous

2 Responses

Subscribe to comments with RSS.

Thanks for sharing this info, but you make it too simple to be true. You didn’t fully describe how you used phpMyAdmin to execute those commands. I tried to use phpMyAdmin to execute those commands and it failed. How can you reconnected from with phpMyadmin when you are already connected!?

Please show me the exact commands you typed in phpMyAdmin so that I can repeat this process. I am stuck with a phpMyAdmin version on my web-hosting provider that won’t allow me to set the encoding properly. When I export the data, I get garbage. The data is latin1 Arabic.

Thanx in advance.

Khalid

January 18, 2011 at 10:51 pm

Reply
Hi Khalid,

I mainly didn’t use PhpMyAdmin actually, but the mysql command line tools, which is much easier to use in my opinion. I only use PhpMyAdmin for the export and the encoding options were useless: they didn’t change the content of the dump.

plg

January 18, 2011 at 11:45 pm

Reply

Pierrick on Piwigo