Announcement

Collapse
No announcement yet.

SQL error after upgrade

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

  • 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.

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

    http://tracker.vbulletin.com/browse/VBV-13273
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud customization and demonstration site.
    vBulletin 5 Documentation - Updated every Friday. Report issues here.
    vBulletin 5 API - Full / Mobile
    I am not currently available for vB Messenger Chats.

    Comment


    • #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


      • #4
        Originally posted by Ralffan View Post
        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 customization and demonstration site.
        vBulletin 5 Documentation - Updated every Friday. Report issues here.
        vBulletin 5 API - Full / Mobile
        I am not currently available for vB Messenger Chats.

        Comment


        • #5
          Thanks for your reply.

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







          Originally posted by Wayne Luke View Post
          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 12th Mar '18, 12:58pm.

          Comment


          • #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 customization and demonstration site.
            vBulletin 5 Documentation - Updated every Friday. Report issues here.
            vBulletin 5 API - Full / Mobile
            I am not currently available for vB Messenger Chats.

            Comment


            • #8
              Originally posted by Wayne Luke View Post
              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


              • #10
                Originally posted by Ralffan View Post
                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


                • #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 customization and demonstration site.
                  vBulletin 5 Documentation - Updated every Friday. Report issues here.
                  vBulletin 5 API - Full / Mobile
                  I am not currently available for vB Messenger Chats.

                  Comment

                  Related Topics

                  Collapse

                  Working...
                  X