Description:
The SP-cache does not detect when meta-data of the underlying objects
is changed. So, if a stored routine uses a table and the table was changed
since the stored routine was loaded in the cache, the stored routine
will operate with the outdated meta-data.
In particular:
- if a stored procedure uses a table, and a new column is added to
the table after the stored procedure was firstly executed,
further executions of the store procedure will not detect the new
column.
- if a stored procedure uses a table, and a column is dropped after
the stored procedure was firstly exectured, the stored procedure
will return an error on further executions.
How to repeat:
1. A new column is added after first execution:
> CREATE TABLE t1(a INT, b INT);
> INSERT INTO t1 VALUES (1, 2), (3, 4);
> CREATE PROCEDURE p1 SELECT * FROM t1;
> CALL p1(); -- at this point the procedure is compiled
-- and put into the cache.
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.03 sec)
> ALTER TABLE t1 ADD COLUMN c INT AFTER b;
> SELECT * FROM t1;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | NULL |
| 3 | 4 | NULL |
+------+------+------+
2 rows in set (0.01 sec)
> CALL p1(); -- the procedure from the cache is used;
-- no new column in the result.
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.00 sec)
If the user triggers the SP-cache invalidation somehow at this point,
and repeat then "CALL p1()", the procedure will return the correct
recordset (of three columns).
2. An existing column is dropped after first execution:
> CREATE TABLE t1(a INT, b INT);
> INSERT INTO t1 VALUES (1, 2), (3, 4);
> CREATE PROCEDURE p1() SELECT * FROM t1;
> CALL p1();
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.03 sec)
> ALTER TABLE t1 DROP COLUMN b;
> CALL p1();
ERROR 1054 (42S22): Unknown column 'db1.t1.b' in 'field list'
Description: The SP-cache does not detect when meta-data of the underlying objects is changed. So, if a stored routine uses a table and the table was changed since the stored routine was loaded in the cache, the stored routine will operate with the outdated meta-data. In particular: - if a stored procedure uses a table, and a new column is added to the table after the stored procedure was firstly executed, further executions of the store procedure will not detect the new column. - if a stored procedure uses a table, and a column is dropped after the stored procedure was firstly exectured, the stored procedure will return an error on further executions. How to repeat: 1. A new column is added after first execution: > CREATE TABLE t1(a INT, b INT); > INSERT INTO t1 VALUES (1, 2), (3, 4); > CREATE PROCEDURE p1 SELECT * FROM t1; > CALL p1(); -- at this point the procedure is compiled -- and put into the cache. +------+------+ | a | b | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.03 sec) > ALTER TABLE t1 ADD COLUMN c INT AFTER b; > SELECT * FROM t1; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | NULL | | 3 | 4 | NULL | +------+------+------+ 2 rows in set (0.01 sec) > CALL p1(); -- the procedure from the cache is used; -- no new column in the result. +------+------+ | a | b | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec) If the user triggers the SP-cache invalidation somehow at this point, and repeat then "CALL p1()", the procedure will return the correct recordset (of three columns). 2. An existing column is dropped after first execution: > CREATE TABLE t1(a INT, b INT); > INSERT INTO t1 VALUES (1, 2), (3, 4); > CREATE PROCEDURE p1() SELECT * FROM t1; > CALL p1(); +------+------+ | a | b | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.03 sec) > ALTER TABLE t1 DROP COLUMN b; > CALL p1(); ERROR 1054 (42S22): Unknown column 'db1.t1.b' in 'field list'