Database error, deadlock on ipdata

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Wobak
    New Member
    • Sep 2018
    • 27
    • 4.2.x

    Database error, deadlock on ipdata

    Hello everyone,

    I recently migrated our vBulletin installation to a new server, and since then, I have a new error that appeared.
    The only difference is the MariaDB version that changed, but please tell me it can be adressed and that I don't have to downgrade.

    Issue description :
    Users sometimes get a "Database Error" when opening a page.
    Here are the details :
    1. vBulletin Version
      • 4.2.5
    2. PHP Version
      • 7.1.28-1+0~20190412071216.17+stretch~1.gbpbe9b36
    3. MySQL Version
      • 10.3.14-MariaDB-1:10.3.14+maria~stretch-log
    4. Any Addons installed
      • Automatic Language detection for Guests
        CES Cookie Cutter
        Change Posts Owner
        Forum Runner
        LAM - Quote Img/Video to URL
        Multiple Account Detection & Prevention
        New reCaptcha for Human Verification
        Separate Sticky and Normal Threads
        SevenSkins Image Resizer
        Skimlinks Plugin
        Sphinx Search for vB
        Tapatalk
        vbStopForumSpam
        VSa - Advanced Permissions Based on Post Count
        VSa - Login To User Account
    5. Does the issue occur in a default style?
      • Yes, style is irrelevant
    6. Error message on the screen
      • Database Error
      • On my screen, as I'm admin I have a bit more details, but I didn't copy/paste it :'(
      • The message said something about a REPLACE statement on the table ipdata that was not possible, and the reason was a deadlock on the table. If I get it again, I'll post it as an answer.
    7. Browser and Browser version used.
      • Browser-independant
    8. A list of steps that can be used to recreate the issue.
      • Not sure about that.
    Thanks for anyone who can help,
  • djbaxter
    Senior Member
    • Aug 2006
    • 1418
    • 4.2.5

    #2
    Sphinx Search for vB
    Is the Sphinx module installed on your new server?
    Psychlinks Web Services Affordable Web Design & Site Management
    Specializing in Small Businesses and vBulletin/Xenforo Forums

    Comment

    • Wobak
      New Member
      • Sep 2018
      • 27
      • 4.2.x

      #3
      Yes but I never got it to work properly. All the tutorials I could find were outdated and don't work or point to a paying version from an external company.

      So it's probably part of the modules but disabled.

      Comment

      • djbaxter
        Senior Member
        • Aug 2006
        • 1418
        • 4.2.5

        #4
        Best to uninstall that plugin then. That may be part of your problem.
        Psychlinks Web Services Affordable Web Design & Site Management
        Specializing in Small Businesses and vBulletin/Xenforo Forums

        Comment

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

          #5
          Make sure you're using the INNODB table engine for all tables except cpsession, language, phrase, session, userfield. We do not recommend using MyISAM or ARIA table engines.
          Translations provided by Google.

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

          Comment

          • Wobak
            New Member
            • Sep 2018
            • 27
            • 4.2.x

            #6
            I masked the IPs :

            Database error in vBulletin 4.2.5:

            Code:
            Invalid SQL:
            REPLACE INTO forum_ipdata
                (rectype, userid, contentid, contenttypeid, dateline, ip, altip)
            VALUES
                ('view', 13574, 121872, 2, 1556868134, '82.64.xxx.xxx', '82.64.xxx.xxx');
            
            MySQL Error   : Deadlock found when trying to get lock; try restarting transaction
            Error Number  : 1213
            Request Date  : Friday, May 3rd 2019 @ 09:22:14 AM
            Error Date    : Friday, May 3rd 2019 @ 09:22:15 AM
            Script        : http://mysuperforum/threads/123456-any-thread
            Referrer      :
            IP Address    : xx.xx.xx.xx
            Username      : Wobak
            Classname     : vB_Database_MySQLi
            MySQL Version : 10.3.14-MariaDB-1:10.3.14+maria~stretch-log
            Server        :  xx.xx.xxx.xx
            User Agent    :
            I don't see how sphinx could be related to this.

            The InnoDB, however, makes sense. Let me try to alter that specific table to see if it helps.

            Thanks,
            Last edited by Wobak; Mon 13 May '19, 2:19pm.

            Comment

            • Wobak
              New Member
              • Sep 2018
              • 27
              • 4.2.x

              #7
              OK I got all my tables except searchcore_text and the ones you mentioned in InnoDB, and I still have this error.

              Comment

              • Wobak
                New Member
                • Sep 2018
                • 27
                • 4.2.x

                #8
                OK So it seems that this happens mainly when people try to open multiple threads in a very short amount of time. I don't know if that can help ?

                Comment

                • Wobak
                  New Member
                  • Sep 2018
                  • 27
                  • 4.2.x

                  #9
                  No other ideas on what could be causing this ?

                  Comment

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

                    #10
                    No idea. vBulletin 4 doesn't use Transactions and a lock error should only happen on INNODB if two people are trying to write to the same record. This shouldn't really happen.

                    Could be an issue with MariaDB 10.3 and vBulletin 4.2.5. We aren't developing vBulletin 4 anymore and haven't tested it against newer versions of MySQL. We don't run any explicit tests against MariaDB.
                    Translations provided by Google.

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

                    Comment

                    • Wobak
                      New Member
                      • Sep 2018
                      • 27
                      • 4.2.x

                      #11
                      OK I'm going to rollback from 10.3 to 10.1 then to see if it helps.

                      Thanks !

                      Comment

                      • Wobak
                        New Member
                        • Sep 2018
                        • 27
                        • 4.2.x

                        #12
                        OK so downgrade was a no-go as it's not supported by MariaDB.

                        Instead, I opened a bug on the MariaDB JIRA, and it got related to an issue introduced in 10.3.11, fixed in 10.3.15. (I was using 10.3.14)

                        I updated my database to 10.3.15, so far so good.

                        Thanks for your help !

                        (Should I update the first post with "[SOLVED]" in a few days ?)

                        Comment


                        • Wayne Luke
                          Wayne Luke commented
                          Editing a comment
                          (Should I update the first post with "[SOLVED]" in a few days ?)
                          If you wish. I am glad it is working for you.
                      • Wobak
                        New Member
                        • Sep 2018
                        • 27
                        • 4.2.x

                        #13
                        Well... I stand corrected, the issue is still there.

                        Comment

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

                          #14
                          You're running the database in logging mode. Is there any information about this issue in the database's log?
                          Translations provided by Google.

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

                          Comment

                          • Wobak
                            New Member
                            • Sep 2018
                            • 27
                            • 4.2.x

                            #15
                            It seems to be linked to locks due to replication. I'll follow up when the bug is updated on the MariaDB JIRA.

                            Comment

                            Related Topics

                            Collapse

                            Working...