Announcement

Collapse
No announcement yet.

Illegal mix of collations utf8_unicode_ci and utf8_general_ci (Upgrade from 3.8 to 4.2 error)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Illegal mix of collations utf8_unicode_ci and utf8_general_ci (Upgrade from 3.8 to 4.2 error)

    Dear Support and vBulletin..

    I wasupgrading my board from 3.8 to 4.2! I been doing the upgrade a few times befor, and a few times i got the Illegal mix of coalition error, which I always fixed it by checking all my tables and editing my language coalition one by one..

    But this time its giving another error, befor It used to be Illegal mix of collations (utf8_swedish_ci,IMPLICIT) and
    (utf8_general_ci,IMPLICIT) ... But now it saying something different! All tho I did fix the tables, but getting the same error again..

    Below is how the first line of the error looks:
    <strong>MySQL Error</strong> : Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='<br />
    I fixed all the tables with wrong coaltion one by one, and still I got mysql error again. Right now my language is set to utf8_unicode_ci and all my tables are also set toutf8_unicode_ci,

    I also found online I found out that it can happen that even that the coalition is the same, The error will still appear because another type of convesation may have been used by my own server... I have attached a few images to show all my tables and database is the same... Still I get the error.. Can some tell me a fix to thise

    Image of Myphpadmin Frontpage
    Click image for larger version

Name:	1.png
Views:	1
Size:	8.0 KB
ID:	3724920


    Image of my tables, and all looks the same

    Click image for larger version

