[MDEV-20803] IF NOT EXISTS AND NOT EXISTS crash DB server Created: 2019-10-11  Updated: 2021-04-19  Resolved: 2020-10-08

Status: Closed
Project: MariaDB Server
Component/s: PL/SQL, Stored routines
Affects Version/s: 10.4.7, 10.4, 10.5
Fix Version/s: 10.4.13, 10.5.4

Type: Bug Priority: Major
Reporter: Chow King Tak Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 2
Labels: crash
Environment:

Redhat Enterprise Linux 7

Attachments: Text File error.txt    
Issue Links:
Duplicate
is duplicated by MDEV-21199 Segfault Closed
is duplicated by MDEV-21729 Triggeraufruf führt zu Crash des Servers Closed
Relates
relates to MDEV-20284 Some pointers may be invalid and caus... Closed
relates to MDEV-20290 Server crash in st_select_lex::cleanu... Closed
relates to MDEV-20858 Function crash server Closed
relates to MDEV-21315 Server 10.4 sporadically crashes when... Closed

 Description   

Call stored procedure with IF NOT EXISTS AND NOT EXISTS condition crash the DB server.

The case can be reproduced as follows:

1. Create tables 'test' and 'test2' and insert values into them:

CREATE TABLE `test` (
 
  `name` varchar(100) NOT NULL,
 
  `address` varchar(100) DEFAULT NULL,
 
  PRIMARY KEY (`name`));
 
 
 
CREATE TABLE `test2` (
 
  `name` varchar(100) NOT NULL,
 
  `address` varchar(100) DEFAULT NULL,
 
  PRIMARY KEY (`name`));
 
 
 
INSERT INTO test VALUES ('a', 'aaa');
 
INSERT INTO test2 VALUES ('a', 'aaa');

2. Create stored procedure 'test_if':

DELIMITER ;;
 
DROP PROCEDURE IF EXISTS `test_if` ;
 
CREATE  PROCEDURE `test_if`(
 
        IN in_name NVARCHAR(20)
 
)
 
BEGIN
 
 
 
    If NOT EXISTS (select name from test where name = name)
 
    AND NOT EXISTS (select name from test2 where name = name)
 
    THEN
 
       select "NOT EXISTS";
 
    ELSE
 
       select "EXISTS";
 
    END IF;
 
 
 
    SELECT * FROM test;
 
END ;;
 
DELIMITER ;

3. Run: call test_if('a'); (OK, no error)
4. Run again: call test_if('a'); , it will crash the DB server

The error message 'error.txt' is attached for your reference.

Please investigate.

Thanks,

Chow King Tak

 Comments   
Comment by Elena Stepanova [ 2019-10-12 ]

Thanks for the report and test case.

It might be the same problem as MDEV-20290 – the failure on debug builds is identical – but the test case in MDEV-20290 does not fail on non-debug builds, while this one does, so I'll keep both open.

Same test case as originally reported, only put together for MTR:

CREATE TABLE `test` (
 
`name` varchar(100) NOT NULL,
 
`address` varchar(100) DEFAULT NULL,
 
PRIMARY KEY (`name`));
 
 
 
CREATE TABLE `test2` (
 
`name` varchar(100) NOT NULL,
 
`address` varchar(100) DEFAULT NULL,
 
PRIMARY KEY (`name`));
 
 
 
INSERT INTO test VALUES ('a', 'aaa');
 
INSERT INTO test2 VALUES ('a', 'aaa');
 
 
 
DROP PROCEDURE IF EXISTS `test_if` ;
 
 
 
--delimiter $
 
CREATE PROCEDURE `test_if`(
 
IN in_name NVARCHAR(20)
 
)
 
BEGIN
 
 
 
If NOT EXISTS (select name from test where name = name)
 
AND NOT EXISTS (select name from test2 where name = name)
 
THEN
 
select "NOT EXISTS";
 
ELSE
 
select "EXISTS";
 
END IF;
 
 
 
SELECT * FROM test;
 
END $
 
--delimiter ;
 
 
 
call test_if('a');
 
call test_if('a');

