Illegal mix of collations error after moving to new server

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jcard
    Member
    • Jun 2010
    • 32

    [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

    - - - Updated - - -

    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?
    Anyone?
    I can't find definite answers to these three questions
    - can someone else running VBulletin 4.2 let me know what their Server Connection Collation: is set to, AND what their forum database itself is set to - i.e. do they match? are they supposed to? so that I have that as reference.
    My old server via PHPmyadmin does NOT have matching, but it was different versions of MySQL and PHP - so not sure if that is also causing an issue.
    Anyone?

    - - - Updated - - -

    This is what I mean by the Server Connection Collation: - my forum database is latin1_swedish_ci BUT for Server Connection Collation: it shows as utf8_general_ci
    Click image for larger version

Name:	phpmyadmin1.png
Views:	1
Size:	54.6 KB
ID:	3725411


    And I have confirmed all tables and fields ARE using latin1_swedish_ci
    Click image for larger version

Name:	phpmyadmin2.png
Views:	1
Size:	30.2 KB
ID:	3725412

    - - - Updated - - -

    UPDATE: I was able to bring up the old server and check it - it too prevously had Server Connection Collation: set to utf8_general_ci and it ran fine for the last few years; and this is also what IS showing on my new server. Based on that, I believe I do NOT need to have Server Connection Collation: set to latin1_swedish_ci. Correct??

    Which than comes back to one of the other questions I had to troubleshoot - is there something ELSE I am supposed to do (whether via PHPmyadmin OR via another program or mysql or whatever?) AFTER I had performed the "1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top, select the latin1_swedish_ci , press Go and complete ." step???? Anyone??
    Last edited by jcard; Wed 23 Jan '13, 1:51pm.
  • jcard
    Member
    • Jun 2010
    • 32

    #2
    As am not eligible for ticket support at this time since a vB4 forum license only includes 30 days ticket support, really hoping someone here will share what they specifically did when they moved server's and/or when encountered a similiar error.Thank you.

    Comment

    • jcard
      Member
      • Jun 2010
      • 32

      #3
      Its been 5 days, and just got the same error again this morning:


      Database error in vBulletin 4.2.0:
      Invalid SQL: SELECT * FROM session WHERE sessionhash = '11c268a45407c7a950c1279e1faa2c7c Result: èñïîëüçîâàí íèêíåéì "Flulsify1984"; ïèêòîêîä äåøèôðîâàí; çàðåãèñòðèðîâàëèñü; âõîä â àêêàóíò íå óäàëñÿ;' AND lastactivity > 1359399953 AND idhash = '0375986ee37ac2e387bd118402b57fc6';

      MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

      Error Number : 1267

      Request Date : Monday, January 28th 2013 @ 11:35:53 AMError Date : Monday, January 28th 2013 @ 11:35:54 AMScript : http://forum.xxxx.com/showthread.php...E4%E0%EB%F1%FF;Referrer : http://bestcreditdpj7590.bugs3.com/%...%BD%D0%BE.htmlIP Address : xxx.xxx.95.78Username : Classname : vB_DatabaseMySQL Version :


      Does anyone have any other suggestions what to look for? what to check?
      Last edited by jcard; Mon 28 Jan '13, 10:50am.

      Comment

      • Ian Cunningham
        Senior Member
        • Feb 2002
        • 105

        #4
        Did you manage to fix this in the end? I've got a very similar problem.
        Laptop Forums

        Comment

        • jcard
          Member
          • Jun 2010
          • 32

          #5
          Nope. It still continues. I provide support myself, so I definitely know to double-check and triple check myself of those steps I have already listed I have done (everyone is human). But nope, still continues. I'm hoping some good soul reads this posts, confirms what I did do, and answers the question(s) :

          Question: is there something ELSE I am supposed to do (whether via PHPmyadmin OR via another program or mysql or whatever?) AFTER I had performed the "1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top, select the latin1_swedish_ci , press Go and complete ." step???? Anyone??

          Comment

          • BirdOPrey5
            Senior Member
            • Jul 2008
            • 9613
            • 5.6.3

            #6
            In your Admin CP, Languages, settings for your Language, what do you have as the HTML character set?

            Comment

            • jcard
              Member
              • Jun 2010
              • 32

              #7
              ISO-8859-1

              Comment

              • BirdOPrey5
                Senior Member
                • Jul 2008
                • 9613
                • 5.6.3

                #8
                I'm really not sure then. I thought maybe if it was utf-8 you could change it back and see.

                You could try temporary changing it to utf-8 and see if it helps. if it doesn't change it back.

                Comment

                • jcard
                  Member
                  • Jun 2010
                  • 32

                  #9
                  Thank you Joe D. I would really appreciate it if you know or could point me towards answers to the following two:

                  1. I have still not yet been able to figure out from anyone and anywhere what exactly is done that results with the error message so that I can confirm right away whether a change resolves it or not.

                  When I did all the recommendations, my only resort was to wait and see if the error occurred again.

                  Do you know what specific steps I would do that should result in the error if still be affected? I.e. is it registering using a specific Keyboard language setting or using a specific login name or searching for a specific phrase or characters or ???



                  2. What would be the reprocusions of changing the HTML character set to utf-8 and the forum being used, and then being set back? (it' s very time consuming to set up a compete test db and site, so.....)

                  Comment

                  • BirdOPrey5
                    Senior Member
                    • Jul 2008
                    • 9613
                    • 5.6.3

                    #10
                    There should be no long term repercussions to temporarily change the character set, however if you can't know immediately if there will be an error or not then you should not do it. The only issue I can think of is changing the character set may result in posts with special characters not saving and/or not displaying correctly - but it won't affect any existing content on the site once it has been changed back.

                    I am not familiar with charset issues, if I had the answers to your other questions I would provide them. I know VB4 is not fully compatible with UTF-8 in general so I stay away from it unless absolutely necessary.

                    Comment

                    • jcard
                      Member
                      • Jun 2010
                      • 32

                      #11
                      thank you Joe D for your suggestions and support, much appreciated.

                      perhaps another vb guru may know what steps to take to recreate getting these "Illegal mix of collations " error so can properly troubleshoot.

                      Comment

                      Related Topics

                      Collapse

                      Working...