[MDEV-29189] Crash of the second execution of SF using DELETE/UPDATE Created: 2022-07-28  Updated: 2023-03-16  Resolved: 2022-08-02

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Stored routines
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-28883 Re-design the upper level of handling... Closed

 Description   

Present in current version of (as well as in the original/previous version of) bb-10.10-MDEV-28883.
Not present in current trunk of any version 10.3-10.10. Crashes debug + optimized builds.

DELIMITER //
CREATE FUNCTION f() RETURNS INT BEGIN DELETE FROM t;RETURN 1;END;//
DELIMITER ;
CREATE TABLE t (c INT) ENGINE=InnoDB;
PREPARE s FROM 'SELECT 1 FROM t HAVING COUNT(*)=f()';
EXECUTE s;
INSERT INTO t VALUES (1);
SELECT f();

Leads to:

10.10.0 cd8b27bb537d03ed4042cdca3176ad7134b892a7 (Optimized)

Core was generated by `/test/MDEV-28883_MD220722-mariadb-10.10.0-linux-x86_64-opt/bin/mysqld --no-defa'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x00005581d30ca4c6 in Sql_cmd_delete::delete_from_single_table (
    this=0x14633803b988, thd=0x146338000c58)
    at /test/bb-10.10-MDEV-28883_opt/sql/sql_delete.cc:331
331	  COND *conds= select_lex->join->conds;
[Current thread is 1 (Thread 0x146385f2d700 (LWP 1986739))]
(gdb) bt
#0  0x00005581d30ca4c6 in Sql_cmd_delete::delete_from_single_table (this=0x14633803b988, thd=0x146338000c58) at /test/bb-10.10-MDEV-28883_opt/sql/sql_delete.cc:331
#1  0x00005581d30cc88d in Sql_cmd_delete::execute_inner (this=0x14633803b988, thd=0x146338000c58) at /test/bb-10.10-MDEV-28883_opt/sql/sql_delete.cc:1751
#2  0x00005581d3147374 in Sql_cmd_dml::execute (this=0x14633803b988, thd=0x146338000c58) at /test/bb-10.10-MDEV-28883_opt/sql/sql_select.cc:30659
#3  0x00005581d31183c9 in mysql_execute_command (thd=0x146338000c58, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/bb-10.10-MDEV-28883_opt/sql/sql_parse.cc:4386
#4  0x00005581d3062b7b in sp_instr_stmt::exec_core (this=0x14633803d688, thd=<optimized out>, nextp=0x146385f2a994) at /test/bb-10.10-MDEV-28883_opt/sql/sp_head.cc:3836
#5  0x00005581d306bb06 in sp_lex_keeper::reset_lex_and_exec_core (this=0x14633803d6d0, thd=0x146338000c58, nextp=<optimized out>, open_tables=<optimized out>, instr=0x14633803d688) at /test/bb-10.10-MDEV-28883_opt/sql/sp_head.cc:3561
#6  0x00005581d306c477 in sp_instr_stmt::execute (this=0x14633803d688, thd=0x146338000c58, nextp=0x146385f2a994) at /test/bb-10.10-MDEV-28883_opt/sql/sp_head.cc:3742
#7  0x00005581d3066016 in sp_head::execute (this=0x146338039a30, thd=0x146338000c58, merge_da_on_success=true) at /test/bb-10.10-MDEV-28883_opt/sql/sp_head.cc:1438
#8  0x00005581d30683cd in sp_head::execute_function (this=0x146338039a30, thd=thd@entry=0x146338000c58, argp=argp@entry=0x0, argcount=argcount@entry=0, return_value_fld=<optimized out>, func_ctx=func_ctx@entry=0x146338010f38, call_arena=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sp_head.cc:2170
#9  0x00005581d3376291 in Item_sp::execute_impl (this=0x146338010ed8, thd=0x146338000c58, args=0x0, arg_count=0) at /test/bb-10.10-MDEV-28883_opt/sql/item.cc:2922
#10 0x00005581d3376423 in Item_sp::execute (this=this@entry=0x146338010ed8, thd=0x146338000c58, null_value=null_value@entry=0x146338010e94, args=<optimized out>, arg_count=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/item.cc:2836
#11 0x00005581d33cfcb7 in Item_func_sp::execute (this=this@entry=0x146338010e30) at /test/bb-10.10-MDEV-28883_opt/sql/item_func.cc:6629
#12 0x00005581d33e0c95 in Item_func_sp::val_int (this=0x146338010e30) at /test/bb-10.10-MDEV-28883_opt/sql/item_func.h:3881
#13 0x00005581d32b3d1d in Type_handler::Item_send_long (this=<optimized out>, item=0x146338010e30, protocol=0x1463380011d8, buf=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sql_type.cc:7496
#14 0x00005581d3046abe in Protocol::send_result_set_row (this=this@entry=0x1463380011d8, row_items=row_items@entry=0x146338010a40) at /test/bb-10.10-MDEV-28883_opt/sql/protocol.cc:1328
#15 0x00005581d30be327 in select_send::send_data (this=0x146338012808, items=@0x146338010a40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146338011818, last = 0x146338011818, elements = 1}, <No data fields>}) at /test/bb-10.10-MDEV-28883_opt/sql/sql_class.cc:3104
#16 0x00005581d318adef in select_result_sink::send_data_with_check (u=<optimized out>, sent=0, items=<optimized out>, this=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sql_class.h:5689
#17 select_result_sink::send_data_with_check (sent=0, u=<optimized out>, items=<optimized out>, this=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sql_class.h:5679
#18 JOIN::exec_inner (this=0x146338012830) at /test/bb-10.10-MDEV-28883_opt/sql/sql_select.cc:4655
#19 0x00005581d318b218 in JOIN::exec (this=this@entry=0x146338012830) at /test/bb-10.10-MDEV-28883_opt/sql/sql_select.cc:4567
#20 0x00005581d3189421 in mysql_select (thd=0x146338000c58, tables=0x0, fields=@0x146338010a40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146338011818, last = 0x146338011818, elements = 1}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x146338012808, unit=0x146338004cc0, select_lex=0x146338010798) at /test/bb-10.10-MDEV-28883_opt/sql/sql_select.cc:5047
#21 0x00005581d3189b67 in handle_select (thd=thd@entry=0x146338000c58, lex=lex@entry=0x146338004be0, result=result@entry=0x146338012808, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/bb-10.10-MDEV-28883_opt/sql/sql_select.cc:579
#22 0x00005581d310d9f1 in execute_sqlcom_select (thd=0x146338000c58, all_tables=0x0) at /test/bb-10.10-MDEV-28883_opt/sql/sql_parse.cc:6017
#23 0x00005581d311afb4 in mysql_execute_command (thd=0x146338000c58, is_called_from_prepared_stmt=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sql_parse.cc:3939
#24 0x00005581d3108bf5 in mysql_parse (rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, thd=0x146338000c58) at /test/bb-10.10-MDEV-28883_opt/sql/sql_parse.cc:7797
#25 mysql_parse (thd=0x146338000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sql_parse.cc:7719
#26 0x00005581d311471a in dispatch_command (command=COM_QUERY, thd=0x146338000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/bb-10.10-MDEV-28883_opt/sql/sql_class.h:1364
#27 0x00005581d3116642 in do_command (thd=0x146338000c58, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_opt/sql/sql_parse.cc:1405
#28 0x00005581d322c89f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x5581d5c06b98, put_in_cache=put_in_cache@entry=true) at /test/bb-10.10-MDEV-28883_opt/sql/sql_connect.cc:1418
#29 0x00005581d322cb7d in handle_one_connection (arg=0x5581d5c06b98) at /test/bb-10.10-MDEV-28883_opt/sql/sql_connect.cc:1312
#30 0x00001463a31a6609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#31 0x00001463a2d92133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.10.0 cd8b27bb537d03ed4042cdca3176ad7134b892a7 (Debug)

Core was generated by `/test/MDEV-28883_MD220722-mariadb-10.10.0-linux-x86_64-dbg/bin/mysqld --no-defa'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x00005602d0933076 in Sql_cmd_delete::delete_from_single_table (
    this=this@entry=0x1515a8055238, thd=thd@entry=0x1515a8000db8)
    at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:331
