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

Server crash when calling twice procedure using FOR-loop

Details

    Description

      Steps to reproduce:

      1. Create a function, which selects data from any table. It is not enough to just return some constant value.

      2. Create a view, which uses the function created in step 1.

      3. Create a procedure, which runs for-loop. This for loop must query view created on step 2. However, it should not use any columns returned from the query, but a constant, eg. 1, for each row.

      4. Call procedure twice from the same connection. First run success normally. The second one crashes the server without any explanation.

      Minimal Reproducible Example is on attachment server_crash.sql.

      All these details seem to be required to cause the server to crash. Interestingly enough, dropping some of them causes a different issue. If I select any value on cursor definition or skip the view and call the function directly, the first run results an error Table 'test_table' doesn't exist. Example of this is on file table_does_not_exist.sql.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          View 7 older comments
          sanja Oleksandr Byelkin added a comment - - edited

          This fixes suite without view, but test suite with a view crashes by other cause (maybe other bug or this patch should be impruved):

          diff --git a/sql/sp_head.cc b/sql/sp_head.cc
          		 
          index 57ab31d9edf..7f9e15d8dc9 100644
          		 
          --- a/sql/sp_head.cc
          		 
          +++ b/sql/sp_head.cc
          		 
          @@ -4584,6 +4584,7 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp)
          		 
             if (!row->arguments())
          		 
             {
          		 
               sp_cursor tmp(thd, &m_lex_keeper, true);
          		 
          +    TABLE_LIST **query_tables_last_save= m_lex_keeper.get_query_tables_last();
          		 
               // Open the cursor without copying data
          		 
               if (!(ret= tmp.open(thd)))
          		 
               {
          		 
          @@ -4605,6 +4606,7 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp)
          		 
                 thd->restore_active_arena(thd->spcont->callers_arena, &current_arena);
          		 
                 tmp.close(thd);
          		 
               }
          		 
          +    m_lex_keeper.set_query_tables_last(query_tables_last_save);
          		 
             }
          		 
             *nextp= m_ip + 1;
          		 
             DBUG_RETURN(ret);
          		 
          diff --git a/sql/sp_head.h b/sql/sp_head.h
          		 
          index e1cfbb484ad..1fa9232b0d9 100644
          		 
          --- a/sql/sp_head.h
          		 
          +++ b/sql/sp_head.h
          		 
          @@ -1214,6 +1214,17 @@ class sp_lex_keeper
          		 
               m_lex->safe_to_cache_query= 0;
          		 
             }
          		 
           
          +  TABLE_LIST **get_query_tables_last()
          		 
          +  {
          		 
          +    return m_lex->query_tables_last;
          		 
          +  }
          		 
          +
          		 
          +  void set_query_tables_last(TABLE_LIST ** t)
          		 
          +  {
          		 
          +    m_lex->query_tables_last= t;
          		 
          +  }
          		 
          +
          		 
          +
          		 
           private:
          		 
           
             LEX *m_lex;

          sanja Oleksandr Byelkin added a comment - - edited This fixes suite without view, but test suite with a view crashes by other cause (maybe other bug or this patch should be impruved): diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 57ab31d9edf..7f9e15d8dc9 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4584,6 +4584,7 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp) if (!row->arguments()) { sp_cursor tmp(thd, &m_lex_keeper, true); + TABLE_LIST **query_tables_last_save= m_lex_keeper.get_query_tables_last(); // Open the cursor without copying data if (!(ret= tmp.open(thd))) { @@ -4605,6 +4606,7 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp) thd->restore_active_arena(thd->spcont->callers_arena, &current_arena); tmp.close(thd); } + m_lex_keeper.set_query_tables_last(query_tables_last_save); } *nextp= m_ip + 1; DBUG_RETURN(ret); diff --git a/sql/sp_head.h b/sql/sp_head.h index e1cfbb484ad..1fa9232b0d9 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -1214,6 +1214,17 @@ class sp_lex_keeper m_lex->safe_to_cache_query= 0; } + TABLE_LIST **get_query_tables_last() + { + return m_lex->query_tables_last; + } + + void set_query_tables_last(TABLE_LIST ** t) + { + m_lex->query_tables_last= t; + } + + private: LEX *m_lex;
          elenst Elena Stepanova added a comment - - edited

          With the patch the test case with views

          • on debug build: fails a debug assertion on the first execution of the SP, which didn't happen before,
          • on non-debug build: fails same way as it did before, crashing in check_grant.

          Also, it's not exactly specific to a view – as often happens, the view in that test case can be replaced with a subquery with the same effect, as in 

          declare _cur cursor for select 1 from (select get_name(id) from t1) sq;

          So the patch may indeed need further improvement.

          elenst Elena Stepanova added a comment - - edited With the patch the test case with views on debug build: fails a debug assertion on the first execution of the SP, which didn't happen before, on non-debug build: fails same way as it did before, crashing in check_grant. Also, it's not exactly specific to a view – as often happens, the view in that test case can be replaced with a subquery with the same effect, as in declare _cur cursor for select 1 from ( select get_name(id) from t1) sq; So the patch may indeed need further improvement.
          sanja Oleksandr Byelkin added a comment -

          JFYI rewritten without FOR it still cause error where should not (but does not crash)

          CREATE TABLE t1 ( id int, name varchar(24));
          		 
          INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z');
          		 
           
          		 
          create function get_name(_id int) returns varchar(24)
          		 
              return (select name from t1 where id = _id);
          		 
           
          		 
          select get_name(id) from t1;
          		 
           
          		 
          delimiter ^^;
          		 
           
          		 
          create  procedure test_proc()
          		 
          begin
          		 
              DECLARE done INT DEFAULT FALSE;
          		 
              DECLARE rec ROW TYPE OF _cur;
          		 
              declare _cur cursor for select get_name(id) from t1;
          		 
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
          		 
              open _cur;
          		 
              read_loop: LOOP
          		 
                fetch _cur into rec;
          		 
                IF done THEN
          		 
                  LEAVE read_loop;
          		 
                END IF;
          		 
                select 1;
          		 
              end LOOP;
          		 
          end;
          		 
          ^^
          		 
          delimiter ;^^
          		 
           
          		 
          --error 1146
          		 
          call test_proc();
          		 
          --error 1146
          		 
          call test_proc();

          sanja Oleksandr Byelkin added a comment - JFYI rewritten without FOR it still cause error where should not (but does not crash) CREATE TABLE t1 ( id int, name varchar(24)); INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z');   create function get_name(_id int) returns varchar(24) return (select name from t1 where id = _id);   select get_name(id) from t1;   delimiter ^^;   create procedure test_proc() begin DECLARE done INT DEFAULT FALSE; DECLARE rec ROW TYPE OF _cur; declare _cur cursor for select get_name(id) from t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open _cur; read_loop: LOOP fetch _cur into rec; IF done THEN LEAVE read_loop; END IF; select 1; end LOOP; end; ^^ delimiter ;^^   --error 1146 call test_proc(); --error 1146 call test_proc();
          sanja Oleksandr Byelkin added a comment -

          My first patch does not work with views because view also add tables. So I come up with one lined fix which work with views.

          sanja Oleksandr Byelkin added a comment - My first patch does not work with views because view also add tables. So I come up with one lined fix which work with views.
          sanja Oleksandr Byelkin added a comment - 

          commit d8e915a88fcdebd0b42f10b2f6d2165296056dd1 (HEAD -> bb-10.3-MDEV-26009, origin/bb-10.3-MDEV-26009)
          		 
          Author: Oleksandr Byelkin <sanja@mariadb.com>
          		 
          Date:   Fri Mar 18 11:13:09 2022 +0100
          		 
           
          		 
              MDEV-26009 Server crash when calling twice procedure using FOR-loop
          		 
              
              The problem was that instructions sp_instr_cursor_copy_struct and
          		 
              sp_instr_copen uses the same lex, adding and removing "tail" of
          		 
              prelocked tables and forgetting that tail of all tables is kept in
          		 
              LEX::query_tables_last. If the LEX used only by one instruction
          		 
              or the query do not have prelocked tables it is not important.
          		 
              But to work correctly in all cases LEX::query_tables_last should
          		 
              be reset to make new tables added in the correct list (after last
          		 
              table in the LEX instead after last table of the prelocking "tail"
          		 
              which was cut).

          sanja Oleksandr Byelkin added a comment - commit d8e915a88fcdebd0b42f10b2f6d2165296056dd1 (HEAD -> bb-10.3-MDEV-26009, origin/bb-10.3-MDEV-26009) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Fri Mar 18 11:13:09 2022 +0100   MDEV-26009 Server crash when calling twice procedure using FOR-loop The problem was that instructions sp_instr_cursor_copy_struct and sp_instr_copen uses the same lex, adding and removing "tail" of prelocked tables and forgetting that tail of all tables is kept in LEX::query_tables_last. If the LEX used only by one instruction or the query do not have prelocked tables it is not important. But to work correctly in all cases LEX::query_tables_last should be reset to make new tables added in the correct list (after last table in the LEX instead after last table of the prelocking "tail" which was cut).

          People

            sanja Oleksandr Byelkin
            pauluslimma Paulus Limma
            Votes:
            0 Vote for this issue
            Watchers:
            9 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.