Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT

[eth0]

Database error in vBulletin 3.5.4:

Invalid SQL:

SELECT COUNT(*) AS users
FROM ebcforums_user AS user
LEFT JOIN ebcforums_userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN ebcforums_usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
WHERE 1=1 AND FIND_IN_SET('10', user.membergroupids);

MySQL Error : Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'
Error Number : 1267
Date : Monday, June 19th 2006 @ 09:54:27 AM
Script : http://www.---.com/test/forums/admincp/user.php?do=find&user (http://www.ebuzzcafe.com/test/forums/admincp/user.php?do=find&user)[membergroup][]=10
Referrer : http://www.---.com/test/forums/admincp/usergroup.php?do=modify (http://www.ebuzzcafe.com/test/forums/admincp/usergroup.php?do=modify)

[Colin F]

You'll need to manually edit the database tables in phpMyAdmin and make sure they're all set to the same collation.

[eth0]

You'll need to manually edit the database tables in phpMyAdmin and make sure they're all set to the same collation.
to which ? (latin1_swedish_ci,COERCIBLE) or (utf8_general_ci,IMPLICIT) ??

[Colin F]

I'd recommend to latin1_swedish_ci

[d3nnis]

any ideas which command to use to do an update of all the field columns of each tables to a certain collation?

[Freddie Bingham]

http://www.vbulletin.com/forum/bugs35.php?do=view&bugid=1531

The short answer is since you are using MySQL 4.1, you need to be using PHP5. If you aren't using PHP5 then upgrade to PHP5. If you have PHP5 running, edit includes/config.php and look for

#$config['Mysqli']['charset'] = 'utf8';

change it to

$config['Mysqli']['charset'] = 'utf8';

[Sergio68]

As soon as possible (days) I will pass to Suse 10.1 with PHP 5, MySql 5 and Apache 2.2 , but my question still remain : Is it safe to ALTER tables from one character set to the other ?

[Colin F]

As far as I know that's no problem if the data is already in the traget character set, as the data itself won't be converted.
If the data is in the character set the database table is currently set to, then problems could occur.

[Sergio68]

Pardon me, most probaby it's a problem with me and language but this is not very clear for me.

I have a very large data base with mixed up tables, some UTF8 , some other latin1, now, as suggested I set the default character set to latin1 in the my.cnf , what other can I do ? Alter all the tables still utf8 to latin1? (80%)

Any help will be appreciated couse we are also trying to give some support in this unofficial italian forum ;) www.vbulletin.it/forums

[d3nnis]

http://www.vbulletin.com/forum/bugs35.php?do=view&bugid=1531

The short answer is since you are using MySQL 4.1, you need to be using PHP5. If you aren't using PHP5 then upgrade to PHP5. If you have PHP5 running, edit includes/config.php and look for

#$config['Mysqli']['charset'] = 'utf8';

change it to

$config['Mysqli']['charset'] = 'utf8';


This is not an option for me. As i have other hosting account users... so by doing this i will risk breaking everything and causes even more problems (maybe solved for this user but will indirectly affecting other users using other script).

So my question is... is there any mysql commands to do the collation change? if so please advise... otherwise... i would have to do it manually...

[Freddie Bingham]

You'll have to do it manually, easiest way is to use phpMyAdmin.

[Sergio68]

You'll have to do it manually, easiest way is to use phpMyAdmin.
Please, can you confirm me that altering tables doesn't give corruption problems?

[Fusion]

I suggest you follow the instructions laid out in the MySQL documentation (most recent versions found at mysql.com) to avoid any problems.