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

Query optimizer crashes while optimizing a join

    XMLWordPrintable

    Details

      Description

      In our production database the following statement crashes the query optimizer:

      explain select b.info1
      from
      (
      select distinct info1
      from state s1
      where s1.status='FR' and s1.timestamp>'20141101' and s1.timestamp<'20141105'
      ) b
      left outer join state s
        on s.timestamp>='20141201' and s.timestamp < '20141201230000' and s.info1 = b.info1
      where s.`status`='FR'

      This statement is a reduced one, that is the real statement is much larger, but this seems to be the part that crashes the optimizer. When we select slightly different date values, the query can be optimized with the following execution plan:

      'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra','1','PRIMARY','<derived2>','ALL',\N,\N,\N,\N,'5650','Using where','1','PRIMARY','s','ref','timestamp,iStatus,iStatusNext,info1','info1','63','b.info1','27','Using index condition; Using where','2','DERIVED','s1','range','timestamp,iStatus,iStatusNext','iStatus','312',\N,'5650','Using index condition; Using temporary',

      If we replace the left outer join by inner join, the statement works. If we drop the subselect and replace it with something static, it works, too. If we remove the "where" clause and write this condition into the "on" clause (which of course changes the statement), it works, too.

      In the log file of the server we find the following error report:

      150102 12:51:22 [ERROR] mysqld got signal 11 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
       
      To report this bug, see http://kb.askmonty.org/en/reporting-bugs
       
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed,
      something is definitely wrong and this may fail.
       
      Server version: 10.0.14-MariaDB-log
      key_buffer_size=134217728
      read_buffer_size=131072
      max_used_connections=13
      max_threads=502
      thread_count=11
      It is possible that mysqld could use up to
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1233792 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x0x7ffa59f0b008
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 0x7ffc93195d00 thread_stack 0x48000
      /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0xb6b85b]
      /usr/sbin/mysqld(handle_fatal_signal+0x398)[0x723f08]
      /lib64/libpthread.so.0[0x36d1a0f710]
      /usr/sbin/mysqld(_Z16best_access_pathP4JOINP13st_join_tableyjbdP11st_positionS4_+0x158b)[0x5ff87b]
      /usr/sbin/mysqld[0x6012a8]
      /usr/sbin/mysqld[0x601663]
      /usr/sbin/mysqld[0x60198b]
      /usr/sbin/mysqld(_Z11choose_planP4JOINy+0xf5)[0x601ef5]
      /usr/sbin/mysqld[0x52a437]
      /usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x6de)[0x61d3de]
      /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd8)[0x61fab8]
      /usr/sbin/mysqld(_Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result+0x117)[0x6205c7]
      /usr/sbin/mysqld[0x5cad47]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4c97)[0x5d58d7]
      /usr/sbin/mysqld[0x5d7402]
      /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1b20)[0x5d95c0]
      /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x453)[0x6946a3]
      /usr/sbin/mysqld(handle_one_connection+0x42)[0x694772]
      /usr/sbin/mysqld[0xa6137d]
      /lib64/libpthread.so.0[0x36d1a079d1]
      /lib64/libc.so.6(clone+0x6d)[0x36d16e89dd]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7ffa41c22020): is an invalid pointer
      Connection ID (thread ID): 25
      Status: NOT_KILLED
       
      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

      Now I hope, that this is enough information for you to find out, what is happening here. If not, please feel free to ask for anything missing.

      Best regards,

      Thomas Mischke

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              thomas.mischke Thomas Mischke
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: