Details
-
Type:
Bug
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 5.3.9, 5.3.12, 10.0, 5.5
-
Fix Version/s: 10.1
-
Component/s: Data Definition - Alter Table
-
Labels:
Description
The problem is reproducible on all of MariaDB 5.1, 5.2, 5.3, 5.5, MySQL 5.1, 5.5, 5.6, and has also been filed as http://bugs.mysql.com/bug.php?id=64574.
A stored procedure or a trigger, having been executed once, on the second run might not take into account changes in a table structure, which causes a wrong result on the server where it is run, and also might cause SQL slave abort if the master wrongly returns OK after such a change, while the slave throws the expected error.
Below are two test cases. The first one is for the basic scenario: the initial execution of the stored procedure ends with the expected error, then the table structure gets fixed, but the procedure fails anyway, with
query 'CALL p()' failed: 1054: Unknown column 'test.t2.b' in 'field list'
The second test case is a reversed scenario – first execution of the procedure runs fine (correctly), then the table structure is altered, and the second execution must fail, but it does not on master; instead, the statement is written in the binlog with error code 0 and later fails on slave, thus causing a replication failure:
1136 Error 'Column count doesn't match value count at row 1' on query.
I found the ancient bug http://bugs.mysql.com/bug.php?id=6120 which looks similar, only for views, but it was fixed ages ago.
Test case 1 (wrong result - the last procedure call fails while it shouldn't)
CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT, c INT); --delimiter | CREATE PROCEDURE p() BEGIN INSERT INTO t1 SELECT * FROM t2; END | --delimiter ; --error ER_WRONG_VALUE_COUNT_ON_ROW CALL p(); ALTER TABLE t2 DROP COLUMN b; CALL p();
Test case 2 (replication failure)
--source include/master-slave.inc CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT); --delimiter | CREATE PROCEDURE p() BEGIN INSERT INTO t1 SELECT * FROM t2; END | --delimiter ; CALL p(); ALTER TABLE t2 ADD COLUMN c INT; CALL p(); --sync_slave_with_master
Gliffy Diagrams
Attachments
Issue Links
- is part of
-
MDEV-5242 merge 5.6 bugfixes into 10.0
-
- Open
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
fixed in mysql-5.6.6
The upstream bug was fixed in 5.6.6. I think it's okay to have the fix in 10.0 only (after the fix from 5.6 is merged in), so I'm removing 5.x from the 'Fix versions' list.
Still reproducible on current 10.0 (10.0.14+, revno 4471).
Upstream bug was fixed in 5.6.6, apparently as a part of http://dev.mysql.com/worklog/task/?id=4179
evno: 3857 committer: Alexander Nozdrin <alexander.nozdrin@oracle.com> branch nick: trunk-stage.2 timestamp: Wed 2012-05-16 16:36:41 +0400 message: WL#4179: Stored programs: validation of stored program statements. Briefly, that's the implementation of WL#4179. The idea is to remember the original query for SQL-statement within a Stored Programs and re-parse it when meta-data has changed.
It might be too big to get it in 10.0, but maybe 10.1 is okay, since it's not a feature as such, but a big fat bugfix.
Launchpad bug id: 948583