Hello, I'm experiencing the following difficulty moving my database from latin1 to utf8.
I began developing my first Drupal site (4.6.5) using Mysql 4.1, and knowing nothing about character sets The characters sets in use at that time were:
Client, connection, results, system: utf8
database, server: latin1
Accented characters showed up fine in Drupal, but scrambled in PHPMYADMIN. I now know that this is because I had utf8 data stored in a latin1 database. Instead of leaving well-enough alone (doh!), I wanted everything to show up properly in both Drupal and in PHPMYADMIN. Thinking I had the answer, I decided to move to a utf8 database.
Here's what I did:
1. Created a new (empty) database, with character set utf8.
2. Exported the data definition and data from my working database to a text file. Edited the file to change charset of the table definitions from latin1 to utf8, and changed the encoding of the file itself to be utf8.
3. Did an import to the new (empty) database using the "mysql" command.
Results:
I now have the following Character sets:
Client, connection, results, system, database: utf8
server: latin1
All the tables and columns have default character set utf8. In PHPMYADMIN, all the accented characters are displaying properly. Looks beautiful. So far so good.
The problem is that now when I connect the new database to Drupal, the special characters show up as garbage. It's a terrible mess, because not only to accented characters show up as garbage, it breaks the html and the page itself doesn't render properly.
I thought that as long as both the database and the data in it were encoded as utf8, utf8 would come out and Drupal would be happy, as Drupal expects UTF8. Somewhere there is a character conversion issue, but I'm too green to know how to find it.
Does any kind soul have an idea where the problem is, and how to fix it?
Many thanks,
Steve
Comments
SET NAMES utf8
Drupal 4.6 doesn't do anything character-set wise for the database, 4.7 adresses this and requires UTF-8 for the database.
If you use 4.6, you can add a query SET NAMES utf8 to your database.mysql.inc.
--
If you have a problem, please search before posting a question.
Thanks, that fixed some of the problem, but ...
Thank you very much for your reply. It has definitely helped. I added the SET NAMES utf8 to my database.mysql.inc as you suggested, and many of the garbage characters now display correctly. However, many of them still do not.
I looked for a pattern, and I think it may be that special characters that are part of the installed Spanish translation (PO file) are being jumbled, while all other special characters are displaying correctly (to give one example, a paged table of Spanish university names I added displays perfectly with all the accents).
To see if that is indeed the case, I looked at what I think is the table holding the translated strings: locales_target. What I saw there is that the translations are stored as BLOBs (or at least that's what I think is happening).
So... Could it be that when I translated my database dump to UTF8, the translations, since they are stored in Binary format, were left un-converted and so are still being sent to Drupal in Latin1 format, causing them to be rendered incorrectly?
My first thought was to try uninstalling the Spanish translation, and somehow reinstall it in UTF8 format. The problem is that I can't log into the website and get to the admin pages because the jumbled characters are breaking the HTML. The default language for unregistered users is Spanish.
If there was a way to manually set it back to English, I think the problem would go away as there are probable no problematic characters in the English. Then I could try the uninstall/reinstall of the translation.
Any suggestions?
Steve
Revista Y AHORA QUÉ | Selectividad - Universidades - Foro Blog Chat Estudiantes
¡Eureka!
Impatience got the best of me, so making an educated guess and throwing caution to the wind, I tried the following drastic solution:
1: Using PHPMYADMIN, deleted all rows in locale_source
2: Then deleted all rows in locale_target
3: Then deleted all rows in cache
4: Navigated to my drupal homepage...
So far so good! Everything was back in English, and no broken html. Now lets see what happens if we reinstall the Spanish PO
5: Went to admininister>localization, and got the "manage languages" tab, with the "list" page already selected
6: In the listing of installed languages, English and Spanish showed up. I clicked on "delete" to delete the Spanish.
7: Then clicked on "add language" , just to the right of "list", beneath the "manage languages" tab.
8: Chose "Spanish(Español)" from the "From Language List" dropdown menu, and clicked the Add language button.
9: Completed the steps to install the Spanish language
10: Went back to the "list" page, and checked the Enabled checkbox and "default" radio button for the Spanish language.
11: Logged out of Drupal to get back to an unregistered user, which should trigger the pages to render in Spanish now that I have set Spanish as the default language.
It worked!! All the translations were showing up correctly, including the accented characters. After a week of trying to convert my Drupal database from latin1 to utf8... what a relief!
Thanks for your help, and I hope that the solution explained here is helpful to others.
Steve
Revista Y AHORA QUÉ | Selectividad - Universidades - Foro Blog Chat Estudiantes
Er, thanks,
Er, thanks,
but could you lead me a bit more maybe supplying a patch for database.mysql.inc
I am not sure I understand where I can put the SET NAMES utf8
Do I for example add this line
_db_query('SET NAMES utf8');
to the db_connect function just before the return?
AHa!!
AHa!!
mysql_query('SET NAMES utf8', $connection);
seemed to work.
THANKS!
Another Fix
mysql -uusername -ppassword --default-character-set=utf8 database < backup.sql
Worked for fixing drupal 4.7 to drupal 5 upgrade that introduced garbage characters.