[MDEV-10972] Insert from select / view / union -- repeatable crash in 10.1, 10.2 Linux/Mac/Windows Created: 2016-10-06  Updated: 2018-09-01  Resolved: 2017-09-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.18, 10.2.1, 10.1, 10.2
Fix Version/s: 10.1.27, 10.2.9, 10.3.2

Type: Bug Priority: Major
Reporter: Michael Newcomb Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 7 AWS but also Sierra and Windows 10

Issue Links:
Duplicate
is duplicated by MDEV-13174 Repeatable daemon crash in 10.2.6 on ... Closed
is duplicated by MDEV-13365 Crash when select where not in select... Closed
Relates
relates to MDEV-13174 Repeatable daemon crash in 10.2.6 on ... Closed
Sprint: 10.1.19, 10.2.4-1, 10.2.4-2

 Description   

I've hit what appears to be an optimizer bug that crashes the server daemon. The test case works fine on 10.0.x but fails on 10.1.18 and 10.2.1.

 Comments   
Comment by Michael Newcomb [ 2016-10-06 ]

Uploaded test case data to ftp.askmonty.org/private/MDEV-10972.zip.

Steps I use to reproduce the error:

  • Spin up a brand new AWS Centos7 instance (micro)
  • Follow mariadb directions to install 10.1.18 using yum
  • Load the test data:

mysql --user=root --password=(password) --comments < mdbug.sql

This should create a test database. To cause the crash run the provoke shell script or:

mysql --verbose --user=mdbug --password=mdbug --database=mdbug < provoke.sql

This causes a crash 100% of the time for me. The Zip file includes server.cnf and the log files from my test instance.

The same code runs fine on 10.0.27.

If there's anything I can do to help please let me know...

Thanks for your help!

Comment by Michael Newcomb [ 2016-10-06 ]

Offending statement looks like this:

insert into acct_update
 
(
 
    account_name, account_display_name, account_desc, 
    hubspotter_id
 
)
 
select a.account_name, a.account_display_name, a.account_desc,
 
	a.hubspotter_id
 
from
 
(
 
    select account_id
 
    from v_pacgroup_accounts
 
    
    union
 
    
    select account_id
 
    from v_pacgroup_special
 
) guu
 
inner join accounts a on
 
	(guu.account_id = a.account_id)

Comment by Elena Stepanova [ 2016-10-07 ]

Stack trace from 10.1 f35e918989080d1810c2e1c7fc91f3130585607b

 
#3  <signal handler called>
 
#4  0x000055d2ab7e2e32 in Time_and_counter_tracker::incr_loops (this=0x78) at /home/elenst/10.1/sql/sql_analyze_stmt.h:97
 
#5  0x000055d2ab7a6655 in JOIN::exec (this=0x7f400a02e9e0) at /home/elenst/10.1/sql/sql_select.cc:2503
 
#6  0x000055d2ab83d092 in st_select_lex_unit::exec (this=0x7f400e1a2f60) at /home/elenst/10.1/sql/sql_union.cc:832
 
#7  0x000055d2ab73aeec in mysql_derived_fill (thd=0x7f4013271070, lex=0x7f400e19f1e8, derived=0x7f400e1a5298) at /home/elenst/10.1/sql/sql_derived.cc:918
 
#8  0x000055d2ab739945 in mysql_handle_single_derived (lex=0x7f400e19f1e8, derived=0x7f400e1a5298, phases=96) at /home/elenst/10.1/sql/sql_derived.cc:195
 
#9  0x000055d2ab7bf151 in st_join_table::preread_init (this=0x7f400a0cc8f0) at /home/elenst/10.1/sql/sql_select.cc:11698
 
#10 0x000055d2ab7ce843 in sub_select (join=0x7f400a02f6d0, join_tab=0x7f400a0cc8f0, end_of_records=false) at /home/elenst/10.1/sql/sql_select.cc:18282
 
#11 0x000055d2ab7cf0d7 in evaluate_join_record (join=0x7f400a02f6d0, join_tab=0x7f400a0cc5a8, error=0) at /home/elenst/10.1/sql/sql_select.cc:18539
 
#12 0x000055d2ab7ce9e1 in sub_select (join=0x7f400a02f6d0, join_tab=0x7f400a0cc5a8, end_of_records=false) at /home/elenst/10.1/sql/sql_select.cc:18314
 
#13 0x000055d2ab7ce257 in do_select (join=0x7f400a02f6d0, fields=0x7f400e19fac8, table=0x0, procedure=0x0) at /home/elenst/10.1/sql/sql_select.cc:17969
 