Name:	2.png
Views:	1
Size:	53.1 KB
ID:	3724921


    Below is the which I am talking about, I get this everytime

    Unexpected Text:
    Code:
    <?xml version="1.0" encoding="windows-1252"?>
    <?xml version="1.0" encoding="windows-1252"?>
    <errors>
        <error><=!=[=C=D=A=T=A=[<p>Database Error</p>
    
    Invalid SQL:
    
                (SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
                    package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
                    package.class AS class, -1 as isaggregator
                FROM package AS package
                LEFT JOIN product AS product
                        ON product.productid = package.productid
                WHERE product.active = 1
                    OR package.productid = 'vbulletin'
                )
    
                UNION
    
                (SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
                    1, 1, contenttype.class AS class  ,  contenttype.isaggregator
                FROM contenttype AS contenttype
                INNER JOIN package AS package ON package.packageid = contenttype.packageid
                LEFT JOIN product AS product ON product.productid = package.productid
                WHERE product.active = 1
                OR package.productid = 'vbulletin'  );
    
    Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=']=]=></error>
        <error_html><=!=[=C=D=A=T=A=[<p>Database error in vBulletin 3.8.7</p>
    <p>Invalid SQL:
    
                (SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
                    package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
                    package.class AS class, -1 as isaggregator
                FROM package AS package
                LEFT JOIN product AS product
                        ON product.productid = package.productid
                WHERE product.active = 1
                    OR package.productid = 'vbulletin'
                )
    
                UNION
    
                (SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
                    1, 1, contenttype.class AS class  ,  contenttype.isaggregator
                FROM contenttype AS contenttype
                INNER JOIN package AS package ON package.packageid = contenttype.packageid
                LEFT JOIN product AS product ON product.productid = package.productid
                WHERE product.active = 1
                OR package.productid = 'vbulletin'  );<p>
    <p>
    <strong>MySQL Error</strong>   : Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='<br />
    <strong>Error Number</strong>  : 1267<br />
    <strong>Request Date</strong>  : Tuesday, October 23rd 2012 @ 09:05:44 AM<br />
    <strong>Error Date</strong>    : Tuesday, October 23rd 2012 @ 09:05:46 AM<br />
    <strong>Script</strong>        : install/upgrade.php<br />
    <strong>Referrer</strong>      : install/upgrade.php<br />
    <strong>Classname</strong>     : vB_Database<br />
    <strong>MySQL Version</strong> : 5.0.92-log<br />
    </p>]=]=></error_html>
    </errors>
    Hopefully I get some help fixing this once for all! - Thank you very much in ADVANCE, By CM
    Last edited by ChiNa; Tue 23 Oct '12, 12:46pm.
    Check some of my work on vBulletin.org by CLICKING HERE!
    I dedicate my work and passion to those who have helped me and made me become the person I am today! From Coders, Designers to amazing Websites and Forums who always been there for me! Forever Greatfull!

  • #2
    When vBulletin creates a new table in the upgrade process, it asks MySQL what the default collation for the database is and then uses that. Most MySQL installations default to latin1_swedish_ci. If you have changed collations for any reason on tables in the past then you should change the database to use that as the default to prevent this issue from happening in the future.

    You will find more information in the MySQL manual here:
    http://dev.mysql.com/doc/refman/5.1/en/charset.html

    You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database. You need to use program like phpmyadmin to view your collations:

    http://www.phpmyadmin.net/home_page/index.php

    When you click your database name in phpmyadmin it will list all tables, their collations, as well as the collation of the database itself at the bottom. And when you click the name of a table on the left it will list the collations of individual fields within that table.

    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.

    2) To edit the collation of an individual table you need to click its name on the left and then click "Operations" on the top.

    3) To edit the collation of an individual field within a table you need to click the table name on the left and then click the edit image (a little pencil icon) for that field.

    For more information please see: http://www.vbulletin.com/forum/showt...ight=collation

    Note: It is very very very important to make full database backups before proceeding if you are ever manually making any changes to your database, let it be issuing queries, or editing it via phpMyAdmin or any other interface. If done incorrectly, manually modifying the database can potentially cause irreversible damage to your database, and there is no way to perform a "partial restore" to restore just the part you may break.
    Vote for:

    - *Admin Settable Paid Subscription Reminder Timeframe*
    -
    *PM - Add ability to reply to originator only*
    - Add Admin ability to auto-subscribe users to specific channel(s)
    - "Quick Route" Interface...

    Comment


    • #3
      Thank you very much, I really hope this helps, Else I will be in big trouble!
      I hope also this TOPIC will stay here because I think it will help many in future! I will def be coming back to this, and plus thaat I will be reffering this to all of my friends and colleagues!

      THANKS YOU SIR
      Last edited by ChiNa; Wed 24 Oct '12, 11:52am.
      Check some of my work on vBulletin.org by CLICKING HERE!
      I dedicate my work and passion to those who have helped me and made me become the person I am today! From Coders, Designers to amazing Websites and Forums who always been there for me! Forever Greatfull!

      Comment


      • #4
        After searching and searching,,I found the exact same thing pasted by Lynne and other good info,...

        As I said in my thread :
        I fixed all the tables with wrong coaltion one by one, and still I got mysql error again. Right now my language is set to utf8_unicode_ci and all my tables are also set to utf8_unicode_ci,
        But I have 400 tables that I have to check and fix IT will take me forever! But I will have to try, Thank you again
        Last edited by ChiNa; Thu 25 Oct '12, 3:42am.
        Check some of my work on vBulletin.org by CLICKING HERE!
        I dedicate my work and passion to those who have helped me and made me become the person I am today! From Coders, Designers to amazing Websites and Forums who always been there for me! Forever Greatfull!

        Comment


        • #5
          Originally posted by ChiNa-Man View Post
          After searching and searching,,I found the exact same thing pasted by Lynne and other good info,...

          As I said in my thread :

          But I have 400 tables that I have to check and fix IT will take me forever! But I will have to try, Thank you again
          You can write a php script that can change it for all tables in one go. I have written one here for another user who had the same problem and it fixed it for them. Can''t remember the exact thread though. But if you do a little searching it might bring it up.

          Comment


          • #6
            @borbole, Yeh I found that also long befor asking for help, and Finally I fixed it..
            The only thing I had to change was the my tables that started with the word "product"...

            If you can see in my first thread It says a lot of stuff about "product" in the error, so I tried to look for any word that started with product! And that was it.. But now I can see some of the Original Tables for vB4.2 is missing after the upgrade!

            If someone could answer my other thread here I would be very happy: https://www.vbulletin.com/forum/show...lling-new-mods

            Thank you guys for the answers...

            @Borbole BTW - This is the PHP Code for conversation tables, worked great:
            <?php
            $db = mysql_connect('HOSTNAME','USERNAME','PASSWORD');
            if(!$db) echo "Cannot connect to the database - incorrect details";
            mysql_select_db('DATABASENAME'); $result=mysql_query('show tables');
            while($tables = mysql_fetch_array($result)) {foreach ($tables as $key => $value) {mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
            }}
            echo "The collation of your database has been successfully changed!";
            ?>
            Last edited by ChiNa; Thu 25 Oct '12, 11:06am.
            Check some of my work on vBulletin.org by CLICKING HERE!
            I dedicate my work and passion to those who have helped me and made me become the person I am today! From Coders, Designers to amazing Websites and Forums who always been there for me! Forever Greatfull!

            Comment


            • #7
              Glad to hear that you got it fixed. I replied to your other thread as well.

              Comment

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