Trouble Upgrading from 3.8.4 to 4.0.0 - Step 8 crashes with DB issue

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Hotpuppy
    Member
    • May 2009
    • 62
    • 3.8.x

    [Suite] Trouble Upgrading from 3.8.4 to 4.0.0 - Step 8 crashes with DB issue

    I'm getting an invalid SQL error on Step 8.

    (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 (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
    Error Number : 1267
    Request Date : Monday, December 21st 2009 @ 09:29:12 PM
    Error Date : Monday, December 21st 2009 @ 09:29:12 PM
    Script : http://www.<>.org/forums/install/upg...0a1.php?step=8
    Referrer : http://www.<>.org/forums/install/upg...0a1.php?step=7
    IP Address : <myip>
    Username :
    Classname : vB_Database
    MySQL Version : 5.1.30
    **********
    any ideas on this?
  • Hotpuppy
    Member
    • May 2009
    • 62
    • 3.8.x

    #2
    Never did get this fixed..... and it didn't get any better .... currently my site is offline. Very unhappy.

    Notes for others:
    - The "README" in the file set is complete crap. Skip it and go to the manual which is at least somewhat thorough and covers all of the bear traps that the readme forgets to mention.

    Current Status: Cannot restore DB - have tried through cpanel and what's left of the admin cp. Admin cp is pretty trashed from changes made during upgrade. Trying a few fixes appears to have pretty well killed it. This means my site is down because it is 11:50pm and I'm exhausted. I know better than to work on anything complicated when I'm exhausted. I started at 8pm. Should have been able to backup, upgrade, decide to fall back, and restore in that time period.

    Bright points: I did make backups and if needed I can blow away the database and start there in the morning. grrrr. Nothing like poor install processes to make me feel the value of my license.

    Comment

    • Hotpuppy
      Member
      • May 2009
      • 62
      • 3.8.x

      #3
      Upgrade Failed Catastrophically requiring DB and File Restore!

      I did manage to get the site back up..... decided I would delete the DB and restore it from backup. Was able to do that through the cpanel.

      Still very annoyed that I had to do that.

      Running php 5.2.9 and MySQL 5.1.x

      Probably a plugin or mod issue.... just would be nice if the upgrade cleaned up it's own gotchas instead of requiring the admin to go hunt down all the mousetraps.

      Failing that a concise list of known mousetraps would be nice so that folks could go disable or workaround the issues prior to having problems.

      Comment

      • Mike Anime
        Senior Member
        • Oct 2004
        • 1841
        • 3.7.x

        #4
        i can only hope this will help



        way over my head

        it could be a mod you added or plugin you were supposed to disable all those also your old custom skins will not convert.

        Comment

        • dtommy79
          Senior Member
          • Jul 2007
          • 342
          • 4.0.x

          #5
          Have you disabled all your plugins before install?
          If you would like to see a Mass PM feature in VB please vote for it here
          *Learn Hungarian*

          Comment

          • Hotpuppy
            Member
            • May 2009
            • 62
            • 3.8.x

            #6
            Not the first time.... but yes, I did try that...
            I did disable hooks and went into PLUGINS & PRODUCTCS>MANAGE PRODUCTS and disabled them.

            Is there somewhere else I need to disable something?

            I have the following plugins installed:
            AME 2.5.5
            CYB 1.2 ( I have no idea what this is and am about to uninstall it, a previous admin volunteer installed it at some point)
            Edit Poll Options 1.01
            Engage Twitter 1.0
            EZ Newsletter 1.1.4
            LV VB Event Forums 1.1.4
            MGC Chatbox EVO 2.4.2
            VB SEO 2.5 (I'm going to uninstall this one as well - although I doubt it is the culprit)
            Vbulletin Blog 2.0.2 (This one only stings in the wallet, and I doubt it needs to be disabled.... nothing like buying something and being told 6 months later it has no separate value)
            Vbulletin Tweet Poster 1.0.6 (doubt this is causing issues)


            My hunch is that when it failed the first time I should have gone in and disabled all the plugins and restored the database.

            What is frustrating for me..... is that it's very easy to check for this sort of thing in a script:

            IF PLUGINS ENABLED THEN WARN ADMIN
            IF HOOKS DISABLED = FALSE OR NOT DEFINED THEN WARN ADMIN

            There is a nasty-gram for install.php when you load admincp. (saw that when I did the restore)

            I'm also dissappointed that the error handling doesn't have a rollback process. Seems like if you can script in one direction you can script in the other. There are alot of folks who are not whiz bang DB admins and coders who expect this stuff to just work.

            grrrrr....

            I do appreciate the ideas DTommy and Mike Anime.

            Comment

            • Hotpuppy
              Member
              • May 2009
              • 62
              • 3.8.x

              #7
              Originally posted by Mike Anime
              i can only hope this will help




              it could be a mod you added or plugin you were supposed to disable all those also your old custom skins will not convert.
              I used Vbulletin 3.8 to setup the database... so I hope that there isn't a bug there...

              Most likely it is a plugin conflict..... I stupidly trusted the README to be updated and correctly documented with the steps for the upgrade.... I have since learned that the README should contain the following in the UPGRADE heading "Read the manual located at www.vbulletin.com" and nothing else. The manual is excellent, the README is outdated, inaccurate, and missing many important items.

              I'm not using skins....

              Thank you for your response. I'm going to make another attempt.... after a tall glass of Orange Juice.

              Comment

              • Hotpuppy
                Member
                • May 2009
                • 62
                • 3.8.x

                #8
                Just so nobody gets the wrong idea.... I'm hard on the feedback because I know that any team that can write a product as fantastic and complicated as vBulletin can do two things superbly:
                - Have up to date Readmes and documentation
                - build in some safety features in their upgrade code.

                There is NO excuse for learning about the sharp edges by losing a finger on this machine. We can do better. Sharp edges and lost fingers turn customers off and turned off customers don't spend money which makes it hard to pay developer wages in the comfy 6 digits.

                Comment

                • Hotpuppy
                  Member
                  • May 2009
                  • 62
                  • 3.8.x

                  #9
                  Well, a fresh run failed.....

                  same issue, database collation error.

                  -uninstall plugins that are obsolete / not in use.
                  - copy new files to temporary location on server
                  -disable board
                  - run database repair/optimize
                  - backup database
                  - backup files
                  - disable plugins
                  - backup database again to different file
                  - copy files to /forums folder
                  - run upgrade
                  - fail at step 8

                  recovery
                  - delete database
                  - delete forums folder
                  - restore forums folder from backup
                  - restore database from backup before disabling plugins
                  - open trouble ticket with Vbulletin.

                  Comment

                  • Hotpuppy
                    Member
                    • May 2009
                    • 62
                    • 3.8.x

                    #10
                    Hmm, I think this holds the solution:


                    I think adding to the sql to specify the collation would resolve this

                    Comment

                    • Hotpuppy
                      Member
                      • May 2009
                      • 62
                      • 3.8.x

                      #11
                      Okay, so the root issue is that two mods created tables as default, which is unicode on this server. The mods should have specified that they wanted to create utf8_general_ci.

                      Wall of shame:
                      vbgooglemap - I uninstalled this anyhow, never could get it to work right. It didn't remove itself from the db. On par for my experience with that mod.
                      EVO Chat - this mod worked great and was really popular, sadly it created tables without specifying the collation.

                      Fix:
                      alter table collation. I'm not familiar with MySQL enough to know the exact statement... can someone give me a boost here?

                      I suspect it is something along the lines of:
                      alter table mgc_cb_evo_bot set collation=utf8_unicode_ci

                      I would appreciate someone helping me fix my SQL code so I can fix this.

                      alternate scenario is to remove the mod.

                      Comment

                      • Hotpuppy
                        Member
                        • May 2009
                        • 62
                        • 3.8.x

                        #12
                        I finally solved this...
                        Root cause is that the ISP has utf_unicode_ci as the default mysql collation.

                        Resolution:
                        - Delete database (be sure to have a backup first!!!)
                        - Create DB using cpanel
                        - Edit collation and set to UTF8_general_ci
                        - Import / Restore Data
                        - manually check for unicode tables and remove or convert as needed. These may be left over from mods you installed.

                        Comment

                        • Trevor Hannant
                          vBulletin Support
                          • Aug 2002
                          • 24323
                          • 5.7.X

                          #13
                          Some customers have recommended this third party utility for converting a database in the past:

                          http://vcharset.com/

                          Note that conversions are not always possible, and sometimes result in a few bad characters.
                          Vote for:

                          - Admin Settable Paid Subscription Reminder Timeframe (vB6)
                          - Add Admin ability to auto-subscribe users to specific channel(s) (vB6)

                          Comment

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