Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24163

Mariadb is restarting after sending this message [Warning] Aborted connection 5889 to db: 'xxxx' user: 'xxxxx' host:

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.3.26, 10.5.7
    • N/A
    • Optimizer
    • PhpBB 3.3 interacting with mariadb

    Description

      After updating to either version from 10.5.x and 10.3.25 respectively, two different users of phpBB board started getting errors and journal registered mariadb restarting with the following message:

      [Warning] Aborted connection 5889 to db: 'xxxx' user: 'xxxxx' host:

      The backtrace in phpBB is:

       
      		 
      SQL ERROR [ mysqli ]
      		 
       
      		 
      MySQL server has gone away [2006]
      		 
       
      		 
      SQL
      		 
       
      		 
      DELETE FROM phpbb_notification_emails WHERE notification_type_id IN (40, 41) AND user_id = '4' AND item_id IN (1156688, 1156686, 1156682, 1156669, 1156668, 1156667, 1156660, 1156659, 1156654, 1156627, 1156624, 1156605, 1156592, 1156590, 1156581)
      		 
       
      		 
      BACKTRACE
      		 
       
      		 
      FILE: (not given by php)
      		 
      LINE: (not given by php)
      		 
      CALL: msg_handler()
      		 
       
      		 
      FILE: [ROOT]/phpbb/db/driver/driver.php
      		 
      LINE: 1023
      		 
      CALL: trigger_error()
      		 
       
      		 
      FILE: [ROOT]/phpbb/db/driver/mysqli.php
      		 
      LINE: 195
      		 
      CALL: phpbb\db\driver\driver->sql_error()
      		 
       
      		 
      FILE: [ROOT]/phpbb/db/driver/factory.php
      		 
      LINE: 345
      		 
      CALL: phpbb\db\driver\mysqli->sql_query()
      		 
       
      		 
      FILE: [ROOT]/phpbb/notification/method/email.php
      		 
      LINE: 132
      		 
      CALL: phpbb\db\driver\factory->sql_query()
      		 
       
      		 
      FILE: [ROOT]/phpbb/notification/manager.php
      		 
      LINE: 177
      		 
      CALL: phpbb\notification\method\email->mark_notifications()
      		 
       
      		 
      FILE: [ROOT]/phpbb/notification/manager.php
      		 
      LINE: 147
      		 
      CALL: phpbb\notification\manager->mark_notifications()
      		 
       
      		 
      FILE: [ROOT]/ext/gfksx/thanksforposts/core/helper.php
      		 
      LINE: 862
      		 
      CALL: phpbb\notification\manager->mark_notifications_read()
      		 
       
      		 
      FILE: [ROOT]/ext/gfksx/thanksforposts/event/listener.php
      		 
      LINE: 281
      		 
      CALL: gfksx\thanksforposts\core\helper->notification_markread()
      		 
       
      		 
      FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
      		 
      LINE: 214
      		 
      CALL: gfksx\thanksforposts\event\listener->markread()
      		 
       
      		 
      FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
      		 
      LINE: 44
      		 
      CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()
      		 
       
      		 
      FILE: [ROOT]/phpbb/event/dispatcher.php
      		 
      LINE: 62
      		 
      CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()
      		 
       
      		 
      FILE: [ROOT]/phpbb/event/dispatcher.php
      		 
      LINE: 46
      		 
      CALL: phpbb\event\dispatcher->dispatch()
      		 
       
      		 
      FILE: [ROOT]/viewtopic.php
      		 
      LINE: 2417
      		 
      CALL: phpbb\event\dispatcher->trigger_event()

      This didn’t happened in previous versions of mariadb. I decided to keep 10.5.7 with email disabled and the other user downgraded to mariadb 10.3.25.

      This is the thread where we discussed it https://www.phpbb.com/community/viewtopic.php?f=661&t=2573166

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24117 Memory management problem in statistics state for queries that have a large field IN (values) part

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            danblack Daniel Black added a comment -

            lbartolome thanks for the bug report. I'm not sure why you marked MDEV-24155, do you have an MariaDB error log that looks to be the same?

            On the summary you've provided it possibly more likely to be MDEV-24121, especially if your PHP version < 7.3. An out of cycle fix is expected for this soon.

            danblack Daniel Black added a comment - lbartolome thanks for the bug report. I'm not sure why you marked MDEV-24155 , do you have an MariaDB error log that looks to be the same? On the summary you've provided it possibly more likely to be MDEV-24121 , especially if your PHP version < 7.3. An out of cycle fix is expected for this soon.
            lbartolome Lucía added a comment -

            Hi @daniel black, this error happened the day before the other error, so I wasn't sure whether this one helped triggered the other one later.

            By the way, I'm using php 7.4.

            lbartolome Lucía added a comment - Hi @daniel black, this error happened the day before the other error, so I wasn't sure whether this one helped triggered the other one later. By the way, I'm using php 7.4.
            stu_m stuart marritt added a comment -

            Hi

            The above report is a copy and a link to my post on phpBB

            I can confirm that the php version was 7.4

            This issue was causing a huge memory spike on my server and crashing the database!

            Oddly turning off board wide emails solved the issue but that's not the solution

            stu_m stuart marritt added a comment - Hi The above report is a copy and a link to my post on phpBB I can confirm that the php version was 7.4 This issue was causing a huge memory spike on my server and crashing the database! Oddly turning off board wide emails solved the issue but that's not the solution
            danblack Daniel Black added a comment -

            Thanks for confirming the PHP version which rules out MDEV-24121 as the cause. The large memory spike and crash rules out MDEV-24155.

            The query, memory exhaustion and the revert fixing it would likely indicate MDEV-24117. And out of band release including a fix for this is expected very shortly (days).

            If you could confirm after the release that this corrects the problem that would be appreciated.

            It it doesn't, can you include:

            request output from

            		 
            SHOW CREATE TABLE  phpbb_notification_emails;
            		 
            EXPLAIN DELETE FROM phpbb_notification_emails WHERE notification_type_id = 26 .. AND user_id = '4' AND item_parent_id IN ('80', '136', '213', ...);
            		 
            (the query that crashed, doesn't have to be the full item_parent_id list but include at least 30 or so).

            Aside if https://github.com/phpbb/phpbb/blob/master/phpBB/phpbb/db/migration/data/v33x/add_notification_emails_table.php#L41 is the structure, the PK order should be [notification_type_id , user_id, item_parent_id, item_id] at least for this query.

            A DELETE ... RETURNING syntax would probably make the retrieval and delete in the same step work well in phpbb but its hard with multiple DB vendors of different capabilities and coding to the highest common denominator is needed.

            I sincerely hope the forth coming update to MDEV-24117 corrects the problem for you.

            danblack Daniel Black added a comment - Thanks for confirming the PHP version which rules out MDEV-24121 as the cause. The large memory spike and crash rules out MDEV-24155 . The query, memory exhaustion and the revert fixing it would likely indicate MDEV-24117 . And out of band release including a fix for this is expected very shortly (days). If you could confirm after the release that this corrects the problem that would be appreciated. It it doesn't, can you include: request output from SHOW CREATE TABLE phpbb_notification_emails; EXPLAIN DELETE FROM phpbb_notification_emails WHERE notification_type_id = 26 .. AND user_id = '4' AND item_parent_id IN ('80', '136', '213', ...); (the query that crashed, doesn't have to be the full item_parent_id list but include at least 30 or so). Aside if https://github.com/phpbb/phpbb/blob/master/phpBB/phpbb/db/migration/data/v33x/add_notification_emails_table.php#L41 is the structure, the PK order should be [notification_type_id , user_id, item_parent_id, item_id] at least for this query. A DELETE ... RETURNING syntax would probably make the retrieval and delete in the same step work well in phpbb but its hard with multiple DB vendors of different capabilities and coding to the highest common denominator is needed. I sincerely hope the forth coming update to MDEV-24117 corrects the problem for you.
            danblack Daniel Black added a comment - - edited

            Well, https://mariadb.org/mariadb-10-5-8-10-4-17-10-3-27-and-10-2-36-now-available/ is out.

            This corrects the memory usage of processing of long lists described in MDEV-24117. Please leave a comment if you still have a problem after upgrading to one of these versions.

            danblack Daniel Black added a comment - - edited Well, https://mariadb.org/mariadb-10-5-8-10-4-17-10-3-27-and-10-2-36-now-available/ is out. This corrects the memory usage of processing of long lists described in MDEV-24117 . Please leave a comment if you still have a problem after upgrading to one of these versions.
            cciccia Charlie Ciccia added a comment - - edited

            exact issue reported above (with the same table and all) is occurring on 10.5.9 (crash output attached). I am using the mariadb:10.5 docker image.

            I realize the IN clause is about 6500 elements along, and could probably work around this exact case by writing an extension to change how this query works in phpbb, but I worry about how many other landmines I haven't found yet.

            My phpbb_* tables are all using InnoDB engine.

            even attempting to EXPLAIN this query at the cli is also causing the crash. mariadb_crash.txt

            cciccia Charlie Ciccia added a comment - - edited exact issue reported above (with the same table and all) is occurring on 10.5.9 (crash output attached). I am using the mariadb:10.5 docker image. I realize the IN clause is about 6500 elements along, and could probably work around this exact case by writing an extension to change how this query works in phpbb, but I worry about how many other landmines I haven't found yet. My phpbb_* tables are all using InnoDB engine. even attempting to EXPLAIN this query at the cli is also causing the crash. mariadb_crash.txt
            cciccia Charlie Ciccia added a comment -

            Daniel, the fix described in https://www.phpbb.com/community/viewtopic.php?p=15679676#p15679676 worked for me. Thank you.

            cciccia Charlie Ciccia added a comment - Daniel, the fix described in https://www.phpbb.com/community/viewtopic.php?p=15679676#p15679676 worked for me. Thank you.
            danblack Daniel Black added a comment -

            Great. Glad you saw my notice there.

            https://www.phpbb.com/community/viewtopic.php?p=15624396#p15624396 might offer some tips how to write it better from my loose reading of the codebase.

            If you need a hand with suggested SQL feel free to ask on https://mariadb.zulipchat.com, https://dba.stackexchange.com/ or even https://stackoverflow.com.

            What MariaDB does need to process these ugly queries without crashing thanks for looking at better ways of doing this. I suspect looking at the core code implementation that generates this list and hows it used could show very portable ways of improving this.

            danblack Daniel Black added a comment - Great. Glad you saw my notice there. https://www.phpbb.com/community/viewtopic.php?p=15624396#p15624396 might offer some tips how to write it better from my loose reading of the codebase. If you need a hand with suggested SQL feel free to ask on https://mariadb.zulipchat.com , https://dba.stackexchange.com/ or even https://stackoverflow.com . What MariaDB does need to process these ugly queries without crashing thanks for looking at better ways of doing this. I suspect looking at the core code implementation that generates this list and hows it used could show very portable ways of improving this.

            People

              danblack Daniel Black
              lbartolome Lucía
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.