[MDEV-15977] Assertion `! thd->in_sub_stmt' failed in trans_commit_stmt / SEQUENCE::read_initial_values Created: 2018-04-22  Updated: 2019-12-02  Resolved: 2019-12-02

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.3.21, 10.4.11, 10.5.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Attachments: File mysql_db.sql     File test_db.sql    

 Description   

10.3 e8b6c150

 
mysqld: /data/src/10.3/sql/transaction.cc:512: bool trans_commit_stmt(THD*): Assertion `! thd->in_sub_stmt' failed.
 
190214 20:36:32 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f625c45aee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#8  0x000056471166dd7b in trans_commit_stmt (thd=0x7f6208000b00) at /data/src/10.3/sql/transaction.cc:512
 
#9  0x00005647116fdd38 in SEQUENCE::read_initial_values (this=0x7f62080b9a08, table=0x7f62080bc380) at /data/src/10.3/sql/sql_sequence.cc:493
 
#10 0x0000564711f68aba in ha_sequence::open (this=0x7f62080bdbd8, name=0x7f620806c3b0 "./test/s1", mode=2, flags=18) at /data/src/10.3/sql/ha_sequence.cc:114
 
#11 0x00005647117f6989 in handler::ha_open (this=0x7f62080bdbd8, table_arg=0x7f62080bc380, name=0x7f620806c3b0 "./test/s1", mode=2, test_if_locked=18, mem_root=0x0, partitions_to_open=0x0) at /data/src/10.3/sql/handler.cc:2733
 
#12 0x000056471160c128 in open_table_from_share (thd=0x7f6208000b00, share=0x7f620806be68, alias=0x7f62080ab890, db_stat=33, prgflag=8, ha_open_flags=18, outparam=0x7f62080bc380, is_create_table=false, partitions_to_open=0x0) at /data/src/10.3/sql/table.cc:3495
 
#13 0x0000564711467157 in open_table (thd=0x7f6208000b00, table_list=0x7f62080ab848, ot_ctx=0x7f625579b480) at /data/src/10.3/sql/sql_base.cc:1975
 
#14 0x000056471146a1a4 in open_and_process_table (thd=0x7f6208000b00, lex=0x7f625579b760, tables=0x7f62080ab848, counter=0x7f625579b54c, flags=1346, prelocking_strategy=0x7f625579b550, has_prelocking_list=false, ot_ctx=0x7f625579b480) at /data/src/10.3/sql/sql_base.cc:3596
 
#15 0x000056471146b3a0 in open_tables (thd=0x7f6208000b00, options=..., start=0x7f625579b530, counter=0x7f625579b54c, flags=1346, prelocking_strategy=0x7f625579b550) at /data/src/10.3/sql/sql_base.cc:4121
 
#16 0x00005647114634e2 in open_tables (thd=0x7f6208000b00, tables=0x7f625579b530, counter=0x7f625579b54c, flags=1346, prelocking_strategy=0x7f625579b550) at /data/src/10.3/sql/sql_base.h:250
 
#17 0x000056471146d342 in open_normal_and_derived_tables (thd=0x7f6208000b00, tables=0x7f62080ab848, flags=1346, dt_phases=35) at /data/src/10.3/sql/sql_base.cc:5059
 
#18 0x000056471146d51b in open_tables_only_view_structure (thd=0x7f6208000b00, table_list=0x7f62080ab848, can_deadlock=true) at /data/src/10.3/sql/sql_base.cc:5116
 
#19 0x0000564711598e0f in fill_schema_table_by_open (thd=0x7f6208000b00, mem_root=0x7f625579cfd0, is_show_fields_or_keys=false, table=0x7f620804dd68, schema_table=0x564712a69b80 <schema_tables+1216>, orig_db_name=0x7f6208064938, orig_table_name=0x7f6208064998, open_tables_state_backup=0x7f625579d020, can_deadlock=true) at /data/src/10.3/sql/sql_show.cc:4566
 
#20 0x000056471159a73f in get_all_tables (thd=0x7f6208000b00, tables=0x7f620803acc8, cond=0x0) at /data/src/10.3/sql/sql_show.cc:5204
 
