MariaDB Development
  1. MariaDB Development
  2. MDEV-4336

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

    Details

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

      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.

        Issue Links

          Activity

          Hide
          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
          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
          Elena Stepanova added a comment -

          See also MDEV-4461

          Show
          Elena Stepanova added a comment - See also MDEV-4461
          Hide
          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
          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
          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
          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 Babaev
              Reporter:
              Martijn Otto
            • Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: