Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.9, 5.3.12, 5.5(EOL), 10.0(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
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
|
|
Attachments
Issue Links
- duplicates
-
MDEV-11652 SP with cursors does not work well in combination with ALTER TABLE
-
- Closed
-
- is duplicated by
-
MDEV-15097 Stored procedures are unaware of metadata changes
-
- Closed
-
- is part of
-
MDEV-5242 merge 5.6 bugfixes into 10.0
-
- Closed
-
-
MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements
-
- Closed
-
- relates to
-
MDEV-6488 Autodiscover + CONNECT + OR REPLACE
-
- Closed
-
-
MDEV-12166 PROCEDURE using a SELECT from a temporary table does not work well
-
- Closed
-
-
MDEV-21234 Server crashes in in setup_on_expr upon 3rd execution of SP
-
- Closed
-
- links to
Activity
Field | Original Value | New Value |
---|---|---|
Labels | Launchpad |
Labels | Launchpad | MariaDB_5.1 |
Labels | MariaDB_5.1 | Launchpad MariaDB_5.1 |
Fix Version/s | Maria 5.1 [ 11600 ] |
Fix Version/s | 5.5.29 [ 11701 ] | |
Fix Version/s | 5.3.10 [ 11500 ] | |
Fix Version/s | 5.2.13 [ 10800 ] | |
Fix Version/s | 5.1.65 [ 10801 ] |
Labels | Launchpad MariaDB_5.1 | Launchpad |
Affects Version/s | 5.3.9 [ 11000 ] |
Fix Version/s | 5.1.67 [ 12100 ] | |
Fix Version/s | 5.1.66 [ 10801 ] |
Fix Version/s | 5.2.14 [ 12101 ] | |
Fix Version/s | 5.2.13 [ 10800 ] |
Fix Version/s | 5.5.29 [ 12102 ] | |
Fix Version/s | 5.5.28a [ 11701 ] |
Fix Version/s | 5.3.12 [ 12000 ] | |
Fix Version/s | 5.3.10 [ 11500 ] |
Labels | Launchpad | Launchpad upstream |
Remote Link | This issue links to "Bug #64574 Stored procedure doesn't not notice ALTER TABLE, might cause replication abort (Web Link)" [ 10013 ] |
Fix Version/s | 5.1.68 [ 12600 ] | |
Fix Version/s | 5.1.67 [ 12100 ] |
Fix Version/s | 5.2.15 [ 12601 ] | |
Fix Version/s | 5.2.14 [ 12101 ] |
Fix Version/s | 5.3.13 [ 12602 ] | |
Fix Version/s | 5.3.12 [ 12000 ] |
Fix Version/s | 5.5.30 [ 11800 ] | |
Fix Version/s | 5.5.29 [ 12102 ] |
Fix Version/s | 5.5.31 [ 12700 ] | |
Fix Version/s | 5.5.30 [ 11800 ] |
Fix Version/s | 10.0.3 [ 12900 ] | |
Fix Version/s | 5.1.68 [ 12600 ] | |
Fix Version/s | 5.2.15 [ 12601 ] | |
Fix Version/s | 5.3.13 [ 12602 ] | |
Fix Version/s | 5.5.31 [ 12700 ] | |
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) 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(); # End of test case 1 ############## # 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 # End of test case 2 |
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 {noformat} query 'CALL p()' failed: 1054: Unknown column 'test.t2.b' in 'field list' {noformat} 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: {noformat} 1136 Error 'Column count doesn't match value count at row 1' on query. {noformat} 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) {code:sql} 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(); {code} Test case 2 (replication failure) {code:sql} --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 {code} |
Remote Link | This issue links to "Bug #64574 (Web Link)" [ 11202 ] |
Remote Link | This issue links to "Bug #64574 (Web Link)" [ 11202 ] |
Fix Version/s | 10.0.4 [ 13101 ] | |
Fix Version/s | 10.0.3 [ 12900 ] |
Fix Version/s | 10.0.5 [ 13201 ] | |
Fix Version/s | 10.0.4 [ 13101 ] |
Fix Version/s | 10.0.6 [ 13202 ] | |
Fix Version/s | 10.0.5 [ 13201 ] |
Fix Version/s | 10.0.7 [ 14100 ] | |
Fix Version/s | 10.0.6 [ 13202 ] |
Fix Version/s | 10.0.8 [ 14200 ] | |
Fix Version/s | 10.0.7 [ 14100 ] |
Fix Version/s | 10.0.9 [ 14400 ] | |
Fix Version/s | 10.0.8 [ 14200 ] |
Fix Version/s | 10.0.10 [ 14500 ] | |
Fix Version/s | 10.0.9 [ 14400 ] |
Fix Version/s | 10.0.11 [ 15200 ] | |
Fix Version/s | 10.0.10 [ 14500 ] |
Fix Version/s | 10.0.12 [ 15201 ] | |
Fix Version/s | 10.0.11 [ 15200 ] |
Workflow | defaullt [ 21618 ] | MariaDB v2 [ 43200 ] |
Fix Version/s | 10.0.13 [ 16000 ] | |
Fix Version/s | 10.0.12 [ 15201 ] |
Fix Version/s | 10.1 [ 16100 ] | |
Fix Version/s | 10.0 [ 16000 ] | |
Affects Version/s | 5.3.12 [ 12000 ] | |
Affects Version/s | 10.0 [ 16000 ] | |
Affects Version/s | 5.5 [ 15800 ] | |
Labels | Launchpad upstream | Launchpad upstream-fixed |
Priority | Minor [ 4 ] | Major [ 3 ] |
Link | This issue is duplicated by TODO-362 [ TODO-362 ] |
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 {noformat} query 'CALL p()' failed: 1054: Unknown column 'test.t2.b' in 'field list' {noformat} 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: {noformat} 1136 Error 'Column count doesn't match value count at row 1' on query. {noformat} 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) {code:sql} 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(); {code} Test case 2 (replication failure) {code:sql} --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 {code} |
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 {noformat} query 'CALL p()' failed: 1054: Unknown column 'test.t2.b' in 'field list' {noformat} 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: {noformat} 1136 Error 'Column count doesn't match value count at row 1' on query. {noformat} 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) {code:sql} 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(); {code} Test case 2 (replication failure) {code:sql} --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 {code} |
Component/s | Data Definition - Alter Table [ 10114 ] |
Labels | Launchpad upstream-fixed | Launchpad upstream-fixed verified |
Workflow | MariaDB v2 [ 43200 ] | MariaDB v3 [ 65213 ] |
Fix Version/s | 10.1 [ 16100 ] |
Status | Open [ 1 ] | Confirmed [ 10101 ] |
Assignee | Alexander Barkov [ bar ] |
Link |
This issue duplicates |
Link |
This issue blocks |
Link |
This issue relates to |
Fix Version/s | 10;4 [ 22633 ] |
Fix Version/s | 10.4 [ 22408 ] | |
Fix Version/s | 10;4 [ 22633 ] |
Assignee | Alexander Barkov [ bar ] |
Link |
This issue is duplicated by |
Link |
This issue relates to |
Link |
This issue blocks |
Workflow | MariaDB v3 [ 65213 ] | MariaDB v4 [ 143813 ] |
Affects Version/s | 10.3 [ 22126 ] | |
Affects Version/s | 10.4 [ 22408 ] | |
Affects Version/s | 10.5 [ 23123 ] | |
Affects Version/s | 10.6 [ 24028 ] | |
Affects Version/s | 10.7 [ 24805 ] | |
Affects Version/s | 10.8 [ 26121 ] | |
Affects Version/s | 10.9 [ 26905 ] | |
Affects Version/s | 10.10 [ 27530 ] |
Fix Version/s | 10.3 [ 22126 ] | |
Fix Version/s | 10.5 [ 23123 ] | |
Fix Version/s | 10.6 [ 24028 ] | |
Fix Version/s | 10.7 [ 24805 ] | |
Fix Version/s | 10.8 [ 26121 ] | |
Fix Version/s | 10.9 [ 26905 ] |
Assignee | Sergei Golubchik [ serg ] |
Assignee | Sergei Golubchik [ serg ] | Dmitry Shulga [ JIRAUSER47315 ] |
Fix Version/s | 10.7 [ 24805 ] |
Fix Version/s | 10.3 [ 22126 ] |
Fix Version/s | 10.8 [ 26121 ] |
Fix Version/s | 10.9 [ 26905 ] |
Fix Version/s | 10.4 [ 22408 ] |
Fix Version/s | 10.11 [ 27614 ] |
Labels | Launchpad upstream-fixed verified | Launchpad not-11.2+ upstream-fixed verified |
Fix Version/s | 11.2.1 [ 29034 ] | |
Fix Version/s | 10.5 [ 23123 ] | |
Fix Version/s | 10.6 [ 24028 ] | |
Fix Version/s | 10.11 [ 27614 ] | |
Resolution | Fixed [ 1 ] | |
Status | Confirmed [ 10101 ] | Closed [ 6 ] |
Launchpad bug id: 948583