Recent Topics

Problem upgrading from 1.10.2 to 4.1.6 - Illegal mix of collations

Started by on Apr 29, 2013 – Contents updated: May 01, 2013

Apr 29, 2013 02:34    

So.. yeah, about time this old blog was updated, right? I've got a staging server set up, and have tested a bare installation of 1.10.2 and another bare installation of 4.1.6 and they both work perfectly, so everything seems to be running properly on the server side of things.

Next I did a fresh export of the live 1.10.2 db, followed by another bare installation of 1.10.2. I then dropped all tables and imported the fresh 1.10.2 export and everything seems to work properly... I can see all the old posts and everything else.

Next, I imported the 1.10.2 export into a new, empty db, and proceeded with the 4.1.6 installer using the "upgrade" option. The installation goes fine for the first 20 seconds or so (except for a problem writing htaccess but that's another matter) until it hits this error:

Loading module: _core/model/__core.install.php
Loading module: collections/model/_collections.install.php
Loading module: files/model/_files.install.php
Loading module: sessions/model/_sessions.install.php
Loading module: messaging/model/_messaging.install.php
Loading module: maintenance/model/_maintenance.install.php

[A LOT OF 'OK' RESPONSES CROPPED HERE]

Upgrading blogs table... OK.
Upgrading items table... OK.
Creating keyphrase table... OK.
Upgrading hitlog table... OK.
Upgrading sessions table... OK.
Creating goal tracking table... OK.
Creating DB schema version checkpoint at 9800... OK. (Elapsed upgrade time: 19 seconds)
Updating keyphrases in hitlog table... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

An unexpected error has occurred!
If this error persists, please report it to the administrator.
Go back to home page

Additional information about this error:
MySQL error!
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='(Errno=1267)
Your query: Get keyphrase ID
SELECT keyp_ID
FROM evo_track__keyphrase
WHERE keyp_phrase = 'sex ???'

So this seems to be something in the antispam table with the keyword of 'sex' and then 3 unreadable characters (I'm guessing) but I have no idea how to go about clearing the error to complete the installation. Is this something that needs to be done on the live 1.10.2 installation prior to exporting the database? Should I clear out the entire antispam table before exporting the db? I'm pretty much lost on how to proceed to successfully upgrade...

Apr 29, 2013 02:43

Use MyPHPAdmin to check your mysql tables. Look at the charset/collation for each table (and possibly each varchar row). You may have different charsets/collations in use, which is not good. The whole database should use the same charset/collation.

Your problem here is (latin1_swedish_ci,IMPLICIT) vs (utf8_general_ci,COERCIBLE).

Btw, version 4.1.7 is out ;)

Apr 29, 2013 05:14

Ugh, ok, will continue this testing with 4.1.7 in that case :)

I just checked the bare 4.1.6 I had installed earlier this evening, and the tables are all utf8_general_ci. Is that what a default b2evolution install is supposed to be now? I ran a php script and updated all the tables on the imported 1.10.2 database, converting it all to utf8_general_ci, and then ran the 4.1.6 upgrade and it completed, after telling me that a lot of the database schema had been changed and needed to be altered (or something to that effect). It then read out a page full of changes similar to this:

Altering table «evo_items__item»...

Changed type of evo_items__item.post_content from longtext to post_content MEDIUMTEXT NULL
Changed type of evo_items__item.post_excerpt from mediumtext to post_excerpt text NULL
Changed type of evo_items__item.post_title from mediumtext to post_title text NOT NULL
Changed type of evo_items__item.post_renderers from mediumtext to post_renderers TEXT NOT NULL

And finally finished with: Upgrade completed successfully!

The site does appear to have upgraded properly. It's my girlfriend's site, so I'll need to have her look over all the accents and special characters tomorrow to confirm that it's all ok, but do the 4.x versions of b2evolution need to be utf8_general_ci, whereas the older versions (1.x) installed as latin1_swedish_ci by default? I guess I don't fully understand how the collation settings come into play and if there is a "right" vs "wrong" setting.

Apr 29, 2013 17:07

The db changes at the end of the procedure is b2evo changing back the charsets of your fields to what it wants. Check what it did and use that charset before running the upgrade.

There is no right or wrong. It just should be the same charset all the time.

I don't have access to the code right now. I'm not sure what determines the charset b2evo uses for the db. Prolly the default locale.

Also it should never have changed. Your db might have been screwed up during a backup restore operation which did not include charset/collation info.

Finally can you post your script? We should prolly include something like that at the beginning of the upgrade procedure.

Apr 29, 2013 19:09

Ok, I'll do some more test exports and imports today to try to figure out where the changes are being introduced which it ends up changing back, but I feel like I've made some progress at least.

The script isn't exactly "mine", it's something I found many copies of on several different websites when searching for php scripts to change collation across an entire mysql database. Here is the one I ended up using for my testing:

<?php
// your connection
mysql_connect("localhost","db_username","db_userpass");
mysql_select_db("db_name");

// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
    foreach ($row as $key => $table)
    {
        mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET db_charset COLLATE db_collation");
        echo $key . " =&gt; " . $table . " CONVERTED<br />";
    }
}
?>

The 5 fields which need to be customized are:
db_name
db_username
db_userpass
db_charset
db_collation

Then I simply ran it locally from a web browser and it outputs a list of changes it makes. Hope this helps.


Form is loading...

CMS with Bootstrap – This forum is powered by b2evolution CMS, a complete engine for your website.