#21 0x00005647115ab7a8 in get_schema_tables_result (join=0x7f62080626c8, executed_place=PROCESSED_BY_JOIN_EXEC) at /data/src/10.3/sql/sql_show.cc:8802
 
#22 0x0000564711544668 in JOIN::exec_inner (this=0x7f62080626c8) at /data/src/10.3/sql/sql_select.cc:4003
 
#23 0x0000564711543cee in JOIN::exec (this=0x7f62080626c8) at /data/src/10.3/sql/sql_select.cc:3834
 
#24 0x0000564711544f8e in mysql_select (thd=0x7f6208000b00, tables=0x7f620803acc8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=4026534656, result=0x7f620804db30, unit=0x7f620803bab0, select_lex=0x7f620803c220) at /data/src/10.3/sql/sql_select.cc:4239
 
#25 0x0000564711536eb4 in handle_select (thd=0x7f6208000b00, lex=0x7f620803b9e8, result=0x7f620804db30, setup_tables_done_option=1073741824) at /data/src/10.3/sql/sql_select.cc:385
 
#26 0x00005647114fb2a8 in mysql_execute_command (thd=0x7f6208000b00) at /data/src/10.3/sql/sql_parse.cc:4837
 
#27 0x000056471142356e in sp_instr_stmt::exec_core (this=0x7f620803b320, thd=0x7f6208000b00, nextp=0x7f625579eb64) at /data/src/10.3/sql/sp_head.cc:3594
 
#28 0x00005647114229cb in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f620803b368, thd=0x7f6208000b00, nextp=0x7f625579eb64, open_tables=false, instr=0x7f620803b320) at /data/src/10.3/sql/sp_head.cc:3322
 
#29 0x0000564711423150 in sp_instr_stmt::execute (this=0x7f620803b320, thd=0x7f6208000b00, nextp=0x7f625579eb64) at /data/src/10.3/sql/sp_head.cc:3500
 
#30 0x000056471141cecb in sp_head::execute (this=0x7f6208039648, thd=0x7f6208000b00, merge_da_on_success=false) at /data/src/10.3/sql/sp_head.cc:1354
 
#31 0x000056471141e04d in sp_head::execute_trigger (this=0x7f6208039648, thd=0x7f6208000b00, db_name=0x7f6208075108, table_name=0x7f6208075118, grant_info=0x7f6208039290) at /data/src/10.3/sql/sp_head.cc:1763
 
#32 0x00005647115e43cc in Table_triggers_list::process_triggers (this=0x7f620800e338, thd=0x7f6208000b00, event=TRG_EVENT_INSERT, time_type=TRG_ACTION_BEFORE, old_row_is_record1=true) at /data/src/10.3/sql/sql_trigger.cc:2212
 
#33 0x00005647114763d0 in fill_record_n_invoke_before_triggers (thd=0x7f6208000b00, table=0x7f62080a6f30, ptr=0x7f6208006a30, values=..., ignore_errors=false, event=TRG_EVENT_INSERT) at /data/src/10.3/sql/sql_base.cc:8623
 
#34 0x00005647114b8dcc in mysql_insert (thd=0x7f6208000b00, table_list=0x7f6208014dc8, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /data/src/10.3/sql/sql_insert.cc:1006
 
#35 0x00005647114fab3a in mysql_execute_command (thd=0x7f6208000b00) at /data/src/10.3/sql/sql_parse.cc:4730
 
#36 0x00005647115058a5 in mysql_parse (thd=0x7f6208000b00, rawbuf=0x7f6208014ce8 "INSERT INTO t1 VALUES (1)", length=25, parser_state=0x7f62557a05f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8095
 
#37 0x00005647114f2a59 in dispatch_command (command=COM_QUERY, thd=0x7f6208000b00, packet=0x7f6208168451 "INSERT INTO t1 VALUES (1)", packet_length=25, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1854
 
#38 0x00005647114f1431 in do_command (thd=0x7f6208000b00) at /data/src/10.3/sql/sql_parse.cc:1396
 
