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

Stored routine with a cursor crashes on the second execution if a DDL statement happened

Details

    Description

      This problem seems to be caused by MDEV-5816, but I'm not 100% sure. Leaving it to shulga to decide.

      This script works in MariaDB 10.11, 11.4, 11.6:

      CREATE OR REPLACE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1);
      		 
       
      		 
      DELIMITER $$
      		 
      CREATE OR REPLACE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE va INT DEFAULT 0;
      		 
        DECLARE cur CURSOR FOR SELECT a FROM t1;
      		 
        OPEN cur;
      		 
        FETCH cur INTO va;
      		 
        SELECT va;
      		 
        CLOSE cur;  
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1;
      		 
      ALTER TABLE t1 MODIFY a INT UNSIGNED;
      		 
      CALL p1;

      and returns this result:

      +------+
      		 
      | va   |
      		 
      +------+
      		 
      |    1 |
      		 
      +------+
      		 
      ...
      		 
      +------+
      		 
      | va   |
      		 
      +------+
      		 
      |    1 |
      		 
      +------+

      If I run the same script in 11.8, the server crashes with the following stack trace:

      #0  0x00007ffff72ae8a4 in __pthread_kill_implementation ()
      		 
         from /lib64/libc.so.6
      		 
      #1  0x00007ffff725c8ee in raise () from /lib64/libc.so.6
      		 
      #2  0x00007ffff72448ff in abort () from /lib64/libc.so.6
      		 
      #3  0x00007ffff724481b in __assert_fail_base.cold () from /lib64/libc.so.6
      		 
      #4  0x00007ffff7254c57 in __assert_fail () from /lib64/libc.so.6
      		 
      #5  0x0000000005dd0894 in alloc_root (mem_root=0x5250002b9ab0, length=160)
      		 
          at /home/bar/maria-git/11.8/mysys/my_alloc.c:278
      		 
      #6  0x00000000013b8344 in Query_arena::calloc<Item*> (this=0x5250002c0710, 
          size=20) at /home/bar/maria-git/11.8/sql/sql_class.h:1289
      		 
      #7  0x00000000012e7aa2 in st_select_lex::setup_ref_array (this=0x5250002ee1c8, 
          thd=0x52b00016c288, order_group_num=0)
      		 
          at /home/bar/maria-git/11.8/sql/sql_lex.cc:3676
      		 
      #8  0x00000000017309f5 in JOIN::prepare (this=0x5250002e6d98, 
          tables_init=0x5250002ee840, conds_init=0x0, og_num=0, order_init=0x0, 
          skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, 
          select_lex_arg=0x5250002ee1c8, unit_arg=0x5250002bea88)
      		 
          at /home/bar/maria-git/11.8/sql/sql_select.cc:1553
      		 
      #9  0x00000000017196e6 in mysql_select (thd=0x52b00016c288, 
          tables=0x5250002ee840, fields=..., conds=0x0, og_num=0, order=0x0, 
          group=0x0, having=0x0, proc_param=0x0, select_options=2164526848, 
          result=0x5250002e6b38, unit=0x5250002bea88, select_lex=0x5250002ee1c8)
      		 
          at /home/bar/maria-git/11.8/sql/sql_select.cc:5331
      		 
      #10 0x0000000001716a56 in handle_select (thd=0x52b00016c288, 
          lex=0x5250002be9a8, result=0x5250002e6b38, setup_tables_done_option=0)
      		 
          at /home/bar/maria-git/11.8/sql/sql_select.cc:633
      		 
      #11 0x00000000014bc41a in execute_sqlcom_select (thd=0x52b00016c288, 
          all_tables=0x5250002ee840)
      		 
          at /home/bar/maria-git/11.8/sql/sql_parse.cc:6191
      		 
      #12 0x000000000147b72f in mysql_execute_command (thd=0x52b00016c288, 
          is_called_from_prepared_stmt=false)
      		 
          at /home/bar/maria-git/11.8/sql/sql_parse.cc:3980
      		 
      #13 0x0000000001137b67 in mysql_open_cursor (thd=0x52b00016c288, 
          result=0x5250002be0c8, pcursor=0x5250002be100)
      		 
          at /home/bar/maria-git/11.8/sql/sql_cursor.cc:135
      		 
      #14 0x0000000000ddebd6 in sp_cursor::open (this=0x5250002be0a8, 
          thd=0x52b00016c288) at /home/bar/maria-git/11.8/sql/sp_rcontext.cc:719
      		 
      #15 0x00000000026449c8 in sp_instr_cpush::exec_core (this=0x5250002be028, 
          thd=0x52b00016c288, nextp=0x7fffc2b021a0)
      		 
          at /home/bar/maria-git/11.8/sql/sp_instr.cc:1745
      		 
      #16 0x000000000261fa6b in sp_lex_keeper::reset_lex_and_exec_core (
      		 
          this=0x5250002be060, thd=0x52b00016c288, nextp=0x7fffc2b021a0, 
          open_tables=false, instr=0x5250002be028, rerun_the_same_instr=true)
      		 
          at /home/bar/maria-git/11.8/sql/sp_instr.cc:314
      		 
      #17 0x0000000002623e2b in sp_lex_keeper::validate_lex_and_exec_core (
      		 
          this=0x5250002be060, thd=0x52b00016c288, nextp=0x7fffc2b021a0, 
          open_tables=false, instr=0x5250002be028)
      		 
          at /home/bar/maria-git/11.8/sql/sp_instr.cc:493
      		 
      #18 0x000000000262a3eb in sp_lex_keeper::cursor_reset_lex_and_exec_core (
      		 
          this=0x5250002be060, thd=0x52b00016c288, nextp=0x7fffc2b021a0, 
          open_tables=false, instr=0x5250002be028)
      		 
          at /home/bar/maria-git/11.8/sql/sp_instr.cc:548
      		 
      #19 0x00000000026460aa in sp_instr_copen::execute (this=0x5250002c0748, 
          thd=0x52b00016c288, nextp=0x7fffc2b021a0)
      		 
          at /home/bar/maria-git/11.8/sql/sp_instr.cc:1853
      		 
      #20 0x0000000000d6a351 in sp_head::execute (this=0x5250002b9a70, 
          thd=0x52b00016c288, merge_da_on_success=true)
      		 
          at /home/bar/maria-git/11.8/sql/sp_head.cc:1289
      		 
      #21 0x0000000000d7e863 in sp_head::execute_procedure (this=0x5250002b9a70, 
          thd=0x52b00016c288, args=0x52b000171520)
      		 
          at /home/bar/maria-git/11.8/sql/sp_head.cc:2305
      		 
      #22 0x00000000014666ca in do_execute_sp (thd=0x52b00016c288, sp=0x5250002b9a70)
      		 
          at /home/bar/maria-git/11.8/sql/sql_parse.cc:3086
      		 
      #23 0x000000000146464d in Sql_cmd_call::execute (this=0x52d0003f2570, 
          thd=0x52b00016c288) at /home/bar/maria-git/11.8/sql/sql_parse.cc:3309
      		 
      #24 0x00000000014aca40 in mysql_execute_command (thd=0x52b00016c288, 
          is_called_from_prepared_stmt=false)
      		 
          at /home/bar/maria-git/11.8/sql/sql_parse.cc:5886
      		 
      #25 0x000000000143c499 in mysql_parse (thd=0x52b00016c288, 
          rawbuf=0x52d0003f24a8 "CALL p1", length=7, parser_state=0x7fffc2ce47f0)
      		 
          at /home/bar/maria-git/11.8/sql/sql_parse.cc:7915
      		 
      #26 0x00000000014258a9 in dispatch_command (command=COM_QUERY, 
          thd=0x52b00016c288, packet=0x5290000f0289 "CALL p1", packet_length=7, 
          blocking=true) at /home/bar/maria-git/11.8/sql/sql_parse.cc:1903
      		 
      #27 0x0000000001443b49 in do_command (thd=0x52b00016c288, blocking=true)
      		 
          at /home/bar/maria-git/11.8/sql/sql_parse.cc:1416
      		 
      #28 0x0000000002117df9 in do_handle_one_connection (connect=0x5110000839c8, 
          put_in_cache=true) at /home/bar/maria-git/11.8/sql/sql_connect.cc:1415
      		 
      #29 0x0000000002116bc2 in handle_one_connection (arg=0x5110000839c8)
      		 
          at /home/bar/maria-git/11.8/sql/sql_connect.cc:1327

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36390 Minor refactoring of the method get_expr_query at the classes sp_instr_cpush/sp_instr_cursor_copy_struct

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - edited

            Hi shulga

            A related problem: the code below will possibly fail if we send a query with a new line or TAB instead of the space immediately after FOR/IS:

              LEX_CSTRING get_expr_query() const override
            		 
              {
            		 
                /*
            		 
                  Lexer on processing the clause CURSOR FOR / CURSOR IS doesn't
            		 
                  move a pointer on cpp_buf after the token FOR/IS so skip it explicitly
            		 
                  in order to get correct value of cursor's query string.
            		 
                */
            		 
                if (strncasecmp(m_cursor_stmt.str, "FOR ", 4) == 0)
            		 
                  return LEX_CSTRING{m_cursor_stmt.str + 4, m_cursor_stmt.length - 4};
            		 
                if (strncasecmp(m_cursor_stmt.str, "IS ", 3) == 0)
            		 
                  return LEX_CSTRING{m_cursor_stmt.str + 3, m_cursor_stmt.length - 3};
            		 
                return m_cursor_stmt;
            		 
              }

            Also this code is duplicated two times. Please move it as a protected method, say get_expr_query_for_cursor_stmt(), into an upper class and reuse it in the low level classes.

            bar Alexander Barkov added a comment - - edited Hi shulga A related problem: the code below will possibly fail if we send a query with a new line or TAB instead of the space immediately after FOR/IS: LEX_CSTRING get_expr_query() const override { /* Lexer on processing the clause CURSOR FOR / CURSOR IS doesn't move a pointer on cpp_buf after the token FOR/IS so skip it explicitly in order to get correct value of cursor's query string. */ if (strncasecmp(m_cursor_stmt.str, "FOR " , 4) == 0) return LEX_CSTRING{m_cursor_stmt.str + 4, m_cursor_stmt.length - 4}; if (strncasecmp(m_cursor_stmt.str, "IS " , 3) == 0) return LEX_CSTRING{m_cursor_stmt.str + 3, m_cursor_stmt.length - 3}; return m_cursor_stmt; } Also this code is duplicated two times. Please move it as a protected method, say get_expr_query_for_cursor_stmt() , into an upper class and reuse it in the low level classes.
            shulga Dmitry Shulga added a comment -

            The branch for review is bb-11.8-MDEV-36079

            shulga Dmitry Shulga added a comment - The branch for review is bb-11.8- MDEV-36079
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push
            shulga Dmitry Shulga added a comment - - edited

            ralf.gebhardt This regression was indirectly caused by the tasks MDEV-5816, MDEV-34517

            shulga Dmitry Shulga added a comment - - edited ralf.gebhardt This regression was indirectly caused by the tasks MDEV-5816 , MDEV-34517

            People

              shulga Dmitry Shulga
              bar Alexander Barkov
              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.