[MDEV-13174] Repeatable daemon crash in 10.2.6 on show procedure / select sql_derived.cc:1081 Created: 2017-06-26  Updated: 2017-09-04  Resolved: 2017-09-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2.6, 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: Duplicate Votes: 0
Labels: None
Environment:

Centos 7 AWS, Windows 10

Issue Links:
Duplicate
duplicates MDEV-10972 Insert from select / view / union -- ... Closed
Relates
relates to MDEV-10972 Insert from select / view / union -- ... Closed
relates to MDEV-13365 Crash when select where not in select... Closed

 Description   

I've hit a repeatable bug that crashes the server daemon. The test case works on 10.0.31 but crashes on 10.2.6. The pattern is show create procedure followed by call to a SP that runs a query. This pattern is important because it is the normal behaviour of the Java MySQL connector.

 Comments   
Comment by Michael Newcomb [ 2017-06-26 ]

I plan to upload the test case data to ftp.askmonty.org/private/MDEV-13174.zip but I can't connect at the moment.

Steps I use to reproduce the error:

  • Spin up a brand new AWS Centos7 instance (micro)
  • Follow mariadb directions to install 10.2.6 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.

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

Thanks for your help!

Comment by Michael Newcomb [ 2017-06-26 ]

The query looks like:

select c.pmonclump_code, c.pmonclump_name, guu.n_events
 
from
 
(
 
    select p.pmonclump_code, count(*) as n_events
 
    from v_pmon_access pa
 
    inner join pmon p on
 
        (pa.pmon_id = p.pmon_id)
 
    where pa.assignee_id = p_hubspotter_id
 
    and (p.pmonstatus_code = 'A' or p.pmonstatus_code = 'O')
 
    group by p.pmonclump_code
 
) guu
 
inner join pmonclumps c on
 
	(guu.pmonclump_code = c.pmonclump_code and
 
     c.pmonclump_enabled)
 
order by c.pmonclump_order, c.pmonclump_name;

The trigger script is just:

USE `mdbug`;
 
 
 
SHOW CREATE PROCEDURE `mdbug`.`p_pmon_assigned`;
 
 
 
CALL p_pmon_assigned(395);

The SP will often work fine if run alone but when it follows show create procedure it always crashes.

Comment by Michael Newcomb [ 2017-06-26 ]

Sample case uploaded as to ftp.askmonty.org/private/MDEV-13174a.zip

Comment by Elena Stepanova [ 2017-06-26 ]

Thank you for the report and test case. Here is the reduced MTR-friendly version:

CREATE TABLE t1 (i1 INT);
 
INSERT INTO t1 VALUES (1),(2); # Unimportant, the table can be empty
 
CREATE TABLE t2 (i2 INT);
 
INSERT INTO t2 VALUES (3),(4); # Unimportant, the table can be empty
 
CREATE TABLE t3 (i3 INT);
 
INSERT INTO t3 VALUES (5),(6);
 
 
 
CREATE VIEW v AS select i1 from t1 union select i2 from t2;
 
 
 
CREATE PROCEDURE pr()
 
  SELECT * FROM (
 
    SELECT COUNT(*) as c FROM v
 
  ) sq
 
  JOIN t3 
;
 
 
 
SHOW CREATE PROCEDURE pr;
 
CALL pr();

10.1 b76b69cd5fe634d8ddb9406aa2c82ef2a375b4d8

 
#3  <signal handler called>
 
#4  0x0000563ea5bd8824 in Time_and_counter_tracker::incr_loops (this=0x78) at /data/src/10.1/sql/sql_analyze_stmt.h:97
 
#5  0x0000563ea5b9ba97 in JOIN::exec (this=0x7fd1729e1788) at /data/src/10.1/sql/sql_select.cc:2509
 
#6  0x0000563ea5b9f06b in mysql_select (thd=0x7fd1744d5070, rref_pointer_array=0x7fd1729aa730, tables=0x7fd1729a7618, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416185088, result=0x7fd1729e1690, unit=0x7fd1729aa818, select_lex=0x7fd1729aa4a0) at /data/src/10.1/sql/sql_select.cc:3445
 
#7  0x0000563ea5b30af1 in mysql_derived_fill (thd=0x7fd1744d5070, lex=0x7fd1729a6088, derived=0x7fd1729956e0) at /data/src/10.1/sql/sql_derived.cc:942
 
#8  0x0000563ea5b2f423 in mysql_handle_single_derived (lex=0x7fd1729a6088, derived=0x7fd1729956e0, phases=96) at /data/src/10.1/sql/sql_derived.cc:195
 
#9  0x0000563ea5bb4a61 in st_join_table::preread_init (this=0x7fd1729492b0) at /data/src/10.1/sql/sql_select.cc:11723
 
#10 0x0000563ea5bc6364 in join_init_read_record (tab=0x7fd1729492b0) at /data/src/10.1/sql/sql_select.cc:19238
 
#11 0x0000563ea5ccfeb6 in JOIN_TAB_SCAN::open (this=0x7fd172947010) at /data/src/10.1/sql/sql_join_cache.cc:3349
 
#12 0x0000563ea5cce436 in JOIN_CACHE::join_matching_records (this=0x7fd1729499a8, skip_last=false) at /data/src/10.1/sql/sql_join_cache.cc:2260
 
