By shimamoto on

I'm transferring my Drupal installation to a new server. I copied all the files over and exported/imported the database. Everything works fine, but I noticed that some of the Asian language characters in nodes are no longer displayed correctly. I think it might be a problem with the collation, but I'm not sure how to fix it.

For example:
Original: 保存した場所
New: ä¿å­˜ã—ãŸå ´æ‰€

Can anyone tell me what is causing this problem and how to resolve it?

Thanks.

⋅ Categories: Drupal 5.x

Comments

ronan’s picture

ronan commented

How did you transfer the database? Did you use the command line mysql client or another interface like phpMyAdmin? If you used the command line client, try executing the following mysql command before importing the file:

SET NAMES utf8;

This should set the appropriate client and connection encodings.

If you used phpMyAdmin you should be able to set the collation from the home page of the tool. Also, make sure the collation of the database is UTF-8 before importing.

Hope this helps
------------------------------------
Ronan - Gorton Studios - http://www.gortonstudios.com/

------------------------------------
Ronan
Founder - NodeSquirrel - https://www.nodesquirrel.com/
Agency Tools Lead - Pantheon - https://www.pantheon.io/

shimamoto’s picture

shimamoto commented

I used phpmyadmin to export the database. And I imported it at the command line.

The collation is set to utf8 in phpmyadmin, but I noticed that a few tables in the exported database have a latin1 charset.

shimamoto’s picture

shimamoto commented

Here's the charset info in phpmyadmin under Variables on the original server:

character set client - utf8
(Global value) - latin1
character set connection - utf8
(Global value) - latin1
character set database - latin1
character set results - utf8
(Global value) - latin1
character set server - latin1
character set system - utf8
character sets dir /usr/share/mysql/charsets/
collation connection - utf8_general_ci
(Global value) - latin1_swedish_ci
collation database - latin1_swedish_ci
collation server - latin1_swedish_ci

jeefers’s picture

jeefers commented

Same here.

jeefers’s picture

jeefers commented

Im importing using the dump file and shell access to my host. Is there a different way of setting that, when importing that way.

Thanks,

Jeff

cog.rusty’s picture

cog.rusty commented

Are you moving between mysql versions, from 4.0 or older to 4.1 or newer?

shimamoto’s picture

shimamoto commented

The original server is running MySQL 4.1.22. The new server is running MySQL 5.0.22.

cog.rusty’s picture

cog.rusty commented

It shouldn't be hard then (if your server supports utf8).

When you create the new database, and while it is still empty, open it with phpmyadmin, go to the "Operations" tab, and select default collation utf8_general_ci.

Then, before importing the database dump, open it with a text editor compatible with utf8, and search and delete any references to latin1 or any other character set.

shimamoto’s picture

shimamoto commented

I added the following to the new server's mysql my.cnf file:

[mysqld]
init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
default-character-set=utf8

[client]
default-character-set=utf8

I deleted the database and imported it again with these settings and it just worked.

jeefers’s picture

jeefers commented

My server doesnt give me write access to the my.cnf file. Also Im preforming a mysql dump restore using shell access how do i set the client to default-character-set=utf8

Thanks,

Jeff

ronan’s picture

ronan commented

You should be able to set these variables from the command line mysql function, the settings will only last for that connection (until you quit the mysql program), but you should only need this while you are importing the database dump.

Try running:

SET NAMES utf8;

from the mysql command before you import the dump.

To check the current connection values you can try running:

mysql> SHOW VARIABLES LIKE 'character_set%';

and

mysql> SHOW VARIABLES LIKE 'collation%';

And set the appropriate variables with:

SET character_set_client = utf8;

etc.

More information can be found at:
http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html

------------------------------------
Ronan - Gorton Studios - http://www.gortonstudios.com/

------------------------------------
Ronan
Founder - NodeSquirrel - https://www.nodesquirrel.com/
Agency Tools Lead - Pantheon - https://www.pantheon.io/

knugar’s picture

knugar commented

I've go a site in Swedish so we got characters such as åäö and ÅÄÖ.

I just tried to back up the site hosted on a web hotel by exporting the DB from phpMyAdmin ==> backup.sql

On my local box I wanted to install a replica by using command line client mysql -uXXX -p < backup.sql

After this all åäö show up like crap but after modifying the /etc/my.cnf as suggested by shimamoto it worked just fine :-)

vkr11’s picture

vkr11 commented

Subscribe
I am having similar problems.

edvanleeuwen’s picture

edvanleeuwen
Primary language Dutch
Location Waalwijk
commented

Be sure to set the compatibility to ANSI when exporting and importing. If you have strange characters, this could lead to several other messages, including:
- unserialize messages
- cannot send header information/header information already sent

Best regards,

Ed

Tom Ash’s picture

Tom Ash commented

What worked for me was using the following syntax for the import:

mysql -uusername -ppassword --default-character-set=utf8 database < backup.sql

lowfour’s picture

lowfour commented

You are the man! I transferred a site with both Spanish (ñ) and Swedish (ö) characters and when i restored the .sql dump the spanish side of the site was broken and there were strange characters everywhere. Spanish (español) was the default language and as it contained the (ñ) in the language name it made the site just work in swedish.

I tried your way of restoring the .sql and it worked perfect. Thank you all for your help.

DrupalGuy24’s picture

DrupalGuy24 commented

Any chance you can break this down for someone who is a bit of a drupal newbie? I have this exact same problem with the swedish characters. Thanks!

lowfour’s picture

lowfour commented

Hi!

I am not an expert. I just exported the database from phpmyadmin with the usual method: http://drupal.org/node/120630

Then I uploaded that .sql file to my new server, where I had created a database and a database user with the same names and password (to avoid any extra work).

Finally I just followed Thomas Ash advice and restored the database dump with the following command, that forces to use UTF8 as character encoding:

1) Open terminal and login via SSH to your Linux VPS server (I don't know if that works also in a normal hosting with ssh)

ssh user@ip.ip.ip.ip

2) Get to the directory where you uploaded the .sql file with the CD command

cd /path/of/your/directory

3) Run Thomas command in the terminal (i modified it a bit for clarity purposes)

mysql -u USERNAME -p --default-character-set=utf8 DATABASENAME < backup.sql

Where:
USERNAME: is your username for mysql. root or admin is the most common.
DATABASENAME: is the database that you want to overwrite/restore from the file
backup.sql: The exact name of the file you first created in phpmyadmin and uploaded to the new server.
< symbol: it indicates "fill up that database with this file".

It will ask you for the password. Use your mysql password.

4) Press intro and it will automagically load the backup in the database with the correct encoding. So basically it is the classic and tested database restore for mysql with the magic "--default-character-set=utf8" bit that does the trick.

I am sorry if I am not too clear, I am not an advanced user by any means. Hope it helps.