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

Calling stored procedure twice in the same session causes MariaDB to crash

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3.16, 10.3.18, 10.3.22, 10.3(EOL), 10.4(EOL)
    • 10.3.23, 10.4.13, 10.5.3
    • Stored routines
    • None
    • Reproduced in CentOS 7 and official MariaDB 10.3 Docker container

    Description

      When running the specified stored procedure twice in the same mysql CLI session, MariaDB crashes without providing an error to the error log (the session simply sees ERROR 2013 (HY000): Lost connection to MySQL server during query).

      To reproduce-

      1. Setup a Linux environment with MariaDB 10.3 installed/running
      2. Ensure at least the following is configured in my.cnf (or similar)-
        1. [server]
          lower_case_table_names=1

      3. Create a new database called test (assuming one of the same name doesn't exist on your system already)
      4. Import the contents of 35872.sql (attached to this issue) into your new, test database
      5. On the mysql CLI...
        1. USE test ;
          CALL YAR_FUNC(str_to_date('2019-07-01', '%Y-%m-%d'),str_to_date('2019-10-01', '%Y-%m-%d'),0,'TEST_ANY_80_2','--MA--','Branch/Subsidiary','Locomotive Branch','GB_A_MOP_GENERAL_XTTY') ;
          CALL YAR_FUNC(str_to_date('2019-07-01', '%Y-%m-%d'),str_to_date('2019-10-01', '%Y-%m-%d'),0,'TEST_ANY_80_2','--MA--','Branch/Subsidiary','Locomotive Branch','GB_A_MOP_GENERAL_XTTY') ;

        2. Note that you can make other queries between the two CALL YAR_FUNC commands and the outcome will remain the same
      6. You should immediately experience a loss of connectivity, and if you check MariaDB's error log you should see evidence of a shutdown/restart

      Backtrace-

      #0  0x00007fb72f7157ab in __GI___clock_gettime (clock_id=clock_id@entry=0, tp=tp@entry=0x7fb7151e43b0) at ../sysdeps/unix/clock_gettime.c:115
      #1  0x000055fdd6d0c113 in my_hrtime () at /usr/src/debug/MariaDB-10.3.22/src_0/mysys/my_getsystime.c:94
      #2  0x000055fdd67b31e4 in handle_fatal_signal (sig=11) at /usr/src/debug/MariaDB-10.3.22/src_0/sql/signal_handler.cc:124
      #3  <signal handler called>
      #4  __memcpy_ssse3 () at ../sysdeps/x86_64/multiarch/memcpy-ssse3.S:2835
      #5  0x000055fdd6655728 in memcpy (__len=4294967292, __src=0x55fdda190a95, __dest=<optimized out>) at /usr/include/bits/string3.h:51
      #6  String::append (this=this@entry=0x7fb7151e4f00,
          s=0x55fdda190a95 " \n\tAND th.nodeid = p_BranchSubsidary \n\tAND FIND_IN_SET(th.child_nodeid, p_Node)\n),\nDISTINCT_BACKTESTCONFIG AS (\n\tSELECT DISTINCT\n\t   backtest_confignum, var_datasetnum\n\tFROM backtest_config\n\tWHERE bac"..., size=4294967292) at /usr/src/debug/MariaDB-10.3.22/src_0/sql/sql_string.cc:555
      #7  0x000055fdd654c045 in copy_up_to (bytes=<optimized out>, this=<synthetic pointer>) at /usr/src/debug/MariaDB-10.3.22/src_0/sql/item.h:554
      #8  append (p=0x55fdda1d1268, this=<synthetic pointer>) at /usr/src/debug/MariaDB-10.3.22/src_0/sql/item.h:564
      #9  subst_spvars (instr=0x55fdda190448, query_str=0x55fdda190480, thd=0x55fdda0c75e8) at /usr/src/debug/MariaDB-10.3.22/src_0/sql/sp_head.cc:1083
      #10 sp_instr_stmt::execute (this=0x55fdda190448, thd=0x55fdda0c75e8, nextp=0x7fb7151e5230) at /usr/src/debug/MariaDB-10.3.22/src_0/sql/sp_head.cc:3503

      Attachments

        Activity

          test suite in terms of our test suite language

           
          --source include/have_innodb.inc
           
          CREATE or REPLACE TABLE `t1` (
          `id1` int(1),
          `id2` varchar(5)
          ) ENGINE=InnoDB;
           
          CREATE or REPLACE TABLE `t2` (
          `id1` int(1)
          ) ENGINE=InnoDB;
           
          CREATE or REPLACE TABLE `t3` (
          `id1` int(1)
          ) ENGINE=InnoDB;
           
          DELIMITER //;
          CREATE OR REPLACE PROCEDURE SP1()
          BEGIN
          DECLARE p_var1 int(1);
          DECLARE p_var2 varchar(5);
          SET p_var1=1;
          SET p_var2='WPAFB';
          WITH XXXXXXXXXXXX AS
          (
              SELECT id1 AS id1 FROM t1
              WHERE id1 = p_var1 
              AND id2 = p_var2 
          ),
          COL2 AS (
              SELECT id1 FROM t2 
              WHERE id1 IN ( SELECT id1 FROM XXXXXXXXXXXX )
          ),
          COL3 AS (
              SELECT id1 FROM t3
              WHERE id1 IN ( SELECT id1 FROM XXXXXXXXXXXX )
          ) SELECT * FROM COL3;
          END//
          DELIMITER ;//
           
          CALL SP1();
          CALL SP1();
          
          

          sanja Oleksandr Byelkin added a comment - test suite in terms of our test suite language   --source include/have_innodb.inc   CREATE or REPLACE TABLE `t1` ( `id1` int(1), `id2` varchar(5) ) ENGINE=InnoDB; CREATE or REPLACE TABLE `t2` ( `id1` int(1) ) ENGINE=InnoDB; CREATE or REPLACE TABLE `t3` ( `id1` int(1) ) ENGINE=InnoDB; DELIMITER //; CREATE OR REPLACE PROCEDURE SP1() BEGIN DECLARE p_var1 int(1); DECLARE p_var2 varchar(5); SET p_var1=1; SET p_var2='WPAFB'; WITH XXXXXXXXXXXX AS ( SELECT id1 AS id1 FROM t1 WHERE id1 = p_var1 AND id2 = p_var2 ), COL2 AS ( SELECT id1 FROM t2 WHERE id1 IN ( SELECT id1 FROM XXXXXXXXXXXX ) ), COL3 AS ( SELECT id1 FROM t3 WHERE id1 IN ( SELECT id1 FROM XXXXXXXXXXXX ) ) SELECT * FROM COL3; END// DELIMITER ;//   CALL SP1(); CALL SP1();

          can not be reproduced on 10.2

          sanja Oleksandr Byelkin added a comment - can not be reproduced on 10.2

          It important to have 2 CTE inside other one, type of subquery, position in the query, and table type are not important, so here is even more simple test suite:

          CREATE or REPLACE TABLE `t1` (
          `id1` int(1),
          `id2` varchar(5)
          );
           
          CREATE or REPLACE TABLE `t2` (
          `id1` int(1)
          );
           
           
          DELIMITER //;
          CREATE OR REPLACE PROCEDURE SP1()
          BEGIN
            DECLARE p_var1 int(1);
            DECLARE p_var2 varchar(5);
            SET p_var1=1;
            SET p_var2='WPAFB';
            WITH XXXXXXXXXXXX AS
            (
              SELECT id1 AS id1 FROM t1
              WHERE id1 = p_var1 
              AND id2 = p_var2 
            ),
            COL3 AS (
              SELECT (SELECT id1 FROM XXXXXXXXXXXX LIMIT 1) = 1 or
                (SELECT id1 FROM XXXXXXXXXXXX LIMIT 1) = 2 FROM t2
            ) SELECT  FROM COL3;
          END//
          DELIMITER ;//
           
          CALL SP1();
          CALL SP1();
          

          sanja Oleksandr Byelkin added a comment - It important to have 2 CTE inside other one, type of subquery, position in the query, and table type are not important, so here is even more simple test suite: CREATE or REPLACE TABLE `t1` ( `id1` int(1), `id2` varchar(5) ); CREATE or REPLACE TABLE `t2` ( `id1` int(1) ); DELIMITER //; CREATE OR REPLACE PROCEDURE SP1() BEGIN DECLARE p_var1 int(1); DECLARE p_var2 varchar(5); SET p_var1=1; SET p_var2='WPAFB'; WITH XXXXXXXXXXXX AS ( SELECT id1 AS id1 FROM t1 WHERE id1 = p_var1 AND id2 = p_var2 ), COL3 AS ( SELECT (SELECT id1 FROM XXXXXXXXXXXX LIMIT 1) = 1 or (SELECT id1 FROM XXXXXXXXXXXX LIMIT 1) = 2 FROM t2 ) SELECT FROM COL3; END// DELIMITER ;//   CALL SP1(); CALL SP1();

          Ok to push

          sanja Oleksandr Byelkin added a comment - Ok to push
          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.3

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.3

          People

            igor Igor Babaev
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            7 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.