Assertion `thd == thd_param' failed at Item_subselect::fix_fields(THD*, Item**) on executing UPDATE with aggregate function in subselect

Bug #910083 reported by Elena Stepanova on 2011-12-30
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Critical
Igor Babaev

Bug Description

mysqld: item_subselect.cc:215: virtual bool Item_subselect::fix_fields(THD*, Item**): Assertion `thd == thd_param' failed.
111230 16:17:49 [ERROR] mysqld got signal 6 ;

#8 0xb74c2014 in __assert_fail () from /lib/libc.so.6
#9 0x08278924 in Item_subselect::fix_fields (this=0x94c6548, thd_param=0x93ee4a0, ref=0x94c6644)
    at item_subselect.cc:215
#10 0x0833d204 in setup_fields (thd=0x93ee4a0, ref_pointer_array=0x0, fields=...,
    mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x0, allow_sum_func=false) at sql_base.cc:7790
#11 0x083a9811 in mysql_update (thd=0x93ee4a0, table_list=0x94c5990, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:317
#12 0x082e58bb in mysql_execute_command (thd=0x93ee4a0) at sql_parse.cc:3109
#13 0x084cee27 in sp_instr_stmt::exec_core (this=0x94c6648, thd=0x93ee4a0, nextp=0xae93a188)
    at sp_head.cc:2976
#14 0x084ce760 in sp_lex_keeper::reset_lex_and_exec_core (this=0x94c6670, thd=0x93ee4a0,
    nextp=0xae93a188, open_tables=false, instr=0x94c6648) at sp_head.cc:2794
#15 0x084cec04 in sp_instr_stmt::execute (this=0x94c6648, thd=0x93ee4a0, nextp=0xae93a188)
    at sp_head.cc:2919
#16 0x084cb188 in sp_head::execute (this=0x94c4af8, thd=0x93ee4a0) at sp_head.cc:1283
#17 0x084cbb0d in sp_head::execute_trigger (this=0x94c4af8, thd=0x93ee4a0, db_name=0x944fff4,
    table_name=0x944fffc, grant_info=0x94c47a4) at sp_head.cc:1586
#18 0x084deb58 in Table_triggers_list::process_triggers (this=0x94c4710, thd=0x93ee4a0,
    event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true)
    at sql_trigger.cc:2132
#19 0x083aa9fd in mysql_update (thd=0x93ee4a0, table_list=0x945d540, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:719
#20 0x082e58bb in mysql_execute_command (thd=0x93ee4a0) at sql_parse.cc:3109
#21 0x082ee673 in mysql_parse (thd=0x93ee4a0, rawbuf=0x945d4a0 "UPDATE t1 SET a = 2", length=19,
    found_semicolon=0xae93b234) at sql_parse.cc:6149
#22 0x082e1076 in dispatch_command (command=COM_QUERY, thd=0x93ee4a0,
    packet=0x94471d9 "UPDATE t1 SET a = 2", packet_length=19) at sql_parse.cc:1227
#23 0x082e0520 in do_command (thd=0x93ee4a0) at sql_parse.cc:922
#24 0x082dd4e5 in handle_one_connection (arg=0x93ee4a0) at sql_connect.cc:1193
#25 0xb7750b25 in start_thread () from /lib/libpthread.so.0
#26 0xb757134e in clone () from /lib/libc.so.6

Query (0x94b8ab8): UPDATE article SET amountImage = ( SELECT COUNT(*) FROM picture WHERE articleId = NEW.articleId ) WHERE id = NEW.articleId
Connection ID (thread ID): 2
Status: NOT_KILLED

bzr version-info
revision-id: <email address hidden>
date: 2011-12-28 12:12:48 +0400
build-date: 2011-12-30 16:34:16 +0400
revno: 3371
branch-nick: maria-5.3

Also reproducible on 5.3.2 and 5.3.3 releases (the original crash was reported in winqual for 5.3.2 release)
Could not reproduce on 5.2 and 5.5, but since the test case involves a race condition, it's not a definite sign it doesn't exist there.

