Upgrade Error - Illegal mix of collations

[GoTTi]

Database error in vBulletin 3.7.3:
Invalid SQL:
SELECT prefix.*, prefixpermission.usergroupid AS restriction
FROM prefix AS prefix
LEFT JOIN prefixpermission AS prefixpermission ON (prefix.prefixid = prefixpermission.prefixid)
ORDER BY prefix.displayorder;
MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
Error Number : 1267
Request Date : Monday, January 26th 2009 @ 10:41:48 PM
Error Date : Monday, January 26th 2009 @ 10:41:48 PM
Script : SITE
Referrer : SITE
IP Address : IP
Username :
Classname : vB_Database
MySQL Version : 5.0.67-community-log

ok so im just doing a upgrade and i ge this error...i look on here, and i see this error all over the place for upgrades to 3.8.0 but there is no clear answer on how to fix this problem. i dont understand what i need to do to upgrade without the error. can someone please explain how to get the upgrade done by fixing this error. i read the the post by steve, but i have no idea what this means...

someone help me out here...first time EVER ive had a problem upgrading any forum with this software, cant believe in 2009 im running into my first vbulletin conflict.

[Steve Machol]

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

You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database. You need to use program like phpmyadmin to view your collations:

http://www.phpmyadmin.net/home_page/index.php

When you click your database name in phpmyadmin it will list all tables, their collations, as well as the collation of the database itself at the bottom. And when you click the name of a table on the left it will list the collations of individual fields within that table.

1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top.

2) To edit the collation of an individual table you need to click its name on the left and then click "Operations" on the top.

3) To edit the collation of an individual field within a table you need to click the table name on the left and then click the edit image (a little pencil icon) for that field.

For more information please see: http://www.vbulletin.com/forum/showt...ight=collation

Note: 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.

[GoTTi]

steve i said i read that post by you already and i dont understand it at all....

what should the collation be?...i dunno what im suppose to ask....

what do i do!!

[bigwater]

If you don't understand what Steve said, copy and paste it into a message you can give to tech support at your host. Give them a copy of the database error you got and Steve's explanation of how to fix it. They'll understand it for sure.

[GoTTi]

i understand some of what he said...if i have to go into phpmyadmin and change the collation, what should i change it to? ive never had to change it before, never even heard of it untill now.

so what should it be?

[bigwater]

You'll have to check to see how the collation settings are configured for the server. When you first launch phpMyAdmin you should see a column for "localhost". Under it you will see the MySQL connection collation. That is what you will want to make sure all of your tables/fields are set to.

latin1_swedish_ci is a very common collation for servers based in Europe and utf8_unicode_ci is very common in the US, although you can really find any encoding anywhere (and there's about 50 to choose from). If you transferred your site from one server to another and the two server's connection collation settings are different, you'll have problems. Either reset the collation on the server to match the database (if possible), or reset the collation on the database/tables/fields to match the server.

But as Steve said, make sure to do a backup first. If you mess up you stand a good chance of losing your data.

[GoTTi]

ok i see in phpmyadmin it shows utf8_unicode_ci in the drop down beside: MySQL connection collation

everything in the tables say swedish tho, and i changed the few that i saw say general to swedish....

so what am i suppose to do? change EVER single collation to swedish or unicode in ever single table????u gotta be kidding me!

[bigwater]

Actually latin1_swedish_ci and utf8_unicode_ci should co-exist happily. Your error indicated an incompatible collation between latin1_swedish_ci and latin1_general_ci. I'd look for the latin1_general_ci tables and convert them to latin1_swedish_ci.

[GoTTi]

bigwater, i just understood EXACTLY what you said, and fixed the issue because i understood what i had to do. you said the error indicated a problem between both these collations...so glancing at the error again i see prefixpermission is the table with the problem, and right there is where it was....

THATS ALL I HAD TO BE TOLD!!!wow, thats makes me disappointed by what i was told by steve. the info in that post has nothing to do with what i had to do, water nailed it on the head. there is a conflict between this collation bs on 1 of the tables, which is right there in the error, change that collation and ur problem is fixed.

REALLY, someone take note of this so you guys can let current and future problems with this error know what to do.

[bigwater]

Well honestly, Steve and I said pretty much the same thing, just in different ways. You gotta realize that Steve has handled thousands upon thousands of tech support questions, and many of these questions are repetetive in nature, such as collation problems. As a result of this he has canned solutions for many issues that work 95% of the time. For the 5% of the time they don't work, it's usually because the person reading the solution doesn't follow the instructions carefully or doesn't understand what the instructions really mean. At that point it's time to roll the sleeves up and make it plain.

Steve deserves a lot of credit for what he does here.

[wakelover]

I would also like to say that I too am grateful as I was able to fix my problem. Many thanks

[GoTTi]

Well honestly, Steve and I said pretty much the same thing, just in different ways. You gotta realize that Steve has handled thousands upon thousands of tech support questions, and many of these questions are repetetive in nature, such as collation problems. As a result of this he has canned solutions for many issues that work 95% of the time. For the 5% of the time they don't work, it's usually because the person reading the solution doesn't follow the instructions carefully or doesn't understand what the instructions really mean. At that point it's time to roll the sleeves up and make it plain.

Steve deserves a lot of credit for what he does here.

oh i know steve does alot of work, i have been a vb user for years and hes helped me on alot of issues, im just saying that default type-up reply to this problem isnt the right way to word it. it barely touches the surface compared to what you said, and how i realized after reading what you said what i had to do. it was right under my nose...

anyways, thanks for the help!

[nando.dominios]

Can any make a php script for all members that change all tables with vb_ prefix (or other) to the correct collation?

The small fix only change the afected table, the final solution for the future is change all tables (i can´t change one by one --tables and fields-- manually!).