[MDEV-774] LP:948583 - Stored procedure doesn't not take into account ALTER TABLE, causes wrong result or replication abort - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 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)
Fix Version/s: 11.2.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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

LPexportBug948583.xml
4 kB
2012-10-03 11:32

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

Bug #64574 Stored procedure doesn't not notice ALTER TABLE, might cause replication abort

(2 relates to, 1 links to)

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2012-03-07 00:04

Rasmus Johansson (Inactive) made changes - 2012-10-04 11:35

Field	Original Value	New Value
Labels		Launchpad

Rasmus Johansson (Inactive) made changes - 2012-10-04 11:38

Labels

Launchpad

MariaDB_5.1

Rasmus Johansson (Inactive) made changes - 2012-10-04 11:42

Labels

MariaDB_5.1

Launchpad MariaDB_5.1

Rasmus Johansson (Inactive) made changes - 2012-10-04 11:43

Fix Version/s

Maria 5.1 [ 11600 ]

Rasmus Johansson (Inactive) made changes - 2012-10-04 13:14

Key	~~IMT-7667~~	~~MDEV-774~~
Project	ImportTest [ 10200 ]	MariaDB Development [ 10000 ]
Workflow	jira [ 21316 ]	defaullt [ 21618 ]

Sergei Golubchik made changes - 2012-10-04 14:59

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 ]

Sergei Golubchik made changes - 2012-10-06 15:27

Labels

Launchpad MariaDB_5.1

Launchpad

Elena Stepanova made changes - 2012-10-30 23:42

Affects Version/s

5.3.9 [ 11000 ]

Sergei Golubchik made changes - 2012-11-26 17:44

Fix Version/s		5.1.67 [ 12100 ]
Fix Version/s	5.1.66 [ 10801 ]

Sergei Golubchik made changes - 2012-11-26 17:44

Fix Version/s		5.2.14 [ 12101 ]
Fix Version/s	5.2.13 [ 10800 ]

Sergei Golubchik made changes - 2012-11-29 14:50

Fix Version/s		5.5.29 [ 12102 ]
Fix Version/s	5.5.28a [ 11701 ]

Sergei Golubchik made changes - 2012-12-25 18:59

Fix Version/s		5.3.12 [ 12000 ]
Fix Version/s	5.3.10 [ 11500 ]

Sergei Golubchik made changes - 2013-01-01 23:14

Labels

Launchpad

Launchpad upstream

Sergei Golubchik made changes - 2013-01-01 23:15

Remote Link

This issue links to "Bug #64574 Stored procedure doesn't not notice ALTER TABLE, might cause replication abort (Web Link)" [ 10013 ]

Sergei Golubchik made changes - 2013-01-31 11:20

Fix Version/s		5.1.68 [ 12600 ]
Fix Version/s	5.1.67 [ 12100 ]

Sergei Golubchik made changes - 2013-01-31 11:21

Fix Version/s		5.2.15 [ 12601 ]
Fix Version/s	5.2.14 [ 12101 ]

Sergei Golubchik made changes - 2013-01-31 11:21

Fix Version/s		5.3.13 [ 12602 ]
Fix Version/s	5.3.12 [ 12000 ]

Sergei Golubchik made changes - 2013-01-31 11:21

Fix Version/s		5.5.30 [ 11800 ]
Fix Version/s	5.5.29 [ 12102 ]

Sergei Golubchik made changes - 2013-03-11 22:25

Fix Version/s		5.5.31 [ 12700 ]
Fix Version/s	5.5.30 [ 11800 ]

Elena Stepanova made changes - 2013-05-08 02:51

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}

Elena Stepanova made changes - 2013-05-08 02:51

Remote Link

This issue links to "Bug #64574 (Web Link)" [ 11202 ]

Elena Stepanova made changes - 2013-05-08 02:51

Remote Link

This issue links to "Bug #64574 (Web Link)" [ 11202 ]

Sergei Golubchik made changes - 2013-06-04 15:39

Fix Version/s		10.0.4 [ 13101 ]
Fix Version/s	10.0.3 [ 12900 ]

Sergei Golubchik made changes - 2013-08-16 16:27

Fix Version/s		10.0.5 [ 13201 ]
Fix Version/s	10.0.4 [ 13101 ]

Sergei Golubchik made changes - 2013-11-05 23:01

Fix Version/s		10.0.6 [ 13202 ]
Fix Version/s	10.0.5 [ 13201 ]

Sergei Golubchik made changes - 2013-11-18 21:52

Fix Version/s		10.0.7 [ 14100 ]
Fix Version/s	10.0.6 [ 13202 ]

Sergei Golubchik made changes - 2013-12-28 17:50

Fix Version/s		10.0.8 [ 14200 ]
Fix Version/s	10.0.7 [ 14100 ]

Sergei Golubchik made changes - 2014-01-14 15:37

Link

