OT - collation error - upgrade 9061 to 9121

Mystic

Just performed this upgrade (9.0.6.1 to 9.1.2.1) on one of my less important sites and ran into this problem...

I cannot login to the site now as a user (just get the "Database error" message).

I can login as admin.

I am currently logged in as a user to this site on another computer and when I try to logout I get the same "Database Error" message.

Strange...

Here's the stats for what that site is currently running on...

CMS Version 9.1.2.1
PHP Version 4.4.4
MySQL Version 4.1.22-standard (client: 4.1.22)
GD Version bundled (2.0.28 compatible)

rlgura

Can you turn on debug options, see if it gives you an error - or see if more details were sent in an email to the admin?

Mystic

Shows nothing on debug, but here it what it is sending me...

Quote::

On /index.php?name=Your_Account&op=logout&redirect
While executing query "DELETE FROM cms_session WHERE host_addr='?��ò/' AND guest<>1"

the following error occured: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

In: /home/ninthpor/public_html/hendersonhideaway/includes/classes/cpg_member.php on line: 46

layingback · Forum Admin Offline Joined: Apr 19, 2004 Posts: 1040

Ah, that old thorny issue...

You inadvertently like all of us, created your original db in something other that the collation required by 9.1.x. (Swedish seems to be the default for the (Swedish?) MySQL!) So you need to convert it, field by field. There are many posts concerning this, plus entry in Wiki.

*If* you are using MySQL 5, then you might get away with a simpler task - change the couple of entries it complains about and the file level collations and it should work, without you having to change every text field.

(I have to do same on an old from-PHP-Nuke site soon myself - shudder!)

Mystic

layingback wrote:

Ah, that old thorny issue...

You inadvertently like all of us, created your original db in something other that the collation required by 9.1.x. (Swedish seems to be the default for the (Swedish?) MySQL!) So you need to convert it, field by field. There are many posts concerning this, plus entry in Wiki.

*If* you are using MySQL 5, then you might get away with a simpler task - change the couple of entries it complains about and the file level collations and it should work, without you having to change every text field.

(I have to do same on an old from-PHP-Nuke site soon myself - shudder!)

yuk! What fresh hell is this? (I love that line since I heard it on "Big Bang Theory"). Apparently I need to go do some more reading. I have like 10 sites to update, this is going to royaly suck!!!

rlgura

Easiest trick I found to deal with this:
1. Export your db to text file
2. Find and Replace "latin1_swedish_ci" with "utf8_general_ci"
3. Make sure your phpMyAdmin default is set to utf8, create new db and import the edited file.
4. change config.php to point to new database

(this leaves the existing db intact - "just in case" Smile

you can drop it once you're satisfied everything is working)

Mystic

rlgura wrote:

Easiest trick I found to deal with this:
1. Export your db to text file
2. Find and Replace "latin1_swedish_ci" with "utf8_general_ci"
3. Make sure your phpMyAdmin default is set to utf8, create new db and import the edited file.
4. change config.php to point to new database

(this leaves the existing db intact - "just in case" Smile

you can drop it once you're satisfied everything is working)

Thanks, this should make things go a little quicker...

warden

Quote::

Easiest trick I found to deal with this:
1. Export your db to text file
2. Find and Replace "latin1_swedish_ci" with "utf8_general_ci"
3. Make sure your phpMyAdmin default is set to utf8, create new db and import the edited file.
4. change config.php to point to new database

Neat little bit of information. I tried it out and it worked like a charm, however, my exported database had "latin_1" as the charset instead of "latin_1_swedish_ci". So, I had to find in the exported .sql file all mentions of "latin_1" and I replaced it with "utf8". At first I replaced it with "utf8_general_ci" but the database would not import. I received no error message, it just timed out after 300 seconds (using phpmyadmin). Replaced again with the "utf8" and it worked fine. Then I ran this sql statement to change the database from latin_1_swedish_ci" to "utf8_general_ci":

Quote::

ALTER DATABASE mydatabase CHARSET=utf8 COLLATE utf8_general_ci;

Now, my old 9.0.6.1 database is upgraded to a utf8 charset. I tested it out and have received no errors. I did have to change the config file to show the new charset, from "null" to "utf8".

This sure was easier than running sql statements for each table!

rlgura

thanks for the clarification Warden - that was from memory! Smile

Checking my corrected sql file, the correct setting should be:
DEFAULT CHARSET=utf8

The first time I had this problem, I spent about 3-4 hours modifying tables, then individual fields. The next time I had the problem, I thought I'd try this so I could do a 'find and replace' and it took me about 15 minutes! Smile

rlgura

Uh - confusion from thread hijacking - Mystic, you do not have the "column count" error - you have the "illegal mix of collation" error - follow my instructions with Warden's correction of "DEFAULT CHARSET=utf8"

Mystic

rlgura wrote:

Uh - confusion from thread hijacking - Mystic, you do not have the "column count" error - you have the "illegal mix of collation" error - follow my instructions with Warden's correction of "DEFAULT CHARSET=utf8"

Great! Thanks for the clarification rlgura. Looks like I'll be playing with databases and upgrades this weekend. Wink

tpaljr

Is this what you are talking about or should I try the trick above?

rlgura

Looks like you have the problem, yes.
You can either change your tables one at a time (and some tables will have fields that have collation defined that will need to be changed) or you can "try the trick above" Smile

tpaljr

Ok this might be a stupid question... when I tried exporting and opened with notepad I haven't found an instance of latin_swedish_ci ..am I exporting it wrong or opening wrong?

tpaljr

Just wondering if someone came up with something like this;

PHP:

<?php
 
// this script will output the queries need to change all fields/tables to a different collation
// it is HIGHLY suggested you take a MySQL dump prior to running any of the generated
// this code is provided as is and without any warranty
 
die("Make a backup of your MySQL database then remove this line");
 
set_time_limit(0);
 
// collation you want to change:
$convert_from = 'latin1_swedish_ci';
 
// collation you want to change it to:
$convert_to   = 'utf8_general_ci';
 
// character set of new collation:
$character_set= 'utf8';
 
$show_alter_table = true;
$show_alter_field = true;
 
// DB login information
$username = 'dbusername';
$password = 'dbpass';
$database = 'database';
$host     = 'localhost';
 
mysql_connect($host, $username, $password);
mysql_select_db($database);
 
$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());
 
print '<pre>';
while ($row_tables = mysql_fetch_row($rs_tables)) {
    $table = mysql_real_escape_string($row_tables[0]);
    
    // Alter table collation
    // ALTER TABLE `account` DEFAULT CHARACTER SET utf8
    if ($show_alter_table) {
        echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\r\n");
    }
 
    $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
    while ($row=mysql_fetch_assoc($rs)) {
        
        if ($row['Collation']!=$convert_from)
            continue;
 
        // Is the field allowed to be null?
        if ($row['Null']=='YES') {
            $nullable = ' NULL ';
        } else {
            $nullable = ' NOT NULL';
        }
 
        // Does the field default to null, a string, or nothing?
        if ($row['Default']==NULL) {
            $default = " DEFAULT NULL";
        } else if ($row['Default']!='') {
            $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
        } else {
            $default = '';
        }
 
        // Alter field collation:
        // ALTER TABLE `account` CHANGE `email` `email` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
        if ($show_alter_field) {
            $field = mysql_real_escape_string($row['Field']);
            echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default; \r\n";
        }
    }
}
 
?>

Was wondering if this would work?