help! latin1_swedish_ci to utf8_unicode_ci

I have been having a similar problem. I have tried to backup and restore a Drupa 4.7 install with PhpMyAdmin. When I look at the backup file with a text editor, the backup looks fine with accents and all. Unfortunately, after restoring the backup file at my host, something went wrong with the text encoding and all non-standard letters turned into a mess.

I have searched the archives and PhpMyAdmin and SQL archives, but haven't found a solution yet.

I hope someone can point towards a solution.

To fully understand what's going on with tour accents please check these 2 links: http://www.joelonsoftware.com/articles/Unicode.html (to understand how character sets work) and http://www.oreillynet.com/pub/wlg/9022 (to find out a fix). I haven't tried but I guess Zen's tip should be enough anyway.

A few months ago I decided to switch my Drupal database from Latin1 to UTF8, here are my notes. I was working on a windows platform at the time, you should be able to do the same thing in linux. Please note that I am certainly no expert, so I give no guarantee that this will work for you, nor that this is the easiest way to do it. It's simply what worked for me. With that warning out of the way, here are the notes I took:

Some people have a Drupal site that is using a Mysql database which uses the Latin1 character set, and they want to convert their database to UTF8 so that the accented characters show up correctly in PHPMYADMIN. Here is one way I have found to do it.

A quick aside before we get started. Prior to MYSQL 4.1, the character set of all data was interpreted using the Mysql server’s character set. As of 4.1, it is now possible to set character set and collation at 5 levels: Server, Database, Table, Column and String Constant. This means that you can have, for example, a table with a character set A, and yet set one (or more) of its columns to have different character sets if you want to. To see what character sets are in use in your database, you can use the following commands:

You want to know the character set defined for a given: SQL Command to use
DATABASE: SHOW VARIABLES LIKE ‘character_set_database’; - or - SHOW CREATE DATABASE db_name;
TABLE: SHOW CREATE table_name;
COLUMN: SHOW CREATE table_name;
STRING CONSTANT: SELECT CHARSET(string_constant);

Now, here are the steps I followed to convert my Drupal Latin1 database to a utf8 database, (thus making it possible to see accented characters correctly in PHPMYADMIN).

1. Create a new database that has UTF8 specified as its default character set
a. Execute SQL statement: CREATE DATABASE new_db_name CHARACTER SET utf8 COLLATE collation;
b. AS my website is in Spanish, I used collation utf8_spanish_ci. You should choose the collation for the language your website uses. To see what character sets and collations are available on your server you can use the SQL command : SHOW CHARACTER SET. By the way, if you don’t see utf8 listed as one of the character sets, then you are stuck. Talk to your system administrator.

2. Export your database structure AND data
a. From a dos command line, type the following: mysqldump –uuser_name –ppassword latin1_database > backup.sql (user_name and password are your MYSQL user name and password, latin1_database is the name of your existing Drupal database, and backup.sql is the name you want to give to the backup file.)

3. Modify the SQL file you just finished exporting
a. Open the file you saved using the windows Notepad.
b. Find and replace every instance of “CHARSET=latin1” with “CHARSET=utf8”. When you’re done, do a search on just “latin1” to see if it shows up anywhere. You may find it in collate statements (e.g. COLLATE=latin1_swedish_ci). If this is the case, you can either
i. delete the collate statements, and let the tables default to the collation set at the database level (or server level, if collation hasn’t been specified for the database)
ii. Replace the offending collation with the specific utf8 collation you want to use
c. Do a “Save as”, and in the window that pops up, just change the value shown in the “Encoding” dropdown window to ANSI. Then click save. It will ask if you want to overwrite the existing file, answer yes. Then exit notepad.
d. Note: ANSI is a superset of the Latin1 character set. What we have basically just done is make sure this file is encoded using Latin1. As all (most?) of the characters used in the SQL language have the same encoding in both Latin1 and UTF8, the SQL keywords and syntax will be interpreted correctly regardless of whether MYSQL thinks it is looking at Latin1 or UTF8. The accented and special characters in your data, on the other hand, are a different story. We now have in a text file the same situation as in your original Drupal database: UTF8 data stored in a file that thinks it contains Latin1 data.

4. Import the structure and data into your new UTF8 database
a. At a dos command line write the following: mysql –uuser_name –ppassword utf8_database < backup.sql (user_name and password are your MYSQL user name and password, utf8_database is the name of your new database created in step 1, and backup.sql is the name you gave to the backup file created in step 2.) Because the database has utf8 as its default characterset, it interprets the backup file as if it were utf8 data, which is exactly what we want.
b. You’ll want to check your data’s integrity to make sure it all made it across safely. At a bare minimum, check that the row counts (check using SQL, NOT by looking at the row counts in PHPMYADMIN which aren’t reliable) in the old and new database are the same. PHPMYADMIN has been known at times not to export all the rows in large tables.

5. Make Drupal look at your new UTF8 database instead of your old Latin1 database
a. If everything has worked, your data is now sitting in utf8 format inside your new utf8 database. Use PHPMYADMIN to browse the contents of a table that contains data with accented characters. They should be perfectly legible. No more garbage characters in PHPMYADMIN!! The next step is to switch Drupal to the new database. As you will see, it’s as easy as renaming both databases.
b. In PHPMYADMIN, select your OLD database, and make sure that you are on the main page. You can tell because the main section of the screen will be a list of all the tables in the database and their attributes.
c. In the row of tabs at the top of the page, click the tab labelled “Operations”. When the screen refreshes, the second box from the top is titled “Rename database to”, and contains a text entry field. Type a new name for your old database, perhaps adding _old to the end of the existing name, and hit the “go” button. You have now renamed the old database.
d. Now do the same for your NEW database. Start by selecting your new database, then clicking on “operations”, etc. Change the name of your NEW database to be the name your OLD database had originally. That’s it, you’re done.
e. Crank up Drupal, navigate some pages and make sure everything is working, and that the accented and special characters are displaying correctly.

6. It’s Miller time!