Forum Illegal mix of collations error after moving to new server
 

Performed a test move to new server, all appeared to be good.
Went ahead with going live on new server.

Old server Windows 2003 32 bit, MySQL 5.1.47, PHP 5.2.13, Vbulletin 4.2.0 fix 3
New server Windows Server 2008 64bit, MySQL 5.1.66, PHP 5.3.19 Vbulletin 4.2.0 fix 3

Receiving Illegal mix of collations errors over the last few days such as:


Database error in vBulletin 4.2.0:Invalid SQL:SELECT userid, username FROM user WHERE username = '#file_links[C:\\Documents and Settings\\???è?è???à?î?\\?à?î÷èé ??îë\\????-??\\pohudet-za-bbv.txt,1,S]';MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='Error Number : 1267Request Date : Wednesday, January 23rd 2013 @ 02:15:40 AMError Date : Wednesday, January 23rd 2013 @ 02:15:41 AMScript : http://forum.xxxx.com/register.php?do=addmemberReferrer : http://forum.xxxx.com/register.phpIP Address : xx.xx.77.235Username : VahinAcadoClassname : vB_DatabaseMySQL Version :


Database error in vBulletin 4.2.0:Invalid SQL: SELECT * FROM session WHERE sessionhash = '11c268a45407c7a950c1279e1faa2c7c Result: è?ïîëüçîâà? ?èê??é? "Flulsify1984"; ïèê?îêî? ???èô?îâà?; çà???è???è?îâàëè?ü; â?î? â àêêà??? ?? ??àë??;' AND lastactivity > 1358939351 AND idhash = '50f8876e8968b7c717d2475a445ed5fa';MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='Error Number : 1267Request Date : Wednesday, January 23rd 2013 @ 03:39:11 AMError Date : Wednesday, January 23rd 2013 @ 03:39:12 AMScript : http://forum.xxxx.com/showthread.php...E4%E0%EB%F1%FF;Referrer : http://www.world66.com/member/sharprakgen1992_buIP Address : xx.xx.119.200Username : Classname : vB_DatabaseMySQL Version :

(ip addresses and forum URL have been edited above)


I have searched through this forum and found references to the need to have the database ALL of the same collation - which makes sense.

Via PHPMYadmin, I selected my forum database xxxx_forum and confirmed that ALL tables were set to latin1_swedish_ci
I also opened each table and confirmed each itelf inside is also all set to latin1_swedish_ci
When viewing my forum viaing phpmyadmin, I DID find that at the very bottom where it lists the # of tables (mine is 195) it did show that the database was set to utf8_general_ci ..........SO as per other forum posts, I selected Operations on my database, selected latin1_swedish_ci as the Collation, and GO.
No errors occured.
And now at the bottom it shows 195 tables and that the collation IS latin1_swedish_ci

What I have not been able to find is:

The other tables showing via PHPMYadmin ALL are set to use utf8_general_ci - should these ALL also be changed?
(the other tables are information_schema, mysql, phpmyadmin, test)

AND when I first load PHPMYadmin, on the left it shows the list of 5 databases, in the main area it has Server Connection Collation: utf8_general_ci - is THIS also supposed to be set to latin1_swedish_ci ?

AND the other question is, is there something I am supposed to ALSO do after setting my forum database via Operations to collation of latin1_swedish_ci and selecting Go? Am I supposed to do anything else other than this?

I appreciate any help on this. Thank you