#39 0x000056471165974f in do_handle_one_connection (connect=0x564713b3b7b0) at /data/src/10.3/sql/sql_connect.cc:1403
 
#40 0x00005647116594d3 in handle_one_connection (arg=0x564713b3b7b0) at /data/src/10.3/sql/sql_connect.cc:1309
 
#41 0x0000564711af52e1 in pfs_spawn_thread (arg=0x564713b7d150) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#42 0x00007f625e131494 in start_thread (arg=0x7f62557a1700) at pthread_create.c:333
 
#43 0x00007f625c51793f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Test case based on alice's reproducer from the comment

--source include/have_innodb.inc
 
 
 
CREATE SEQUENCE s1 ENGINE=InnoDB;
 
ALTER TABLE s1 FORCE;
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
CREATE TABLE t2 (b VARCHAR(64)) ENGINE=MyISAM;
 
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PARTITIONS;
 
INSERT INTO t1 VALUES (1);
 
 
 
# Cleanup
 
DROP TABLE t1, t2, s1;

Reproducible with at least MyISAM and Aria for base tables, and with InnoDB for the sequence; but not vice versa.
No visible effect on a non-debug build.

 Comments   
Comment by Alice Sherepa [ 2019-02-13 ]

also on 10.4 4e7ee166a9c76eb35:

mysqld: /10.4/sql/transaction.cc:507: bool trans_commit_stmt(THD*): Assertion `! thd->in_sub_stmt' failed.
 
 
 
/10.4/sql/mysqld(_Z17trans_commit_stmtP3THD+0x73)[0x5616b0fa96f4]
 
sql/transaction.cc:509(trans_commit_stmt(THD*))[0x5616b104b78c]
 
sql/sql_sequence.cc:495(SEQUENCE::read_initial_values(TABLE*))[0x5616b18fab3c]
 
sql/ha_sequence.cc:114(ha_sequence::open(char const*, int, unsigned int))[0x5616b1163001]
 
sql/handler.cc:2897(handler::ha_open(TABLE*, char const*, int, unsigned int, st_mem_root*, List<String>*))[0x5616b0f44b3a]
 
sql/table.cc:3520(open_table_from_share(THD*, TABLE_SHARE*, st_mysql_const_lex_string const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, List<String>*))[0x5616b0d90b89]
 
sql/sql_base.cc:2035(open_table(THD*, TABLE_LIST*, Open_table_context*))[0x5616b0d93e8e]
 
sql/sql_base.cc:3728(open_and_process_table(THD*, LEX*, TABLE_LIST*, unsigned int*, unsigned int, Prelocking_strategy*, bool, Open_table_context*))[0x5616b0d95348]
 
sql/sql_base.cc:4261(open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x5616b0d8c937]
 
sql/sql_base.h:258(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x5616b0d97398]
 
sql/sql_base.cc:5206(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int, unsigned int))[0x5616b0d9756f]
 
sql/sql_base.cc:5256(open_tables_only_view_structure(THD*, TABLE_LIST*, bool))[0x5616b0ed0193]
 
sql/sql_show.cc:4582(fill_schema_table_by_open(THD*, st_mem_root*, bool, TABLE*, st_schema_table*, st_mysql_const_lex_string*, st_mysql_const_lex_string*, Open_tables_backup*, bool))[0x5616b0ed1af7]
 
sql/sql_show.cc:5220(get_all_tables(THD*, TABLE_LIST*, Item*))[0x5616b0ee2cb0]
 
sql/sql_show.cc:8812(get_schema_tables_result(JOIN*, enum_schema_table_state))[0x5616b0e7b0c8]
 
sql/sql_select.cc:4056(JOIN::exec_inner())[0x5616b0e7a750]
 
sql/sql_select.cc:3889(JOIN::exec())[0x5616b0e7ba06]
 
sql/sql_select.cc:4295(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*))[0x5616b0e6d38b]
 
sql/sql_select.cc:385(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5616b0e2f5fd]
 
sql/sql_parse.cc:4859(mysql_execute_command(THD*))[0x5616b0d49fac]
 
sql/sp_head.cc:3578(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x5616b0d493d5]
 
sql/sp_head.cc:3306(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x5616b0d49b52]
 
sql/sp_head.cc:3484(sp_instr_stmt::execute(THD*, unsigned int*))[0x5616b0d43848]
 
sql/sp_head.cc:1337(sp_head::execute(THD*, bool))[0x5616b0d449af]
 
sql/sp_head.cc:1746(sp_head::execute_trigger(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_grant_info*))[0x5616b0f1c034]
 
sql/sql_trigger.cc:2202(Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool))[0x5616b0da03e0]
 
sql/sql_base.cc:8744(fill_record_n_invoke_before_triggers(THD*, TABLE*, Field**, List<Item>&, bool, trg_event_type))[0x5616b0de5ec7]
 
sql/sql_insert.cc:1010(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool))[0x5616b0e2ed67]
 
sql/sql_parse.cc:4752(mysql_execute_command(THD*))[0x5616b0e3ad7f]
 
sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5616b0e26295]
 
sql/sql_parse.cc:1808(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5616b0e24b99]
 
sql/sql_parse.cc:1356(do_command(THD*))[0x5616b0f93e5e]
 
sql/sql_connect.cc:1399(do_handle_one_connection(CONNECT*))[0x5616b0f93b9c]
 
sql/sql_connect.cc:1303(handle_one_connection)[0x5616b18bbe7f]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fe1b92976ba]
 
x86_64/clone.S:111(clone)[0x7fe1b852841d]
 
 
 
Query (0x7fe1218e7978): INSERT INTO transforms.trigger226360  SELECT * FROM INFORMATION_SCHEMA.`PARTITIONS` AS table1 WHERE table1.`SUBPARTITION_METHOD` IN ( 5, 8, 3 ) LIMIT 167

Comment by Alice Sherepa [ 2019-02-14 ]

steps to reproduce:

use mysql;
 
source mysql_db.sql
 
use test
 
source test_db.sql
 
INSERT INTO t1 VALUES (1);


10.3/src/sql/transaction.cc:512: bool trans_commit_stmt(THD*): Assertion `! thd->in_sub_stmt' failed.
 
