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

MariaDB crash with big_tables=1 and CTE

    XMLWordPrintable

    Details

      Description

      SET big_tables=1;
       
      CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int);
      INSERT INTO t1 VALUES
        (1, "A", 2, 3),(2, "LA", 4, 5),(4, "LLA", 6, 7),(6, "LLLA", NULL, NULL),(7, "RLLA", NULL, NULL),(5, "RLA", 8, 9),(8, "LRLA", NULL, NULL),
        (9, "RRLA", NULL, NULL),(3, "RA", 10, 11),(10, "LRA", 12, 13),(11, "RRA", 14, 15),(15, "RRRA", NULL, NULL),(16, "B", 17, 18),
        (17, "LB", NULL, NULL),(18, "RB", NULL, NULL);
       
      CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand();
       
      WITH RECURSIVE tree_of_a AS
        (SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
          UNION ALL
        SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar
          UNION ALL
        SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar)
      SELECT * FROM tree_of_a
      ORDER BY path;
       
      DROP TABLE t1,t2;
      

      180313 13:31:55 [ERROR] mysqld got signal 11 ;
      Server version: 10.2.14-MariaDB-debug-log
      key_buffer_size=1048576
      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 = 63177 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x7fb46c000a98
      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 = 0x7fb47c8c9e70 thread_stack 0x49000
      /home/alice/git/10.2/sql/mysqld(my_print_stacktrace+0x38)[0x56289c7f614a]
      /home/alice/git/10.2/sql/mysqld(handle_fatal_signal+0x3dc)[0x56289c08863d]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fb48323e390]
      sql/sql_select.cc:17585(create_internal_tmp_table(TABLE*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long))[0x56289be84fa2]
      sql/sql_select.cc:18345(instantiate_tmp_table(TABLE*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long))[0x56289be86e8c]
      sql/sql_cte.cc:1402(With_element::instantiate_tmp_tables())[0x56289bfe58cf]
      sql/sql_derived.cc:1023(TABLE_LIST::fill_recursive(THD*))[0x56289bde961f]
      sql/sql_derived.cc:1097(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x56289bde98e2]
      sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x56289bde7922]
      sql/sql_select.cc:11941(st_join_table::preread_init())[0x56289be771fb]
      sql/sql_select.cc:18659(sub_select(JOIN*, st_join_table*, bool))[0x56289be87370]
      sql/sql_select.cc:18235(do_select(JOIN*, Procedure*))[0x56289be86aa2]
      sql/sql_select.cc:3566(JOIN::exec_inner())[0x56289be6150f]
      sql/sql_select.cc:3362(JOIN::exec())[0x56289be609b0]
      sql/sql_select.cc:3763(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56289be61b80]
      sql/sql_select.cc:376(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56289be56161]
      sql/sql_parse.cc:6456(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56289be220e0]
      sql/sql_parse.cc:3467(mysql_execute_command(THD*))[0x56289be17e5e]
      sql/sql_parse.cc:7902(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56289be25acc]
      sql/sql_parse.cc:1808(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56289be136fb]
      sql/sql_parse.cc:1360(do_command(THD*))[0x56289be12057]
      sql/sql_connect.cc:1335(do_handle_one_connection(CONNECT*))[0x56289bf607d5]
      sql/sql_connect.cc:1242(handle_one_connection)[0x56289bf60555]
      perfschema/pfs.cc:1864(pfs_spawn_thread)[0x56289c2bdfd8]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fb4832346ba]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fb4826c941d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7fb46c0d6010): with recursive tree_of_a as ( select *, cast(id as char(200)) as path from t2 where name="A" union all select t2.*, concat(tree_of_a.path,",",t2.id) from t2 join tree_of_a on t2.id=tree_of_a.leftpar union all select t2.*, concat(tree_of_a.path,",",t2.id) from t2 join tree_of_a on t2.id=tree_of_a.rightpar ) select * from tree_of_a order by path
      Connection ID (thread ID): 4
      Status: NOT_KILLED
      
      

       
      Program terminated with signal SIGSEGV, Segmentation fault.
      #0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
      62	../sysdeps/unix/sysv/linux/pthread_kill.c: No such file or directory.
      [Current thread is 1 (Thread 0x7fb47c8ca700 (LWP 3045))]
      #0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
      #1  0x000056289c7f623c in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477
      #2  0x000056289c088a65 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305
      #3  <signal handler called>
      #4  0x000056289be84fa2 in create_internal_tmp_table (table=0x7fb46c0cc130, keyinfo=0x7fb46c0cd240, start_recinfo=0x7fb46c0cd308, recinfo=0x7fb46c0e3e00, options=0) at /home/alice/git/10.2/sql/sql_select.cc:17585
      #5  0x000056289be86e8c in instantiate_tmp_table (table=0x7fb46c0cc130, keyinfo=0x7fb46c0cd240, start_recinfo=0x7fb46c0cd308, recinfo=0x7fb46c0e3e00, options=0) at /home/alice/git/10.2/sql/sql_select.cc:18345
      #6  0x000056289bfe58cf in With_element::instantiate_tmp_tables (this=0x7fb46c05f3b0) at /home/alice/git/10.2/sql/sql_cte.cc:1403
      #7  0x000056289bde961f in TABLE_LIST::fill_recursive (this=0x7fb46c0d5650, thd=0x7fb46c000a98) at /home/alice/git/10.2/sql/sql_derived.cc:1023
      #8  0x000056289bde98e2 in mysql_derived_fill (thd=0x7fb46c000a98, lex=0x7fb46c004578, derived=0x7fb46c0d5650) at /home/alice/git/10.2/sql/sql_derived.cc:1097
      #9  0x000056289bde7922 in mysql_handle_single_derived (lex=0x7fb46c004578, derived=0x7fb46c0d5650, phases=96) at /home/alice/git/10.2/sql/sql_derived.cc:197
      #10 0x000056289be771fb in st_join_table::preread_init (this=0x7fb46c1ba510) at /home/alice/git/10.2/sql/sql_select.cc:11942
      #11 0x000056289be87370 in sub_select (join=0x7fb46c1cfbe0, join_tab=0x7fb46c1ba510, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18659
      #12 0x000056289be86aa2 in do_select (join=0x7fb46c1cfbe0, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18235
      #13 0x000056289be6150f in JOIN::exec_inner (this=0x7fb46c1cfbe0) at /home/alice/git/10.2/sql/sql_select.cc:3566
      #14 0x000056289be609b0 in JOIN::exec (this=0x7fb46c1cfbe0) at /home/alice/git/10.2/sql/sql_select.cc:3361
      #15 0x000056289be61b80 in mysql_select (thd=0x7fb46c000a98, tables=0x7fb46c0d5650, wild_num=1, fields=..., conds=0x0, og_num=1, order=0x7fb46c057ff0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb46c0350d0, unit=0x7fb46c004640, select_lex=0x7fb46c004d98) at /home/alice/git/10.2/sql/sql_select.cc:3761
      #16 0x000056289be56161 in handle_select (thd=0x7fb46c000a98, lex=0x7fb46c004578, result=0x7fb46c0350d0, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376
      #17 0x000056289be220e0 in execute_sqlcom_select (thd=0x7fb46c000a98, all_tables=0x7fb46c0d5650) at /home/alice/git/10.2/sql/sql_parse.cc:6456
      #18 0x000056289be17e5e in mysql_execute_command (thd=0x7fb46c000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3467
      #19 0x000056289be25acc in mysql_parse (thd=0x7fb46c000a98, rawbuf=0x7fb46c0d6010 "with recursive tree_of_a as\n(\nselect *, cast(id as char(200)) as path from t2 where name=\"A\"\nunion all\nselect t2.*, concat(tree_of_a.path,\",\",t2.id) from t2 join tree_of_a on\nt2.id=tree_of_a.leftpar\nu"..., length=345, parser_state=0x7fb47c8c91f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7902
      #20 0x000056289be136fb in dispatch_command (command=COM_QUERY, thd=0x7fb46c000a98, packet=0x7fb46c007fe9 "", packet_length=345, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1806
      #21 0x000056289be12057 in do_command (thd=0x7fb46c000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
      #22 0x000056289bf607d5 in do_handle_one_connection (connect=0x56289f993c18) at /home/alice/git/10.2/sql/sql_connect.cc:1335
      #23 0x000056289bf60555 in handle_one_connection (arg=0x56289f993c18) at /home/alice/git/10.2/sql/sql_connect.cc:1241
      #24 0x000056289c2bdfd8 in pfs_spawn_thread (arg=0x56289f8f3838) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
      #25 0x00007fb4832346ba in start_thread (arg=0x7fb47c8ca700) at pthread_create.c:333
      #26 0x00007fb4826c941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: