Announcement

Collapse
No announcement yet.

Database error, deadlock on ipdata

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

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

  • #2
    Sphinx Search for vB
    Is the Sphinx module installed on your new server?
    Psychlinks Mental Health Support Forum
    Local Search Forum

    Comment


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


      • #4
        Best to uninstall that plugin then. That may be part of your problem.
        Psychlinks Mental Health Support Forum
        Local Search Forum

        Comment


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


          • #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 13th May '19, 2:19pm.

            Comment


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

              Comment


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


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

                  Comment


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


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

                      Thanks !

                      Comment


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

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

                        Comment


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


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