Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23130

Server crashes on 2nd execution of sp

    XMLWordPrintable

Details

    Description

      Reproducible on 5.5-10.5, with Myisam/Innodb, on debug and non-debug versions

      CREATE TABLE t1 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t1 VALUES (NULL,NULL,8);
       
      CREATE TABLE t2 ( pk int NOT NULL auto_increment, i1 int, id int, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t2 VALUES (63,NULL,NULL),(64,NULL,NULL),(65,NULL,NULL),(66,NULL,NULL),(69,NULL,NULL),(67,NULL,NULL),(68,NULL,NULL),(70,NULL,NULL),(78,NULL,NULL),(77,NULL,NULL),(75,NULL,NULL),(76,NULL,NULL);
       
      CREATE TABLE t3 ( id int, i1 int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t3 VALUES (NULL,NULL,3),(NULL,NULL,9);
       
      CREATE TABLE t4 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t4 VALUES (NULL,NULL,3);
       
      CREATE TABLE t5 ( i1 int, pk int NOT NULL auto_increment, id int, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t5 VALUES (NULL,116,NULL),(NULL,123,NULL),(NULL,124,NULL),(NULL,125,NULL),(NULL,126,NULL);
       
      CREATE VIEW v1 AS
      SELECT t2.i1, t2.id, t2.pk  FROM t1 JOIN t2
      WHERE exists 
      (SELECT at3.pk FROM (t3 JOIN t3 at3 on t3.pk = at3.i1 ) WHERE at3.id = t2.pk);
       
      CREATE PROCEDURE sp()
      SELECT
      	(SELECT max(v1.pk) FROM (v1 JOIN (t2 AS dt2 JOIN t4 AS dt4 
      		ON dt4.i1 = dt2.id)  ON dt4.i1 = dt2.id) 
      		WHERE (dt2.pk > v1.i1 AND v1.pk > v1.i1)) AS f1,
      	a1.id AS f3 
      FROM t2 AS a1, v1 AS a2
      WHERE NOT EXISTS (SELECT t5.id FROM t5 WHERE t5.pk IN 
      	(SELECT t2.i1 FROM t2 LEFT JOIN t4 ON t4.i1 = t2.pk))
      GROUP BY f1, f3;
       
      call sp();
      call sp();
      

      5.5

      #3  <signal handler called>
      #4  update_depend_map_for_order (join=0x7f6f7ad86a50, order=0x7f6f7b13d320) at /5.5/sql/sql_select.cc:11342
      #5  0x0000559575678677 in remove_const (join=0x7f6f7ad86a50, first_order=0x7f6f7b13d320, cond=0x7f6f7ad867c8, change_list=true, simple_order=0x7f6f7ad86d6c) at /5.5/sql/sql_select.cc:11406
      #6  0x000055957565dfb6 in JOIN::optimize (this=0x7f6f7ad86a50) at /5.5/sql/sql_select.cc:1612
      #7  0x00005595756633d1 in mysql_select (thd=0x7f6f7ec72ea0, rref_pointer_array=0x7f6f7ada3f40, tables=0x7f6f7b15ff38, wild_num=0, fields=..., conds=0x7f6f7ad867c8, og_num=2, order=0x0, group=0x7f6f7b13d320, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7f6f7ad86a30, unit=0x7f6f7ada35b8, select_lex=0x7f6f7ada3ca0) at /5.5/sql/sql_select.cc:3121
      #8  0x0000559575659aab in handle_select (thd=0x7f6f7ec72ea0, lex=0x7f6f7ada3508, result=0x7f6f7ad86a30, setup_tables_done_option=0) at /5.5/sql/sql_select.cc:312
      #9  0x00005595756327de in execute_sqlcom_select (thd=0x7f6f7ec72ea0, all_tables=0x7f6f7b15ff38) at /5.5/sql/sql_parse.cc:4683
      #10 0x000055957562b70f in mysql_execute_command (thd=0x7f6f7ec72ea0) at /5.5/sql/sql_parse.cc:2221
      #11 0x000055957590a544 in sp_instr_stmt::exec_core (this=0x7f6f7b13d4b0, thd=0x7f6f7ec72ea0, nextp=0x7f6f81c8c368) at /5.5/sql/sp_head.cc:3221
      #12 0x0000559575909cfd in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f6f7b13d4f0, thd=0x7f6f7ec72ea0, nextp=0x7f6f81c8c368, open_tables=false, instr=0x7f6f7b13d4b0) at /5.5/sql/sp_head.cc:2999
      #13 0x000055957590a272 in sp_instr_stmt::execute (this=0x7f6f7b13d4b0, thd=0x7f6f7ec72ea0, nextp=0x7f6f81c8c368) at /5.5/sql/sp_head.cc:3145
      #14 0x00005595759060ce in sp_head::execute (this=0x7f6f7ada2878, thd=0x7f6f7ec72ea0, merge_da_on_success=true) at /5.5/sql/sp_head.cc:1432
      #15 0x0000559575907e4a in sp_head::execute_procedure (this=0x7f6f7ada2878, thd=0x7f6f7ec72ea0, args=0x7f6f7ec76e68) at /5.5/sql/sp_head.cc:2198
      #16 0x0000559575630c14 in mysql_execute_command (thd=0x7f6f7ec72ea0) at /5.5/sql/sql_parse.cc:4120
      #17 0x000055957563538f in mysql_parse (thd=0x7f6f7ec72ea0, rawbuf=0x7f6f7b187078 "call sp()", length=9, parser_state=0x7f6f81c8ce50) at /5.5/sql/sql_parse.cc:5928
      #18 0x0000559575628cb2 in dispatch_command (command=COM_QUERY, thd=0x7f6f7ec72ea0, packet=0x7f6f7f7ba5e1 "call sp()", packet_length=9) at /5.5/sql/sql_parse.cc:1067
      #19 0x0000559575627ec7 in do_command (thd=0x7f6f7ec72ea0) at /5.5/sql/sql_parse.cc:793
      #20 0x000055957572cb2b in do_handle_one_connection (thd_arg=0x7f6f7ec72ea0) at /5.5/sql/sql_connect.cc:1268
      #21 0x000055957572c8a4 in handle_one_connection (arg=0x7f6f7ec72ea0) at /5.5/sql/sql_connect.cc:1184
      #22 0x0000559575aad171 in pfs_spawn_thread (arg=0x7f6f7ec9d140) at /5.5/storage/perfschema/pfs.cc:1015
      #23 0x00007f6f826eefa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
      #24 0x00007f6f81ddb4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      the same on non-debug build:

      [ERROR] mysqld got signal 11 ;
       
      Server version: 10.5.5-MariaDB-log
       
      ??:0(__restore_rt)[0x7f5a1d170730]
      sql/sql_select.cc:14095(update_depend_map_for_order)[0x55dcbe26c08a]
      sql/sql_select.cc:2693(JOIN::optimize_stage2())[0x55dcbe26d6a3]
      sql/sql_select.cc:1620(JOIN::optimize())[0x55dcbe26d7a1]
      sql/sql_select.cc:4641(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55dcbe26e16e]
      sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55dcbe210c30]
      sql/sql_parse.cc:6209(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55dcbe219903]
      sql/sql_parse.cc:5951(mysql_execute_command(THD*))[0x55dcbe180535]
      sql/sp_head.cc:3767(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x55dcbe1879db]
      sql/sp_head.cc:3492(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x55dcbe18840b]
      sql/sp_head.cc:3671(sp_instr_stmt::execute(THD*, unsigned int*))[0x55dcbe1837cf]
      sql/sp_head.cc:1435(sp_head::execute(THD*, bool))[0x55dcbe184ba7]
      psi/mysql_sp.h:79(inline_mysql_end_sp)[0x55dcbe210008]
      sql/sql_parse.cc:3044(do_execute_sp(THD*, sp_head*))[0x55dcbe21448c]
      sql/sql_parse.cc:3288(Sql_cmd_call::execute(THD*))[0x55dcbe219932]
      sql/sql_parse.cc:5951(mysql_execute_command(THD*))[0x55dcbe20c092]
      sql/sql_parse.cc:8010(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55dcbe216bf7]
      sql/sql_parse.cc:1869(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55dcbe217b4d]
      sql/sql_parse.cc:1347(do_command(THD*))[0x55dcbe2fd8f0]
      sql/sql_connect.cc:1411(do_handle_one_connection(CONNECT*, bool))[0x55dcbe2fdcbd]
      sql/sql_connect.cc:1313(handle_one_connection)[0x55dcbe6272db]
      nptl/pthread_create.c:487(start_thread)[0x7f5a1d165fa3]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f5a1c76e4cf]
      

      Nearly the same case (without group by f3):

      CREATE TABLE t1 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t1 VALUES (NULL,NULL,8);
       
      CREATE TABLE t2 ( pk int NOT NULL auto_increment, i1 int, id int, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t2 VALUES (63,NULL,NULL),(64,NULL,NULL),(65,NULL,NULL),(66,NULL,NULL),(69,NULL,NULL),(67,NULL,NULL),(68,NULL,NULL),(70,NULL,NULL),(78,NULL,NULL),(77,NULL,NULL),(75,NULL,NULL),(76,NULL,NULL);
       
      CREATE TABLE t3 ( id int, i1 int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t3 VALUES (NULL,NULL,3),(NULL,NULL,9);
       
      CREATE TABLE t4 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t4 VALUES (NULL,NULL,3);
       
      CREATE TABLE t5 ( i1 int, pk int NOT NULL auto_increment, id int, PRIMARY KEY (pk), KEY id (id));
      INSERT INTO t5 VALUES (NULL,116,NULL),(NULL,123,NULL),(NULL,124,NULL),(NULL,125,NULL),(NULL,126,NULL);
       
      CREATE VIEW v1 AS
      SELECT t2.i1, t2.id, t2.pk  FROM t1 JOIN t2
      WHERE exists 
      (SELECT at3.pk FROM (t3 JOIN t3 at3 on t3.pk = at3.i1 ) WHERE at3.id = t2.pk);
       
      CREATE PROCEDURE sp()
      SELECT
      	(SELECT max(v1.pk) FROM (v1 JOIN (t2 AS dt2 JOIN t4 AS dt4 
      		ON dt4.i1 = dt2.id)  ON dt4.i1 = dt2.id) 
      		WHERE (dt2.pk > v1.i1 AND v1.pk > v1.i1)) AS f1
      FROM t2 AS a1, v1 AS a2
      WHERE NOT EXISTS (SELECT t5.id FROM t5 WHERE t5.pk IN 
      	(SELECT t2.i1 FROM t2 LEFT JOIN t4 ON t4.i1 = t2.pk))
      GROUP BY f1;
       
      call sp();
      call sp();
      

      #3  <signal handler called>
      #4  0x000055fc5b85794f in get_sort_by_table (a=0x0, b=0x0, tables=..., const_tables=2) at /5.5/sql/sql_select.cc:21318
      #5  0x000055fc5b82cd67 in make_join_statistics (join=0x7fcf58786a40, tables_list=..., conds=0x7fcf587867b8, keyuse_array=0x7fcf58786d68) at /5.5/sql/sql_select.cc:3636
      #6  0x000055fc5b824a66 in JOIN::optimize (this=0x7fcf58786a40) at /5.5/sql/sql_select.cc:1264
      #7  0x000055fc5b82b3d1 in mysql_select (thd=0x7fcf5c7f10a0, rref_pointer_array=0x7fcf587a3f10, tables=0x7fcf58b5fda0, wild_num=0, fields=..., conds=0x7fcf587867b8, og_num=1, order=0x0, group=0x7fcf58b3c978, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7fcf58786a20, unit=0x7fcf587a3588, select_lex=0x7fcf587a3c70) at /5.5/sql/sql_select.cc:3121
      #8  0x000055fc5b821aab in handle_select (thd=0x7fcf5c7f10a0, lex=0x7fcf587a34d8, result=0x7fcf58786a20, setup_tables_done_option=0) at /5.5/sql/sql_select.cc:312
      #9  0x000055fc5b7fa7de in execute_sqlcom_select (thd=0x7fcf5c7f10a0, all_tables=0x7fcf58b5fda0) at /5.5/sql/sql_parse.cc:4683
      #10 0x000055fc5b7f370f in mysql_execute_command (thd=0x7fcf5c7f10a0) at /5.5/sql/sql_parse.cc:2221
      #11 0x000055fc5bad2544 in sp_instr_stmt::exec_core (this=0x7fcf58b3c9b8, thd=0x7fcf5c7f10a0, nextp=0x7fcf5f1ad368) at /5.5/sql/sp_head.cc:3221
      #12 0x000055fc5bad1cfd in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fcf58b3c9f8, thd=0x7fcf5c7f10a0, nextp=0x7fcf5f1ad368, open_tables=false, instr=0x7fcf58b3c9b8) at /5.5/sql/sp_head.cc:2999
      #13 0x000055fc5bad2272 in sp_instr_stmt::execute (this=0x7fcf58b3c9b8, thd=0x7fcf5c7f10a0, nextp=0x7fcf5f1ad368) at /5.5/sql/sp_head.cc:3145
      #14 0x000055fc5bace0ce in sp_head::execute (this=0x7fcf587a2878, thd=0x7fcf5c7f10a0, merge_da_on_success=true) at /5.5/sql/sp_head.cc:1432
      #15 0x000055fc5bacfe4a in sp_head::execute_procedure (this=0x7fcf587a2878, thd=0x7fcf5c7f10a0, args=0x7fcf5c7f5068) at /5.5/sql/sp_head.cc:2198
      #16 0x000055fc5b7f8c14 in mysql_execute_command (thd=0x7fcf5c7f10a0) at /5.5/sql/sql_parse.cc:4120
      #17 0x000055fc5b7fd38f in mysql_parse (thd=0x7fcf5c7f10a0, rawbuf=0x7fcf58b87078 "call sp()", length=9, parser_state=0x7fcf5f1ade50) at /5.5/sql/sql_parse.cc:5928
      #18 0x000055fc5b7f0cb2 in dispatch_command (command=COM_QUERY, thd=0x7fcf5c7f10a0, packet=0x7fcf5d339961 "call sp()", packet_length=9) at /5.5/sql/sql_parse.cc:1067
      #19 0x000055fc5b7efec7 in do_command (thd=0x7fcf5c7f10a0) at /5.5/sql/sql_parse.cc:793
      #20 0x000055fc5b8f4b2b in do_handle_one_connection (thd_arg=0x7fcf5c7f10a0) at /5.5/sql/sql_connect.cc:1268
      #21 0x000055fc5b8f48a4 in handle_one_connection (arg=0x7fcf5c7f10a0) at /5.5/sql/sql_connect.cc:1184
      #22 0x000055fc5bc75171 in pfs_spawn_thread (arg=0x7fcf5c81c140) at /5.5/storage/perfschema/pfs.cc:1015
      #23 0x00007fcf5fc0ffa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
      #24 0x00007fcf5f2fc4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      Also if prepared statement is used instead of procedure:

      sql/sql_select.cc:24906(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55d915db88f8]
      sql/sql_select.cc:5267(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55d915d29ced]
      sql/sql_select.cc:2242(JOIN::optimize_inner())[0x55d915d0a8df]
      sql/sql_select.cc:1618(JOIN::optimize())[0x55d915d03e3d]
      sql/sql_select.cc:4641(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55d915d2424b]
      sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55d915cf65b5]
      sql/sql_parse.cc:6209(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55d915c66cf0]
      sql/sql_parse.cc:3931(mysql_execute_command(THD*))[0x55d915c55f68]
      sql/sql_prepare.cc:4791(Prepared_statement::execute(String*, bool))[0x55d915cc38ea]
      sql/sql_prepare.cc:4280(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55d915cbf1f1]
      sql/sql_prepare.cc:3393(mysql_sql_stmt_execute(THD*))[0x55d915cb93f0]
      sql/sql_parse.cc:3948(mysql_execute_command(THD*))[0x55d915c55fad]
      sql/sql_parse.cc:7993(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d915c71e47]
      sql/sql_parse.cc:1869(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d915c488e2]
      sql/sql_parse.cc:1347(do_command(THD*))[0x55d915c45160]
      sql/sql_connect.cc:1411(do_handle_one_connection(CONNECT*, bool))[0x55d916073b5f]
      sql/sql_connect.cc:1315(handle_one_connection)[0x55d9160734b8]
      perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55d916d38531]
      nptl/pthread_create.c:463(start_thread)[0x7f66afa726db]
      x86_64/clone.S:97(clone)[0x7f66aec58a3f]
      

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.