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.