Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.14, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
-
CentOS 7
-
5.5.50
Description
Hi- konnichiwa
Database server to crash by the second time SQL execution.
CREATE TABLE `table_A` (
|
`key_code` INT(11) NOT NULL,
|
`value_string` VARCHAR(50) NULL DEFAULT NULL,
|
PRIMARY KEY (`key_code`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
;
|
|
|
CREATE TABLE `table_B` (
|
`key_code` INT(11) NOT NULL,
|
`target_date` DATE NULL DEFAULT NULL,
|
PRIMARY KEY (`key_code`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
;
|
|
|
CREATE TABLE `table_C` (
|
`now_date` DATE NOT NULL,
|
PRIMARY KEY (`now_date`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
;
|
|
|
CREATE VIEW view_B
|
AS
|
SELECT
|
B.key_code,
|
B.target_date
|
FROM
|
table_B B INNER JOIN table_C C ON
|
B.target_date = C.now_date
|
;
|
After creating an environment as described above , and run the next certain SQL statements.
SET @s = 'SELECT A.* FROM table_A A WHERE A.key_code IN (SELECT key_code FROM view_B)';
|
PREPARE stmt FROM @s;
|
EXECUTE stmt; #1st time -> success
|
EXECUTE stmt; #2nd time -> crash
|
DEALLOCATE PREPARE stmt;
|
a) This SQL is Prepare statement
b) Structure of view_B
c) view_B is written in the In statement
Three of the elements of the above , I think to be related to this problem.
|
*** Error Log *** |
May 29 10:40:37 ryokin mysqld: 160529 10:40:37 [ERROR] mysqld got signal 11 ;
|
May 29 10:40:37 ryokin mysqld: This could be because you hit a bug. It is also possible that this binary
|
May 29 10:40:37 ryokin mysqld: or one of the libraries it was linked against is corrupt, improperly built,
|
May 29 10:40:37 ryokin mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
|
May 29 10:40:37 ryokin mysqld: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
May 29 10:40:37 ryokin mysqld: We will try our best to scrape up some info that will hopefully help
|
May 29 10:40:37 ryokin mysqld: diagnose the problem, but since we have already crashed,
|
May 29 10:40:37 ryokin mysqld: something is definitely wrong and this may fail.
|
May 29 10:40:37 ryokin mysqld: Server version: 10.1.14-MariaDB
|
May 29 10:40:37 ryokin mysqld: key_buffer_size=134217728
|
May 29 10:40:37 ryokin mysqld: read_buffer_size=131072
|
May 29 10:40:37 ryokin mysqld: max_used_connections=1
|
May 29 10:40:37 ryokin mysqld: max_threads=153
|
May 29 10:40:37 ryokin mysqld: thread_count=1
|
May 29 10:40:37 ryokin mysqld: It is possible that mysqld could use up to
|
May 29 10:40:37 ryokin mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467113 K bytes of memory
|
May 29 10:40:37 ryokin mysqld: Hope that's ok; if not, decrease some variables in the equation.
|
May 29 10:40:37 ryokin mysqld: Thread pointer: 0x0x7f3d61fc2008
|
May 29 10:40:37 ryokin mysqld: Attempting backtrace. You can use the following information to find out
|
May 29 10:40:37 ryokin mysqld: where mysqld died. If you see no messages after this, something went
|
May 29 10:40:37 ryokin mysqld: terribly wrong...
|
May 29 10:40:37 ryokin mysqld: stack_bottom = 0x7f3d66bac130 thread_stack 0x48400
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f3d676bf1be]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f3d671eaf0d]
|
May 29 10:40:37 ryokin mysqld: /lib64/libpthread.so.0(+0xf100)[0x7f3d66809100]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN13st_select_lex17mark_as_dependentEP3THDPS_P4Item+0xbd)[0x7f3d6705d5fd]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN10Item_field15fix_outer_fieldEP3THDPP5FieldPP4Item+0x83d)[0x7f3d67209ead]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x41f)[0x7f3d6720a7ef]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x17c)[0x7f3d6702b68c]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x312)[0x7f3d670b4b12]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN18st_select_lex_unit7prepareEP3THDP13select_resultm+0x953)[0x7f3d67101ca3]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_Z21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST+0x1f5)[0x7f3d67047f85]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xe4)[0x7f3d67048e34]
|
May 29 10:40:37 ryokin mysqld: /usr/sbin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x7f3d6705fc37]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0xd3)[0x7f3d670b48d3]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x924)[0x7f3d670bff44]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x245)[0x7f3d670c0205]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(+0x428c91)[0x7f3d67062c91]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5f8f)[0x7f3d6706e89f]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b6)[0x7f3d67085816]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(+0x44b983)[0x7f3d67085983]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0xbb)[0x7f3d67085f9b]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x17ad)[0x7f3d6706a0bd]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x28e)[0x7f3d6707217e]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x25db)[0x7f3d6707563b]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7f3d67075eb9]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7f3d6713962a]
|
May 29 10:40:38 ryokin mysqld: /usr/sbin/mysqld(handle_one_connection+0x40)[0x7f3d67139800]
|
May 29 10:40:38 ryokin mysqld: /lib64/libpthread.so.0(+0x7dc5)[0x7f3d66801dc5]
|
May 29 10:40:38 ryokin mysqld: /lib64/libc.so.6(clone+0x6d)[0x7f3d64c2428d]
|
May 29 10:40:38 ryokin mysqld: Trying to get some variables.
|
May 29 10:40:38 ryokin mysqld: Some pointers may be invalid and cause the dump to abort.
|
May 29 10:40:38 ryokin mysqld: Query (0x7f3d46f6d0e8): SELECT A.* FROM table_A A WHERE A.key_code IN (SELECT key_code FROM view_B)
|
May 29 10:40:38 ryokin mysqld: Connection ID (thread ID): 2
|
May 29 10:40:38 ryokin mysqld: Status: NOT_KILLED
|
May 29 10:40:38 ryokin mysqld: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=on,exists_to_in=on
|
May 29 10:40:38 ryokin mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
|
May 29 10:40:38 ryokin mysqld: information that should help you find out what is causing the crash.
|
May 29 10:40:38 ryokin systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE
|
May 29 10:40:38 ryokin systemd: Unit mariadb.service entered failed state.
|
May 29 10:40:38 ryokin systemd: mariadb.service failed.
|
Thanks K.S
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
The failing assertion:
/*
|
This assert is to ensure we have an outer contex when *from_field
|
is set.
|
If this would not be the case, we would assert in mark_as_dependent
|
as last_checked_countex == context
|
*/
|
DBUG_ASSERT(outer_context || !*from_field ||
|
*from_field == not_found_field);
|
member variables
(gdb) print outer_context
|
$6 = (Name_resolution_context *) 0x0
|
(gdb) p*from_field
|
$8 = (Field *) 0x7fff7506f648
|
(gdb) p *from_field == not_found_field
|
$10 = false
|
(gdb) p this->name
|
$12 = 0x7fff751046c8 "target_date"
|
stack trace
#3 0x00007ffff677b0f2 in __GI___assert_fail (assertion=0xe03340 "outer_context || !*from_field || *from_field == not_found_field", file=0xe02d08 "/home/psergey/dev-git/5.5/sql/item.cc", line=4888, function=0xe04ea0 <Item_field::fix_outer_field(THD*, Field**, Item**)::__PRETTY_FUNCTION__> "int Item_field::fix_outer_field(THD*, Field**, Item**)") at assert.c:101
|
#4 0x00000000007ef3d2 in Item_field::fix_outer_field (this=0x7fff75104578, thd=0x7fff84bf5060, from_field=0x7ffff7f6a468, reference=0x7fff751046c0) at /home/psergey/dev-git/5.5/sql/item.cc:4887
|
#5 0x00000000007f066d in Item_field::fix_fields (this=0x7fff75104578, thd=0x7fff84bf5060, reference=0x7fff751046c0) at /home/psergey/dev-git/5.5/sql/item.cc:5289
|
#6 0x00000000005b1c4c in setup_fields (thd=0x7fff84bf5060, ref_pointer_array=0x7fff75112c58, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fff75045ca0, allow_sum_func=true) at /home/psergey/dev-git/5.5/sql/sql_base.cc:8184
|
#7 0x000000000063b887 in JOIN::prepare (this=0x7fff75045960, rref_pointer_array=0x7fff7510fa78, tables_init=0x7fff75110388, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7510f808, unit_arg=0x7fff7510f128) at /home/psergey/dev-git/5.5/sql/sql_select.cc:722
|
#8 0x00000000006c64a3 in st_select_lex_unit::prepare (this=0x7fff7510f128, thd_arg=0x7fff84bf5060, sel_result=0x7fff75045870, additional_options=0) at /home/psergey/dev-git/5.5/sql/sql_union.cc:332
|
#9 0x00000000005e2dd1 in mysql_derived_prepare (thd=0x7fff84bf5060, lex=0x7fff7505d080, derived=0x7fff751038b8) at /home/psergey/dev-git/5.5/sql/sql_derived.cc:661
|
#10 0x00000000005e217f in mysql_handle_single_derived (lex=0x7fff7505d080, derived=0x7fff751038b8, phases=2) at /home/psergey/dev-git/5.5/sql/sql_derived.cc:194
|
#11 0x00000000006e9b26 in TABLE_LIST::handle_derived (this=0x7fff751038b8, lex=0x7fff7505d080, phases=2) at /home/psergey/dev-git/5.5/sql/table.cc:6649
|
#12 0x00000000005ff574 in st_select_lex::handle_derived (this=0x7fff7505d810, lex=0x7fff7505d080, phases=2) at /home/psergey/dev-git/5.5/sql/sql_lex.cc:3597
|
#13 0x000000000063b471 in JOIN::prepare (this=0x7fff75045330, rref_pointer_array=0x7fff7505da80, tables_init=0x7fff7505e608, wild_num=0, conds_init=0x7fff75045110, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7505d810, unit_arg=0x7fff7505d130) at /home/psergey/dev-git/5.5/sql/sql_select.cc:637
|
#14 0x0000000000644794 in mysql_select (thd=0x7fff84bf5060, rref_pointer_array=0x7fff7505da80, tables=0x7fff7505e608, wild_num=0, fields=..., conds=0x7fff75045110, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fff75104090, unit=0x7fff7505d130, select_lex=0x7fff7505d810) at /home/psergey/dev-git/5.5/sql/sql_select.cc:3074
|
#15 0x000000000063aa43 in handle_select (thd=0x7fff84bf5060, lex=0x7fff7505d080, result=0x7fff75104090, setup_tables_done_option=0) at /home/psergey/dev-git/5.5/sql/sql_select.cc:319
|
#16 0x00000000006111ab in execute_sqlcom_select (thd=0x7fff84bf5060, all_tables=0x7fff7505e608) at /home/psergey/dev-git/5.5/sql/sql_parse.cc:4689
|
#17 0x0000000000609daa in mysql_execute_command (thd=0x7fff84bf5060) at /home/psergey/dev-git/5.5/sql/sql_parse.cc:2234
|
#18 0x000000000062d160 in Prepared_statement::execute (this=0x7fff750f3460, expanded_query=0x7ffff7f6bc40, open_cursor=false) at /home/psergey/dev-git/5.5/sql/sql_prepare.cc:3930
|
#19 0x000000000062c162 in Prepared_statement::execute_loop (this=0x7fff750f3460, expanded_query=0x7ffff7f6bc40, open_cursor=false, packet=0x0, packet_end=0x0) at /home/psergey/dev-git/5.5/sql/sql_prepare.cc:3589
|
#20 0x000000000062a08f in mysql_sql_stmt_execute (thd=0x7fff84bf5060) at /home/psergey/dev-git/5.5/sql/sql_prepare.cc:2738
|
So, the query is
SELECT A.* FROM table_A A WHERE A.key_code IN (SELECT key_code FROM view_B)
|
and the view definition:
CREATE VIEW view_B
|
AS
|
SELECT
|
B.key_code,
|
B.target_date
|
FROM
|
table_B B INNER JOIN table_C C ON
|
B.target_date = C.now_date
|
- The optimizer converts the view into semi-join.
- On second PS execution, fix_fields operation is done for the view's select list.
- Something goes wrong when resolving B.target_date. Maybe,subquery->semijoin conversion messed up something in the VIEW's name resolution data structures.
I apply the candidate fix for MDEV-7691 https://gist.github.com/spetrunia/c4e1a40d0d36369323dc8cc02ec98500 , and I still get the crash in the same assertion, but now it's for a different Item.
The crash happens when fixing the Item_field object, which is the first Item_field that PREPARE commands calls Item_field::fix_fields for. item->name="key_code".
for PREPARE and first EXECUTE, Item_field::fix_fields resolves the item locally, in particular, this condition is FALSE and the if-branch is not taken.
if (!outer_fixed && table_list && table_list->select_lex &&
|
context->select_lex &&
|
table_list->select_lex != context->select_lex &&
|
!context->select_lex->is_merged_child_of(table_list->select_lex) &&
|
is_outer_table(table_list, context->select_lex))
|
{
|
It is false, because table_list->select_lex != context->select_lex evaluates to FALSE.
Then, first EXECUTE does subquery-to-semi-join conversion, and the second EXECUTE has
(gdb) p table_list->select_lex->select_number
|
$574 = 1
|
(gdb) p context->select_lex->select_number
|
$575 = 3
|
(The table is in select#1, our name resolution context is in select#3)
Also,
context->select_lex->is_merged_child_of(table_list->select_lex) == FALSE
|
Which seems incorrect. Debugging st_select_lex::is_merged_child_of(), I can see that it starts from this=<select with id=3>, goes one level up (out of the merged view definition), looks at
Item *subs= sl->master_unit()->item;
|
finds that subs==NULL, and returns FALSE. That is, st_select_lex::is_merged_child_of doesn't take merged views/derived tables into account.
Sanja, please review:
http://lists.askmonty.org/pipermail/commits/2016-December/010295.html
Fix pushed into 5.5 tree. account@zam.att.ne.jp, thanks for the bug report and for your patience!
Thanks for the report and the test case.
It is likely to be a duplicate of
MDEV-7691, but I will assign it to psergey to double-check the provided scenario onceMDEV-7691is fixed.