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
The following simple test demonstrate the problem:
createtable t1 (a int, b int);
insertinto 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;
deallocateprepare stmt;
Execution of this test case causes a crash of the server.
The crash happens when running the first 'execute stmt;'.
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;'.
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:
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 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.
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 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 Babaev
Peter VARGA
Votes:
0Vote for this issue
Watchers:
4Start 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.
{"report":{"fcp":1024.3999999761581,"ttfb":295.10000002384186,"pageVisibility":"visible","entityId":100829,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"b464118d-a041-4b58-864c-df01aef6ef93","navigationType":0,"readyForUser":1099.3000000715256,"redirectCount":0,"resourceLoadedEnd":698.6000000238419,"resourceLoadedStart":327.8000000715256,"resourceTiming":[{"duration":55.199999928474426,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":327.8000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":327.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":383,"responseStart":0,"secureConnectionStart":0},{"duration":55.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":328.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":328.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":383.60000002384186,"responseStart":0,"secureConnectionStart":0},{"duration":184.29999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":328.3000000715256,"connectEnd":328.3000000715256,"connectStart":328.3000000715256,"domainLookupEnd":328.3000000715256,"domainLookupStart":328.3000000715256,"fetchStart":328.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":387.3000000715256,"responseEnd":512.6000000238419,"responseStart":398.7000000476837,"secureConnectionStart":328.3000000715256},{"duration":215.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":328.5,"connectEnd":328.5,"connectStart":328.5,"domainLookupEnd":328.5,"domainLookupStart":328.5,"fetchStart":328.5,"redirectEnd":0,"redirectStart":0,"requestStart":387.5,"responseEnd":544.2000000476837,"responseStart":406.7000000476837,"secureConnectionStart":328.5},{"duration":81.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":328.7000000476837,"connectEnd":328.7000000476837,"connectStart":328.7000000476837,"domainLookupEnd":328.7000000476837,"domainLookupStart":328.7000000476837,"fetchStart":328.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":389.39999997615814,"responseEnd":410.2000000476837,"responseStart":407.5,"secureConnectionStart":328.7000000476837},{"duration":81.80000007152557,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":328.89999997615814,"connectEnd":328.89999997615814,"connectStart":328.89999997615814,"domainLookupEnd":328.89999997615814,"domainLookupStart":328.89999997615814,"fetchStart":328.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":389.8000000715256,"responseEnd":410.7000000476837,"responseStart":408.5,"secureConnectionStart":328.89999997615814},{"duration":85,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":329,"connectEnd":329,"connectStart":329,"domainLookupEnd":329,"domainLookupStart":329,"fetchStart":329,"redirectEnd":0,"redirectStart":0,"requestStart":394.39999997615814,"responseEnd":414,"responseStart":411.2000000476837,"secureConnectionStart":329},{"duration":64.19999992847443,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":329.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":329.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":393.39999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":85.30000007152557,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":329.5,"connectEnd":329.5,"connectStart":329.5,"domainLookupEnd":329.5,"domainLookupStart":329.5,"fetchStart":329.5,"redirectEnd":0,"redirectStart":0,"requestStart":395,"responseEnd":414.8000000715256,"responseStart":411.8000000715256,"secureConnectionStart":329.5},{"duration":63.89999997615814,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":329.60000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":329.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":393.5,"responseStart":0,"secureConnectionStart":0},{"duration":86.39999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":329.8000000715256,"connectEnd":329.8000000715256,"connectStart":329.8000000715256,"domainLookupEnd":329.8000000715256,"domainLookupStart":329.8000000715256,"fetchStart":329.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":397.8000000715256,"responseEnd":416.2000000476837,"responseStart":414.89999997615814,"secureConnectionStart":329.8000000715256},{"duration":213.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":335.39999997615814,"connectEnd":335.39999997615814,"connectStart":335.39999997615814,"domainLookupEnd":335.39999997615814,"domainLookupStart":335.39999997615814,"fetchStart":335.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":495.3000000715256,"responseEnd":548.8000000715256,"responseStart":546.1000000238419,"secureConnectionStart":335.39999997615814},{"duration":343.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":354.8000000715256,"connectEnd":354.8000000715256,"connectStart":354.8000000715256,"domainLookupEnd":354.8000000715256,"domainLookupStart":354.8000000715256,"fetchStart":354.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":670.1000000238419,"responseEnd":698.6000000238419,"responseStart":679.2000000476837,"secureConnectionStart":354.8000000715256},{"duration":272.8000000715256,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":649.5,"connectEnd":649.5,"connectStart":649.5,"domainLookupEnd":649.5,"domainLookupStart":649.5,"fetchStart":649.5,"redirectEnd":0,"redirectStart":0,"requestStart":887.3000000715256,"responseEnd":922.3000000715256,"responseStart":921.6000000238419,"secureConnectionStart":649.5}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":130,"responseStart":295,"responseEnd":353,"domLoading":326,"domInteractive":1185,"domContentLoadedEventStart":1185,"domContentLoadedEventEnd":1242,"domComplete":2933,"loadEventStart":2933,"loadEventEnd":2933,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1151},{"name":"bigPipe.sidebar-id.end","time":1151.8999999761581},{"name":"bigPipe.activity-panel-pipe-id.start","time":1152},{"name":"bigPipe.activity-panel-pipe-id.end","time":1155.1000000238419},{"name":"activityTabFullyLoaded","time":1258.3999999761581}],"measures":[],"correlationId":"90b17e2635938b","effectiveType":"4g","downlink":9.8,"rtt":0,"serverDuration":113,"dbReadsTimeInMs":14,"dbConnsTimeInMs":22,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
The following simple test demonstrate the problem:
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 )
Execution of this test case causes a crash of the server.
The crash happens when running the first 'execute stmt;'.