#13 0x0000563ea5ccdfde in JOIN_CACHE::join_records (this=0x7fd1729499a8, skip_last=false) at /data/src/10.1/sql/sql_join_cache.cc:2097
 
#14 0x0000563ea5bc3f47 in sub_select_cache (join=0x7fd1729e0240, join_tab=0x7fd1729492b0, end_of_records=true) at /data/src/10.1/sql/sql_select.cc:18115
 
#15 0x0000563ea5bc4131 in sub_select (join=0x7fd1729e0240, join_tab=0x7fd172948f68, end_of_records=true) at /data/src/10.1/sql/sql_select.cc:18285
 
#16 0x0000563ea5bc3c09 in do_select (join=0x7fd1729e0240, fields=0x7fd1729a6968, table=0x0, procedure=0x0) at /data/src/10.1/sql/sql_select.cc:17991
 
#17 0x0000563ea5b9e843 in JOIN::exec_inner (this=0x7fd1729e0240) at /data/src/10.1/sql/sql_select.cc:3221
 
#18 0x0000563ea5b9baed in JOIN::exec (this=0x7fd1729e0240) at /data/src/10.1/sql/sql_select.cc:2511
 
#19 0x0000563ea5b9f06b in mysql_select (thd=0x7fd1744d5070, rref_pointer_array=0x7fd1729a6ae0, tables=0x7fd1729956e0, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7fd1729e0220, unit=0x7fd1729a6150, select_lex=0x7fd1729a6850) at /data/src/10.1/sql/sql_select.cc:3445
 
#20 0x0000563ea5b94ac4 in handle_select (thd=0x7fd1744d5070, lex=0x7fd1729a6088, result=0x7fd1729e0220, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:384
 
#21 0x0000563ea5b649bd in execute_sqlcom_select (thd=0x7fd1744d5070, all_tables=0x7fd1729956e0) at /data/src/10.1/sql/sql_parse.cc:5922
 
#22 0x0000563ea5b5aeb9 in mysql_execute_command (thd=0x7fd1744d5070) at /data/src/10.1/sql/sql_parse.cc:2979
 
#23 0x0000563ea5ee74c6 in sp_instr_stmt::exec_core (this=0x7fd1729a7c98, thd=0x7fd1744d5070, nextp=0x7fd17d918624) at /data/src/10.1/sql/sp_head.cc:3212
 
#24 0x0000563ea5ee6bb8 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fd1729a7cd8, thd=0x7fd1744d5070, nextp=0x7fd17d918624, open_tables=false, instr=0x7fd1729a7c98) at /data/src/10.1/sql/sp_head.cc:2978
 
#25 0x0000563ea5ee718c in sp_instr_stmt::execute (this=0x7fd1729a7c98, thd=0x7fd1744d5070, nextp=0x7fd17d918624) at /data/src/10.1/sql/sp_head.cc:3128
 
#26 0x0000563ea5ee2a52 in sp_head::execute (this=0x7fd1729a9088, thd=0x7fd1744d5070, merge_da_on_success=true) at /data/src/10.1/sql/sp_head.cc:1316
 
#27 0x0000563ea5ee48ec in sp_head::execute_procedure (this=0x7fd1729a9088, thd=0x7fd1744d5070, args=0x7fd1744d9728) at /data/src/10.1/sql/sp_head.cc:2103
 
#28 0x0000563ea5b595c9 in do_execute_sp (thd=0x7fd1744d5070, sp=0x7fd1729a9088) at /data/src/10.1/sql/sql_parse.cc:2412
 
#29 0x0000563ea5b624c7 in mysql_execute_command (thd=0x7fd1744d5070) at /data/src/10.1/sql/sql_parse.cc:5284
 
#30 0x0000563ea5b680db in mysql_parse (thd=0x7fd1744d5070, rawbuf=0x7fd172843088 "CALL pr()", length=9, parser_state=0x7fd17d9195e0) at /data/src/10.1/sql/sql_parse.cc:7339
 
#31 0x0000563ea5b570c8 in dispatch_command (command=COM_QUERY, thd=0x7fd1744d5070, packet=0x7fd176ff9071 "", packet_length=9) at /data/src/10.1/sql/sql_parse.cc:1490
 
#32 0x0000563ea5b55e27 in do_command (thd=0x7fd1744d5070) at /data/src/10.1/sql/sql_parse.cc:1109
 
#33 0x0000563ea5c8d54f in do_handle_one_connection (thd_arg=0x7fd1744d5070) at /data/src/10.1/sql/sql_connect.cc:1349
 
#34 0x0000563ea5c8d2b3 in handle_one_connection (arg=0x7fd1744d5070) at /data/src/10.1/sql/sql_connect.cc:1261
 
#35 0x0000563ea6043f56 in pfs_spawn_thread (arg=0x7fd17ac39ef0) at /data/src/10.1/storage/perfschema/pfs.cc:1860
 
#36 0x00007fd17d599494 in start_thread (arg=0x7fd17d91ab00) at pthread_create.c:333
 
#37 0x00007fd17b6e493f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Reproducible with MyISAM and InnoDB, on 10.2 and 10.1 (including old 10.1 releases). Could not reproduce on 10.0.

Comment by Oleksandr Byelkin [ 2017-09-01 ]

fail because of absence analyze/explain info

Comment by Oleksandr Byelkin [ 2017-09-01 ]

It is duplicate of MDEV-10972

Generated at Thu Feb 08 08:03:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.