331	  COND *conds= select_lex->join->conds;
[Current thread is 1 (Thread 0x1515f8179700 (LWP 2461011))]
(gdb) bt
#0  0x00005602d0933076 in Sql_cmd_delete::delete_from_single_table (this=this@entry=0x1515a8055238, thd=thd@entry=0x1515a8000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:331
#1  0x00005602d0935a7e in Sql_cmd_delete::execute_inner (this=0x1515a8055238, thd=0x1515a8000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:1751
#2  0x00005602d09cc0af in Sql_cmd_dml::execute (this=0x1515a8055238, thd=0x1515a8000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:30659
#3  0x00005602d09901e0 in mysql_execute_command (thd=0x1515a8000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:4386
#4  0x00005602d08b7f8c in sp_instr_stmt::exec_core (this=0x1515a8058038, thd=<optimized out>, nextp=0x1515f8176934) at /test/bb-10.10-MDEV-28883_dbg/sql/sp_head.cc:3836
#5  0x00005602d08c5a78 in sp_lex_keeper::reset_lex_and_exec_core (this=this@entry=0x1515a8058080, thd=thd@entry=0x1515a8000db8, nextp=nextp@entry=0x1515f8176934, open_tables=open_tables@entry=false, instr=instr@entry=0x1515a8058038) at /test/bb-10.10-MDEV-28883_dbg/sql/sp_head.cc:3561
#6  0x00005602d08c61f3 in sp_instr_stmt::execute (this=0x1515a8058038, thd=0x1515a8000db8, nextp=0x1515f8176934) at /test/bb-10.10-MDEV-28883_dbg/sql/sp_head.cc:3742
#7  0x00005602d08be12e in sp_head::execute (this=this@entry=0x1515a80532e0, thd=thd@entry=0x1515a8000db8, merge_da_on_success=merge_da_on_success@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sp_head.cc:1438
#8  0x00005602d08c1521 in sp_head::execute_function (this=0x1515a80532e0, thd=thd@entry=0x1515a8000db8, argp=argp@entry=0x0, argcount=argcount@entry=0, return_value_fld=<optimized out>, func_ctx=func_ctx@entry=0x1515a8014458, call_arena=0x1515a8014e18) at /test/bb-10.10-MDEV-28883_dbg/sql/sp_head.cc:2170
#9  0x00005602d0c9bad9 in Item_sp::execute_impl (this=this@entry=0x1515a80143f8, thd=thd@entry=0x1515a8000db8, args=args@entry=0x0, arg_count=arg_count@entry=0) at /test/bb-10.10-MDEV-28883_dbg/sql/item.cc:2922
#10 0x00005602d0c9bcd3 in Item_sp::execute (this=this@entry=0x1515a80143f8, thd=0x1515a8000db8, null_value=null_value@entry=0x1515a80143b4, args=args@entry=0x0, arg_count=arg_count@entry=0) at /test/bb-10.10-MDEV-28883_dbg/sql/item.cc:2836
#11 0x00005602d0d1379d in Item_func_sp::execute (this=this@entry=0x1515a8014350) at /test/bb-10.10-MDEV-28883_dbg/sql/item_func.cc:6629
#12 0x00005602d0d27fe7 in Item_func_sp::val_int (this=0x1515a8014350) at /test/bb-10.10-MDEV-28883_dbg/sql/item_func.h:3881
#13 0x00005602d0b9c4b3 in Type_handler::Item_send_long (this=<optimized out>, item=0x1515a8014350, protocol=0x1515a80013c0, buf=<optimized out>) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_type.cc:7496
#14 0x00005602d0ba52fd in Type_handler_long::Item_send (this=<optimized out>, item=<optimized out>, protocol=<optimized out>, buf=<optimized out>) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_type.h:5687
#15 0x00005602d085dcde in Item::send (this=0x1515a8014350, protocol=0x1515a80013c0, buffer=0x1515f8177170) at /test/bb-10.10-MDEV-28883_dbg/sql/item.h:1227
#16 0x00005602d0894a33 in Protocol::send_result_set_row (this=this@entry=0x1515a80013c0, row_items=row_items@entry=0x1515a8013f60) at /test/bb-10.10-MDEV-28883_dbg/sql/protocol.cc:1328
#17 0x00005602d09227cb in select_send::send_data (this=0x1515a8015e28, items=@0x1515a8013f60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1515a8014e38, last = 0x1515a8014e38, elements = 1}, <No data fields>}) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.cc:3104
#18 0x00005602d0a16cf1 in select_result_sink::send_data_with_check (sent=0, u=<optimized out>, items=<optimized out>, this=<optimized out>) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.h:5689
#19 JOIN::exec_inner (this=this@entry=0x1515a8015e50) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:4655
#20 0x00005602d0a17d54 in JOIN::exec (this=this@entry=0x1515a8015e50) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:4567
#21 0x00005602d0a15ad8 in mysql_select (thd=thd@entry=0x1515a8000db8, tables=0x0, fields=@0x1515a8013f60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1515a8014e38, last = 0x1515a8014e38, elements = 1}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x1515a8015e28, unit=0x1515a8004fe0, select_lex=0x1515a8013cb8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:5047
#22 0x00005602d0a162ce in handle_select (thd=thd@entry=0x1515a8000db8, lex=lex@entry=0x1515a8004f00, result=result@entry=0x1515a8015e28, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:579
#23 0x00005602d0982c50 in execute_sqlcom_select (thd=thd@entry=0x1515a8000db8, all_tables=0x0) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:6017
#24 0x00005602d098efc4 in mysql_execute_command (thd=thd@entry=0x1515a8000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:3939
#25 0x00005602d097cfe5 in mysql_parse (thd=thd@entry=0x1515a8000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1515f8178460) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:7797
#26 0x00005602d098a53a in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x1515a8000db8, packet=packet@entry=0x1515a800b6e9 "", packet_length=packet_length@entry=10, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.h:1364
#27 0x00005602d098cc47 in do_command (thd=0x1515a8000db8, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:1405
#28 0x00005602d0aebae4 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x5602d3a2c028, put_in_cache=put_in_cache@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_connect.cc:1418
#29 0x00005602d0aebfed in handle_one_connection (arg=0x5602d3a2c028) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_connect.cc:1312
#30 0x000015161162b609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#31 0x0000151611217133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95



 Comments   
