The right way to fix the (Illegal mix of collations) problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Mohamed Ashraf
    New Member
    • Jun 2006
    • 9

    The right way to fix the (Illegal mix of collations) problem

    Hello every body ,,

    I noticed that alot of people had the (Illegal mix of collations) problem ( me too ) ,

    Code:
    Database error in vBulletin 3.6.0:Invalid SQL:
                SELECT cron.*
                FROM cron AS cron
                LEFT JOIN product AS product ON (cron.product = product.productid)
                WHERE cron.nextrun <= 1154876909 AND cron.active = 1
                    AND (product.productid IS NULL OR product.active = 1)
                ORDER BY cron.nextrun
                LIMIT 1;MySQL Error  : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and(latin1_general_ci,IMPLICIT) for operation '='Error Number : 1267
     
    Date         : Sunday, August 6th 2006 @ 06:08:29 PM
    Script       : [URL="http://xxxxxxxxx/vb/cron.php?&rand=732223"][COLOR=#0000ff]http://xxxxxxxxx/vb/cron.php?&rand=732223[/COLOR][/URL]
    Referrer     : [URL="http://xxxxxxxxx/vb/forumdisplay.php?f=25"][COLOR=#0000ff]http://xxxxxxxxx/vb/forumdisplay.php?f=25[/COLOR][/URL]
    IP Address   : xx.xxx.xxx.xxx
    Username     :  
    Classname    : vb_database
    so here is the right way to fix it ,,,

    # first we have to know which Tables we have to work on it ,, in the message we can see

    [ LEFT JOIN product AS product ON (cron.product = product.productid)
    WHERE cron.nextrun <= 1154876909 AND cron.active = 1
    AND (product.productid IS NULL OR product.active = 1)
    ORDER BY cron.nextrun ] ,,,

    and change the collations from ( latin1_general_ci ) to ( latin1_swedish_ci )

    so we will go to Table Product and i think Table productcode & productdependency & Plugin too (*this an example*),,

    then follow the pics
    ********************************************


    ********************************************


    ********************************************


    ********************************************
    But remember this was only an example you have to figure which table you have to change its Collation

    Regards,
    Mohamed Ashraf
  • Steve Machol
    Former Customer Support Manager
    • Jul 2000
    • 154488

    #2
    You need to change the tables that are latin1_general_ci to latin1_swedish_ci.
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment

    • Mohamed Ashraf
      New Member
      • Jun 2006
      • 9

      #3
      Dear ,
      Steve Machol ,,

      thank you for your post

      Regards,
      Mohamed Ashraf

      Comment

      • simsim
        Senior Member
        • Nov 2005
        • 1625
        • 3.6.x

        #4
        Thank you Mohamed,

        This did exactly solve my problem.
        You're spending millions of dollars on a website?!

        Comment

        • Mohamed Ashraf
          New Member
          • Jun 2006
          • 9

          #5
          thank you simsim for your post

          all the best

          Comment

          • MA2000
            New Member
            • Apr 2007
            • 1
            • 3.6.x

            #6
            Thank you Mohamed

            Thank you Mohamed very much

            to help solve this problem

            Comment

            • Esoteric
              New Member
              • Mar 2006
              • 7
              • 3.5.x

              #7
              I have changed all my collations to 'latin1_swedish_ci' but I still get the error. Please help.

              Edit: Found my problem, be aware that some fields have collations to, you need to update the fields too so they are all the same.
              Last edited by Esoteric; Fri 13 Apr '07, 5:28pm.

              Comment

              • Truculencia
                New Member
                • Aug 2006
                • 5
                • 3.6.x

                #8
                Delete this post please.
                Last edited by Truculencia; Mon 17 Mar '08, 12:17pm.

                Comment

                • Biek
                  New Member
                  • Mar 2006
                  • 6

                  #9
                  Thanks a bunch Mohamed, you saved my a** (And my board ) with your solution.

                  Comment

                  • Machi
                    New Member
                    • Oct 2007
                    • 3
                    • 3.6.x

                    #10
                    Hi Mohamed, I have same problem with reputation field in members.php and with admin help. I've tried to change collation exactly as you said but nothing happened. It looks ok, but collation is still the same as before, please is there any solution?
                    http://img507.imageshack.us/img507/4383/changejx9.jpg
                    Take a look on this picture it's after I've changed collatins from utf8_czech_ci to latin1_swedish_ci, colud you see it? nothing changed
                    thanks a lot

                    Comment

                    • Mohamed Ashraf
                      New Member
                      • Jun 2006
                      • 9

                      #11
                      Originally posted by Machi
                      Hi Mohamed, I have same problem with reputation field in members.php and with admin help. I've tried to change collation exactly as you said but nothing happened. It looks ok, but collation is still the same as before, please is there any solution?
                      http://img507.imageshack.us/img507/4383/changejx9.jpg
                      Take a look on this picture it's after I've changed collatins from utf8_czech_ci to latin1_swedish_ci, colud you see it? nothing changed
                      thanks a lot
                      please post the DB error you got

                      and screen shot of your tables like my first pic

                      i'll try to figure out why it didnt save the changes

                      Comment

                      • sbarbz
                        New Member
                        • Oct 2007
                        • 1
                        • 3.7.x

                        #12
                        i have a 198 tabels ..
                        approximately 50% is latin1_swedish_ci and 50% more is latin1_general_ci .
                        which exactly(name), to change

                        Thank you very much for your post

                        Comment

                        • peterska2
                          Senior Member
                          • Oct 2003
                          • 8869
                          • 3.7.x

                          #13
                          Please start your own thread in the relevant forum for the version of vBulletin that you are running.

                          Comment

                          • isomikis
                            New Member
                            • Apr 2007
                            • 8

                            #14
                            Thanks I solved this problem with changing table encoding.

                            Comment

                            • Mermaid
                              Senior Member
                              • Mar 2008
                              • 761
                              • 3.6.x

                              #15
                              Is it possible for someone to explain this solution simpler terms or more basic steps please?

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...