Announcement

Collapse
No announcement yet.

Illegal mix of collations error while upgrading from 3.8.2 to 4.01

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

  • [Forum] Illegal mix of collations error while upgrading from 3.8.2 to 4.01

    Illegal mix of collations error while upgrading from 3.8.2 to 4.01

    currently i have 3.8.2. tried upgrading to 4.0.2. got these errors.

    any idea how to solve this?? thnx


    Database error in vBulletin 3.8.2:

    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' );

    MySQL Error : Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
    Error Number : 1267
    Request Date : Saturday, January 23rd 2010 @ 04:37:57 AM
    Error Date : Saturday, January 23rd 2010 @ 04:37:57 AM
    Script : http://tunesbd.net/upload/install/up...0a1.php?step=9
    Referrer : http://tunesbd.net/upload/install/up...0a1.php?step=8
    IP Address : 123.200.9.190
    Username :
    Classname : vB_Database
    MySQL Version : 5.0.41-community-log

  • #2
    list of plugins installed ? some might have caused issues see http://www.vbulletin.com/forum/showt...=1#post1880954
    :: Always Back Up Forum Database + Attachments BEFORE upgrading !
    :: Nginx SPDY SSL - World Flags Demo [video results]
    :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

    Comment


    • #3
      thnx for the reply. i have uninstalled all the plugins and tried upgrading. but same error. dont what else to do. please advice.

      Comment


      • #4
        I feel your pain.
        Dummy fix for Illegal mix of collations

        Comment


        • #5
          Originally posted by Videx View Post
          thnx for it but it dint help. man i am sad. any1 else please?

          Comment


          • #6
            Sorry, but if all your fields and tables are now the same collation, and yet you still get the error, I think you're screwed. Can we at least assume you can set up a new clean vb4 with a blank db? Maybe you can use Impex to import what's left of your old db.

            Comment


            • #7
              yes i can install clean vb4. no problem with that. will impex import all stuffs from old 3.8.2?? cheers

              Comment


              • #8
                Do you still have working vB 3.8.2 copy of your database and/or a mysqldump backup of your vB 3.8.2 database ? If you have vB 3.8.2 backup sql saved, have you tried creating a new empty database and importing the vB 3.8.2 backup into it and then doing upgrade of this copy of database instead ?

                Submit a support ticket if you do and provide ssh2 telnet, phpmyadmin access info so staff member can check your vB 3.8.2 database tables to see if they are same collation.
                :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                :: Nginx SPDY SSL - World Flags Demo [video results]
                :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                Comment


                • #9
                  i had this problem and i have the 100% solution for you!

                  1- go to your phpmyadmin.
                  2- check your default collations and change it to [ex: utf8_general_ci]
                  3- you have to know that the problem is upgrading to vb 4.0 doesn't accept mixed collations [for example in one table your collations = utf8_general_ci and others = utf_unicode_ci OR thier individual fields not the same with all the collations] this will NOT work and you will keep having this [illegal mixed collation]
                  4- what you have to do now is to convert all your tables AND thier individual fields (THIS IS IMPORTANT) not only the tables to [utf8_general_ci]!
                  5- if you don't know how to do that, just ask your host to do it for you (just like what i did) and ask them to convert all the tables and thier
                  individual fields.
                  6- when you are done converted all the tables and thier
                  individual fields + put default collations to [utf8_general_ci]
                  7- then do the upgrade.

                  this is the only solution and it's 100% that's what i did..

                  DON'T FORGET TO TAKE A BACKUP!
                  good luck..

                  Comment


                  • #10
                    Originally posted by eva2000 View Post
                    Do you still have working vB 3.8.2 copy of your database and/or a mysqldump backup of your vB 3.8.2 database ? If you have vB 3.8.2 backup sql saved, have you tried creating a new empty database and importing the vB 3.8.2 backup into it and then doing upgrade of this copy of database instead ?

                    Submit a support ticket if you do and provide ssh2 telnet, phpmyadmin access info so staff member can check your vB 3.8.2 database tables to see if they are same collation.
                    hi. yeah i have backup. actually i wasnt trying the upgrade on live board and i learnt this after running vb for last 5 years. . so everything i am doing with backups. only prob is that my host doesnt support ssh and thats why i cant just dl a backup and edit it in pc and restore it.

                    what i did is... create another database in phpmyadmin and copy existing vb 3.8.2 database to that new database and upgrade that new database to vb4. and btw i have uninstalled every plugin i had.

                    Comment


                    • #11
                      I got past this by downloading a database copy with msql admin (free), use textpad (shareware) to find and replace example_example_example collation name with example2_example2_example2 then I restored it and it was fixed and they were all labeled the same.

                      Comment


                      • #12
                        thnx for your replies guys. i will try these. cheers

                        Comment


                        • #13
                          so after everything failed, I asked my host to change my database collation and internal collation to utf-general-ci. and after it got changed i tried to upgrade to vb4 again and it got upgraded nicely. nice i am feeling relieved.

                          thnx every1 for helping me out. cheers

                          Comment


                          • #14
                            ohhh.......... peoples.. 1 thing..... i am facing another prob after upgrading. after upgrading successfully when i went to my forum it just shows a blank page. not a single page shows anything. simply blank white page. but my admincp works pretty fine.

                            Comment

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