Comment by Igor Babaev [ 2022-07-28 ]

Here's a simpler sequence of commands that causes the same problem:

INSERT INTO t VALUES (1);
SELECT 1 FROM t WHERE f() = 1;
SELECT f();

Note that the first statement here reports the error message:

ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

If we use t1 instead of t in the first SELECT

DELIMITER //
CREATE FUNCTION f() RETURNS INT BEGIN DELETE FROM t;RETURN 1;END;//
DELIMITER ;
CREATE TABLE t (c INT) ENGINE=InnoDB;
INSERT INTO t VALUES (1);
CREATE TABLE t1 (c INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
SELECT 1 FROM t1 WHERE f() = 1;
SELECT f();

no error message is reported and everything is fine.

Comment by Igor Babaev [ 2022-07-28 ]

The problem is that the function Sql_cmd_dml::execute() does not call Sql_cmd::unprepare()
after call of st_select_lex_unit::cleanup() when an error occurs. As a result the executed command remains marked as prepared for the following call of the stored function 'f' and the second execution of 'f' misses the call of Sql_cmd_dml::prepare(). With missing prepare phase the SELECT_LEX for DELETE lacks join structure and this leads to a crash.

Comment by Igor Babaev [ 2022-07-28 ]

The following test case using UPDATE in the stored function crashes for the same reason:

CREATE TABLE t1 (c int);
 
DELIMITER //;
CREATE FUNCTION f2() RETURNS INT BEGIN UPDATE t1 SET c=2; RETURN 1; END;//
DELIMITER ;//
 
INSERT INTO t1 VALUES (3), (7), (1);
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
SELECT * FROM t1 WHERE f2() = 1;
SELECT f2();
 
DROP FUNCTION f2()
DROP TABLE t1;

with a similar stack

Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x00005555560e8fee in Sql_cmd_update::execute_inner (this=0x7fffe006cd80, thd=0x7fffe0000b18) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sql_update.cc:3056
3056        delete result;
[Current thread is 1 (Thread 0x7ffff0dec700 (LWP 5418))]
#0  0x00005555560e8fee in Sql_cmd_update::execute_inner (this=0x7fffe006cd80, thd=0x7fffe0000b18) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sql_update.cc:3056
#1  0x0000555556045cc3 in Sql_cmd_dml::execute (this=0x7fffe006cd80, thd=0x7fffe0000b18) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sql_select.cc:30659
#2  0x0000555555f90dcd in mysql_execute_command (thd=0x7fffe0000b18, is_called_from_prepared_stmt=false) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sql_parse.cc:4386
#3  0x0000555555e8c26b in sp_instr_stmt::exec_core (this=0x7fffe006cdd0, thd=0x7fffe0000b18, nextp=0x7ffff0de933c) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sp_head.cc:3856
#4  0x0000555555e8b5bc in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fffe006ce18, thd=0x7fffe0000b18, nextp=0x7ffff0de933c, open_tables=false, instr=0x7fffe006cdd0) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sp_head.cc:3581
#5  0x0000555555e8be29 in sp_instr_stmt::execute (this=0x7fffe006cdd0, thd=0x7fffe0000b18, nextp=0x7ffff0de933c) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sp_head.cc:3762
#6  0x0000555555e8523c in sp_head::execute (this=0x7fffe006b310, thd=0x7fffe0000b18, merge_da_on_success=true) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sp_head.cc:1458
#7  0x0000555555e87112 in sp_head::execute_function (this=0x7fffe006b310, thd=0x7fffe0000b18, argp=0x0, argcount=0, return_value_fld=0x7fffe00177f0, func_ctx=0x7fffe00157a8, call_arena=0x7fffe0016168) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/sp_head.cc:2190
#8  0x000055555637deed in Item_sp::execute_impl (this=0x7fffe0015748, thd=0x7fffe0000b18, args=0x0, arg_count=0) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/item.cc:2924
#9  0x000055555637daa1 in Item_sp::execute (this=0x7fffe0015748, thd=0x7fffe0000b18, null_value=0x7fffe0015704, args=0x0, arg_count=0) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/item.cc:2836
#10 0x00005555563f6fd5 in Item_func_sp::execute (this=0x7fffe00156a0) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/item_func.cc:6629
#11 0x00005555563fec68 in Item_func_sp::val_int (this=0x7fffe00156a0) at /home/igor/maria-git/bb-10.10-MDEV-28883/sql/

Comment by Igor Babaev [ 2022-08-02 ]

A fix for this bug was pushed into bb-10.10-MDEV-28883

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