Drupal supports Unicode (UTF-8) out of the box and so Unicode should be used for all your Drupal installations. Problem is, some things you do with your Drupal installation might result in a database or a table that have non-unicode  collation.

Here are a few general tips on making sure everything is using Unicode :

Install manually, don’t use Fantastico 

One of the things that Fantastico does is install the database in the wrong collation. Create your database by yourself, make sure that the collation is set right, and then proceed with the Drupal installation.

Verify by running a Unicode change script

Some Drupal modules still have bugs of creating tables with the wrong collation. Here’s a piece of quick code you can run from your shell if you notice mixed collation in your DB. I will go table by table and change the collation to Unicode. If you don’t have shell, try a shell solution for PHP.

Change all tables to Unicode:

for table in mysql -h<DBserver>-u<DBuser> <DBname> -p<DBpassword> -e "show tables" | grep -v Tables; do mysql -h<DBserver>-u<DBuser> <DBname> -p<DBpassword> -e "alter table $table convert to character set utf8" ; done

Change DB to Unicode :

mysql -h<DBserver> -u<DBuser> -p<DBpassword> alter database <DBname> character set utf8;

Tagged with:
 

7 Responses to Drupal : Change DB and table collation to Unicode (UTF-8)

  1. Grant says:

    There is a semi colon missing in your code

  2. Grant says:

    Sorry before the done in the for line.

  3. fiLi says:

    Oh, yeah :$

    Thanks for catching that.

  4. Grant says:

    Thankyou by the way – this still results in some character recall issues on my database i.e. GB pound signs that were previously okay must have been encoded in latin. I experienced this problem exporting from mysql 4 database to 4.1. Using mysqldump.

  5. fiLi says:

    I’m not sure this is what you mean, but the following might be helpful:
    Content search-n-replace?

    Naturally, this can be done on the dump with a big-text-files editor.

  6. iGuide says:

    Great! Can this also be done with the ALTER TABLE syntax?

  7. Grant says:

    That is using the Alter table syntax! So yes.

    If you have no data to worry about, you can do a mysqldump, change the collation in the text file, and then upload the dumpfile.

    Comment 5 suggestion , looks overblown but maybe an easy solution if you are a windows user.

    Otherwise you can do a mysqldump, change the table defs collation, and use recode or iconv unix utilities on the dump file. I think I had success with iconv in the past.

    Make sure you keep a backup!

Leave a Reply

Your email address will not be published. Required fields are marked *

Set your Twitter account name in your settings to use the TwitterBar Section.