MySQL Bugs: #74665: Assertion `! thd->in_sub_stmt' failed in mysql_execute

Bug #74665	Assertion `! thd->in_sub_stmt' failed in mysql_execute_command
Submitted:	3 Nov 2014 8:41	Modified:	24 Oct 2015 17:56
Reporter:	Ramesh Sivaraman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.20-debug, 5.6.22	OS:	Linux (CentOS 7)
Assigned to:		CPU Architecture:	Any

Description:
mysqld: /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:2535: int mysql_execute_command(THD*): Assertion `! thd->in_sub_stmt' failed.
08:37:01 UTC - mysqld got signal 6 ;

***** GDB info

#0  0x00007f4981a23771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000a9035e in my_write_core (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/mysys/stacktrace.c:422
#2  0x0000000000723688 in handle_fatal_signal (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007f498082f5c9 in raise () from /lib64/libc.so.6
#5  0x00007f4980830cd8 in abort () from /lib64/libc.so.6
#6  0x00007f4980828536 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f49808285e2 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000007cf257 in mysql_execute_command (thd=0x7f48ee724000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:2535
#9  0x000000000097901e in sp_instr_stmt::exec_core (this=0x7f48e4956930, thd=0x7f48ee724000, nextp=0x7f4982018158) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:901
#10 0x0000000000977e62 in sp_lex_instr::reset_lex_and_exec_core (this=0x7f48e4956930, thd=0x7f48ee724000, nextp=0x7f4982018158, open_tables=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:390
#11 0x0000000000978778 in sp_lex_instr::validate_lex_and_execute_core (this=0x7f48e4956930, thd=0x7f48ee724000, nextp=0x7f4982018158, open_tables=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:638
#12 0x0000000000978ca2 in sp_instr_stmt::execute (this=0x7f48e4956930, thd=0x7f48ee724000, nextp=0x7f4982018158) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:808
#13 0x000000000072c322 in sp_head::execute (this=0x7f48e4955010, thd=0x7f48ee724000, merge_da_on_success=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_head.cc:645
#14 0x000000000072e042 in sp_head::execute_procedure (this=0x7f48e4955010, thd=0x7f48ee724000, args=0x7f48e4880378) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_head.cc:1315
#15 0x00000000007d4ce6 in mysql_execute_command (thd=0x7f48ee724000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:4569
#16 0x000000000097901e in sp_instr_stmt::exec_core (this=0x7f48e4880b88, thd=0x7f48ee724000, nextp=0x7f49820195e8) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:901
#17 0x0000000000977e62 in sp_lex_instr::reset_lex_and_exec_core (this=0x7f48e4880b88, thd=0x7f48ee724000, nextp=0x7f49820195e8, open_tables=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:390
#18 0x0000000000978778 in sp_lex_instr::validate_lex_and_execute_core (this=0x7f48e4880b88, thd=0x7f48ee724000, nextp=0x7f49820195e8, open_tables=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:638
#19 0x0000000000978ca2 in sp_instr_stmt::execute (this=0x7f48e4880b88, thd=0x7f48ee724000, nextp=0x7f49820195e8) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_instr.cc:808
#20 0x000000000072c322 in sp_head::execute (this=0x7f48e487f010, thd=0x7f48ee724000, merge_da_on_success=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_head.cc:645
#21 0x000000000072cd17 in sp_head::execute_trigger (this=0x7f48e487f010, thd=0x7f48ee724000, db_name=0x7f48e48981b0, table_name=0x7f48e48981c0, grant_info=0x7f497fc186d8) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sp_head.cc:890
#22 0x0000000000856927 in Table_triggers_list::process_triggers (this=0x7f497fc18610, thd=0x7f48ee724000, event=TRG_EVENT_INSERT, time_type=TRG_ACTION_AFTER, old_row_is_record1=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_trigger.cc:2178
#23 0x00000000007b1615 in write_record (thd=0x7f48ee724000, table=0x7f48e4851600, info=0x7f4982019b00, update=0x7f4982019b80) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_insert.cc:1936
#24 0x00000000007af379 in mysql_insert (thd=0x7f48ee724000, table_list=0x7f48e481f0f8, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_insert.cc:1072
#25 0x00000000007d159f in mysql_execute_command (thd=0x7f48ee724000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:3443
#26 0x00000000007d8fa4 in mysql_parse (thd=0x7f48ee724000, rawbuf=0x7f48e481f010 "INSERT INTO t1 VALUES(0,0)", length=26, parser_state=0x7f498201ae70) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:6245
#27 0x00000000007cc557 in dispatch_command (command=COM_QUERY, thd=0x7f48ee724000, packet=0x7f48ee762001 "INSERT INTO t1 VALUES(0,0)", packet_length=26) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1332
#28 0x00000000007cb646 in do_command (thd=0x7f48ee724000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1034
#29 0x0000000000793a61 in do_handle_one_connection (thd_arg=0x7f48ee724000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:982
#30 0x000000000079354a in handle_one_connection (arg=0x7f48ee724000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:898
#31 0x0000000000ad4b5c in pfs_spawn_thread (arg=0x7f497dffeb00) at /ssd/ramesh/mysql-server/mysql-5.6/storage/perfschema/pfs.cc:1860
#32 0x00007f4981a1edf3 in start_thread () from /lib64/libpthread.so.0
#33 0x00007f49808f001d in clone () from /lib64/libc.so.6

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
create PROCEDURE p1()stop slave;
create TABLE t1(a1 int,a2 CHAR (1)) ROW_FORMAT=DYNAMIC;
create trigger ai after insert on t1 for each row call p1();
INSERT INTO t1 VALUES(0,0);

Hello Ramesh Sivaraman,

Thank you for the bug report and test case.
Confirmed that latest 5.6.22 debug build asserts.

Thanks,
Umes

// 5.6.22 debug build

(gdb) bt
#0  0x0000003deb00c8ac in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b0011b in my_write_core (sig=6) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/mysys/stacktrace.c:422
#2  0x000000000077ecb8 in handle_fatal_signal (sig=6) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x0000003deac32625 in raise () from /lib64/libc.so.6
#5  0x0000003deac33e05 in abort () from /lib64/libc.so.6
#6  0x0000003deac2b74e in __assert_fail_base () from /lib64/libc.so.6
#7  0x0000003deac2b810 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000082d888 in mysql_execute_command (thd=0x8e94d20) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_parse.cc:2535
#9  0x00000000009e1a71 in sp_instr_stmt::exec_core (this=0x7f285001ee50, thd=0x8e94d20, nextp=0x7f28732b73e8) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:901
#10 0x00000000009e089e in sp_lex_instr::reset_lex_and_exec_core (this=0x7f285001ee50, thd=0x8e94d20, nextp=0x7f28732b73e8, open_tables=false)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:390
#11 0x00000000009e11ac in sp_lex_instr::validate_lex_and_execute_core (this=0x7f285001ee50, thd=0x8e94d20, nextp=0x7f28732b73e8, open_tables=false)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:638
#12 0x00000000009e16e7 in sp_instr_stmt::execute (this=0x7f285001ee50, thd=0x8e94d20, nextp=0x7f28732b73e8) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:808
#13 0x0000000000787b7f in sp_head::execute (this=0x7f285001d530, thd=0x8e94d20, merge_da_on_success=true) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_head.cc:645
#14 0x0000000000789974 in sp_head::execute_procedure (this=0x7f285001d530, thd=0x8e94d20, args=0x7f2850018878) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_head.cc:1315
#15 0x000000000083389d in mysql_execute_command (thd=0x8e94d20) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_parse.cc:4569
#16 0x00000000009e1a71 in sp_instr_stmt::exec_core (this=0x7f2850019088, thd=0x8e94d20, nextp=0x7f28732b88d8) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:901
#17 0x00000000009e089e in sp_lex_instr::reset_lex_and_exec_core (this=0x7f2850019088, thd=0x8e94d20, nextp=0x7f28732b88d8, open_tables=false)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:390
#18 0x00000000009e11ac in sp_lex_instr::validate_lex_and_execute_core (this=0x7f2850019088, thd=0x8e94d20, nextp=0x7f28732b88d8, open_tables=false)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:638
#19 0x00000000009e16e7 in sp_instr_stmt::execute (this=0x7f2850019088, thd=0x8e94d20, nextp=0x7f28732b88d8) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_instr.cc:808
#20 0x0000000000787b7f in sp_head::execute (this=0x7f2850017510, thd=0x8e94d20, merge_da_on_success=false) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_head.cc:645
#21 0x00000000007885aa in sp_head::execute_trigger (this=0x7f2850017510, thd=0x8e94d20, db_name=0x7f2850010110, table_name=0x7f2850010120, grant_info=0x7f2850011308)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sp_head.cc:890
#22 0x00000000008b7fca in Table_triggers_list::process_triggers (this=0x7f2850011240, thd=0x8e94d20, event=TRG_EVENT_INSERT, time_type=TRG_ACTION_AFTER, old_row_is_record1=true)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_trigger.cc:2178
#23 0x000000000080f546 in write_record (thd=0x8e94d20, table=0x7f285005ea10, info=0x7f28732b8e80, update=0x7f28732b8e00)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_insert.cc:1936
#24 0x000000000080d21b in mysql_insert (thd=0x8e94d20, table_list=0x7f28500050b8, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_insert.cc:1072
#25 0x000000000082fe71 in mysql_execute_command (thd=0x8e94d20) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_parse.cc:3443
#26 0x00000000008380e1 in mysql_parse (thd=0x8e94d20, rawbuf=0x7f2850004fd0 "INSERT INTO t1 VALUES(0,0)", length=26, parser_state=0x7f28732ba680)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_parse.cc:6357
#27 0x000000000082aa63 in dispatch_command (command=COM_QUERY, thd=0x8e94d20, packet=0x8fc3dc1 "INSERT INTO t1 VALUES(0,0)", packet_length=26)
    at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_parse.cc:1332
#28 0x0000000000829ac8 in do_command (thd=0x8e94d20) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_parse.cc:1034
#29 0x00000000007f0f17 in do_handle_one_connection (thd_arg=0x8e94d20) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_connect.cc:982
#30 0x00000000007f09e8 in handle_one_connection (arg=0x8e94d20) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/sql/sql_connect.cc:898
#31 0x0000000000e64c25 in pfs_spawn_thread (arg=0x8f874f0) at /pb2/build/sb_0-13562263-1414689457.82/mysqlcom-pro-5.6.22/storage/perfschema/pfs.cc:1860
#32 0x0000003deb0079d1 in start_thread () from /lib64/libpthread.so.0
#33 0x0000003deace89dd in clone () from /lib64/libc.so.6

apparently i filed this bug 2 years ago, and the fix went into 5.7.1.
Bug 15985318 - ASSERTION FAILED: ! THD->IN_SUB_STMT WITH CERTAIN COMMANDS IN TRIGGERS

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html

"For debug builds, executing a statement within a trigger or stored function that caused an implicit commit raised an assertion. (Bug #15985318)"

[23 Jan 2013 5:18] Paul Dubois

Fixed under the heading of Bug#15985318.

Noted in 5.7.1 changelog.

For debug builds, executing a statement within a trigger or stored
function that caused an implicit commit raised an assertion.

Also updated reference manual with correct information about CREATE SERVER
and implicit commit. (Ditto for ALTER/DROP server.)