| Bug #32868 | Stored routines do not detect changes in meta-data. | ||
|---|---|---|---|
| Submitted: | 30 Nov 2007 9:40 | Modified: | 20 Jun 2012 16:51 |
| Reporter: | Alexander Nozdrin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.1, 5.0 BK, 5.1.24-BK | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Nov 2007 9:51]
Sveta Smirnova
Thank you for the report. Verified as described.
[5 Dec 2007 14:31]
Alexander Nozdrin
See also: Bug#33000 (Triggers do not detect changes in meta-data.)
[25 Feb 2008 18:37]
Valeriy Kravchuk
Bug is still repeatable with latest 5.1.24-BK.
[17 May 2008 14:02]
Sveta Smirnova
Bug #36774 was marked as duplicate of this one.
[21 Oct 2008 5:39]
Sveta Smirnova
Bug #40191 was marked as duplicate of this one.
[23 Mar 2009 6:54]
Sveta Smirnova
Bug #43792 was marked as duplicate of this one.
[27 Mar 2009 18:29]
Konstantin Osipov
Risk and effort are for 6.0. In 5.1 both are higher (the fix depends on WL#3726 and WL#4284).
[13 Oct 2010 12:29]
Davi Arnaut
Bug#54309 has been closed as a duplicate of this one.
[15 Sep 2011 4:03]
Valeriy Kravchuk
Bug #62438 was marked as a duplicate of this one.
[16 Sep 2011 6:11]
MySQL Verification Team
Workaround: flush the stored routine cache by doing this: CREATE OR REPLACE VIEW `tmpview` AS SELECT 1;
[20 Jun 2012 16:51]
Paul DuBois
Noted in 5.6.6 changelog. Metadata was handled incorrectly for objects such as tables or views that were used in a stored program. Metadata for each such object was gathered at the beginning of program execution, but not updated if DDL statements modified the object during program execution (or in later executions of the program if it remained in the stored program cache). This resulted in mismatches between the actual object structure and the structure used within the stored program and caused problems such as data errors or server crashes.
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'