[MDEV-33010] Crash MariaDB Server after repeating executing queries. Created: 2023-12-13  Updated: 2024-01-30

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.1.2, 11.2.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Critical
Reporter: John Jove Assignee: Oleg Smirnov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Server version: 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 source revision: 9bc25d98209df6810f7a7d5e7dd3ae677a313ab5
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=1
max_threads=153
thread_count=1

Issue Links:
Relates
relates to MDEV-14574 Assertion `!join->select_lex->master_... Confirmed

 Description   

I first run the following statements to create tables.

create table t1 (`c1` bigint, key (`c1`));
 
insert into t1 values (-2);
 
create table t2 ( `c2` double unsigned );
 
insert into t2 values (12.991);

After creating tables, I repeat executing the following statement through the MariaDB CLI.

SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `t1`
 
         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `t3`
 
              ON (EXISTS (SELECT `c2` FROM `t2`)) AND
 
                 (NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`));

After repeating several times (maybe 20 times), I lost the connection to the server.

Or try to run the following EXPLAIN query twice, I can get the same error.

EXPLAIN SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `t1`
 
         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `t3`
 
              ON (EXISTS (SELECT `c2` FROM `t2`)) AND
 
                 (NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`));

The following trace is gotten from the log.

stack_bottom = 0x7f4c90082c38 thread_stack 0x49000
 
Printing to addr2line failed
 
mariadbd(my_print_stacktrace+0x32)[0x563dd110a7c2]
 
mariadbd(handle_fatal_signal+0x488)[0x563dd0be3cf8]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x42520)[0x7f4ca74bd520]
 
mariadbd(_Z15optimize_keyuseP4JOINP16st_dynamic_array+0x148)[0x563dd09b5088]
 
mariadbd(+0x8ad551)[0x563dd09eb551]
 
mariadbd(_ZN4JOIN14optimize_innerEv+0x1322)[0x563dd09f07e2]
 
mariadbd(_ZN4JOIN8optimizeEv+0xda)[0x563dd09f0e2a]
 
mariadbd(+0x7ec60c)[0x563dd092a60c]
 
mariadbd(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0x95)[0x563dd0929e35]
 
mariadbd(_ZN4JOIN14optimize_innerEv+0xb27)[0x563dd09effe7]
 
mariadbd(_ZN4JOIN8optimizeEv+0xda)[0x563dd09f0e2a]
 
mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd1)[0x563dd09f0f21]
 
mariadbd(_Z13handle_selectP3THDP3LEXP13select_resulty+0x154)[0x563dd09f1774]
 
mariadbd(+0x826f55)[0x563dd0964f55]
 
mariadbd(_Z21mysql_execute_commandP3THDb+0x419e)[0x563dd0973f0e]
 
mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x563dd0975237]
 
mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x14bd)[0x563dd0977a1d]
 
mariadbd(_Z10do_commandP3THDb+0x138)[0x563dd0979818]
 
mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3bf)[0x563dd0aa13af]
 
mariadbd(handle_one_connection+0x5d)[0x563dd0aa16fd]
 
mariadbd(+0xcd1906)[0x563dd0e0f906]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x94ac3)[0x7f4ca750fac3]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x126a40)[0x7f4ca75a1a40]
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.



 Comments   
Comment by John Jove [ 2023-12-13 ]

When removing the key build in column c1 in table t1, the error is gone.

Comment by Alice Sherepa [ 2023-12-14 ]

Thank you!
I repeated as described on 10.4-11.3 with InnoDB, not Myisam.
On the release build it crashes after 20-30 executions of the query, while on debug on my machine that was ~9000 executions: 

 --source include/have_innodb.inc
 
 
 
create table t1 (`c1` bigint, key (`c1`)) engine=innodb;
 
insert into t1 values (-2);
 
create table t2 ( `c2` double unsigned );
 
insert into t2 values (12.991);
 
 
 
 
 
let $1= 10000;
 
while ($1)
 
{
 
  SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `t1`
 
         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `t3`
 
              ON (EXISTS (SELECT `c2` FROM `t2`)) AND
 
                 (NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`));
 
  dec $1;
 
}
 
drop table t1,t2;


231214 11:22:49 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: 9f5078a1d79031c4a781d378af18df9c8c9d2321
 
 
 
sql/signal_handler.cc:235(handle_fatal_signal)[0x558d689effd3]
 
sigaction.c:0(__restore_rt)[0x7f9a6f82c420]
 
sql/sql_select.cc:7139(optimize_keyuse(JOIN*, st_dynamic_array*))[0x558d6831ceba]
 
sql/sql_select.cc:5708(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x558d6830fb03]
 
sql/sql_select.cc:2388(JOIN::optimize_inner())[0x558d682ed791]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x558d682e67a7]
 
sql/sql_derived.cc:1029(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x558d681529ef]
 
sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x558d6814d19a]
 
sql/sql_select.cc:2221(JOIN::optimize_inner())[0x558d682ebd0c]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x558d682e67a7]
 
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*))[0x558d68307791]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x558d682d81f8]
 
sql/sql_parse.cc:6523(execute_sqlcom_select(THD*, TABLE_LIST*))[0x558d6823f1fd]
 
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x558d6822c81d]
 
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x558d68248779]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x558d6821e925]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x558d6821b450]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x558d686304c6]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x558d6862fd6a]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x558d692cde4c]
 
nptl/pthread_create.c:478(start_thread)[0x7f9a6f820609]
 
 
 
Query (0x62b0000a1290): SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `t1`
 
         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `t3`
 
              ON (EXISTS (SELECT `c2` FROM `t2`)) AND
 
(NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`))

Generated at Thu Feb 08 10:35:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.