How to migrate data from latin1 to utf8

Quote from: OpenGeek at Jun 03, 2007, 07:07 PM

One thing to keep in mind in the character set/collation stuff is that it is database specific. In other words, this new feature controls only the PHP/MySQL client connection, and has absolutely nothing to do with the character set with which you are presenting your site in, although those should match to avoid problems with presenting characters outside of the encoding your pages are indicating. I always create my databases using utf8 and utf8_general_ci, and present them using UTF-8 to avoid any problems; generally, this is the easiest way to ensure you can handle the widest number of possible languages.

Thanks. I don’t know where the utf8_swedish_ci came from. Can I just change those tables to utf8_general_ci in phpMyAdmin without corrupting anything? My config file has Latin1 and my site is all in English.

Bob

Quote from: BobRay at Jun 04, 2007, 01:17 AM

Thanks. I don’t know where the utf8_swedish_ci came from. Can I just change those tables to utf8_general_ci in phpMyAdmin without corrupting anything? My config file has Latin1 and my site is all in English.

Bob

Unfortunately no, there is no easy way to convert data stored in latin1 collations to a utf8 collation, but there are several ways to do it, depending on what tools you have access to. You might read articles like this http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html which I found with a quick Google search on the terms "convert latin1 to utf8 encoding MySQL"
;)

Quote from: OpenGeek at Jun 04, 2007, 01:44 AM

Quote from: BobRay at Jun 04, 2007, 01:17 AM

Thanks.  I don't know where the utf8_swedish_ci came from. Can I just change those tables to utf8_general_ci in phpMyAdmin without corrupting anything? My config file has Latin1 and my site is all in English.

Bob

Unfortunately no, there is no easy way to convert data stored in latin1 collations to a utf8 collation, but there are several ways to do it, depending on what tools you have access to.  You might read articles like this http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html which I found with a quick Google search on the terms "convert latin1 to utf8 encoding MySQL"
;)

Thanks again. Sorry to be so much trouble but I'd like to do the conversion and want to make sure I have it straight. The sooner I do it, the less trouble it will be and I'm probably not the only person with this issue in MODx.

PhpMyAdmin will do the sqldump for me with "export" (with "drop table and create table options") and doesn't ask about character stuff.

It dumps the character stuff for each table - full of latin1 and swedish.  PhpMyAdmin tells me the mysql defaults for the database are utf8 and utf8_unicode_ci.  I assume that I have to change the second to utf8_general_ci but I'm assuming that I change that after the export and before the import , or is it safe to do before the export?.

I assume that I go through dump.sql changing all instances of  "swedish" to "general" and "latin1" to "utf8."

Then  "import" dump.sql.

The only thing that has me worried is that "import" asks for the "character set of the file."  Should that be utf8 because that's what we want, or should it be latin1 because that's the original format?

Final step would then be editing config.php.inc to change latin1 to utf8.

Does that sound about right?

BTW, PhpMyAdmin will allow me to edit the structure of each table and change the collation of each problem field. I'm guessing from your earlier comments that this is not a complete solution and therefore unsafe? I guess the method above would be faster anyway.

Bob





[Update] This is NOT the way to convert your DB. See the link in Susan's message below. [ed. note: BobRay last edited this post 10 years, 9 months ago.]

Hello.

I know it is an old thread, but I had not luck with the script.

Even though I am able to go through the process, when I look at "modx_site_content" table, a lot of content is delete.

Is there something I am doing wrong ?

1 - Export the database ( with drop table checked )
2 - Import it on localhost ( using mamp )
3 - Run the script
4 - Paste the result in mysql



Thanks

This is a really old thread. BobRay has another solution http://bobsguides.com/convert-db-utf8.html