On 5.3 release versions it generally requires more rows in the tables to hit the crash. When it happens, it represents with the following stack trace (for 5.3.3):

#3 0x0825ab96 in handle_segfault (sig=11) at mysqld.cc:2838
#4 <signal handler called>
#5 subselect_single_select_engine::prepare (this=0x92a5968) at item_subselect.cc:2787
#6 0x0820d9cf in Item_subselect::fix_fields (this=0x92a58a0, thd_param=0x91f63a8, ref=0x92a599c)
    at item_subselect.cc:234
#7 0x082ad2c0 in setup_fields (thd=0x91f63a8, ref_pointer_array=0x0, fields=...,
    mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x0, allow_sum_func=false) at sql_base.cc:7790
#8 0x08307d8b in mysql_update (thd=0x91f63a8, table_list=0x92a4ce8, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:317
#9 0x08266e53 in mysql_execute_command (thd=0x91f63a8) at sql_parse.cc:3109
#10 0x083e4b01 in sp_instr_stmt::exec_core (this=0x92a59a0, thd=0x91f63a8, nextp=0xae9a9b84)
    at sp_head.cc:2976
#11 0x083e4dc9 in sp_lex_keeper::reset_lex_and_exec_core (this=0x92a59c4, thd=0x91f63a8,
    nextp=0xae9a9b84, open_tables=false, instr=0x92a59a0) at sp_head.cc:2794
#12 0x083eb2cc in sp_instr_stmt::execute (this=0x92a59a0, thd=0x91f63a8, nextp=0xae9a9b84)
    at sp_head.cc:2919
#13 0x083e91c7 in sp_head::execute (this=0x92a3e58, thd=0x91f63a8) at sp_head.cc:1283
#14 0x083e9d43 in sp_head::execute_trigger (this=0x92a3e58, thd=0x91f63a8, db_name=0x9281e64,
    table_name=0x9281e6c, grant_info=0x92a3b04) at sp_head.cc:1586
#15 0x083f399e in Table_triggers_list::process_triggers (this=0x92a3a70, thd=0x91f63a8,
    event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true)
    at sql_trigger.cc:2132
#16 0x08308aaa in mysql_update (thd=0x91f63a8, table_list=0x924b438, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:717
#17 0x08266e53 in mysql_execute_command (thd=0x91f63a8) at sql_parse.cc:3109
#18 0x0826ec45 in mysql_parse (thd=0x91f63a8, rawbuf=0x924b398 "UPDATE t1 SET a = 2", length=19,
    found_semicolon=0xae9aae74) at sql_parse.cc:6149
#19 0x0826ff3d in dispatch_command (command=COM_QUERY, thd=0x91f63a8,
    packet=0x927d631 "UPDATE t1 SET a = 2", packet_length=19) at sql_parse.cc:1227
#20 0x082709ec in do_command (thd=0x91f63a8) at sql_parse.cc:922
#21 0x0825f8b9 in handle_one_connection (arg=0x91f63a8) at sql_connect.cc:1193
#22 0xb77c1b25 in start_thread () from /lib/libpthread.so.0

Also, some variations of the test case on release binaries produce this:

#3 0x0825ab96 in handle_segfault (sig=11) at mysqld.cc:2838
#4 <signal handler called>
#5 setup_tables (thd=0x9259890, context=0x929a91c, from_clause=0x929a9ac, tables=0x929af60,
    leaves=..., select_insert=false, full_table_list=false) at sql_base.cc:7969
#6 0x082b3263 in setup_tables_and_check_access (thd=0x9259890, context=0x929a91c,
    from_clause=0x929a9ac, tables=0x929af60, leaves=..., select_insert=<value optimized out>,
    want_access_first=1, want_access=1, full_table_list=<value optimized out>) at sql_base.cc:8054
