[Forum] Illegal mix of collations (latin1_danish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT

[wii]

For the first time ever in my years with vbulletin I get upgrade errors, what can I do?

Database error in vBulletin 3.8.4:

Invalid SQL:

(SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
package.class AS class, -1 as isaggregator
FROM vbulletinpackage AS package
LEFT JOIN vbulletinproduct AS product
ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin'
)

UNION

(SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
1, 1, contenttype.class AS class , contenttype.isaggregator
FROM vbulletincontenttype AS contenttype
INNER JOIN vbulletinpackage AS package ON package.packageid = contenttype.packageid
LEFT JOIN vbulletinproduct AS product ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin' );

MySQL Error : Illegal mix of collations (latin1_danish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
Error Number : 1267
Request Date : Saturday, February 6th 2010 @ 04:13:59 PM
Error Date : Saturday, February 6th 2010 @ 04:13:59 PM
Script : upgrade_400a1.php?step=9
Referrer : upgrade_400a1.php?step=8
IP Address : 77.215.68.91
Username :
Classname : vB_Database
MySQL Version : 5.0.45

[F4Flake]

thru your cpane, open phpmyadmin.

essentially what you'll have to do is view the structure of each table in the database, then click operations, and manually change the clations of every field that has one to the same collation.

likely most will be swedish, so you should change them all to swedish.

then change the colation of each table, then set the collation of the database itself.

/there are a couple of fields that require you to run an sql query as they are co-dependent within the table.

What I did was copy the sql error into notepad, add a comma, then copy the next error from 'change...' onwards, then copy and paste it into an sql box and run it.

if that doesn;t resolve the issues re-run the 4.0 update from the start.

[F4Flake]

oh, also there are a couple of fields that will not be of the latin1 variety. they don't need to be changed.

it'll take you about an hour to do this.

[wii]

Ohh man, and why is this necessary? Hasn't this been tested? The language is danish, not swedish

Can I run a script through vbulletin instead?

I have no idea what to look for in phpmyadmin?

[wii]

Ok, I'm in phpmyadmin, what should I look at? Found this:

dec8 dec8_swedish_ci DEC West European 1 cp850 cp850_general_ci DOS West European 1 hp8 hp8_english_ci HP West European 1 koi8r koi8r_general_ci KOI8-R Relcom Russian 1 latin1 latin1_swedish_ci cp1252 West European 1 latin2 latin2_general_ci ISO 8859-2 Central European 1 swe7 swe7_swedish_ci 7bit Swedish 1 ascii ascii_general_ci US ASCII 1 hebrew hebrew_general_ci ISO 8859-8 Hebrew 1 koi8u koi8u_general_ci KOI8-U Ukrainian 1 greek greek_general_ci ISO 8859-7 Greek 1 cp1250 cp1250_general_ci Windows Central European 1 latin5 latin5_turkish_ci ISO 8859-9 Turkish 1 armscii8 armscii8_general_ci ARMSCII-8 Armenian 1 utf8 utf8_general_ci UTF-8 Unicode 3 cp866 cp866_general_ci DOS Russian 1 keybcs2 keybcs2_general_ci DOS Kamenicky Czech-Slovak 1 macce macce_general_ci Mac Central European 1 macroman macroman_general_ci Mac West European 1 cp852 cp852_general_ci DOS Central European 1 latin7 latin7_general_ci ISO 8859-13 Baltic 1 cp1251 cp1251_general_ci Windows Cyrillic 1 cp1256 cp1256_general_ci Windows Arabic 1 cp1257 cp1257_general_ci Windows Baltic 1 binary binary Binary pseudo charset 1 geostd8 geostd8_general_ci GEOSTD8 Georgian 1

[F4Flake]

I just told you. unless you run multiple queries first selecting all fields where collations are one thing, then saving that and running a query to change the collation of each field then you're doing it by hand.
It's a long time since I did that much sql.

Perhaps ask your host to change the collations for you, but basically it'll be quicker to to do it by hand. go into php my admin, click on your forum database and start looking through the tables.

There's a fair bit of documentation regarding collation problems in the forums.

That said the installation process does appear to like 'swedish', but it's of no consequence what the collation is so long as they're the same.


Perhaps the install should not be allowed to run at all if it discovers mixed collation, but I'm afraid you have little choice at this point.


Ask your hosts nicely. But it's not enough to just set the collation of the tables, you must also set the collation of each collated field within the tables.

[F4Flake]

oh, and because of various errors my upgrade took me about 14 hours. so perhaps you ought to get some food delivered before you crack on.

[wii]

Damn, I'm going to restore it back to the version 3.8.4 and does this another time, I just knew it somehow that the new version will cause problems....

[Lynne]

You know it is recommended to always try upgrading a test board before you try to upgrade your live site. That way you can see what problems you will run into.

[borbole]

I agree. Doing that at the test board it will save a lot of headache. Anyway, wii, if you want I can write you a script which will change all the db tables coallition to the same latin1 and you can give the upgrade another go. All you have to do is uplaod the script to the forum root and run it from the browser. Let me know if you want me to do that.

[wii]

Oh yes please borbole, can you do that?

[borbole]

Oh yes please borbole, can you do that?

Sure, Here you go. After you download the file, open it with a php editor programe and enter your db info, save the changes and upload it in the root of your forum folder and call it from the browser. Let me know how it will go.

[wii]

It works thanks, but now other problems.....oh well..

[borbole]

It works thanks, but now other problems.....oh well..

You are welcome. What other problems have come up?

[wii]

Oh, just buggy code, more than ever before in a Gold release, I will give it some time.