Latin1 to UTF-8

[nholliss]

Hello All,

 

I wonder if anybody could help me.

 

I am trying to upgrade from MySQL 4.0 to MySQL 5.0.

 

MySQL 4.0 uses the latin1 character encoding, and MySQL 5.0 uses UTF-8. This is causing me problems.

 

I backed up my original database to a file called backup.sql. I then populated the MySQL 5.0 database using the backup file. This worked, however foreign characters are effectively lost. This is a big problem for me as most of my customers are German and French. This problem stems from the fact that the backup.sql file is encoded in latin1, however it is imported into MySQL 5.0 as UTF-8.

 

I have looked all over the internet for a solution. I've tried to convert the backup.sql file to UTF-8, however with no luck. There is an application out there called ICONV which I think will convert the file, however it works with Linux and is quite complicated to use (I'm not used to using command prompts).

 

Would anybody be able to help me with this, or suggest a possible solution?

 

Kind Regards

Nicholas

[♥geoffreywalton]

Are you trying to import into a db where the collation is utf8?

 

There is a default you can set in phpmyadmin for new dbs.

 

Also does your dump drop and re-create the structure.

 

If so does it speciy file fields as latin1-general?

 

Just some things to look at.

[nholliss]

Hello,

 

Thank you for your reply.

 

Yes, I am trying to import into MySQL 5.0, which uses UTF-8. I want all of the data in the new database to use UTF-8, so this isn't a matter of changing the MySQL 5.0 database to a different collation using phpmyadmin Basically, I need everything in UTF-8 from now on because of the way my accounts system synchronises with my website.

 

So, my dump.sql from the MySQL 4.0 database (latin1) doesn't actually specify latin1 in the CREATE TABLE sql code.

 

When I load dump.sql into an empty MySQL 5.0 database which uses UTF-8 I lose characters such as é, à, ê, è, as well as all my German characters. They are all replaced with question marks.

 

I read this help page: http://hostingfu.com/article/mysql-upgrade...-unicode-issues however I can't really understand the commands. I don't think I can use those commands from within phpmyadmin.

 

Kind Regards

Nicholas

 

Are you trying to import into a db where the collation is utf8?

 

There is a default you can set in phpmyadmin for new dbs.

 

Also does your dump drop and re-create the structure.

 

If so does it speciy file fields as latin1-general?

 

Just some things to look at.

[♥geoffreywalton]

Have a look at the mysql section of w3.schools.com and the mysql documentatin, http://dev.mysql.com/doc/refman/5.1/en/com...ne-options.html

 

you can run them by pasteing them into a sql box

 

ALTER DATABASE [database name] DEFAULT CHARACTER SET utf8 -> COLLATE utf8_general_ci;

 

mysqldump --opt --allow-keywords --hex-blob > --default-character-set=latin1 -qc [database name] > database.sql

[nholliss]

I'll give it a go. However I am using shared space (1and1) so I'm not sure whether this will work. A

[nholliss]

I'm really having trouble.

 

I enter this into the SQL window in phpmyadmin (on the old database):

 

ALTER DATABASE db212207457 DEFAULT CHARACTER SET utf8 -> COLLATE utf8_general_ci

 

And I get the error:

 

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE db212207457 DEFAULT CHARACTER SET utf8

-> COLLATE

 

I am so confused

[nholliss]

ok, I think I need to use SSH.

[♥geoffreywalton]

Try doing using options available in phpmy admin

[♥Vger]

Don't forget that you also have to change the default character set used by osCommerce, which is iso-8859-1 (also called Latin 1), and that you'll also have to open every osCommerce file in a plain text editor set to utf and save those files (no edits, just save them).

 

No matter what options you use in phpMyAdmin or via SSH you'll have problems converting a Latin 1 db to utf8. Characters recognised by Latin 1 will not be recogised by utf8 and will be converted to question marks or other code.

 

Basically you may as well open the db in a plain text editor set to use utf8 and save it - and then go through every line and replace the corrupted characters. Its long-winded, but once it's done it's done.

 

Vger

[nholliss]

I've tried using iconv to change the sqldump to UTF-8 but have had no luck.

 

I think I might have to just change the new MySQL 5.0 db to latin1 and try importing that way,

 

 

 

Don't forget that you also have to change the default character set used by osCommerce, which is iso-8859-1 (also called Latin 1), and that you'll also have to open every osCommerce file in a plain text editor set to utf and save those files (no edits, just save them).

 

No matter what options you use in phpMyAdmin or via SSH you'll have problems converting a Latin 1 db to utf8. Characters recognised by Latin 1 will not be recogised by utf8 and will be converted to question marks or other code.

 

Basically you may as well open the db in a plain text editor set to use utf8 and save it - and then go through every line and replace the corrupted characters. Its long-winded, but once it's done it's done.

 

Vger

[nholliss]

This is so incredibly difficult.

 

Can someone please explain to me - does OSC always use latin1, even when installing on a MySQL 5.0 db? If so, how does it use latin1 when the default is UTF-8?

 

I used the following code:

 

mysqldump -h XXX --user=XXX--password=XXX--default-character-set=latin1 --skip-set-charset XXX> dump.sql

 

mysql -h XXX--user=XXX--password=XXX--default-character-set=latin1 XXX< dump.sql

 

Which got the dump from old DB in latin, and then restored this dump into the new DB in latin. I thought this would be the simplest way.

 

However, all of the tables have been set up with latin1_german2_ci collation, for no apparent reason. I'm so damn confused.

 

Can anybody help? I give up converting to UTF, I just want to get my data into MySQL 5.0 in any way possible!

[papitu769]

Could you create a backup script sql. That contains all the data. Replace the characteres are going to be changed fot somethgin like +*+=@(It is very unlikely someone has that in his name). When you have imported the data to UTF8 replace again with the most similar character you could find.