SQL error after upgrade

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ralffan
    New Member
    • Aug 2017
    • 26
    • 5.7.0

    SQL error after upgrade

    Hello,

    We recently updated our site (in test environment) from 4.2.5 to 5.3.4.

    I have had some errors that I managed to correct by doing another upgrade (again from original 4.2.5 installation) and deleting all plugins/products installed before upgrade, but I enabled VB Blogs and VB CMS (as recommended by Joe D. in other post).

    I have error if I try to change banner for all styles, so I tried to run option in AdminCP -> Maintenance -> General Update Tools -> Rebuild styles. If I only check the first option, everything runs fine, but when I check option "Renumber all templates from 1" the following error occurs:


    Database error in vBulletin 5.3.4:

    Invalid SQL:

    INSERT INTO template_temp
    (styleid, title, template, template_un, templatetype, dateline, username, version, product, mergestatus, textonly)
    SELECT {styleid, title, template, template_un, templatetype, dateline, username, version, product, mergestatus, textonly}
    FROM template
    ORDER BY styleid, templatetype, title
    /**template_table_query_insert**/;

    MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' title, template, template_un, templatetype, dateline, username, version, produc' at line 3
    Error Number : 1064
    Request Date : Sunday, March 4th 2018 @ 05:50:37 PM
    Error Date : Sunday, March 4th 2018 @ 05:50:37 PM
    Script : xxxxx/vb5/admincp/misc.php?do=rebuildstyles
    Referrer : xxxx/admincp/misc.php?do=chooser&
    IP Address : xxx
    Username : xxxx
    Classname : vB_Database_MySQLi
    MySQL Version : 5.7.14

    I'm using wamp, and PHP version 7.0.10.

    Thanks.
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    Don't renumber the templates. We removed this option in 5.4.0. The system allows for 4 Billion templates to be stored.

    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • Ralffan
      New Member
      • Aug 2017
      • 26
      • 5.7.0

      #3
      I have other issues, I thought them could be related with it:

      In control panel home, the following message appears:
      Out-of-Date Custom Templates Found!
      There are currently 1 customized template(s) that need to be updated or reverted. Some sections of vBulletin may not function if you do not do this!
      However, it doesn't look like I have any template that needs to be updated nor reverted...

      Another error is when I try to change banner image for all templates in the site builder.

      If I should open separate threads for each issue, please let me know.

      Thanks!

      Comment

      • Wayne Luke
        vBulletin Technical Support Lead
        • Aug 2000
        • 73981

        #4
        Originally posted by Ralffan
        I have other issues, I thought them could be related with it:

        In control panel home, the following message appears:
        Out-of-Date Custom Templates Found!
        There are currently 1 customized template(s) that need to be updated or reverted. Some sections of vBulletin may not function if you do not do this!
        However, it doesn't look like I have any template that needs to be updated nor reverted...
        Nothing shows in the Find Updated Templates code?

        Another error is when I try to change banner image for all templates in the site builder.
        Turn off your ad blocker.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API

        Comment

        • Ralffan
          New Member
          • Aug 2017
          • 26
          • 5.7.0

          #5
          Thanks for your reply.

          Originally posted by Wayne Luke
          Nothing shows in the Find Updated Templates code?
          Nope:







          Originally posted by Wayne Luke
          Turn off your ad blocker.
          I haven't it installed. I think it can be a timeout error because it takes too much time before the error:



          Also, I have realized that the logo changes in the first 7 styles or so... so yes, it looks like a timeout error. If I save the logo only for one style, it also takes a lot of time. I have done a separate clean test install of vB5 in same host, and performance is much better. Our site isn't that large to have those performance issues, what can I do to try improve it? I already ran option repair and optimize tables for all tables.

          When navigating through the forum, performance looks just fine, so I don't know... I have only noted this "slowlyness" editing site logo at the moment.

          Note: To clarify, with banner I mean "site logo".
          Last edited by Ralffan; Mon 12 Mar '18, 12:58pm.

          Comment

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

            #6
            Are you using the same logo on all styles?
            Vote for:

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

            Comment

            • Wayne Luke
              vBulletin Technical Support Lead
              • Aug 2000
              • 73981

              #7
              A few things...
              1. Do not store templates in the file system. This is almost always slower than storing them in the database.
              2. Do store CSS in the filesystem. These are cached by the browser which makes them faster.
              3. Delete any Themes that you won't be using. To prevent them from reoccurring, delete their folders from /core/install/themes before upgrading to the next version.
              4. Delete any incompatible vBulletin 3 and vBulletin 4 themes from your system.
              5. Make sure to remove all vBulletin 3 and vBulletin 4 add-on product files from the system.
              6. Set your PHP Memory Limit to 128 MB.
              7. Make sure that the temporary directory is properly set for PHP.
              8. Make sure that the PHP File Upload limit is set properly.
              9. Update the attachment file sizes for images after running your upgrades. These are very small in vBulletin 3 and vBulletin 4. They were set before Smart Phones existed. The new limits in vBulletin 5 require less CPU and memory to process. The vBulletin 5 defaults are 1440 X 900 pixels and 900,000 bytes. AdminCP -> Attachments -> Attachment Type Manager.
              10. If all else fails, clear your system cache. AdminCP -> Maintenance -> Clear System Cache.
              Also, upgrade to 5.4.0. You should always be on the latest version of vBulletin 5. A properly customized vBulletin 5 system will keep all of its customizations on upgrade. Even across multiple styles. Newer versions almost always include refactored code that fixes issues, removes obsolete code, makes the system more robust, and improves performance.
              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API

              Comment

              • Ralffan
                New Member
                • Aug 2017
                • 26
                • 5.7.0

                #8
                Originally posted by Wayne Luke
                A few things...[LIST=1][*]Do not store templates in the file system. This is almost always slower than storing them in the database.[*]Do store CSS in the filesystem. These are cached by the browser which makes them faster.
                How can I do or check this configuration? (I think templates are being stored in databae. In any case I haven't modified these options so they must be whatever the default is).

                When I first did the upgrade, most recent version was 5.3.4. I will update to 5.4.0 and post if issues remain there.

                Comment

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

                  #9
                  AdminCP > Settings > Options > Style & Language Settings > Cache Templates As Files
                  Vote for:

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

                  Comment

                  • Ralffan
                    New Member
                    • Aug 2017
                    • 26
                    • 5.7.0

                    #10
                    Originally posted by Ralffan
                    I have other issues, I thought them could be related with it:

                    In control panel home, the following message appears:
                    Out-of-Date Custom Templates Found!
                    There are currently 1 customized template(s) that need to be updated or reverted. Some sections of vBulletin may not function if you do not do this!
                    However, it doesn't look like I have any template that needs to be updated nor reverted...
                    Hello again, regarding this, I found this post: https://www.vbulletin.com/forum/foru...-after-upgrade

                    This made me have a look to "template" table, and I see a lot of old things from previous versions that are absolutely not useful (and even I think there is a registry with code injected by hackers) Is there a way to clean this table and delete all data and let only 5.X things? (if there isn't, I will try to do it manually in database in the test site, after backing up).

                    Thank you for you replies.

                    Comment

                    • Wayne Luke
                      vBulletin Technical Support Lead
                      • Aug 2000
                      • 73981

                      #11
                      So there is only a brute-force method of cleaning out extraneous information from the style system.

                      First, you have to run these three queries:
                      Code:
                      TRUNCATE `style`;
                      TRUNCATE `stylevar`;
                      TRUNCATE `template`;
                      This will make your site inoperable until you run /core/install/upgrade.php. To do that, you'll need to upload the /core/install folder again and loading the page in your browser. This will cause the system to reimport and rebuild all vBulletin 5 styles.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment

                      Related Topics

                      Collapse

                      Working...