#14 0x000055d2ab7a93ff in JOIN::exec_inner (this=0x7f400a02f6d0) at /home/elenst/10.1/sql/sql_select.cc:3215
 
#15 0x000055d2ab7a66ab in JOIN::exec (this=0x7f400a02f6d0) at /home/elenst/10.1/sql/sql_select.cc:2505
 
#16 0x000055d2ab7a9c11 in mysql_select (thd=0x7f4013271070, rref_pointer_array=0x7f400e19fc28, tables=0x7f400e1a5298, wild_num=0, fields=..., conds=0x0, og
 
_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3498839552, result=0x7f400a02f630, unit=0x7f400e19f2b0, select_lex=0x7f400e19f9b0)
 
 at /home/elenst/10.1/sql/sql_select.cc:3438
 
#17 0x000055d2ab79f727 in handle_select (thd=0x7f4013271070, lex=0x7f400e19f1e8, result=0x7f400a02f630, setup_tables_done_option=1073741824) at /home/elenst/10.1/sql/sql_select.cc:384
 
#18 0x000055d2ab768449 in mysql_execute_command (thd=0x7f4013271070) at /home/elenst/10.1/sql/sql_parse.cc:4003
 
#19 0x000055d2abaeff6c in sp_instr_stmt::exec_core (this=0x7f400e1a5fa8, thd=0x7f4013271070, nextp=0x7f403951c674) at /home/elenst/10.1/sql/sp_head.cc:3215
 
#20 0x000055d2abaef6a2 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f400e1a5fe8, thd=0x7f4013271070, nextp=0x7f403951c674, open_tables=false, instr=0x7f400e1a5fa8) at /home/elenst/10.1/sql/sp_head.cc:2981
 
#21 0x000055d2abaefc6c in sp_instr_stmt::execute (this=0x7f400e1a5fa8, thd=0x7f4013271070, nextp=0x7f403951c674) at /home/elenst/10.1/sql/sp_head.cc:3131
 
#22 0x000055d2abaeb541 in sp_head::execute (this=0x7f400e23d088, thd=0x7f4013271070, merge_da_on_success=true) at /home/elenst/10.1/sql/sp_head.cc:1319
 
#23 0x000055d2abaed3d2 in sp_head::execute_procedure (this=0x7f400e23d088, thd=0x7f4013271070, args=0x7f4013275710) at /home/elenst/10.1/sql/sp_head.cc:2106
 
#24 0x000055d2ab763b0d in do_execute_sp (thd=0x7f4013271070, sp=0x7f400e23d088) at /home/elenst/10.1/sql/sql_parse.cc:2403
 
#25 0x000055d2ab76ce0f in mysql_execute_command (thd=0x7f4013271070) at /home/elenst/10.1/sql/sql_parse.cc:5254
 
#26 0x000055d2ab772eef in mysql_parse (thd=0x7f4013271070, rawbuf=0x7f400b7f7088 "CALL p_update_ccs_system(122)", length=29, parser_state=0x7f403951d630) at /home/elenst/10.1/sql/sql_parse.cc:7318
 
#27 0x000055d2ab7615f3 in dispatch_command (command=COM_QUERY, thd=0x7f4013271070, packet=0x7f4013297071 "CALL p_update_ccs_system(122)", packet_length=29) at /home/elenst/10.1/sql/sql_parse.cc:1487
 
#28 0x000055d2ab760323 in do_command (thd=0x7f4013271070) at /home/elenst/10.1/sql/sql_parse.cc:1108
 
#29 0x000055d2ab896719 in do_handle_one_connection (thd_arg=0x7f4013271070) at /home/elenst/10.1/sql/sql_connect.cc:1350
 
#30 0x000055d2ab896468 in handle_one_connection (arg=0x7f4013271070) at /home/elenst/10.1/sql/sql_connect.cc:1262
 
#31 0x00007f4037e146fa in start_thread (arg=0x7f403951eb00) at pthread_create.c:333
 
#32 0x00007f40374bfb5d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Elena Stepanova [ 2016-10-08 ]

mnewcomb2016, thanks for the report and test case.

