Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.24, 10.6.17, 10.11.7, 11.3.2, 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
-
None
Description
Consider the following simplified test case:
create table bug (
|
id binary(16) not null,
|
c1 int(11),
|
c2 varchar(9),
|
c3 varchar(6),
|
updated_at datetime
|
);
|
|
prepare `bug_stmt` FROM "select i1_0.c1 from bug i1_0 where i1_0.id in(select (select i3_0.id from bug i3_0 where i3_0.c2=i2_0.c2 and i3_0.c3='123' order by i3_0.updated_at desc limit 1) from bug i2_0 where i2_0.c3='123' group by i2_0.c2)";
|
|
execute `bug_stmt`;
|
We get a crash while running the execute statement
240221 9:36:45 [ERROR] mysqld got signal 11 ;
|
2024-02-21T09:36:45.665298605Z Sorry, we probably made a mistake, and this is a bug.
|
2024-02-21T09:36:45.665300783Z
|
2024-02-21T09:36:45.665302492Z Your assistance in bug reporting will enable us to fix this for the next release.
|
2024-02-21T09:36:45.665304154Z To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
2024-02-21T09:36:45.665305862Z
|
2024-02-21T09:36:45.665307438Z We will try our best to scrape up some info that will hopefully help
|
2024-02-21T09:36:45.665309093Z diagnose the problem, but since we have already crashed,
|
2024-02-21T09:36:45.665310812Z something is definitely wrong and this may fail.
|
2024-02-21T09:36:45.665312409Z
|
2024-02-21T09:36:45.665314005Z Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004 source revision: 15c75ad083a55e198ae78324f22970694b72f22b
|
2024-02-21T09:36:45.665315790Z key_buffer_size=16384
|
2024-02-21T09:36:45.665319428Z read_buffer_size=262144
|
2024-02-21T09:36:45.665321182Z max_used_connections=13
|
2024-02-21T09:36:45.665322790Z max_threads=153
|
2024-02-21T09:36:45.665324396Z thread_count=13
|
2024-02-21T09:36:45.665326088Z It is possible that mysqld could use up to
|
2024-02-21T09:36:45.665327678Z key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 52983 K bytes of memory
|
2024-02-21T09:36:45.665329312Z Hope that's ok; if not, decrease some variables in the equation.
|
2024-02-21T09:36:45.665330868Z
|
2024-02-21T09:36:45.665332460Z Thread pointer: 0x7f755c000c58
|
2024-02-21T09:36:45.665334159Z Attempting backtrace. You can use the following information to find out
|
2024-02-21T09:36:45.665335880Z where mysqld died. If you see no messages after this, something went
|
2024-02-21T09:36:45.665337571Z terribly wrong...
|
2024-02-21T09:36:45.665340121Z stack_bottom = 0x7f75c06f5d18 thread_stack 0x80000
|
2024-02-21T09:36:45.667177433Z Printing to addr2line failed
|
2024-02-21T09:36:45.667474790Z mariadbd(my_print_stacktrace+0x32)[0x5617eccba9c2]
|
2024-02-21T09:36:45.668019437Z mariadbd(handle_fatal_signal+0x475)[0x5617ec769af5]
|
2024-02-21T09:36:45.669267901Z /lib/x86_64-linux-gnu/libpthread.so.0(+0x14420)[0x7f75d7961420]
|
2024-02-21T09:36:45.671052531Z mariadbd(+0x7f4f49)[0x5617ec548f49]
|
2024-02-21T09:36:45.671729842Z mariadbd(_ZN10Item_equal4sortEPFiP4ItemS1_PvES2_+0x95)[0x5617ec7b84a5]
|
2024-02-21T09:36:45.672146617Z mariadbd(+0x8125ff)[0x5617ec5665ff]
|
2024-02-21T09:36:45.672664416Z mariadbd(_ZN4JOIN15optimize_stage2Ev+0x5bb)[0x5617ec5899bb]
|
2024-02-21T09:36:45.673134204Z mariadbd(_ZN4JOIN14optimize_innerEv+0x1bd0)[0x5617ec58dd90]
|
2024-02-21T09:36:45.673484384Z mariadbd(_ZN4JOIN8optimizeEv+0xb0)[0x5617ec590110]
|
2024-02-21T09:36:45.673874487Z mariadbd(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0x116)[0x5617ec4fa996]
|
2024-02-21T09:36:45.674170878Z mariadbd(_ZN4JOIN15optimize_stage2Ev+0x1e27)[0x5617ec58b227]
|
2024-02-21T09:36:45.674578463Z mariadbd(_ZN4JOIN14optimize_innerEv+0x1bd0)[0x5617ec58dd90]
|
2024-02-21T09:36:45.674976511Z mariadbd(_ZN4JOIN8optimizeEv+0xb0)[0x5617ec590110]
|
2024-02-21T09:36:45.675332970Z mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xb7)[0x5617ec5901e7]
|
2024-02-21T09:36:45.675737164Z mariadbd(_Z13handle_selectP3THDP3LEXP13select_resultm+0x157)[0x5617ec5909f7]
|
2024-02-21T09:36:45.676195233Z mariadbd(+0x7c7701)[0x5617ec51b701]
|
2024-02-21T09:36:45.676552715Z mariadbd(_Z21mysql_execute_commandP3THDb+0x4781)[0x5617ec52a181]
|
2024-02-21T09:36:45.676851058Z mariadbd(_ZN18Prepared_statement7executeEP6Stringb+0x4aa)[0x5617ec53af2a]
|
2024-02-21T09:36:45.677160724Z mariadbd(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0xa1)[0x5617ec53b151]
|
2024-02-21T09:36:45.677485294Z mariadbd(_Z22mysql_sql_stmt_executeP3THD+0x193)[0x5617ec53b483]
|
2024-02-21T09:36:45.677791537Z mariadbd(_Z21mysql_execute_commandP3THDb+0x29c6)[0x5617ec5283c6]
|
2024-02-21T09:36:45.678088478Z mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x5617ec515f27]
|
2024-02-21T09:36:45.678443229Z mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x1085)[0x5617ec522825]
|
2024-02-21T09:36:45.678776055Z mariadbd(_Z10do_commandP3THDb+0x13e)[0x5617ec524b8e]
|
2024-02-21T09:36:45.679112037Z mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3b7)[0x5617ec63dc17]
|
2024-02-21T09:36:45.679441540Z mariadbd(handle_one_connection+0x5d)[0x5617ec63df6d]
|
2024-02-21T09:36:45.679914125Z mariadbd(+0xc70f76)[0x5617ec9c4f76]
|
2024-02-21T09:36:45.681245962Z /lib/x86_64-linux-gnu/libpthread.so.0(+0x8609)[0x7f75d7955609]
|
2024-02-21T09:36:45.682451789Z /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f75d7541353]
|
2024-02-21T09:36:45.682461370Z
|
2024-02-21T09:36:45.682464165Z Trying to get some variables.
|
2024-02-21T09:36:45.682467292Z Some pointers may be invalid and cause the dump to abort.
|
2024-02-21T09:36:45.682502035Z Query (0x7f755c01f1f8): select i1_0.c1 from bug i1_0 where i1_0.id in(select (select i3_0.id from bug i3_0 where i3_0.c2=i2_0.c2 and i3_0.c3='123' order by i3_0.updated_at desc limit 1) from bug i2_0 where i2_0.c3='123' group by i2_0.c2)
|
2024-02-21T09:36:45.682506366Z
|
2024-02-21T09:36:45.682508141Z Connection ID (thread ID): 16
|
2024-02-21T09:36:45.682522495Z Status: NOT_KILLED
|
2024-02-21T09:36:45.682524683Z
|
2024-02-21T09:36:45.682535292Z 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,hash_join_cardinality=off,cset_narrowing=off
|
2024-02-21T09:36:45.682540932Z
|
2024-02-21T09:36:45.682542770Z The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ contains
|
2024-02-21T09:36:45.682544541Z information that should help you find out what is causing the crash.
|
2024-02-21T09:36:45.682557474Z Writing a core file...
|
2024-02-21T09:36:45.682563861Z Working directory at /var/lib/mysql
|
2024-02-21T09:36:45.682566463Z Resource Limits:
|
2024-02-21T09:36:45.682576509Z Limit Soft Limit Hard Limit Units
|
2024-02-21T09:36:45.682581531Z Max cpu time unlimited unlimited seconds
|
2024-02-21T09:36:45.682583492Z Max file size unlimited unlimited bytes
|
2024-02-21T09:36:45.682585198Z Max data size unlimited unlimited bytes
|
2024-02-21T09:36:45.682586900Z Max stack size 8388608 unlimited bytes
|
2024-02-21T09:36:45.682588503Z Max core file size unlimited unlimited bytes
|
2024-02-21T09:36:45.682590057Z Max resident set unlimited unlimited bytes
|
2024-02-21T09:36:45.682591630Z Max processes unlimited unlimited processes
|
2024-02-21T09:36:45.682593215Z Max open files 1048576 1048576 files
|
2024-02-21T09:36:45.682594809Z Max locked memory 8388608 8388608 bytes
|
2024-02-21T09:36:45.682596379Z Max address space unlimited unlimited bytes
|
2024-02-21T09:36:45.682597931Z Max file locks unlimited unlimited locks
|
2024-02-21T09:36:45.682599587Z Max pending signals 126461 126461 signals
|
2024-02-21T09:36:45.682601227Z Max msgqueue size 819200 819200 bytes
|
2024-02-21T09:36:45.682602851Z Max nice priority 0 0
|
2024-02-21T09:36:45.682604462Z Max realtime priority 0 0
|
2024-02-21T09:36:45.682610745Z Max realtime timeout unlimited unlimited us
|
2024-02-21T09:36:45.682615939Z Core pattern: |/usr/share/apport/apport -p%p -s%s -c%c -d%d -P%P -u%u -g%g -- %E
|
2024-02-21T09:36:45.682618018Z
|
2024-02-21T09:36:45.682620158Z Kernel version: Linux version 6.5.0-18-generic (buildd@lcy02-amd64-070) (x86_64-linux-gnu-gcc-12 (Ubuntu 12.3.0-1ubuntu1~22.04) 12.3.0, GNU ld (GNU Binutils for Ubuntu) 2.38) #18~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Feb 7 11:40:03 UTC 2
|
2024-02-21T09:36:45.682622944Z
|
I agree the sql statement is not clean, but crashing seems to be too much
Maybe related to MDEV-31296 ; but the issue is still there in 10.6.17 and 10.11.7
Can be reproduced in 11.3.2 too
Thank you for the report! I repeated as descibed on 10.4-11.4:
--source include/have_innodb.inc
(SELECT
(SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 ORDER BY a1.c1 LIMIT 1)
FROM t1 a2
240221 16:21:37 [ERROR] mysqld got signal 11 ;
Server version: 10.4.34-MariaDB-debug-log source revision: 8a505980c52e9d56e3aa523d17b6c1bc1f259ce5
sql/signal_handler.cc:235(handle_fatal_signal)[0x55706d9f4887]
sigaction.c:0(__restore_rt)[0x7f1055e51420]
sql/sql_select.cc:15836(compare_fields_by_table_order(Item*, Item*, void*))[0x55706d359a0b]
sql/sql_list.h:655(void bubble_sort<Item>(List<Item>*, int (*)(Item*, Item*, void*), void*))[0x55706db186a3]
sql/item_cmpfunc.cc:6944(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x55706db0180a]
sql/sql_select.cc:16315(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x55706d35c14e]
sql/sql_select.cc:2537(JOIN::optimize_stage2())[0x55706d2f35b4]
sql/sql_select.cc:2418(JOIN::optimize_inner())[0x55706d2f1e5e]
sql/sql_select.cc:1731(JOIN::optimize())[0x55706d2ea993]
sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x55706d1baf6e]
sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x55706d7378a1]
sql/sql_select.cc:2966(JOIN::optimize_stage2())[0x55706d2f7f81]
sql/sql_select.cc:2418(JOIN::optimize_inner())[0x55706d2f1e5e]
sql/sql_select.cc:1731(JOIN::optimize())[0x55706d2ea993]
sql/sql_select.cc:4836(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*))[0x55706d30bbed]
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55706d2dc3e4]
sql/sql_parse.cc:6549(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55706d2433b4]
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55706d230599]
sql/sql_prepare.cc:5025(Prepared_statement::execute(String*, bool))[0x55706d2a2b1f]
sql/sql_prepare.cc:4472(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55706d29e012]
sql/sql_prepare.cc:5150(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55706d2a3c8b]
sql/sql_prepare.cc:2967(mysql_sql_stmt_execute_immediate(THD*))[0x55706d2948fb]
sql/sql_parse.cc:3987(mysql_execute_command(THD*))[0x55706d2305b6]
sql/sql_parse.cc:8088(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55706d24c92f]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55706d2226a1]
sql/sql_parse.cc:1378(do_command(THD*))[0x55706d21f1cc]
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55706d634c02]
sql/sql_connect.cc:1324(handle_one_connection)[0x55706d6344a6]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55706e2d3b3e]
nptl/pthread_create.c:478(start_thread)[0x7f1055e45609]
Query (0x62b0000a2b38): SELECT t1.c1 FROM t1 WHERE t1.id in
(SELECT
(SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 ORDER BY a1.c1 LIMIT 1)
FROM t1 a2
GROUP BY a2.c2)
240221 15:17:30 [ERROR] mysqld got signal 11 ;
Server version: 10.4.33-MariaDB source revision: 46e3a7658b774942e9320a1acb234373bb44e874
sql/signal_handler.cc:235(handle_fatal_signal)[0x55e502f058ff]
sigaction.c:0(__restore_rt)[0x7f13bea40420]
sql/sql_select.cc:15831(compare_fields_by_table_order(Item*, Item*, void*))[0x55e502d526f6]
sql/sql_list.h:655(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x55e502f4e55d]
sql/sql_list.h:441(base_list_iterator::next_fast())[0x55e502d40f47]
sql/sql_class.h:4040(THD::is_error() const)[0x55e502d669f4]
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55e502d69875]
sql/sql_select.cc:1733(JOIN::optimize())[0x55e502d6bffd]
sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x55e502cf9260]
sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x55e502d67e9c]
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55e502d69875]
sql/sql_select.cc:1733(JOIN::optimize())[0x55e502d6bffd]
sql/sql_select.cc:4832(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*))[0x55e502d6c111]
sql/sql_select.cc:454(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e502d6cd37]
sql/sql_parse.cc:6524(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e502bf02f0]
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55e502d0f009]
sql/sql_prepare.cc:5025(Prepared_statement::execute(String*, bool))[0x55e502d28275]
sql/sql_prepare.cc:4472(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55e502d2838e]
sql/sql_prepare.cc:5150(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55e502d288ea]
sql/sql_class.h:1456(Item_change_list_savepoint::rollback(Item_change_list*))[0x55e502d28af3]
sql/sql_parse.cc:3986(mysql_execute_command(THD*))[0x55e502d0ce14]
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e502d126a2]
sql/sql_parse.cc:1921(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e502d14b53]
sql/sql_parse.cc:1379(do_command(THD*))[0x55e502d1631f]
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55e502dfd372]
sql/sql_connect.cc:1325(handle_one_connection)[0x55e502dfd45d]
nptl/pthread_create.c:478(start_thread)[0x7f13bea34609]
Query (0x7f1358025f08): SELECT t1.c1
FROM t1
WHERE t1.id in
(SELECT
(SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 AND a1.c3='123' ORDER BY a1.dt DESC LIMIT 1)
FROM t1 a2
WHERE a2.c3='123'
GROUP BY a2.c2)
if the select inside the stored procedure - it crashes on the 2nd call.
240221 15:18:41 [ERROR] mysqld got signal 11 ;
Server version: 10.4.33-MariaDB source revision: 46e3a7658b774942e9320a1acb234373bb44e874
sql/signal_handler.cc:235(handle_fatal_signal)[0x5621537058ff]
sigaction.c:0(__restore_rt)[0x7f4909cd4420]
sql/sql_select.cc:15831(compare_fields_by_table_order(Item*, Item*, void*))[0x5621535526f6]
sql/sql_list.h:655(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x56215374e55d]
sql/sql_list.h:441(base_list_iterator::next_fast())[0x562153540f47]
sql/sql_class.h:4040(THD::is_error() const)[0x5621535669f4]
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x562153569875]
sql/sql_select.cc:1733(JOIN::optimize())[0x56215356bffd]
sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x5621534f9260]
sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x562153567e9c]
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x562153569875]
sql/sql_select.cc:1733(JOIN::optimize())[0x56215356bffd]
sql/sql_select.cc:4832(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*))[0x56215356c111]
sql/sql_select.cc:454(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56215356cd37]
sql/sql_parse.cc:6524(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5621533f02f0]
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x56215350f009]
sql/sp_head.cc:3763(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x56215346d085]
sql/sp_head.cc:3499(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x562153475915]
sql/sp_head.cc:3668(sp_instr_stmt::execute(THD*, unsigned int*))[0x56215347642c]
sql/sp_head.cc:1372(sp_head::execute(THD*, bool))[0x562153470df4]
sql/sp_head.cc:2407(sp_head::execute_procedure(THD*, List<Item>*))[0x5621534727a9]
sql/sql_parse.cc:3069(do_execute_sp(THD*, sp_head*))[0x562153502e2a]
sql/sql_parse.cc:3308(Sql_cmd_call::execute(THD*))[0x562153505206]
sql/sql_parse.cc:3319(Sql_cmd_call::execute(THD*))[0x562153505a40]
sql/sql_parse.cc:6266(mysql_execute_command(THD*))[0x56215350cddc]
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5621535126a2]
sql/sql_parse.cc:1921(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x562153514b53]
sql/sql_parse.cc:1379(do_command(THD*))[0x56215351631f]
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x5621535fd372]
sql/sql_connect.cc:1325(handle_one_connection)[0x5621535fd45d]
nptl/pthread_create.c:478(start_thread)[0x7f4909cc8609]
Query (0x7f488c041d90): SELECT t1.c1 FROM t1 WHERE t1.id in (SELECT (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 AND a1.c3='123' ORDER BY a1.dt DESC LIMIT 1) FROM t1 a2 WHERE a2.c3='123' GROUP BY a2.c2)