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

Crash When Using sort_union Optimization

    XMLWordPrintable

Details

    • 5.5.54

    Description

      Our servers running MariaDB 10.1.17 in a galera cluster experienced a crash relating to the index merge sort_union optimization. The error caused all three of ou servers crash because the query, after failing on the first server due to the crash, was passed to the next server causing it to crash. I have disable the optimization using `optimizer_switch="index_merge_sort_union=off"` since the error continues to occur without it.

      The crash occurs when the following query is run:

      SELECT SQL_NO_CACHE contact_id FROM users_assignment FORCE INDEX (user_id,company_id) WHERE user_id = '57291' OR (user_id IN(55838,55839,56998,57260,57291,60227,121997,137571,173600,219233,306480,354991,358594,376261,398572,447223,472009,646282,932548,993011,1004609,1021262,1177117,1234288,1283121,1288749,1337771,57291) AND role_id = '1194') OR company_id = '2321'
      

      I have attached the relevant log excerpts from the servers as well as some output from running mysqld in gdb and a SQL file that creates a table with data against which running the sample query above crashes.

      EXPLAIN EXTENDED of the query above:

      +------+-------------+----------------------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------+
      | id   | select_type | table                      | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                             |
      +------+-------------+----------------------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------+
      |    1 | SIMPLE      | users_assignment_brad_test | index_merge | user_id,company_id | user_id,company_id | 4,4     | NULL | 8193 |   100.00 | Using sort_union(user_id,company_id); Using where |
      +------+-------------+----------------------------+-------------+--------------------+--------------------+---------+------+------+----------+---------------------------------------------------+
      

      Attachments

        1. db1.txt
          5 kB
          Brad Jorgensen
        2. db2.txt
          1 kB
          Brad Jorgensen
        3. db3.txt
          5 kB
          Brad Jorgensen
        4. gdb.txt
          6 kB
          Brad Jorgensen
        5. index_merge_crash_table.sql
          374 kB
          Brad Jorgensen
        6. my.cnf
          4 kB
          Brad Jorgensen
        7. my2.cnf
          4 kB
          Brad Jorgensen

        Activity

          People

            varun Varun Gupta (Inactive)
            bradjorgensen Brad Jorgensen
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.