Bug #57623 | subquery within before insert trigger causes crash (semijoin=on) | ||
---|---|---|---|
Submitted: | 21 Oct 2010 10:43 | Modified: | 2 Mar 2011 3:16 |
Reporter: | SaiKumar V | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
Version: | mysql-next-mr-opt-backporting | OS: | Any |
Assigned to: | Roy Lyseng | CPU Architecture: | Any |
Tags: | optimizer_switch, semijoin |
[21 Oct 2010 10:43]
SaiKumar V
[21 Oct 2010 10:44]
SaiKumar V
semijoin trigger crash
Attachment: semijoin_crash.txt (text/plain), 10.88 KiB.
[21 Oct 2010 13:26]
SaiKumar V
Trace from Crash:- key_buffer_size=1048576 read_buffer_size=131072 max_used_connections=3 max_threads=151 thread_count=2 connection_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60163 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0xaf11110 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0xa8c7b388 thread_stack 0x30000 (my_print_stacktrace+0x32)[0x8633ae3] (handle_segfault+0x2d1)[0x815356b] [0x194420] (_ZN4JOIN18flatten_subqueriesEv+0x602)[0x82067a6] (_ZN4JOIN8optimizeEv+0xab)[0x82154d3] (_ZN30subselect_single_select_engine4execEv+0xbb)[0x8385e1d] (_ZN14Item_subselect4execEv+0x8c)[0x8386826] (_ZN21Item_exists_subselect8val_boolEv+0x49)[0x8383163] (_ZN12Item_cond_or7val_intEv+0x65)[0x8337631] (_ZN4Item8val_boolEv+0x50)[0x831cf54] (_ZN13Item_cond_and7val_intEv+0x65)[0x83376fb] [0x81f72c5] (_Z10sub_selectP4JOINP13st_join_tableb+0x149)[0x81f7a5d] [0x82046bb] (_ZN4JOIN4execEv+0xa2b)[0x821d7ed] (_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x30d)[0x8219829] (_Z13handle_selectP3THDP3LEXP13select_resultm+0x1e4)[0x821f1f4] (_Z21mysql_execute_commandP3THD+0x2a73)[0x81cac1f] (_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x11)[0x83d845f] (_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x17b)[0x83d8dfb] (_ZN13sp_instr_stmt7executeEP3THDPj+0x1c2)[0x83d9596] (_ZN7sp_head7executeEP3THD+0x51b)[0x83db91b] (_ZN7sp_head15execute_triggerEP3THDPK19st_mysql_lex_stringS4_P13st_grant_info+0x316)[0x83dd5ea] (_ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_action_time_typeb+0x16f)[0x82520c5] (_Z36fill_record_n_invoke_before_triggersP3THDPP5FieldR4ListI4ItemEbP19Table_triggers_list14trg_event_type+0x60)[0x817c438] (_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0xa03)[0x81b8175] (_Z21mysql_execute_commandP3THD+0x27a4)[0x81ca950] (_Z11mysql_parseP3THDPcjP12Parser_state+0x1e2)[0x81cff2e] (_Z16dispatch_command19enum_server_commandP3THDPcj+0x905)[0x81d1261] (_Z10do_commandP3THD+0x242)[0x81d24be] (_Z24do_handle_one_connectionP3THD+0x18c)[0x8293042] (handle_one_connection+0x25)[0x8293125] /lib/libpthread.so.0[0x81443b] /lib/libc.so.6(clone+0x5e)[0x76bfde] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xb04ee90 = INSERT INTO transforms.trigger2994 SELECT ( ( table1 . `col_int_nokey` ) * ( table2 . `pk` ) ) AS field1 FROM ( C AS table1 INNER JOIN CC AS table2 ON (table2 . `pk` = table1 . `col_int_key` ) ) WHERE ( EXISTS ( SELECT DISTINCT SUBQUERY1_t1 . `col_int_key` AS SUBQUERY1_field1 FROM ( BB AS SUBQUERY1_t1 STRAIGHT_JOIN ( C AS SUBQUERY1_t2 INNER JOIN B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `col_int_nokey` ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `pk` AND ( SUBQUERY1_t1 . `col_varchar_nokey` , SUBQUERY1_t1 . `col_varchar_nokey` ) IN ( SELECT CHILD_SUBQUERY1_t1 . `col_varchar_nokey` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t1 . `col_varchar_key` AS CHILD_SUBQUERY1_field2 FROM ( view_C AS CHILD_SUBQUERY1_t1 RIGHT OUTER JOIN ( ( C AS CHILD_SUBQUERY1_t2 LEFT JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) ) ON (CHILD_SUBQUERY1_t3 . `pk` = CHILD_SUBQUERY1_t2 . `pk` ) ) WHERE ( CHILD_SUBQUERY1_t1 . ` thd->thread_id=7 thd->killed=NOT_KILLED
[27 Oct 2010 13:49]
Roy Lyseng
Preliminary analysis shows that this problem has the same root cause as one of the problems described in WL5561, the problem described in the section titled "Assert failure in backout_nj_sj_state()". The problem is that changes to "prep_on_expr" are not applied permanently. Hence, it seems that the problem is related to INNER JOIN specifications, as well as OUTER JOINs.
[13 Jan 2011 14:21]
Roy Lyseng
The problem seems to be of a slightly more general nature: It may happen for every subquery for which semijoin transformation is rejected, prepared (non-convential) mode is active, and the subquery predicate is part of a join condition attached to a join nest.
[13 Jan 2011 14:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128664 3323 Roy Lyseng 2011-01-13 Bug#57623: subquery within before insert trigger causes crash (semijoin=on) Crash when attempting to transform a subquery using IN_TO_EXISTS inside JOIN::flatten_subqueries(), when semijoin transformation is impossible for the subquery, and in prepared (non-conventional) mode. The problem is that replace_subcondition() is attempted with prep_on_expr as "tree" argument, but prep_on_expr has not yet been set. prep_on_expr is set for prepared statements in fix_prepare_info_in_table_list(), but only for table objects, not for join nest objects. In this case, prep_on_expr is not set before the subsequently called simplify_joins() function, which will propagate the desired information. mysql-test/include/subquery_sj.inc Added test case for bug#57623. mysql-test/r/subquery_sj_all.result Added test results for bug#58561. mysql-test/r/subquery_sj_all_jcl6.result Added test results for bug#58561. mysql-test/r/subquery_sj_all_jcl7.result I think you are starting to get the picture by now... mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result sql/sql_select.cc Added test on *tree being non-NULL before calling replace_subcondition() in JOIN::flatten_subqueries().
[24 Jan 2011 16:03]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/129466 3329 Roy Lyseng 2011-01-24 Bug#57623: subquery within before insert trigger causes crash (semijoin=on) Crash when attempting to transform a subquery using IN_TO_EXISTS inside JOIN::flatten_subqueries(), when semijoin transformation is impossible for the subquery, and in prepared (non-conventional) mode. The problem is that replace_subcondition() is attempted with prep_on_expr as "tree" argument, but prep_on_expr has not yet been set. prep_on_expr is set for prepared statements in fix_prepare_info_in_table_list(), but only for table objects, not for join nest objects. In this case, prep_on_expr is not set before the subsequently called simplify_joins() function, which will propagate the desired information. mysql-test/include/subquery_sj.inc Added test case for bug#57623. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Added test results for bug#57623. sql/sql_select.cc Added test on *tree being non-NULL before calling replace_subcondition() in JOIN::flatten_subqueries().
[25 Jan 2011 7:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/129508 3329 Roy Lyseng 2011-01-25 Bug#57623: subquery within before insert trigger causes crash (semijoin=on) Crash when attempting to transform a subquery using IN_TO_EXISTS inside JOIN::flatten_subqueries(), when semijoin transformation is impossible for the subquery, and in prepared (non-conventional) mode. The problem is that replace_subcondition() is attempted with prep_on_expr as "tree" argument, but prep_on_expr has not yet been set. prep_on_expr is set for prepared statements in fix_prepare_info_in_table_list(), but only for table objects, not for join nest objects. In this case, prep_on_expr is not set before the subsequently called simplify_joins() function, which will propagate the desired information. mysql-test/include/subquery_sj.inc Added test case for bug#57623. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Added test results for bug#57623. sql/sql_select.cc Added test on *tree being non-NULL before calling replace_subcondition() in JOIN::flatten_subqueries().
[27 Jan 2011 11:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/129743 3334 Roy Lyseng 2011-01-27 Bug#57623: subquery within before insert trigger causes crash (semijoin=on) Crash when attempting to transform a subquery using IN_TO_EXISTS inside JOIN::flatten_subqueries(), when semijoin transformation is impossible for the subquery, and in prepared (non-conventional) mode. The problem is that replace_subcondition() is attempted with prep_on_expr as "tree" argument, but prep_on_expr has not yet been set. prep_on_expr is set for prepared statements in fix_prepare_info_in_table_list(), but only for table objects, not for join nest objects. In this case, prep_on_expr is not set before the subsequently called simplify_joins() function, which will propagate the desired information. mysql-test/include/subquery_sj.inc Added test case for bug#57623. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Added test results for bug#57623. sql/sql_select.cc Added test on *tree being non-NULL before calling replace_subcondition() in JOIN::flatten_subqueries(). Added DBUG_ASSERT to validate that the assumption for this fix is true.
[2 Feb 2011 13:24]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:jorgen.loland@oracle.com-20110202132358-khrjqzdcs3jrda3i) (version source revid:jorgen.loland@oracle.com-20110202132358-khrjqzdcs3jrda3i) (merge vers: 5.6.2) (pib:24)
[2 Mar 2011 3:16]
Paul DuBois
Noted in 5.6.2 changelog. With semi-join optimization enabled, the server crashed when attempting to transform a subquery using IN_TO_EXISTS inside JOIN::flatten_subqueries(), when semi-join transformation is impossible for the subquery, and in prepared mode. CHANGESET - http://lists.mysql.com/commits/129743
[8 Apr 2011 14:53]
Paul DuBois
Correction. Bug does not affect 5.6.x users. No changelog entry needed.