#7 0x082d4b73 in JOIN::prepare (this=0x929d920, rref_pointer_array=0x929aa24, tables_init=0x929af60,
    wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0,
    proc_param_init=0x0, select_lex_arg=0x929a8e8, unit_arg=0x929aac8) at sql_select.cc:586
#8 0x0820ecc5 in subselect_single_select_engine::prepare (this=0x929b270) at item_subselect.cc:2789
#9 0x0820d9cf in Item_subselect::fix_fields (this=0x929b1a8, thd_param=0x91f6320, ref=0x929b2a4)
    at item_subselect.cc:234
#10 0x082ad2c0 in setup_fields (thd=0x91f6320, ref_pointer_array=0x0, fields=...,
    mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x0, allow_sum_func=false) at sql_base.cc:7790
#11 0x08307d8b in mysql_update (thd=0x91f6320, table_list=0x929a5f0, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:317
#12 0x08266e53 in mysql_execute_command (thd=0x91f6320) at sql_parse.cc:3109
#13 0x083e4b01 in sp_instr_stmt::exec_core (this=0x929b2a8, thd=0x91f6320, nextp=0xae9ceb84)
    at sp_head.cc:2976
#14 0x083e4dc9 in sp_lex_keeper::reset_lex_and_exec_core (this=0x929b2cc, thd=0x91f6320,
    nextp=0xae9ceb84, open_tables=false, instr=0x929b2a8) at sp_head.cc:2794
#15 0x083eb2cc in sp_instr_stmt::execute (this=0x929b2a8, thd=0x91f6320, nextp=0xae9ceb84)
    at sp_head.cc:2919
#16 0x083e91c7 in sp_head::execute (this=0x9299760, thd=0x91f6320) at sp_head.cc:1283
#17 0x083e9d43 in sp_head::execute_trigger (this=0x9299760, thd=0x91f6320, db_name=0x9281be4,
    table_name=0x9281bec, grant_info=0x929940c) at sp_head.cc:1586
#18 0x083f399e in Table_triggers_list::process_triggers (this=0x9299378, thd=0x91f6320,
    event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true)
    at sql_trigger.cc:2132
#19 0x08308aaa in mysql_update (thd=0x91f6320, table_list=0x924b4a0, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551613, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:717
#20 0x08266e53 in mysql_execute_command (thd=0x91f6320) at sql_parse.cc:3109
#21 0x0826ec45 in mysql_parse (thd=0x91f6320, rawbuf=0x924b400 "UPDATE t1 SET a = 2", length=19,
    found_semicolon=0xae9cfe74) at sql_parse.cc:6149
#22 0x0826ff3d in dispatch_command (command=COM_QUERY, thd=0x91f6320,
    packet=0x927d3b1 "UPDATE t1 SET a = 2", packet_length=19) at sql_parse.cc:1227
#23 0x082709ec in do_command (thd=0x91f6320) at sql_parse.cc:922
#24 0x0825f8b9 in handle_one_connection (arg=0x91f6320) at sql_connect.cc:1193
#25 0xb77e6b25 in start_thread () from /lib/libpthread.so.0
#26 0xb762634e in clone () from /lib/libc.so.6

or this:

#3 0x0825ab96 in handle_segfault (sig=11) at mysqld.cc:2838
#4 <signal handler called>
#5 build_equal_items_for_cond (thd=0x925d890, cond=0x92b6e40, inherited=0x0) at sql_select.cc:11057
#6 0x082d450b in build_equal_items (thd=0x925d890, cond=0x92b6e40, inherited=0x0,
    join_list=0x92b6454, cond_equal_ref=0x929b5e4) at sql_select.cc:11179
#7 0x082d4669 in optimize_cond (join=<value optimized out>, conds=0x1,
    join_list=<value optimized out>, cond_value=0x929b554, cond_equal=0x929b5e4)
    at sql_select.cc:12615
