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

Stored procedures are unaware of metadata changes

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.12, 10.3.4
    • N/A
    • Stored routines
    • None

    Description

      In MySQL 5.6 stored routines are aware of metadata changes, and automatic re-prepare happens in a similar fashion as with prepared statements, see e.g.:

      http://alexandernozdrin.blogspot.de/2012/09/mysql-56-handling-metadata-changes-in.html

      MariaDB stored procedures are not aware of metadata changes though.

      Example:

      Setup:

      MariaDB [test]> CREATE TABLE t1(a INT);
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> insert into t1 values(1),(2),(3);
      Query OK, 3 rows affected (0.02 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> CREATE PROCEDURE p1()     SELECT * FROM t1;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> call p1();
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.01 sec)
       
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]> ALTER TABLE t1 ADD COLUMN b INT DEFAULT -1;
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      Now calling the procedure again in the same session
      MySQL 5.6 returns:

      MySQL[test]> call p1();
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |   -1 |
      |    2 |   -1 |
      |    3 |   -1 |
      +------+------+
      3 rows in set (0.00 sec)
       
      Query OK, 0 rows affected (0.00 sec)
      

      MariaDB does not notice the schema change though, and so the procedure still returns only one column instead of two when called from the same session as before:

      MariaDB [test]> call p1();
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.00 sec)
       
      Query OK, 0 rows affected (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.