CODEWITHSUNDEEP
mysqlencodingutf-8
user1351482
user1351482

Reputation: 379

Converting "wrong" MySQL encoding from old server to correct UTF-8 on new server

I set up a web project on a web server with apparently wrong encoding. But somehow (I really don't know why), we figured how we had to deal with it and the encoding worked for us. But now we moved the data to a new server with a correctly set up UTF8 database and surprise, the encoding is wrong. How can we "correct" the data, is there any best practice?

Example old server: http://www.orat.io/stmt/200 new server: http://www.firefile.net/stmt/200

Thanks a lot!

Upvotes: 3

Views: 548

Answers (1)

Tomas
Tomas

Reputation: 59505

This actually happens in practice - I've seen it sometimes :-) What usually happens:

  • database is in utf-8
  • input/output is in encoding1 - say it is iso-8859-2 (i.e. the meta-charset in HTML)
  • but, because the previous admin didn't know how to properly set the database connection encoding (this is the charset which is specified using set names SQL command), the database connection is configured as if the input/output is in different encoding2 - say iso-8859-1 - default on many linux systems.

To get the correct data out of the database, you must access it in the same erroneous way as you did before - e.g. for the example above, set iso-8859-1 encoding but in fact get the iso-8859-2. Everything "works" until someone starts to access the database correctly.

You have not provided enough detail about what is encoding1/encoding2 in your case and you probably don't know it. So, either look at the old setup or try to figure out by trial and error.

The easiest way to re-code your database is probably to do mysqldump in encoding2, then claim it's encoding1 (just put the set names encoding1 at the beginning of sql file) and import the database into an empty one by source-ing the sql file. All database fields of course stay in utf-8 all the time.

Be careful and use PHPMyAdmin as an independent tool to see the "real" state of the database. :-) Good luck with this rebus.

Upvotes: 2

Related Questions