sql/sql_class.h:4759(THD::merge_unsafe_rollback_flags())[0x5613821c00b7]
 
sql/sql_sequence.cc:493(SEQUENCE::read_initial_values(TABLE*))[0x5613822edb1d]
 
sql/ha_sequence.cc:114(ha_sequence::open(char const*, int, unsigned int))[0x56138350d043]
 
sql/handler.cc:2733(handler::ha_open(TABLE*, char const*, int, unsigned int, st_mem_root*, List<String>*))[0x5613824d208e]
 
sql/table.cc:3495(open_table_from_share(THD*, TABLE_SHARE*, st_mysql_const_lex_string const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, List<String>*))[0x5613820fa435]
 
sql/sql_base.cc:1975(open_table(THD*, TABLE_LIST*, Open_table_context*))[0x561381d4c638]
 
sql/sql_base.cc:3596(open_and_process_table)[0x561381d55cdf]
 
sql/sql_base.cc:5059(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int, unsigned int))[0x561381d588fd]
 
sql/sql_base.cc:5110(open_tables_only_view_structure(THD*, TABLE_LIST*, bool))[0x561381d58c34]
 
sql/sql_show.cc:4566(fill_schema_table_by_open(THD*, st_mem_root*, bool, TABLE*, st_schema_table*, st_mysql_const_lex_string*, st_mysql_const_lex_string*, Open_tables_backup*, bool))[0x561381fd0e84]
 
sql/sql_show.cc:5204(get_all_tables(THD*, TABLE_LIST*, Item*))[0x56138200d54d]
 
sql/sql_show.cc:8802(get_schema_tables_result(JOIN*, enum_schema_table_state))[0x561382012ae4]
 
sql/sql_select.cc:4002(JOIN::exec_inner())[0x561381fbaf7e]
 
sql/sql_select.cc:3835(JOIN::exec())[0x561381fbc9b6]
 
