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

Server crashes while executing query with CTE in PS/SP

Details

    Description

      Running a quite long CTE [over 170 lines] in a procedure suddenly crashes the server. It looks to me that it started with 10.5.11 as before i worked perfectly. Below the dump. For the moment I don't want to publish the CTE but I can do it privately.

      Running the CTE command NOT in a stored procedure works so I have for the moment a work-around.

      {{Server version: 10.5.11-MariaDB
      key_buffer_size=134217728
      read_buffer_size=131072
      max_used_connections=2
      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 = 467873 K bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.

      Thread pointer: 0x7fdf0c0009b8
      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 = 0x7fdf68117c90 thread_stack 0x49000
      ??:0(my_print_stacktrace)[0x559fcba7579e]
      ??:0(handle_fatal_signal)[0x559fcb47a457]
      sigaction.c:0(__restore_rt)[0x7fdf81fa4630]
      ??:0(Item_cond_and::mark_as_condition_AND_part(TABLE_LIST*))[0x559fcb4b2d96]
      ??:0(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x559fcb20de29]
      ??:0(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x559fcb2bedcf]
      ??:0(st_select_lex_unit::prepare_join(THD*, st_select_lex*, select_result*, unsigned long long, bool))[0x559fcb31b7ed]
      ??:0(st_select_lex_unit::prepare(TABLE_LIST*, select_result*, unsigned long long))[0x559fcb31fda6]
      ??:0(TABLE_LIST::find_derived_handler(THD*))[0x559fcb2335b2]
      ??:0(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x559fcb2325ad]
      ??:0(st_select_lex::handle_derived(LEX*, unsigned int))[0x559fcb24d3d7]
      ??:0(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x559fcb335f06]
      ??:0(st_select_lex::handle_derived(LEX*, unsigned int))[0x559fcb24d3d7]
      ??:0(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x559fcb335f06]
      ??:0(mysql_prepare_insert(THD*, TABLE_LIST*, List<Item>&, List<Item>, List<Item>&, List<Item>&, enum_duplicates, Item*, bool))[0x559fcb239bd2]
      ??:0(mysql_insert_select_prepare(THD*, select_result*))[0x559fcb23e216]
      ??:0(mysql_execute_command(THD*))[0x559fcb278900]
      ??:0(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x559fcb1ce506]
      ??:0(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x559fcb1d6fe2]
      ??:0(sp_instr_stmt::execute(THD*, unsigned int*))[0x559fcb1d79dc]
      ??:0(sp_head::execute(THD*, bool))[0x559fcb1d2524]
      ??:0(sp_head::execute_procedure(THD*, List<Item>*))[0x559fcb1d3c1f]
      ??:0(comp_ne_creator(bool))[0x559fcb26aaca]
      ??:0(Sql_cmd_call::execute(THD*))[0x559fcb26f48a]
      ??:0(mysql_execute_command(THD*))[0x559fcb2772be]
      ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x559fcb27b1c5]
      ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x559fcb27d891]
      ??:0(do_command(THD*))[0x559fcb27f03f]
      ??:0(do_handle_one_connection(CONNECT*, bool))[0x559fcb369b52]
      ??:0(handle_one_connection)[0x559fcb369e14]
      ??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x559fcb6c756d]
      pthread_create.c:0(start_thread)[0x7fdf81f9cea5]
      ??:0(__clone)[0x7fdf814b79fd]
      }}

      {{Connection ID (thread ID): 10
      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}}

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment - - edited

            The following simple test demonstrate the problem:

            create table t1 (a int, b int);
            insert into t1 value (1,3), (3,2), (1,3), (4,1);
             
            prepare stmt from "
            with
              cte1 as ( select a,b from t1 where a = 1 AND b = 3 ),
              cte2 as ( select a,b from cte1 ),
              cte3 as ( select a,b from cte2 )
            select * from cte3, cte2; ";
             
            execute stmt;
            execute stmt;
            deallocate prepare stmt;
            

            Execution of this test case causes a crash of the server.
            The crash happens when running the first 'execute stmt;'.

            igor Igor Babaev added a comment - - edited The following simple test demonstrate the problem: create table t1 (a int , b int ); insert into t1 value (1,3), (3,2), (1,3), (4,1);   prepare stmt from " with cte1 as ( select a,b from t1 where a = 1 AND b = 3 ), cte2 as ( select a,b from cte1 ), cte3 as ( select a,b from cte2 ) select * from cte3, cte2; " ;   execute stmt; execute stmt; deallocate prepare stmt; Execution of this test case causes a crash of the server. The crash happens when running the first 'execute stmt;'.
            igor Igor Babaev added a comment -

            An analysis shows that the SELECT created for the second usage of cte1 via the usage of cte2 is not included in all_select_list for the query. It happens for the same reason as the similar problem of the bug MDEV-26108 a fix for which was applied to 10.4. However if we apply this patch to 10.2 the above test case still crashes the server though now on the second execution of PS. So the fix for MDEV-26108 was not quite correct. A correct fix would be like this:

             diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
            index 702db8f..b720eac 100644
            --- a/sql/sql_cte.cc
            +++ b/sql/sql_cte.cc
            @@ -1012,6 +1012,7 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex,
             
               bool parse_status= false;
               st_select_lex *with_select;
            +  st_select_lex *last_clone_select;
             
               char save_end= unparsed_spec.str[unparsed_spec.length];
               unparsed_spec.str[unparsed_spec.length]= '\0';
            @@ -1099,11 +1100,6 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex,
               lex->unit.include_down(with_table->select_lex);
               lex->unit.set_slave(with_select);
               lex->unit.cloned_from= spec;
            -  old_lex->all_selects_list=
            -    (st_select_lex*) (lex->all_selects_list->
            -                     insert_chain_before(
            -                       (st_select_lex_node **) &(old_lex->all_selects_list),
            -                        with_select));
             
               /*
                 Now all references to the CTE defined outside of the cloned specification
            @@ -1119,6 +1115,15 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex,
                 goto err;
               }
             
            +  last_clone_select= lex->all_selects_list;
            +  while (last_clone_select->next_select_in_list())
            +    last_clone_select= last_clone_select->next_select_in_list();
            +  old_lex->all_selects_list=
            +    (st_select_lex*) (lex->all_selects_list->
            +                     insert_chain_before(
            +                       (st_select_lex_node **) &(old_lex->all_selects_list),
            +                       last_clone_select));
            +
              lex->sphead= NULL;    // in order not to delete lex->sphead
            

            The difference between this patch and the patch for MDEV-26108 is that the added piece of code is added after the call of resolve_references_to_cte(), not before this call. This is important because the call of resolve_references_to_cte() can add new selects to the list lex->all_selects_list and all selects from this list should be inserted into the list
            old_lex->all_selects_list.

            igor Igor Babaev added a comment - An analysis shows that the SELECT created for the second usage of cte1 via the usage of cte2 is not included in all_select_list for the query. It happens for the same reason as the similar problem of the bug MDEV-26108 a fix for which was applied to 10.4. However if we apply this patch to 10.2 the above test case still crashes the server though now on the second execution of PS. So the fix for MDEV-26108 was not quite correct. A correct fix would be like this: diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 702db8f..b720eac 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1012,6 +1012,7 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, bool parse_status= false; st_select_lex *with_select; + st_select_lex *last_clone_select; char save_end= unparsed_spec.str[unparsed_spec.length]; unparsed_spec.str[unparsed_spec.length]= '\0'; @@ -1099,11 +1100,6 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, lex->unit.include_down(with_table->select_lex); lex->unit.set_slave(with_select); lex->unit.cloned_from= spec; - old_lex->all_selects_list= - (st_select_lex*) (lex->all_selects_list-> - insert_chain_before( - (st_select_lex_node **) &(old_lex->all_selects_list), - with_select)); /* Now all references to the CTE defined outside of the cloned specification @@ -1119,6 +1115,15 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, goto err; } + last_clone_select= lex->all_selects_list; + while (last_clone_select->next_select_in_list()) + last_clone_select= last_clone_select->next_select_in_list(); + old_lex->all_selects_list= + (st_select_lex*) (lex->all_selects_list-> + insert_chain_before( + (st_select_lex_node **) &(old_lex->all_selects_list), + last_clone_select)); + lex->sphead= NULL; // in order not to delete lex->sphead The difference between this patch and the patch for MDEV-26108 is that the added piece of code is added after the call of resolve_references_to_cte(), not before this call. This is important because the call of resolve_references_to_cte() can add new selects to the list lex->all_selects_list and all selects from this list should be inserted into the list old_lex->all_selects_list.
            igor Igor Babaev added a comment -

            The cause of both bugs is the same and they should be fixed by the same patch.

            igor Igor Babaev added a comment - The cause of both bugs is the same and they should be fixed by the same patch.

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.2. A special care should be taken when merging this patch into 10.4: the fix for MDEV-26108 should be reverted, but not the test cases added with this fix.

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2. A special care should be taken when merging this patch into 10.4: the fix for MDEV-26108 should be reverted, but not the test cases added with this fix.

            People

              igor Igor Babaev
              Peter V Peter VARGA
              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.