10.4 debug 36824d2b

 
mysqld: /data/src/10.4/sql/sql_prepare.cc:2998: void reinit_stmt_before_use(THD*, LEX*): Assertion `sl->join == 0' failed.
 
191012 21:40:42 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007ff252b0cf12 in __GI___assert_fail (assertion=0x55b63692ca16 "sl->join == 0", file=0x55b63692c330 "/data/src/10.4/sql/sql_prepare.cc", line=2998, function=0x55b63692d860 <reinit_stmt_before_use(THD*, LEX*)::__PRETTY_FUNCTION__> "void reinit_stmt_before_use(THD*, LEX*)") at assert.c:101
 
#8  0x000055b635c5bf1b in reinit_stmt_before_use (thd=0x7ff23c000b00, lex=0x7ff23c19b178) at /data/src/10.4/sql/sql_prepare.cc:2998
 
#9  0x000055b635b43e32 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7ff23c072078, thd=0x7ff23c000b00, nextp=0x7ff24ccdc6b4, open_tables=true, instr=0x7ff23c072020) at /data/src/10.4/sql/sp_head.cc:3371
 
#10 0x000055b635b45bdf in sp_instr_jump_if_not::execute (this=0x7ff23c072020, thd=0x7ff23c000b00, nextp=0x7ff24ccdc6b4) at /data/src/10.4/sql/sp_head.cc:3946
 
#11 0x000055b635b3deb9 in sp_head::execute (this=0x7ff23c198418, thd=0x7ff23c000b00, merge_da_on_success=true) at /data/src/10.4/sql/sp_head.cc:1346
 
#12 0x000055b635b40ced in sp_head::execute_procedure (this=0x7ff23c198418, thd=0x7ff23c000b00, args=0x7ff23c0058e8) at /data/src/10.4/sql/sp_head.cc:2351
 
#13 0x000055b635c2fc01 in do_execute_sp (thd=0x7ff23c000b00, sp=0x7ff23c198418) at /data/src/10.4/sql/sql_parse.cc:3014
 
#14 0x000055b635c308e8 in Sql_cmd_call::execute (this=0x7ff23c0131e8, thd=0x7ff23c000b00) at /data/src/10.4/sql/sql_parse.cc:3256
 
#15 0x000055b635c3bbdd in mysql_execute_command (thd=0x7ff23c000b00) at /data/src/10.4/sql/sql_parse.cc:6094
 
#16 0x000055b635c412b1 in mysql_parse (thd=0x7ff23c000b00, rawbuf=0x7ff23c013128 "call test_if('a')", length=17, parser_state=0x7ff24ccde170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7912
 
#17 0x000055b635c2c544 in dispatch_command (command=COM_QUERY, thd=0x7ff23c000b00, packet=0x7ff23c008331 "call test_if('a')", packet_length=17, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1841
 
#18 0x000055b635c2abd1 in do_command (thd=0x7ff23c000b00) at /data/src/10.4/sql/sql_parse.cc:1359
 
#19 0x000055b635db2603 in do_handle_one_connection (connect=0x55b63933fae0) at /data/src/10.4/sql/sql_connect.cc:1412
 
#20 0x000055b635db2352 in handle_one_connection (arg=0x55b63933fae0) at /data/src/10.4/sql/sql_connect.cc:1316
 
#21 0x000055b6367b38bb in pfs_spawn_thread (arg=0x55b63935b670) at /data/src/10.4/storage/perfschema/pfs.cc:1862
 
#22 0x00007ff2546824a4 in start_thread (arg=0x7ff24ccdf700) at pthread_create.c:456
 
#23 0x00007ff252bc9d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

10.4 non-debug 36824d2b

 
#3  <signal handler called>
 
#4  0x00005611786891b7 in JOIN::destroy (this=0x7fcde804ccf8) at /data/src/10.4/sql/sql_select.cc:4407
 
#5  0x00005611786e3e6c in st_select_lex::cleanup (this=0x7fcde80629e8) at /data/src/10.4/sql/sql_union.cc:2058
 
#6  0x00005611788e1928 in subselect_single_select_engine::prepare (this=0x7fcde80638e0, thd=0x7fcde80009a8) at /data/src/10.4/sql/item_subselect.cc:3711
 
#7  0x00005611788e1368 in Item_subselect::fix_fields (this=0x7fcde8063740, thd_param=<optimized out>, ref=0x7fcde80639b0) at /data/src/10.4/sql/item_subselect.cc:283
 
#8  0x000056117889d483 in Item::fix_fields_if_needed (ref=0x7fcde80639b0, thd=0x7fcde80009a8, this=0x7fcde8063740) at /data/src/10.4/sql/item.h:956
 
#9  Item_func::fix_fields (this=this@entry=0x7fcde8063920, thd=thd@entry=0x7fcde80009a8, ref=ref@entry=0x7fcde8064340) at /data/src/10.4/sql/item_func.cc:351
 
#10 0x00005611788744be in Item_func_not::fix_fields (this=0x7fcde8063920, thd=0x7fcde80009a8, ref=0x7fcde8064340) at /data/src/10.4/sql/item_cmpfunc.cc:6351
 
#11 0x0000561178874c7f in Item::fix_fields_if_needed (ref=<optimized out>, thd=0x7fcde80009a8, this=0x7fcde8063920) at /data/src/10.4/sql/item.h:956
 
#12 Item::fix_fields_if_needed_for_scalar (ref=<optimized out>, thd=0x7fcde80009a8, this=0x7fcde8063920) at /data/src/10.4/sql/item.h:960
 
#13 Item::fix_fields_if_needed_for_bool (ref=<optimized out>, thd=0x7fcde80009a8, this=0x7fcde8063920) at /data/src/10.4/sql/item.h:964
 
#14 Item_cond::fix_fields (this=0x7fcde80639f0, thd=0x7fcde80009a8, ref=<optimized out>) at /data/src/10.4/sql/item_cmpfunc.cc:4845
 
#15 0x00005611785be940 in Item::fix_fields_if_needed (ref=0x7fcde8064398, thd=0x7fcde80009a8, this=0x7fcde80639f0) at /data/src/10.4/sql/item.h:956
 
#16 THD::sp_fix_func_item (this=0x7fcde80009a8, it_addr=it_addr@entry=0x7fcde8064398) at /data/src/10.4/sql/sp_head.cc:356
 
#17 0x00005611785be95d in THD::sp_prepare_func_item (this=<optimized out>, it_addr=it_addr@entry=0x7fcde8064398, cols=cols@entry=1) at /data/src/10.4/sql/sp_head.cc:342
 
#18 0x00005611785be9ad in sp_instr_jump_if_not::exec_core (this=0x7fcde8064348, thd=<optimized out>, nextp=0x7fcdf9e12cc4) at /data/src/10.4/sql/sp_head.cc:3956
 
#19 0x00005611785c4648 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fcde80643a0, thd=0x7fcde80009a8, nextp=0x7fcdf9e12cc4, open_tables=<optimized out>, instr=0x7fcde8064348) at /data/src/10.4/sql/sp_head.cc:3398
 
#20 0x00005611785c052a in sp_head::execute (this=this@entry=0x7fcde805d320, thd=thd@entry=0x7fcde80009a8, merge_da_on_success=merge_da_on_success@entry=true) at /data/src/10.4/sql/sp_head.cc:1346
 
#21 0x00005611785c1a0c in sp_head::execute_procedure (this=0x7fcde805d320, thd=thd@entry=0x7fcde80009a8, args=0x7fcde80055d0) at /data/src/10.4/sql/sp_head.cc:2351
 
#22 0x000056117864266f in do_execute_sp (thd=0x7fcde80009a8, sp=<optimized out>) at /data/src/10.4/sql/sql_parse.cc:3014
 
#23 0x0000561178643a46 in Sql_cmd_call::execute (this=this@entry=0x7fcde800fe00, thd=thd@entry=0x7fcde80009a8) at /data/src/10.4/sql/sql_parse.cc:3256
 
#24 0x00005611786442ea in Sql_cmd_call::execute (this=0x7fcde800fe00, thd=0x7fcde80009a8) at /data/src/10.4/sql/sql_parse.cc:3210
 
#25 0x000056117864ae00 in mysql_execute_command (thd=thd@entry=0x7fcde80009a8) at /data/src/10.4/sql/sql_parse.cc:6094
 
#26 0x0000561178651fa9 in mysql_parse (thd=thd@entry=0x7fcde80009a8, rawbuf=<optimized out>, length=17, parser_state=parser_state@entry=0x7fcdf9e161b0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.4/sql/sql_parse.cc:7912
 
#27 0x00005611786543b8 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fcde80009a8, packet=packet@entry=0x7fcde8007999 "call test_if('a')", packet_length=packet_length@entry=17, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.4/sql/sql_parse.cc:1841
 
#28 0x0000561178655b49 in do_command (thd=0x7fcde80009a8) at /data/src/10.4/sql/sql_parse.cc:1359
 
#29 0x0000561178724d44 in do_handle_one_connection (connect=connect@entry=0x56117b9f6f48) at /data/src/10.4/sql/sql_connect.cc:1412
 
#30 0x0000561178724df4 in handle_one_connection (arg=arg@entry=0x56117b9f6f48) at /data/src/10.4/sql/sql_connect.cc:1316
 
#31 0x0000561178cd1f04 in pfs_spawn_thread (arg=0x56117b9a20b8) at /data/src/10.4/storage/perfschema/pfs.cc:1862
 
#32 0x00007fce017bc4a4 in start_thread (arg=0x7fcdf9e17700) at pthread_create.c:456
 
#33 0x00007fcdffd03d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Not reproducible on 10.3.

Comment by Oleksandr Byelkin [ 2020-10-08 ]

Duplicate of MDEV-23094

Generated at Thu Feb 08 09:02:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.