Illegal mix of collations after upgrading to 3.8RC2

[Yellow Slider]

Database error in vBulletin 3.8.0 Release Candidate 2:

Invalid SQL:

SELECT prefix.*
FROM forumprefixset AS forumprefixset
INNER JOIN prefixset AS prefixset ON (prefixset.prefixsetid = forumprefixset.prefixsetid)
INNER JOIN prefix AS prefix ON (prefix.prefixsetid = prefixset.prefixsetid)
WHERE forumprefixset.forumid = 2
ORDER BY prefixset.displayorder, prefix.displayorder;

MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
Error Number : 1267
Request Date : Sunday, December 21st 2008 @ 10:30:38 PM
Error Date : Sunday, December 21st 2008 @ 10:30:38 PM
Script : http://www.mastul.co.il/newthread.php?do=newthread&f=2
Referrer : http://www.mastul.co.il/forumdisplay.php?f=2
IP Address : 84.109.178.47
Username : Bar
Classname : vB_Database
MySQL Version : 5.0.51a-community-log

How do I convert the Collations of the necessary tables? 50% of the DB is in latin1_swedish_ci / the rest is latin1_general_ci there is an UTF8 table too.

what do I need to do? I'm getting the error when trying to open up a new thread.

[mlx]

The SQL query to convert a table would be something like this:

ALTER  TABLE  `prefixset`  DEFAULT  CHARACTER  SET latin1 COLLATE latin1_swedish_ci

[pixelad]

The SQL query to convert a table would be something like this:

ALTER  TABLE  `prefixset`  DEFAULT  CHARACTER  SET latin1 COLLATE latin1_swedish_ci

I also have this problem however is there a single SQL command I can execute to set all tables to the same collation value?

Thanks in Advance,

Pix

[kyrgyz]

How do I convert the Collations of the necessary tables? 50% of the DB is in latin1_swedish_ci / the rest is latin1_general_ci there is an UTF8 table too.

what do I need to do? I'm getting the error when trying to open up a new thread.

You can change collations manually using phpMyAdmin. It's super easy and doesn't really take that long.

[Wayne Luke]

Collations are applied to individual fields as well so you would have to change them as well.

When vBulletin creates a new table in the upgrade process, it asks MySQL what the default collation for the database is and then uses that. Most MySQL installations default to latin1_swedish_ci. If you have changed collations for any reason on tables in the past then you should change the database to use that as the default to prevent this issue from happening in the future.

You will find more information in the MySQL manual here:

http://dev.mysql.com/doc/refman/5.1/en/charset.html

[Andy Huang]

Please be reminded that it is very very very important to make full database backups before proceeding if you are ever manually making any changes to your database, let it be issuing queries, or editing it via phpMyAdmin or any other interface. If done incorrectly, manually modifying the database can potentially cause irreversible damage to your database, and there is no way to perform a "partial restore" to restore just the part you may break.

[pixelad]

Thanks to all for the information and advice, its appreciated

Pix

[johnmb]

Just had exactly the same problem and fixed it using the method detailed above - thanks (bit tedious though ).

It's a bit of a gotcha as it has been caused, at least in my case, by moving the site between web servers that have different default collations and doing upgrades at different times.