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

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.30
    • 10.0.3, 5.5.31, 5.3.13
    • None
    • 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.

      Attachments

        Issue Links

          Activity

            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.

            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.

            See also MDEV-4461

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

            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.

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

            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

              igor Igor Babaev
              martijnotto Martijn Otto
              Votes:
              3 Vote for this issue
              Watchers:
              7 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.