Upgrade to 5.2.6. killed our forum - SQL INSERT error now showing during post

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mark.hs
    Senior Member
    • Dec 2016
    • 197
    • 5.7.0

    Upgrade to 5.2.6. killed our forum - SQL INSERT error now showing during post

    I did a standard upgrade from v5.2.5 to v5.2.6 last night and now we are getting SQL INSERT errors whenever a user tries to post, reply or edit posts on our forum. The exact error message is :


    Error Invalid SQL: INSERT INTO vbdb( id, title, content, contenttypeid, lastcontentid, parentid, starter, starterparent, starteruser, closure, depth, userid, authorname, setfor, sentto, showpublished, approved, showapproved, viewperms, featured, inlist, protected, votes, lastcontent, created, publishdate, unpublishdate, prefixid, tagid, textcount, eventstartdate, eventenddate ) VALUES ( 63726, '', ' kjsdafjsf', 30, 63726, 51357, 51357, 22, 408, (63726,51357,22,15,2,1), '{"n63726":0,"n51357":1,"n22":2,"n15":3,"n2":4,"n1":5}', 513, 'Mark.MotionRC', 0, (), 1, 1, 1, 2, 0, 1, 0, 0, 1487786181, 1487786181, 1487786181, 0, 0, (), 0, 0, 0 );

    "vbdb" is the name of our Vbulletin database.

    I've upgraded VB many times using the procedure in the manual and have never had this problem. Our forum is currently down because of this so I hope someone can help me out and let me know how I might fix this.
    1. vBulletin Version 5.2.6
    2. PHP Version 7.0.16
    3. MySQL Version 5.5.52-MariaDB
    4. Any Addons installed Yes - but I disabled both and the problem still persists
    5. Does the issue occur in a default style? Yes
    6. Does the issue occur using the English language provided? Yes
    7. Error message on the screen' See attachment
    8. Browser and Browser version used. Chrome Version 56.0.2924.87
    9. Did you clear the browser cache and did the error continue? Yes
    10. A list of steps that can be used to recreate the issue. Post a message. Reply to a message. Edit a Message. All of these will show this error. HOWEVER, even though the error message shows the post still gets captured by the database. If you click OK and refresh your browser you will see the post, so this error message is really a false message of sorts??
    11. DB Search type = Sphinx


    Thank you,
    Mark
    www.hobbysquawk.com Click image for larger version  Name:	vberror.png Views:	1 Size:	50.7 KB ID:	4365095
    Last edited by mark.hs; Wed 22 Feb '17, 11:28am.
    Admin for hobbysquawk.com
    VB 5.74
    PHP 8.2
    Maria DB 10.6
  • mark.hs
    Senior Member
    • Dec 2016
    • 197
    • 5.7.0

    #2
    It looks like the issue is somehow related to Sphinx search. When I change from Sphinx to DB Search the error goes away. I would love to just use default DB search, but unfortunately, we must use Sphinx because of the significant limitations of the built in DB search (primarily it does not handle words/phrases with dashes).

    If any fellow forum owners figured out how to get v5.2.6 working with Sphinx please let me know, as the documentation on Sphinx and VB is so outdated and poorly organized (sorry to be blunt) that it's extremely hard to follow. It would be great if VB/IB could release an updated Sphinx install guide that was comprehensive and accurate for modern Linux distros.

    mark
    Last edited by mark.hs; Thu 23 Feb '17, 8:59am.
    Admin for hobbysquawk.com
    VB 5.74
    PHP 8.2
    Maria DB 10.6

    Comment

    • mark.hs
      Senior Member
      • Dec 2016
      • 197
      • 5.7.0

      #3
      I followed the steps to update the Sphinx Schema but I am still getting the error in the post above. Only DB Search works in v5.2.6 for me. Can someone please assist.

      Thank you,
      Mark
      Admin for hobbysquawk.com
      VB 5.74
      PHP 8.2
      Maria DB 10.6

      Comment

      • AScherff
        Senior Member
        • Jun 2008
        • 141
        • 5.7.0

        #4
        Did you follow the instuctions in the update to 5.2.6 ?

        Rebuild Spinx Index

        Had also such error. Following was my solution (backup your server):

        Stop sphinx
        purche all files in {sphinxpath]/data/
        be shure you have the vbulletin-sphinx.php from 5.2.6 "do_not_uplad"
        edit the path in vbulletin-sphinx.php
        start sphinx

        at this point vb should work with sphinx-search with an empty index

        rebuild index

        Comment

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

          #5
          You need to use the vbulletin-sphinx.php that comes with 5.2.6. There are additional fields added to the schema to handle events.
          Translations provided by Google.

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

          Comment

          • mark.hs
            Senior Member
            • Dec 2016
            • 197
            • 5.7.0

            #6
            I must be doing something wrong.. I keep getting the INSERT error.

            Yes, I am using the vbulletin-sphinx.php from v5.2.6

            I think the issue is somehow related to the existing index. I removed all files from /usr/local/sphinx/data but I dont think that removes the existing index. I ran php searchindex.php and chose Option 1, which I thought removes the existing index. Also, if it helps troubleshoot, when I try to rebuild index within AdminCP I get a timeout after about 60 seconds (Bad Gateway Error)

            Here is the exact steps I am doing to try and get this working.
            1. Stop the Sphinx service on your server: systemctl stop searchd
            2. Replace your existing Sphinx configuration file (vbulletin-sphinx.php) with the one provided in the current version of vBulletin 5 Connect. You can find this file in the do_not_upload directory. The Config file location is /usr/local/sphinx/etc
            3. Update the file as provided in the instructions to create the vBulletin sphinx configuration file found in the sphinx installation instructions for your OS.
              • Update the first line in vbulletin-sphinx.php to match your php installation path: /usr/bin/php
              • Change $myforumroot in vbulletin-sphinx.php to the exact forum root: /var/www/vhosts/hobbysquawk.com/vbulletin
              • Set vbulletin-sphinx.php file to executable using: chmod +x /usr/local/sphinx/etc/vbulletin-sphinx.php
            1. Purge all files in /usr/local/sphinx/data
            2. Change directory to your sphinx folder using: cd /usr/local/sphinx
            1. Start the Sphinx service on your server using: searchd --config /usr/local/sphinx/etc/vbulletin-sphinx.php
            2. Copy searchindex.php to from the do_not_upload directory to the root vBulletin directory on your server.
            3. Run "php searchindex.php" from your server's command line. You will need to run this while in the forum's root directory. as a user with sufficient privileges to run Sphinx index commands.
            4. Select the correct vBulletin core directory (you can probably use the default) and choose to empty the search index; option 1 (otherwise, the schema will not be recreated from the config)
            5. When the indexing process is complete delete the searchindex.php file from your server.
            6. Run this in a SSH : indexer -c /usr/local/sphinx/etc/vbulletin-sphinx.php --rotate --all
            7. Clear Cache in Vbulletin from AdminCP
            Admin for hobbysquawk.com
            VB 5.74
            PHP 8.2
            Maria DB 10.6

            Comment

            • mark.hs
              Senior Member
              • Dec 2016
              • 197
              • 5.7.0

              #7
              RESOLVED

              I was able to get this working with the help of a fellow user (thank you AScherff !!) who has a similar install. Three things that we did to get it fixed
              1. stop the Sphinx using the command searchd --config /usr/local/sphinx/etc/vbulletin-sphinx.php --stop
              2. removing all files in the Sphinx Data directory /usr/local/sphinx/data
              3. rebuilding the index using the command php searchindex.php. Make sure to choose 0 when it asks 0 or 1

              I thought I had done all of these before, but I think #1 was the key, I dont think Sphinx was actually stopped when I initially tried to make the changes. Also for #3 above I chose "1" when running the reindex which may have been a problem, I now know I should have chosen "0". Anyways, thanks for the input and help and a special thank to A. Scheff for taking the time to help out a fellow user.

              Mark
              Last edited by mark.hs; Thu 23 Feb '17, 1:08pm.
              Admin for hobbysquawk.com
              VB 5.74
              PHP 8.2
              Maria DB 10.6

              Comment

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

                #8
                Glad it is resolved.
                Translations provided by Google.

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

                Comment

                Related Topics

                Collapse

                Working...