This issue is part of ~~MDEV-5242~~ [ ~~MDEV-5242~~ ]

Sergei Golubchik made changes - 2014-02-10 16:13

Fix Version/s		10.0.9 [ 14400 ]
Fix Version/s	10.0.8 [ 14200 ]

Sergei Golubchik made changes - 2014-03-09 22:02

Fix Version/s		10.0.10 [ 14500 ]
Fix Version/s	10.0.9 [ 14400 ]

Sergei Golubchik made changes - 2014-03-31 14:23

Fix Version/s		10.0.11 [ 15200 ]
Fix Version/s	10.0.10 [ 14500 ]

Sergei Golubchik made changes - 2014-05-10 23:13

Fix Version/s		10.0.12 [ 15201 ]
Fix Version/s	10.0.11 [ 15200 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 21618 ]

MariaDB v2 [ 43200 ]

Sergei Golubchik made changes - 2014-06-16 13:27

Fix Version/s		10.0.13 [ 16000 ]
Fix Version/s	10.0.12 [ 15201 ]

Elena Stepanova made changes - 2014-11-09 18:40

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 ]

Elena Stepanova made changes - 2015-01-15 17:34

Link

This issue is duplicated by TODO-362 [ TODO-362 ]

Elena Stepanova made changes - 2015-02-11 19:19

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}

Elena Stepanova made changes - 2015-02-11 19:20

Component/s

Data Definition - Alter Table [ 10114 ]

Elena Stepanova made changes - 2015-02-11 19:20

Labels

Launchpad upstream-fixed

Launchpad upstream-fixed verified

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 43200 ]

MariaDB v3 [ 65213 ]

Sergei Golubchik made changes - 2016-03-15 14:57

Link

This issue is part of ~~MDEV-5816~~ [ ~~MDEV-5816~~ ]

Sergei Golubchik made changes - 2016-03-15 15:14

Fix Version/s

10.1 [ 16100 ]

Elena Stepanova made changes - 2016-05-13 16:46

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2016-12-26 19:50

Assignee

Alexander Barkov [ bar ]

Elena Stepanova made changes - 2016-12-26 19:51

Link

This issue duplicates ~~MDEV-11652~~ [ ~~MDEV-11652~~ ]

Alexander Barkov made changes - 2017-03-02 15:13

Link

This issue blocks ~~MDEV-12011~~ [ ~~MDEV-12011~~ ]

Alexander Barkov made changes - 2017-03-03 07:48

Link

This issue relates to ~~MDEV-12166~~ [ ~~MDEV-12166~~ ]

Sergei Golubchik made changes - 2017-09-25 12:27

Fix Version/s

10;4 [ 22633 ]

Sergei Golubchik made changes - 2017-09-25 12:27

Fix Version/s		10.4 [ 22408 ]
Fix Version/s	10;4 [ 22633 ]

Sergei Golubchik made changes - 2017-09-25 12:28

Assignee

Alexander Barkov [ bar ]

Elena Stepanova made changes - 2018-01-27 20:27

Link

This issue is duplicated by ~~MDEV-15097~~ [ ~~MDEV-15097~~ ]

Aleksey Midenkov made changes - 2019-12-05 17:10

Link

This issue relates to ~~MDEV-21234~~ [ ~~MDEV-21234~~ ]

Julien Fritsch made changes - 2020-05-11 07:14

Link

This issue blocks ~~MDEV-12011~~ [ ~~MDEV-12011~~ ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 65213 ]

MariaDB v4 [ 143813 ]

Alice Sherepa made changes - 2022-12-05 15:38

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 ]

Alice Sherepa made changes - 2022-12-05 15:39

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 ]

Alice Sherepa made changes - 2022-12-05 15:42

Assignee

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2022-12-05 17:00

Assignee

Sergei Golubchik [ serg ]

Dmitry Shulga [ JIRAUSER47315 ]

Julien Fritsch made changes - 2023-03-03 17:31

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 14:18

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2023-04-27 14:50

Fix Version/s

10.8 [ 26121 ]

Julien Fritsch made changes - 2023-11-28 10:38

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2024-09-10 15:04

Fix Version/s

10.4 [ 22408 ]

Alice Sherepa made changes - 2024-10-23 14:10

Fix Version/s

10.11 [ 27614 ]

Alice Sherepa made changes - 2024-10-23 14:11

Labels

Launchpad upstream-fixed verified

Launchpad not-11.2+ upstream-fixed verified

Alice Sherepa made changes - 2024-10-23 14:50

Link

This issue relates to ~~MDEV-6488~~ [ ~~MDEV-6488~~ ]

Sergei Golubchik made changes - 2024-10-23 14:55

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 ]

People

Assignee:: Dmitry Shulga

Reporter:: Elena Stepanova

Votes:: 2 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2012-03-07 00:04

Updated:: 2024-10-23 14:55

Resolved:: 2024-10-23 14:55

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration