Thread

I have a multi-language site that I’m moving to another hosting company. I follow the instructions from the wiki:
http://expressionengine.com/knowledge_base/article/how_do_i_move_ee_to_another_server/

The import of database with phpMyAdmin is ok, but the foreign characters are messed up. I tried on both MySQL 4.1.22 (live) and 5.0.51a (localhost).

I use UTF8 encoding, because I have english, french, german, italian, spanish, romanian and russian. After first import the collation was changed to latin1_swedish_ci. I changed to utf8_unicode_ci, but the result was the same. I’m not sure where the swedish collation come from. I used the Foreign Character Extension 1.1.1 for adding content. Anyway, I tried every combination with these 2 collation settings, but no success.

Can anybody with more experience with MySQL help me, please?

EE version is 1.6.2 build 20080206

When you use phpMyAdmin to back up the data from MySQL4, check what is displayed as the Charset.
(Also make sure that “Connection (and) Collation” is set to UTF-8 when you back up!)
If “latin1” is displayed as the Charset, then MySQL thinks the data is “latin1”, whereas you know it’s “UTF-8”.
In this case, “latin1” will be in the top of the backup file, which is a text file.
You need to change this to “utf8” (small letters, no hyphen, I think) so that the data is imported to MySQL5
as “UTF-8” not “latin1”.
When you import to MySQL5, you need to make sure that “Connection (and) Collation” is set to UTF-8!
Note: For the “Collation” part of “Connection (and) Collation” you can use the default (for UTF-8) of “unicode_ci”.
Note: The browser encoding, when exporting and importing, may need to be set to UTF-8!

HTH!

Thank you for your answer. I discovered that the default collation when a new MySQL database is created is latin1_swedish. This is the hosting default. Right now I made again the export, first with latin1_swedish and second with utf8_unicode. The result look the same and in both cases the foreign characters are replaced with “?”.

I have a localhost setup with XAMPP. The default collation is latin1_swedish, too. Since MySQL is a swedish company, that make sense.

I have a copy of the entire directory of my previous hosting. I tried to import the MySQL from here, but I received some errors. The exported version is different from the copied one, but the second seems to have all the foreign characters intact. Except the database format, that look different. I think a conversion is possible, but I have no idea how this can be done. Here is the first line of the file:

Exported version:
—phpMyAdmin SQL Dump

Copied version:
—MySQL dump 10.9

If anyone can help, please do it. Thank you.

> the default collation when a new MySQL database is created is latin1_swedish

Can you use phpMyAdmin to backup and restore the database, rather than MySQL Dump?
You will see “Connection (and) Collation”—this is actually *two* settings:
(1) the Connection Character Set, which is very important, and
(2) the Collation, which is not important.
You should change the Connection setting to UTF-8 when you back up, and make sure
that your browser encoding is UTF-8.

If you look inside the head of the backup file (which is a text file) you will see
some commands to create the database (and define its default character set), then
some to create the tables within the database (and define their character sets).
With MySQL5 you should be able to set the encoding of tables within the database
to UTF-8, regardless of the default encoding of the database.  You need to change
the encoding shown in the backup file from “latin1” to “utf8” for the tables
containing the multilingual data.
When you import the file you should change the Connection setting to UTF-8,
and make sure that your browser encoding is UTF-8.

> Can you use phpMyAdmin to backup and restore the database, rather than MySQL Dump?

Yes, I can and I did, but the result is the same, no mather what character set or collation I choose. In browser I used a lot of available encoding, but nothing display what I need.
MySQL dump is the only place where the foreign characters are correct.

Anyway, thank for your input. I’ll check what you said asap.

This may have to wait until tomorrow so I can bump Ingmar, the resident expert on characterset issues.  But- when you export- and don’t change any settings- what’s it showing in the table export for the language setting (if anything)- and what version of mysql are you exporting from?

Right now I can’t access the previous hosting account so I don’t know the answer. This is strange because this problem appeared in the last 3-4 hours. Anyway, I am happy if I can extract the content from the MySQL copy.

But I have another problem: I can’t access the control panel in the previous EE install. The domain name is no longer pointing to this host, but I have an alternative address where I can access the CP login page. I enter the user name and the password and nothing happen, the browser display the same CP login page. I tried the “Forgot your password?” route, but no email is sent. Is there any way to access the CP?

Did you see Moving Servers on the Wiki? Some other links that may help are:
Can’t login, Cookie domain, Sample Config.php, Moving servers, Using UTF-8, Using UTF-8 (another)

No, I used only the Knowledge Base infos:
http://expressionengine.com/knowledge_base/article/how_do_i_move_ee_to_another_server/

Thank you for the link, I will check how it work.

There are quite a few threads going on here, and this one is getting rather long.  So- let’s try to keep things simple.  Start a new thread for the login issue- should be able to override the current settings via editing the config.php file directly and get in that way.

Now- for the character set issue- you want to keep the character set the same from one db to the other one.  If you want to change things, you will need to convert the actual db, not just change the db settings.

So- it would help to know the version of mysql you are exporting from and importing to- and take a look at the export- does it specify collation settings in there?