sanja, below is a smaller test for debugging; but after fixing, please make sure that your fix also works for the initial test case!
(it's big and takes long time).

drop table if exists t, tmp_t;
 
create table t (id int auto_increment primary key);
 
insert into t values (9494),(9495),(9496),(9497),(9498),(9499),(9500),(9501),(9502),(9503);
 
 
 
create or replace VIEW v AS 
select id from t
 
union 
select id from t
 
;
 
 
 
drop procedure if exists p;
 
create procedure p()
 
insert into tmp_t select t.id from (
 
    select id from v
 
    union
 
    select id from v
 
) sq
 
inner join t on (sq.id = t.id);
 
 
 
--error ER_NO_SUCH_TABLE
 
CALL p();
 
create table tmp_t (id int null);
 
CALL p();

Comment by Oleksandr Byelkin [ 2017-01-11 ]

The problem is that we have 2 SELECT # 2

Comment by Oleksandr Byelkin [ 2017-01-11 ]

There is only 1 select #2, but explain information was not cleaned after error, which causes problem:
we check if there is explein info for select # 2 and there is (from forest run with error), so we do not create new, and so JOIN::explain is left NULL.

Comment by Oleksandr Byelkin [ 2017-01-13 ]

OK, despite the same SQL inside it is 2 different SELECT_LEX & JOIN with number #2 and number #3 and both part of the view UNION.
so probably the view was built in the statement before error, then made it second time. then optimized one select #2 and explain saved, then second select #2 and explain is not saved (because it is "present") then it fail.

Comment by Oleksandr Byelkin [ 2017-01-15 ]

mysql_make_view works only in CALL without error.

Comment by Oleksandr Byelkin [ 2017-01-15 ]

first time mysql_new_select called during compiling call with error, second during mysql_make_view in normal CALL.

Comment by Oleksandr Byelkin [ 2017-01-15 ]

If there is no error all calls made in the same first CALL, and everything is OK.

Comment by Oleksandr Byelkin [ 2017-01-15 ]

Normal execution has only one select #2 and one #3 used everywhere (as it should).

Comment by Oleksandr Byelkin [ 2017-01-15 ]

incorrect switching Query_arenas ?

Comment by Oleksandr Byelkin [ 2017-01-16 ]

Correct execution has the same SELECT_LEX (why view selects 2 times?), but counting them from beginning because process divided between 2 calls cause the problem we observe.

Comment by Oleksandr Byelkin [ 2017-01-16 ]

Workaround can be store thd->select_number and then restore it before execution, but what is statement like this will not be the first in the procedure.

Comment by Oleksandr Byelkin [ 2017-01-17 ]

There is other way (without error) to repeat the problem. It need just other statement to reset thd->select_number between parsing and execution.

create table t (id int auto_increment primary key);
 
insert into t values (9494),(9495),(9496),(9497),(9498),(9499),(9500),(9501),(9502),(9503);
 
 
create VIEW v AS 
select id from t
 
union 
select id from t
 
;
 
 
 
delimiter |;
 
create procedure p()
 
begin
 
  select 1;
 
  insert into tmp_t select t.id from (
 
      select id from v
 
      union
 
      select id from v
 
  ) sq
 
  inner join t on (sq.id = t.id);
 
end |
 
delimiter ;|
 
 
create table tmp_t (id int null);
 
CALL p();
 
 
 
drop procedure p;
 
drop view v;
 
drop table t, tmp_t;

Comment by Oleksandr Byelkin [ 2017-01-17 ]

As a solution I see storing the counter (thd->select_number) in sp_head and restore it before each instruction.

Comment by Oleksandr Byelkin [ 2017-01-17 ]

Above was wrong (rests of experimenting with instructions).

Stored procedure do not reset the counter between instructions, so it number all selects in the procedure.

Comment by Oleksandr Byelkin [ 2017-01-17 ]

Problem present in 5.5/10.0 but do not lead to crash, because it has different EXPLAIN system.

Comment by Oleksandr Byelkin [ 2017-01-17 ]

revision-id: f360516046af16336e874b1ab9fb22efb1881723 (mariadb-10.1.20-33-gf360516046a)
parent(s): 5044dae239d094582879792de7a762d3428223ce
committer: Oleksandr Byelkin
timestamp: 2017-01-17 13:09:04 +0100
message:

MDEV-10972: Insert from select / view / union – repeatable crash in 10.1, 10.2 Linux/Mac/Windows

save thd->select_number between parsing and executions (in case it was not complete executed due to errors (for example epsent table))

—

Comment by Oleksandr Byelkin [ 2017-01-17 ]

The very first SELECT_LEX which belong to LEX should not be a problem.

Comment by Oleksandr Byelkin [ 2017-09-04 ]

revision-id: c62864b57568e81d77a1ffa2d7ad96d5daccfe97 (mariadb-10.1.26-26-gc62864b5756)
parent(s): 4d158d71a667dfbf8f679e1e6abd5f46813b5e2f
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2017-09-04 15:29:58 +0200
message:

Post review change (prevents contant increasing, which harmless but not right)

—

Comment by Sergei Petrunia [ 2017-09-04 ]

Review input provided in chat window. Ok to push after addressed.

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