[MDEV-10148] Database crashes in the query to the View Created: 2016-05-29  Updated: 2016-12-20  Resolved: 2016-12-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.1.14, 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.54

Type: Bug Priority: Major
Reporter: Ken Sugawara Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: None
Environment:

CentOS 7

Issue Links:
Duplicate
duplicates MDEV-10979 mariadb 5.5.47 and 5.5.44 Crash syste... Closed
is duplicated by MDEV-7691 Assertion `outer_context || !*from_fi... Closed
Relates
relates to MDEV-11293 Hit signal 11 when excuting a procedu... Closed
Sprint: 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

 Comments   
Comment by Elena Stepanova [ 2016-05-29 ]

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 once MDEV-7691 is fixed.

Comment by Ken Sugawara [ 2016-05-30 ]

Hi Elena.
Thank you for the comment.
I watch the MDEV-7691.

Comment by Sergei Petrunia [ 2016-12-15 ]

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

Comment by Sergei Petrunia [ 2016-12-15 ]

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.
Comment by Sergei Petrunia [ 2016-12-16 ]

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.

Comment by Sergei Petrunia [ 2016-12-16 ]

Sanja, please review:
http://lists.askmonty.org/pipermail/commits/2016-December/010295.html

Comment by Sergei Petrunia [ 2016-12-19 ]

Fix pushed into 5.5 tree. account@zam.att.ne.jp, thanks for the bug report and for your patience!

Comment by Ken Sugawara [ 2016-12-20 ]

Hi,Sergei.
Thank you for your work.

Generated at Thu Feb 08 07:40:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.