Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.12, 10.3.4
-
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
- duplicates
-
MDEV-774 LP:948583 - Stored procedure doesn't not take into account ALTER TABLE, causes wrong result or replication abort
- Closed