sql/sql_select.cc:4241(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*))[0x561381fb7c4f]
 
sql/sql_select.cc:385(handle_select(THD*, LEX*, select_result*, unsigned long))[0x561381fb846f]
 
sql/sql_parse.cc:4837(mysql_execute_command(THD*))[0x561381e9489a]
 
sql/sp_head.cc:3596(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x561381caa5c7]
 
sql/sp_head.cc:3322(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x561381cc719f]
 
sql/sp_head.cc:3500(sp_instr_stmt::execute(THD*, unsigned int*))[0x561381cc91e2]
 
sql/sp_head.cc:1354(sp_head::execute(THD*, bool))[0x561381cb5cab]
 
sql/sp_head.cc:1763(sp_head::execute_trigger(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_grant_info*))[0x561381cb7e18]
 
sql/sql_trigger.cc:2212(Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool))[0x5613820875a3]
 
sql/sql_base.cc:8623(fill_record_n_invoke_before_triggers(THD*, TABLE*, Field**, List<Item>&, bool, trg_event_type))[0x561381d66d14]
 
sql/sql_insert.cc:1006(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool))[0x561381e0b2cf]
 
sql/sql_parse.cc:4728(mysql_execute_command(THD*))[0x561381e93d84]
 
sql/sql_parse.cc:8095(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x561381ea367e]
 
sql/sql_parse.cc:1878(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x561381ea97e2]
 
sql/sql_parse.cc:1396(do_command(THD*))[0x561381eb0b0a]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x561382196105]
 
sql/sql_connect.cc:1311(handle_one_connection)[0x5613821965f1]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7feb3ff5a6ba]
 
x86_64/clone.S:111(clone)[0x7feb3f60541d]
 
 
 
Query (0x625000192988): INSERT INTO t2   SELECT * FROM INFORMATION_SCHEMA.PARTITIONS

Comment by Elena Stepanova [ 2019-02-14 ]

alice, thanks. A simplified test case based on the above has been added to the description.

Comment by Robert Bindar [ 2019-04-15 ]

Hi elenst. I'm trying to come up with a fix or at least an explanation for this bug. Is ALTER TABLE .. FORCE allowed for a sequence? The KB docs don't say anything about this being a valid use case. I totally agree the server shouldn't crash if the user wrongly forces a table rebuild on a sequence.

Comment by Elena Stepanova [ 2019-04-15 ]

robertbindar,

Sure it is. Everything that is allowed is allowed, and unlike many, this operation is not even meaningless. It's still a table, you might want to have it rebuilt on whatever reason, ALTER... FORCE is the straightforward way to attempt to do so.

Comment by Robert Bindar [ 2019-04-15 ]

Thanks for the quick reply, elenst.
I can't see any particular use case for running ALTER .. FORCE on a sequence except for dropping the current cache of the sequence, and couldn't find ALTER .. FORCE in the list of allowed table operations for sequences here, this is where my question came from.
The bug is not easy to trace, I can't simplify the test case anymore and still get a crash from the server. So I was trying to see if maybe ALTER .. FORCE has side effects because it wasn't designed to be executed on sequences.

Comment by Elena Stepanova [ 2019-04-15 ]

It was most certainly not designed to be executed on sequences, as it was implemented years before sequences. The goal is to adjust it so that it could work with the new stuff.
If you have a strong opinion that ALTER ... FORCE is useless for sequences, you can raise it and discuss with serg the possibility of forbidding it (throwing an error that it is not allowed for sequences). I'm not going to argue if you come to such an agreement, although I imagine it might be a bit problematic since it's already well after GA for 10.3.

For test simplification – there are only 6 primary SQL statements in that test, it rarely gets much better than that.

Comment by Robert Bindar [ 2019-04-15 ]

I don't have a strong opinion on ALTER .. FORCE being useless, mostly probably there are reasons I don't yet understand for it not being silently ignored / forbidden.

Comment by Robert Bindar [ 2019-12-02 ]

Merged with 3aa2a739209a021ba2e542e4d6c7c29ccecf97a0.

Generated at Thu Feb 08 08:25:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.