By neomenlo on

Part of my Drupal database is in UTF-8 and part is in Latin-1. This seems to be causing problems during backups and restores.

I don't know how it happened, but I would like to change the whole database to one or the other.

I do have a local database set up on my computer, so don't be too afraid of breaking it.

⋅ Categories: Drupal 5.x

Comments

cog.rusty’s picture

cog.rusty commented

If you have only English content it won't be hard because you won't need to do any conversions, just change the default collations of the whole database, of the database tables, and of the table columns (whichever are in latin1).

In phpmyadmin this can be done from the Operations tab. Without any table selected, you can set the database's default to utf8_general_ci. Then select each latin1 table, go to the Operations tab and set it the same way.

Take a backup first, and don't do this for "binary" tables or columns, only for "latin1".

Or using SQL statements:

ALTER TABLE {tablename} CHARACTER SET utf8 COLLATE utf8_general_ci

After that, if you find that searches fail to find existing stuff, you may need to empty your search tables and reindex the site.

HTF’s picture

HTF commented

I too have this problem, as per Rusty's instructions, I have changed the database's default Collation to utf8_general_ci. Then I selected each latin1 table and changed them to uft8_general_ci using the Operations tab.

I have noticed that when looking at the structure of many of the tables which were originally Latin-1_swedish_ci the fields are still classed as latin_1_swedish_ci after I have changed the table to uft8_general_ci

Should I now go through and change the collation of the fields as well? This could take some time. If I do need to do this is there are quick way?

Many thanks for your help

cog.rusty’s picture

cog.rusty commented

The easiest way I know, besides changing the columns one by one, is to

- take a database dump,
- edit it with a good text editor, aware of utf8, and replace any occurrences of latin1_swedish_ci to utf8_general_ci
- load it back.

Something like this: http://drupal.org/node/84214
I am assuming that your content is english-only and won''t need conversion.

And remember to keep a backup.

HTF’s picture

HTF commented

Hi @cog.rusty

Many thanks for getting back to me to quickly, I will give it a go