Updating to 3.8 beta error

[texterted]

Oh no I get an error on step 5 of the upgrade :(



Database error in vBulletin 3.7.3:Invalid SQL: SELECT prefix.*,
prefixpermission.usergroupid AS restriction FROM vb_prefix AS prefix LEFT JOIN vb_prefixpermission AS prefixpermission ON
(prefix.prefixid = prefixpermission.prefixid) ORDER BY prefix.displayorder;
MySQL Error : Illegal mix of collations (latin1_german2_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Anyone got a clue why? I disabled all of my plug-ins prior to the upgrade.

Any ideas will be great... :)

[Wayne Luke]

You need to make sure that all the database collations for tables and fields in your database are the same. It appears that you either created or converted the database using latin1_german2_ci whereas MySQL is set to use latin1_swedish_ci as the default still.

Making sure all fields are using latin1_german2_ci will resolve your problems. You can change field and table collations using phpMyAdmin. However make sure to create a backup first because database changes of this nature can corrupt data.

[texterted]

Hey Wayne, thanks for your reply!

Have you any clue as to how this could have happenend, as I've never touched the database tables?

[beishe8]

Have you any clue as to how this could have happenend, as I've never touched the database tables?
A mod could have caused it.

[texterted]

Oh great...

I've contacted my host to see if they can help me change them back.

Thanks for your reply.

[texterted]

It was strange that all the effected tables were relating to vBlog 2.0 beta!
They all had collation changed to Latin_Swedish????

I've just spent the whole night rebuilding my forum, thank God it works ok again now!

I think I've learned a lesson to keep away from unsupported beta software...

[Kinneas]

I had a similar problem a while back, and it took me ages to change the collation on all the affected tables/rows. It only seemed to be the older tables (Those that remained from vb3.0) that were affected, so I blame myself for it due to the many times I've been forced to move hosts (sometimes doing slightly dodgy export/imports).

[texterted]

It's not a good senario is it?

Which should they be by default though...

Latin1_Swedish or Latin1_German2 Any ideas?

[Kinneas]

Well my tables were half latin1_general and half latin1_swedish - I changed them all to latin1_general.

I don't think it matters all that much, as long as they're just all the same collation. Might be worth seeing that Jelsoft have to say though :)

[texterted]

...Might be worth seeing that Jelsoft have to say though :)


Yep, that'd help!

[Wayne Luke]

When installing a Jelsoft product it will just use the MySQL defaults. That is latin1_swedish_ci. You would need to change the defaults in MySQL to make sure that all products installed use the appropriate collation.

[texterted]

Hey Wayne, I wish you'd have said that in your 1st reply to the question! I changed them all to latin1_german2 as you suggested there... ho hum.

[Wayne Luke]

Well... I am sure if you looked, you would have had more latin1_german2_ci fields than latin1_swedish_ci. However just tell MySQL that the default for that database is latin1_german2_ci so you don't have that problem in the future. You can find instructions on how to do that in the MySQL documentation under Language Support.

[texterted]

Well I just did what you said to do... I just hope that any further updates don't keep the Jelsoft default collation like the Blog beta 2.0 did.

I've checked and the default is already set to latin1_german2 as you suggest, so thanks for that.

[joomlajon]

Database error in vBulletin 3.7.3:

Invalid SQL:

SELECT prefix.*, prefixpermission.usergroupid AS restriction
FROM vb_prefix AS prefix
LEFT JOIN vb_prefixpermission AS prefixpermission ON (prefix.prefixid = prefixpermission.prefixid)
ORDER BY prefix.displayorder;

MySQL Error : Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
Error Number : 1267
Request Date : Friday, October 10th 2008 @ 07:20:58 PM
Error Date : Friday, October 10th 2008 @ 07:20:59 PM

MySQL Version : 5.0.45-log

I got this when upgrading to beta 3.8

most tables are utf8_unicode_ci
and som are utf8_general_ci

What should I change to what? general to unicode or vice verca?
Is it just to change coalition in phpmyadmin???

please help

I have never changed anything in the database after the first install, so this was changed by the blog or 3.8. No trouble before.

[Floris]

I see you use mysqli, did you set the mysqli charset?

// ****** MySQLI OPTIONS *****
// When using MySQL 4.1+, MySQLi should be used to connect to the database.
// If you need to set the default connection charset because your database
// is using a charset other than latin1, you can set the charset here.
// If you don't set the charset to be the same as your database, you
// may receive collation errors. Ignore this setting unless you
// are sure you need to use it.
// $config['Mysqli']['charset'] = 'utf8';

[Floris]

@ Wayne, I remember Marco had a charset script, I do not know if this works on 3.8, but perhaps it's worth 'trying' that? I searched the staff forum but couldn't find it.

[texterted]

What should I change to what? general to unicode or vice verca?
Is it just to change coalition in phpmyadmin???

Well, following Wayne's advice above, you should change the one with the least amount of mismatches. I had to change them all individually in phpmyadmin and there where quite a few!

Make sure your database is set to the same collation as its default.

Make sure you have a back-up first though as it can break the tables!

Good luck :)

[Wayne Luke]

@ Wayne, I remember Marco had a charset script, I do not know if this works on 3.8, but perhaps it's worth 'trying' that? I searched the staff forum but couldn't find it.
Its very primitive though and can cause its own problems.

Well there is this:
http://www.vcharset.com/

But it is third-party and unsupported by us. It only converts to UTF-8 as far as I am aware as well. Even then it can cause problems. vCharset is more sophisticated than the 2 year old staff script though.


Well, following Wayne's advice above, you should change the one with the least amount of mismatches. I had to change them all individually in phpmyadmin and there where quite a few!

Yes, it can be tedious. If there was a better supported method, I would have given it to you. Future versions of vBulletin should handle this better. We just need to wait for proper UTF-8 Support.

[joomlajon]

I have a mysqli charset, uncommented the utf-8.

When I installed vbulletin a while ago everything has been utf8_general

Now the most have changed to utf8_unicode, and I have no plugins besides the blog provided by vbulletin. It is as if the 2.0 blog and 3.8 differs from previous versions in some way???

Well, I have changed it to unicode standard, which was before general. And then changed the tabels with general to unicode. So now everything says it is unicode. But still I get the same database error from step five.

In admincp it says that I use 3.7.3 and that I should upgrade to 3.7.3 pl1, I had the 3.7.3 pl1 version installed before the upgrade.

So is it fixable or shoule I go back to earlier version?

[joomlajon]

Ok, found more inside the tables as well, now I have changed all and the upgrade is complete :)

Now I know if it happens again.

One question left.

I changed a couple of utf8_bin to utf8_unicode_ci as well, was that wrong???

[texterted]

One more question about collation....

Now my tables are set to latin1_german2, what will happen when I try another upgrade?

Seeing as the vbulletin upgrade will want to install it as latin1_swedish... will it again cause a mismatch or will it accept my default setting? (Seeing as blog 2.0 beta didn't!).

[texterted]

Any update on this collation question, anyone?

[Wayne Luke]

If you do not change the default collation of the database then future upgrades can use the improper collation. You need to consult the MySQL documentation to see how to change the default collation of a database.

[texterted]

What should I change to what? general to unicode or vice verca?
Is it just to change coalition in phpmyadmin???


Well, seeing as you have to change all of the affected tables one by one, I'd change the ones with the least amount.
Just check that your databases' default collation is the same. All of this is done from phpmyadmin.