MyBB Community Forums

For my social groups plugin, one of the queries it runs on groups.php is

SELECT g.*, u.*, ug.gid, ug.namestyle FROM mybb_socialgroups g LEFT JOIN mybb_users u ON (g.uid=u.uid) LEFT JOIN mybb_usergroups ug ON (u.displaygroup=ug.gid) WHERE g.leader=u.username

This works fine for me on my website, but some people it will trigger the following error message:

Quote:MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

What can I alter the query to so it will work with all SQL versions or is there even just a better way to write the query? I already have it use the TABLE_PREFIX variable since not everyone will use mybb_.

Don't mix different collations...

I don't understand how it is mixing them in the first place.

The tables have different collations. One is utf8_unicode_ci and the other is utf8_general_ci. They need to be either one, or the other.

What is the default collation for the MyBB tables and how can I make it so when I have it create the tables it will use the same collation?

utf8_general_ci, and you can edit them in phpMyAdmin.

When you create tables there is a way to make sure you do so in uniform with your MyBB installation. It's around somewhere.

(2011-09-26, 08:53 PM)labrocca Wrote: [ -> ]When you create tables there is a way to make sure you do so in uniform with your MyBB installation. It's around somewhere.

Easiest way is to just make sure your database default collation matches MyBB's

Too many people leave their default collation as latin1_swedish_ci, I've suggested that we change this in future versions of the installer so that it forces the default for the MyBB database to our collation type.