Mysql charset problem

Question

I'm trying to import some data from one server to another. But when I do it, I'm having problems with charset.

Words like Goiânia became Goiâni and conceição became conceição

My Application was set to use latin1 charset

Server 1 : MySQL Charset : UTF-8 Unicode (utf8) table collation : latin1_swedish_ci

Server 2 : MySQL Charset: UTF-8 Unicode (utf8) table collation : latin1_swedish_ci

Command I used to export data from server 1 mysqldump -u root -p --default-character-set=iso-8859-1 database_name > db.sql

Command used to restore to server 2 mysql -u root -p database_name < db.sql

Answer 1

Depending on the version of mysql, you want to use either:

--default-character-set=latin1

or

--default-character-set=utf8

There is an odd bug in some versions of mysqldump where specifying utf8 on the command line would double encode a table already encoded as utf8 which was fixed a year or so ago in 5.0.51+ if I recall.

If your character sets are specified correctly in the tables, I don't believe you need to use the default-character-set when doing the dump as it takes it from the table collation and character set.

Answer 2

Doublecheck the collation and charset in server 1. It looks like it's a different format and even though you force another one (iso-8859-1) on the export this could not work. Double-check it on table and database, etc..

Let me know if this helps.