By jessZ on

One of my websites suddenly started incorrectly displaying symbols such as coopyright or accents incorrectly I reviewed the discussion at http://drupal.org/node/142959 and it seems likely to me that the problem is that my drupal databse tables are still in latin1-swedish. I checked with the ISP and they maintain they have not made any upgrades to affect the MYSQL database although they are upgrading the cpanel. According to the ISP The site is using MYSQL 5.0
(mysql Ver 14.12 Distrib 5.0.27, for pc-linux-gnu (i686) using readline 5.0)

Since drupal is what they call a "third party application" the ISP is in no hurry to help me fix this problem. From the discussion in the above thread i concluded that i need to change the tables to utf-8. I do not have shell access so i ahve been looking at the phpmyadmin interface to determin how to change the collation to utf-8.

I went to the operations tab, scrolled to bottom and see a drop down that appears to change the collation. I change to utf-8 but after the operation says it has succesfully completed the individual tables still list latin1 as the coding. The label at the bottom of all the tables does now read utf.

I'd love to hear any suggestions on the correct procedure to get all the tables to shift to utf -8 (also welcome additional hypothesis as to why this suddenly started happening are also welcome especially if they might involve a local drupal setting that i could change without venturing into the realm of making a mass revision of the dbase. (i did back it up just in case and i am planning to uplgrade the site to 5.3 soon , but do to the large number of nodes and xtemplate themes involved it's going to take another month or so.)

⋅ Categories: Drupal 4.6.x

Comments

xmacinfo’s picture

xmacinfo
he/him
Primary language French
Location Canada
commented

First of all, you should start by making a backup of your database before doing any modification. It will be easier to roll back to that version if anything happen.

Which version of MySQL was installed at your ISP before? Version 4.0 to 4.1 or to 5.0 cause problems with accented characters. However, usually ISP will send a message before doing a MySQL upgrade. So it may be another issue.

Changing the collation of a table is not sufficient, you will most probably need to convert all the strings to UTF-8 compatible format.

So, by installing a copy of your site on a development computer, you will be able to do some tests.

Now, the interesting part, how to convert the database. For this you will need to play with a copy of your main database backup. Open the database SQL file in a professional text editor that can convert from Latin1 to UTF-8. I think that Notepad+ should be able to do this. Once converted, on your development Drupal installation, drop the current database and import the newly converted database.

If your text are displaying normaly on your development server, you will safely be able to import it on your production server.

When I was hit by the MySQL 4.0 to 4.1 upgrade characters bug, I had some trial and errors, but finally was able to convert properly my SQL file and import it back to my production server.

/*_*/
http://www.xmacinfo.com

jessZ’s picture

jessZ commented

alas I don''t have development server just a subdirectory where I try out hazardous activities. They say they have been on 5 for quite some time but the problem arose just a few days ago. They did just upgrade the cpanel application

the isp suggests running this script will change the collation. I am going to try that on one of my test sites but meantime if anyon else can shed any light on this issue please feel free to suggest other reasons why drupal suddenly notices the encoding dicrepancy but has been fine with it for 18 months.

 $db = mysql_connect('localhost','USERNAME','PASSWORD'); if(!$db) echo "cannot connect to the database"; mysql_select_db('DATABASENAME'); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE DESIREDCOLLATION");
}

}

You should change the following values with the real one:

USERNAME: with your database username
PASSWORD: with your database password
DATABASENAME: with your database name
DESIREDCOLLATION: with the database collation that you would like to be set.

mnoyes’s picture

mnoyes commented

Having the same problem. I tried the suggested script, but I got this reply:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php
$link = mysql_connect('localhost','username','password')' at line 1

(Of course, I substituted the Username and other values as mentioned above.)

Any suggestions? MySQL version is 5.0.27-standard