By knugar on

I finaly decided to upgrade my drupal site from 4.6.3 to 4.7.3.

After backing up my site, I restored it on my local machin (drupal 4.6.3) without problems. Next I followed the upgrade guide and it seems the update.php script does something with my data base data.

After the upgrade, all non english characters (such as Swedish ÅÄÖ) gets displayed wrong (the character Ä looks liek ä etc)

I've searched the forums and found some similar problems:

On my production site i have:

PHP 5.1.4
MySQL Server 4.1.18

mysql> show variables like "char%";

+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
7 rows in set (0.00 sec)

On my local test site i have:

PHP 5.0.5
MySQL Server 5.0.17-log

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)

I hope anyone can point me in the right direction on how to fix this problem.

⋅ Categories: Drupal 4.6.x, Drupal 4.7.x

Comments

knugar’s picture

knugar commented

Starting out with the upgrade, I didn't know much about MySQL encodings, nor Drupal encodings. Once in a time when I installed Drupal the first time for my Swedish site it just worked out of the box :-)

I found this Convert a db to UTF8 after upgrading to MySQL 4.1 very useful. I figure my old 4.6 Drupal stored utf8 encoded characters in my latin1 data base.

First I made a database dump:

mysqldump --user=XYZ -p --default-character-set=latin1 --skip-set-charset --lock-tables=false greenway_drupal > drupal_db_dump.sql

I didn't have permissions to use LOCK TABLES, therefore I added the --lock-tables=false option.

Next I replaced all occurences of 'latin1' with 'utf8':

chgrep latin1 utf8 drupal_db_dump.sql

I think all that really matters are that the DEFAULT CHARSET=latin1 changes to DEFAULT CHARSET=utf8 so you might need to refine the replace patterns if latin1 occurs in ohter places.

Drop old database and create it again:

DROP DATABASE drupal;
CREATE DATABASE drupal CHARACTER SET utf8 COLLATE utf8_general_ci;

Import data:

 mysql --user=XYZ -p  --default-character-set=utf8 drupal < drupal_db_dump.sql

After running the update.php I got my friends Å Ä Ö back!

alexandreracine’s picture

alexandreracine commented

But... instead of the chgrep program I used "replace"

replace latin1 utf8 -- smdrupalbd-latin1.sql

Alexandre Racine

www.gardienvirtuel.com Sécurité informatique, conformité, consultation, etc

www.salsamontreal.com La référence salsa à Montréal

z.stolar’s picture

z.stolar commented

http://drupal.org/node/77775

======================
Z.Stolar
iStolar Consulting
linnovate.net - Open Source Solutions
iStolar illustration