[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

Rasmus Johansson (Inactive) added a comment - 2012-03-12 22:46

Launchpad bug id: 948583

Rasmus Johansson (Inactive) added a comment - 2012-03-12 22:46 Launchpad bug id: 948583

Sergei Golubchik added a comment - 2013-01-01 23:15

fixed in mysql-5.6.6

Sergei Golubchik added a comment - 2013-01-01 23:15 fixed in mysql-5.6.6

Elena Stepanova added a comment - 2013-05-08 02:51

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.

Elena Stepanova added a comment - 2013-05-08 02:51 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.

Elena Stepanova added a comment - 2014-11-09 18:40

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.

Elena Stepanova added a comment - 2014-11-09 18:40 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.

Elena Stepanova added a comment - 2016-04-14 23:28

serg, some time ago you unset 'Fix version/s' on this report, was there a reason for that – should we close it as 'Won't fix', or target for 10.2, or...?

Elena Stepanova added a comment - 2016-04-14 23:28 serg , some time ago you unset 'Fix version/s' on this report, was there a reason for that – should we close it as 'Won't fix', or target for 10.2, or...?

Sergei Golubchik added a comment - 2016-04-15 10:41

We'll fix it, but 10.2 is unlikely, that's why I've unset the version.

Sergei Golubchik added a comment - 2016-04-15 10:41 We'll fix it, but 10.2 is unlikely, that's why I've unset the version.

Alexander Barkov added a comment - 2017-03-02 15:11

The same problem is repeatable with cursors:

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(10));

INSERT INTO t1 VALUES (10,20);

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE a,b INT;

  DECLARE cur CURSOR FOR SELECT * FROM t1;

  OPEN cur;

  FETCH cur INTO a,b;

  CLOSE cur;

  SELECT a,b;

END;

$$

DELIMITER ;

CALL p1();

The above script returns:

+------+------+

| a    | b    |

+------+------+

|   10 |   20 |

+------+------+

So far so good.

Now I add a new column to the table and call the procedure again

ALTER TABLE t1 ADD c INT;

CALL p1();

Oops. It returns the result, like if the table still had 2 columns.
It should fail, because there are now 3 columns in the table.

Alexander Barkov added a comment - 2017-03-02 15:11 The same problem is repeatable with cursors: SET sql_mode= DEFAULT ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT , b VARCHAR (10)); INSERT INTO t1 VALUES (10,20); DROP PROCEDURE IF EXISTS p1; DELIMITER $$ CREATE PROCEDURE p1() BEGIN DECLARE a,b INT ; DECLARE cur CURSOR FOR SELECT * FROM t1; OPEN cur; FETCH cur INTO a,b; CLOSE cur; SELECT a,b; END ; $$ DELIMITER ; CALL p1(); The above script returns: +------+------+ | a | b | +------+------+ | 10 | 20 | +------+------+ So far so good. Now I add a new column to the table and call the procedure again ALTER TABLE t1 ADD c INT ; CALL p1(); Oops. It returns the result, like if the table still had 2 columns. It should fail, because there are now 3 columns in the table.

Hartmut Holzgraefe added a comment - 2018-01-27 18:27

See also: http://alexandernozdrin.blogspot.de/2012/09/mysql-56-handling-metadata-changes-in.html

Hartmut Holzgraefe added a comment - 2018-01-27 18:27 See also: http://alexandernozdrin.blogspot.de/2012/09/mysql-56-handling-metadata-changes-in.html

Sergei Golubchik added a comment - 2024-10-23 14:55

No longer repeatable after ~~MDEV-5861~~

Sergei Golubchik added a comment - 2024-10-23 14:55 No longer repeatable after MDEV-5861

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