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

left join hangs in optimizing state, consumes all memory and leads to crash

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.5.30
    • Fix Version/s: 10.0.3, 5.5.31, 5.3.13
    • Component/s: None
    • Labels:
    • Environment:
      Linux 3.2.0-38-generic

      Description

      Create a table like this

      CREATE TABLE `foo` (
        `id` int(11) NOT NULL,
        `modified` datetime NOT NULL,
        PRIMARY KEY (`id`)
      );
      

      Then run the following query to crash the server:

      select a.* from foo a left join foo b on a.id = b.id where a.modified > b.modified or b.modified is null;
      

      The query will enter the optimizing state and start consuming memory (and swap, if applicable) until the server runs out of memory and mysqld is killed.

      Note that it does not matter whether the table is an InnoDB or MyISAM table. MySQL 5.5.29 does not exhibit this behavior.

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            elenst Elena Stepanova added a comment -

            It's a regression which came to 5.5.30 with the following revision:
            revno: 3671 [merge]
            revision-id: sergii@pisem.net-20130228214729-t0xhegqa9uzsr1k9
            parent: sergii@pisem.net-20130228191953-70sigyi7o2viwe53
            parent: sergii@pisem.net-20130228204847-vlit40812mz8af3u
            committer: Sergei Golubchik <sergii@pisem.net>
            branch nick: 5.5
            timestamp: Thu 2013-02-28 22:47:29 +0100
            message:
            5.3->5.5 merge

            I couldn't however reproduce it on 5.3 (neither on the current one nor on the version from that time), so I'm keeping it 5.5-only for now.
            Reproducible as described, it doesn't matter whether the table is empty or not.
            EXPLAIN exhibits the same behavior.

            Show
            elenst Elena Stepanova added a comment - It's a regression which came to 5.5.30 with the following revision: revno: 3671 [merge] revision-id: sergii@pisem.net-20130228214729-t0xhegqa9uzsr1k9 parent: sergii@pisem.net-20130228191953-70sigyi7o2viwe53 parent: sergii@pisem.net-20130228204847-vlit40812mz8af3u committer: Sergei Golubchik <sergii@pisem.net> branch nick: 5.5 timestamp: Thu 2013-02-28 22:47:29 +0100 message: 5.3->5.5 merge I couldn't however reproduce it on 5.3 (neither on the current one nor on the version from that time), so I'm keeping it 5.5-only for now. Reproducible as described, it doesn't matter whether the table is empty or not. EXPLAIN exhibits the same behavior.
            Hide
            elenst Elena Stepanova added a comment -

            See also MDEV-4461

            Show
            elenst Elena Stepanova added a comment - See also MDEV-4461
            Hide
            elenst Elena Stepanova added a comment -

            Fixed by the following revision:

            ------------------------------------------------------------
            revno: 3655
            revision-id: igor@askmonty.org-20130504054645-5geeszh105eh7017
            parent: igor@askmonty.org-20130504014520-zupkoo5m4b91assl
            committer: Igor Babaev <igor@askmonty.org>
            branch nick: maria-5.3-bugs
            timestamp: Fri 2013-05-03 22:46:45 -0700
            message:
            Fixed bug mdev-4336.
            When iterating over a list of conditions using List_iterator
            the function remove_eq_conds should skip all predicates that
            replace a condition from the list. Otherwise it can come to
            an infinite recursion.

            Show
            elenst Elena Stepanova added a comment - Fixed by the following revision: ------------------------------------------------------------ revno: 3655 revision-id: igor@askmonty.org-20130504054645-5geeszh105eh7017 parent: igor@askmonty.org-20130504014520-zupkoo5m4b91assl committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.3-bugs timestamp: Fri 2013-05-03 22:46:45 -0700 message: Fixed bug mdev-4336. When iterating over a list of conditions using List_iterator the function remove_eq_conds should skip all predicates that replace a condition from the list. Otherwise it can come to an infinite recursion.
            Hide
            martijnotto Martijn Otto added a comment -

            Could I get a diff of the fix so I can apply it to the current 5.5.30 series? I have been unable to find the commit in launchpad.

            Show
            martijnotto Martijn Otto added a comment - Could I get a diff of the fix so I can apply it to the current 5.5.30 series? I have been unable to find the commit in launchpad.

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                martijnotto Martijn Otto
              • Votes:
                3 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: