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

Server crashes when joining a subselect with 32 tables and GROUP BY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3, 10.4, 10.3.15, 10.4.12, 10.5
    • Fix Version/s: 10.3.23, 10.4.13, 10.5.3
    • Component/s: N/A
    • Labels:
      None
    • Environment:
      $ uname -a
      Linux lchabowski 4.15.0-88-generic #88-Ubuntu SMP Tue Feb 11 20:11:34 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

      Description

      The following SQL queries crashes the mysqld binary:

      CREATE DATABASE crashtest;
       
      USE crashtest;
       
      CREATE TABLE dummy (id INT NOT NULL PRIMARY KEY);
       
      SELECT 1
      FROM dummy t
      LEFT JOIN (
          SELECT dummy0.id
          FROM dummy AS dummy0
          LEFT JOIN dummy AS dummy1 ON 0
          LEFT JOIN dummy AS dummy2 ON 0
          LEFT JOIN dummy AS dummy3 ON 0
          LEFT JOIN dummy AS dummy4 ON 0
          LEFT JOIN dummy AS dummy5 ON 0
          LEFT JOIN dummy AS dummy6 ON 0
          LEFT JOIN dummy AS dummy7 ON 0
          LEFT JOIN dummy AS dummy8 ON 0
          LEFT JOIN dummy AS dummy9 ON 0
          LEFT JOIN dummy AS dummy10 ON 0
          LEFT JOIN dummy AS dummy11 ON 0
          LEFT JOIN dummy AS dummy12 ON 0
          LEFT JOIN dummy AS dummy13 ON 0
          LEFT JOIN dummy AS dummy14 ON 0
          LEFT JOIN dummy AS dummy15 ON 0
          LEFT JOIN dummy AS dummy16 ON 0
          LEFT JOIN dummy AS dummy17 ON 0
          LEFT JOIN dummy AS dummy18 ON 0
          LEFT JOIN dummy AS dummy19 ON 0
          LEFT JOIN dummy AS dummy20 ON 0
          LEFT JOIN dummy AS dummy21 ON 0
          LEFT JOIN dummy AS dummy22 ON 0
          LEFT JOIN dummy AS dummy23 ON 0
          LEFT JOIN dummy AS dummy24 ON 0
          LEFT JOIN dummy AS dummy25 ON 0
          LEFT JOIN dummy AS dummy26 ON 0
          LEFT JOIN dummy AS dummy27 ON 0
          LEFT JOIN dummy AS dummy28 ON 0
          LEFT JOIN dummy AS dummy29 ON 0
          LEFT JOIN dummy AS dummy30 ON 0
          LEFT JOIN dummy AS dummy31 ON 0
          GROUP BY dummy0.id
      ) AS subquery
          ON subquery.id = t.id;
      

      Removing the GROUP BY or a LEFT JOIN and the query executes sucessfully.

      Tested on 10.3.15, 10.4.12 and on the HEAD of 10.5 (was 23685378bafa38a74957e8f07e2d56b57e90fa53 at the time).

      Here is the output of the mysqld process:

      $ ./sql/mysqld 
      2020-03-06 12:52:10 0 [Note] ./sql/mysqld (mysqld 10.5.2-MariaDB-debug) starting as process 11578 ...
      2020-03-06 12:52:10 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
      2020-03-06 12:52:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2020-03-06 12:52:10 0 [Note] InnoDB: Uses event mutexes
      2020-03-06 12:52:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2020-03-06 12:52:10 0 [Note] InnoDB: Number of pools: 1
      2020-03-06 12:52:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
      2020-03-06 12:52:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
      2020-03-06 12:52:10 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
      2020-03-06 12:52:10 0 [Note] InnoDB: Completed initialization of buffer pool
      2020-03-06 12:52:11 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
      2020-03-06 12:52:11 0 [Note] InnoDB: 128 rollback segments are active.
      2020-03-06 12:52:11 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2020-03-06 12:52:11 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2020-03-06 12:52:11 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2020-03-06 12:52:11 0 [Note] InnoDB: 10.5.2 started; log sequence number 45617; transaction id 21
      2020-03-06 12:52:11 0 [Note] InnoDB: Loading buffer pool(s) from /home/lchabowski/mariadb-dev/dbdata/ib_buffer_pool
      2020-03-06 12:52:11 0 [Note] Plugin 'FEEDBACK' is disabled.
      2020-03-06 12:52:11 0 [Note] InnoDB: Buffer pool(s) load completed at 200306 12:52:11
      2020-03-06 12:52:11 0 [Note] Server socket created on IP: '::'.
      2020-03-06 12:52:11 0 [Note] Reading of all Master_info entries succeeded
      2020-03-06 12:52:11 0 [Note] Added new Master_info '' to hash table
      2020-03-06 12:52:11 0 [Note] ./sql/mysqld: ready for connections.
      Version: '10.5.2-MariaDB-debug'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution
      mysqld: /home/lchabowski/src/mariadb-server/sql/sql_select.cc:8679: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570814527423731704357e+308L)' failed.
      200306 12:52:31 [ERROR] mysqld got signal 6 ;
      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 https://mariadb.com/kb/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.5.2-MariaDB-debug
      key_buffer_size=134217728
      read_buffer_size=131072
      max_used_connections=1
      max_threads=153
      thread_count=2
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467885 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x7fb30c000d60
      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 = 0x7fb35c117e50 thread_stack 0x49000
      addr2line: './sql/mysqld': No such file
      ./sql/mysqld(my_print_stacktrace+0x4f)[0x555e0fb0393e]
      Printing to addr2line failed
      ./sql/mysqld(handle_fatal_signal+0x3bd)[0x555e0f1b453c]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb365d25890]
      /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7fb364e1de97]
      /lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7fb364e1f801]
      /lib/x86_64-linux-gnu/libc.so.6(+0x3039a)[0x7fb364e0f39a]
      /lib/x86_64-linux-gnu/libc.so.6(+0x30412)[0x7fb364e0f412]
      addr2line: './sql/mysqld': No such file
      ./sql/mysqld(+0x8f33f7)[0x555e0eec03f7]
      ./sql/mysqld(_Z11choose_planP4JOINy+0x2e5)[0x555e0eebf554]
      ./sql/mysqld(_ZN13st_join_table21choose_best_splittingEdy+0x482)[0x555e0f0de706]
      ./sql/mysqld(_Z16best_access_pathP4JOINP13st_join_tableyPK11st_positionjbdPS3_S6_+0x27b)[0x555e0eebbbb4]
      ./sql/mysqld(+0x8f5404)[0x555e0eec2404]
      ./sql/mysqld(+0x8f5b58)[0x555e0eec2b58]
      ./sql/mysqld(+0x8f334e)[0x555e0eec034e]
      ./sql/mysqld(_Z11choose_planP4JOINy+0x2e5)[0x555e0eebf554]
      ./sql/mysqld(+0x8e9d02)[0x555e0eeb6d02]
      ./sql/mysqld(_ZN4JOIN14optimize_innerEv+0x1de3)[0x555e0eeaa87b]
      ./sql/mysqld(_ZN4JOIN8optimizeEv+0xc6)[0x555e0eea8126]
      ./sql/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x430)[0x555e0eeb38e5]
      ./sql/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x19d)[0x555e0eea2ee7]
      ./sql/mysqld(+0x899695)[0x555e0ee66695]
      ./sql/mysqld(_Z21mysql_execute_commandP3THD+0x1faa)[0x555e0ee5d465]
      ./sql/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2ed)[0x555e0ee6b7a0]
      ./sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x11fe)[0x555e0ee570f0]
      ./sql/mysqld(_Z10do_commandP3THD+0x7a5)[0x555e0ee557dc]
      ./sql/mysqld(_Z24do_handle_one_connectionP7CONNECTb+0x1e0)[0x555e0efffded]
      ./sql/mysqld(handle_one_connection+0x5b)[0x555e0efffb0d]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fb365d1a6db]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb364f0088f]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7fb30c014888): SELECT 1 FROM dummy t LEFT JOIN (     SELECT dummy0.id     FROM dummy AS dummy0     LEFT JOIN dummy AS dummy1 ON 0     LEFT JOIN dummy AS dummy2 ON 0     LEFT JOIN dummy AS dummy3 ON 0     LEFT JOIN dummy AS dummy4 ON 0     LEFT JOIN dummy AS dummy5 ON 0     LEFT JOIN dummy AS dummy6 ON 0     LEFT JOIN dummy AS dummy7 ON 0     LEFT JOIN dummy AS dummy8 ON 0     LEFT JOIN dummy AS dummy9 ON 0     LEFT JOIN dummy AS dummy10 ON 0     LEFT JOIN dummy AS dummy11 ON 0     LEFT JOIN dummy AS dummy12 ON 0     LEFT JOIN dummy AS dummy13 ON 0     LEFT JOIN dummy AS dummy14 ON 0     LEFT JOIN dummy AS dummy15 ON 0     LEFT JOIN dummy AS dummy16 ON 0     LEFT JOIN dummy AS dummy17 ON 0     LEFT JOIN dummy AS dummy18 ON 0     LEFT JOIN dummy AS dummy19 ON 0     LEFT JOIN dummy AS dummy20 ON 0     LEFT JOIN dummy AS dummy21 ON 0     LEFT JOIN dummy AS dummy22 ON 0     LEFT JOIN dummy AS dummy23 ON 0     LEFT JOIN dummy AS dummy24 ON 0     LEFT JOIN dummy AS dummy25 ON 0     LEFT JOIN dummy AS dummy26 ON 0     LEFT JOIN dummy AS dummy27 ON 0     LEFT JOIN dummy AS dummy28 ON 0     LEFT JOIN dummy AS dummy29 ON 0     LEFT JOIN dummy AS dummy30 ON 0     LEFT JOIN dummy AS dummy31 ON 0     GROUP BY dummy0.id ) AS subquery     ON subquery.id = t.id
      Connection ID (thread ID): 3
      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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
       
      The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      information that should help you find out what is causing the crash.
      

      Originally the query was more complex (there are NDAs in place, cant share it, sorry) but I reduced it to the above query. Although the original case crashed with a SIGSEGV and not with a failed assertion:

      200306 13:11:51 [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 https://mariadb.com/kb/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.3.15-MariaDB-1:10.3.15+maria~bionic
      key_buffer_size=134217728
      read_buffer_size=2097152
      max_used_connections=4
      max_threads=10002
      thread_count=9
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 61808382 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x7fb020000c08
      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 = 0x7fb08062fdd8 thread_stack 0x49000
      mysqld(my_print_stacktrace+0x2e)[0x559f00b0606e]
      mysqld(handle_fatal_signal+0x515)[0x559f005a26d5]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb09bb4e890]
      mysqld(_Z45fix_semijoin_strategies_for_picked_join_orderP4JOIN+0x88)[0x559f004d7198]
      mysqld(_ZN4JOIN20get_best_combinationEv+0xba)[0x559f003f8fca]
      mysqld(_ZN4JOIN15optimize_stage2Ev+0x143)[0x559f00416553]
      mysqld(_ZN4JOIN8optimizeEv+0x75)[0x559f0041b0c5]
      mysqld(_Z22mysql_derived_optimizeP3THDP3LEXP10TABLE_LIST+0xef)[0x559f0038f8ff]
      mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xb1)[0x559f0038f1b1]
      mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x559f003aa2f7]
      mysqld(_ZN4JOIN15optimize_stage2Ev+0x16c)[0x559f0041657c]
      mysqld(_ZN4JOIN14optimize_innerEv+0xe69)[0x559f00419619]
      mysqld(_ZN4JOIN8optimizeEv+0x37)[0x559f0041b087]
      mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x886)[0x559f0041ce86]
      mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14d)[0x559f0041d0bd]
      mysqld(+0x5651d1)[0x559f003be1d1]
      mysqld(_Z21mysql_execute_commandP3THD+0x6015)[0x559f003caac5]
      mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x20a)[0x559f003cd42a]
      mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1cd7)[0x559f003cfe57]
      mysqld(_Z10do_commandP3THD+0x178)[0x559f003d0bf8]
      mysqld(_Z24do_handle_one_connectionP7CONNECT+0x212)[0x559f004a1532]
      mysqld(handle_one_connection+0x3d)[0x559f004a170d]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fb09bb436db]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb09b14588f]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7fb02000f5d0): [[ redacted ]]
      Connection ID (thread ID): 84
      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,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
       
      The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      information that should help you find out what is causing the crash.
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              laria Laria Chabowski
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: