Errors after importing Latin-1 as UTF-8

Ange52

Hey all,

I think I messed up quite a few things in my database after importing a backup of it. I imported the backup as UTF-8, while in retrospect I believe that I should've uploaded it in Latin-1. Since I did this import, all of my foreign characters have been replaced by those unintelligible characters once I bring the values up to the browser (they show up as black diamonds with a white question mark in Safari, or just an empty square in IE).

Does anyone out there know any way for me to recover the original values to these characters in my database? Any help here would be GREATLY appreciated.

Thanks in advance,
Ange52

MarkR

When you do a backup and restore, the character set will be stored clearly in the file by mysqldump. mysql will then use this same character set when restoring the data.

So unless you messed with the file in the interim, this will never happen. MySQL dump files are not (just) plain text and should never be treated as such, e.g. edited in a text editors.

Alternatively if you restored it on to some ancient version of MySQL which doesn't handle encodings correctly it will break.

If you used some broken software to do the backup / restore, trouble might happen. To avoid this, always use the proper mysqldump / mysql programs to backup and restore. Things which emulate them (e.g. phpmyadmin) usually get it wrong in subtle but ruinous ways.

Mark

Ange52

I did notice that it specifies with character type is necessary for each table, but I was hoping that the problem would be this simple. My errors actually came about when I was using a backup provided by my webhost -- I was just hoping I could pinpoint the error like that.

Another thing that I am noticing now: in most cases, the actual values are being preserved directly in the MySQL database. Previously, they would be stored in the database as character sets to represent a single letter. That is to say something like Ä« would be in the database, but would show up in the browser as è. Now, however, è is showing up in the database, and an unintelligible character is coming into the browser.

Do you have any idea about why this might be happening, or how I could possibly recover these values to the character sets so that everything works out in the browser?

Thanks,
Ange52