#8 0x082e4b32 in JOIN::optimize (this=0x1) at sql_select.cc:1008
#9 0x08187d5f in st_select_lex::optimize_unflattened_subqueries (this=0x92b59bc) at sql_lex.cc:3136
#10 0x08307e04 in mysql_update (thd=0x91f6320, table_list=0x92b6070, fields=..., values=...,
    conds=0x92b7258, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:324
#11 0x08266e53 in mysql_execute_command (thd=0x91f6320) at sql_parse.cc:3109
#12 0x083e4b01 in sp_instr_stmt::exec_core (this=0x92b7090, thd=0x91f6320, nextp=0xae870b84)
    at sp_head.cc:2976
#13 0x083e4dc9 in sp_lex_keeper::reset_lex_and_exec_core (this=0x92b70b4, thd=0x91f6320,
    nextp=0xae870b84, open_tables=false, instr=0x92b7090) at sp_head.cc:2794
#14 0x083eb2cc in sp_instr_stmt::execute (this=0x92b7090, thd=0x91f6320, nextp=0xae870b84)
    at sp_head.cc:2919
#15 0x083e91c7 in sp_head::execute (this=0x92b50d8, thd=0x91f6320) at sp_head.cc:1283
#16 0x083e9d43 in sp_head::execute_trigger (this=0x92b50d8, thd=0x91f6320, db_name=0x92404e4,
    table_name=0x92404ec, grant_info=0x9274384) at sp_head.cc:1586
#17 0x083f399e in Table_triggers_list::process_triggers (this=0x92742f0, thd=0x91f6320,
    event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true)
    at sql_trigger.cc:2132
#18 0x08308aaa in mysql_update (thd=0x91f6320, table_list=0x924b4f0, fields=..., values=...,
    conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false) at sql_update.cc:717
#19 0x08266e53 in mysql_execute_command (thd=0x91f6320) at sql_parse.cc:3109
#20 0x0826ec45 in mysql_parse (thd=0x91f6320,
    rawbuf=0x924b400 "UPDATE picture SET articleId = 9 WHERE articleId = 5 OR 1=1", length=59,
    found_semicolon=0xae871e74) at sql_parse.cc:6149
#21 0x0826ff3d in dispatch_command (command=COM_QUERY, thd=0x91f6320,
    packet=0x927d3b1 "UPDATE picture SET articleId = 9 WHERE articleId = 5 OR 1=1", packet_length=59)
    at sql_parse.cc:1227
#22 0x082709ec in do_command (thd=0x91f6320) at sql_parse.cc:922
#23 0x0825f8b9 in handle_one_connection (arg=0x91f6320) at sql_connect.cc:1193
#24 0xb7688b25 in start_thread () from /lib/libpthread.so.0
#25 0xb74c834e in clone () from /lib/libc.so.6

I assume all of them have the same root cause

Elena Stepanova (elenst) wrote :

Minimal optimizer_switch: materialization=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

# The test case involves a concurrency element,
# so it's not 100% deterministic.
# It fails regularly on current maria-5.3 debug build for me,
# but if you are not getting the failure,
# try to increase the number of rows in the tables, it seems to help.
# With more knowledge about possible problem
# it might be possible to create a deterministic test case with sync points,
# and maybe even get rid of the trigger.

# Test case:

SET optimizer_switch = 'materialization=on';

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );

CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW
  UPDATE t2 SET b = ( SELECT COUNT(a) FROM t1 );

INSERT INTO t1
  VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO t2
  VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0);

send
  UPDATE t1 SET a = 3;

connect(con1,localhost,root,,);
  SELECT COUNT(*) FROM t1;
disconnect con1;

connection default;
reap;
UPDATE t1 SET a = 2;

# Cleanup
DROP TABLE t1, t2;

# End of test case

Changed in maria:
assignee: nobody → Timour Katchaounov (timour)
milestone: none → 5.3
Changed in maria:
importance: Undecided → Critical
status: New → Confirmed
Changed in maria:
assignee: Timour Katchaounov (timour) → Igor Babaev (igorb-seattle)
status: Confirmed → In Progress
Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers