Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-774

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

Details

    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

          Activity

            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...?

            elenst Elena Stepanova added a comment - 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...?

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

            serg Sergei Golubchik added a comment - We'll fix it, but 10.2 is unlikely, that's why I've unset the version.

            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.

            bar Alexander Barkov added a comment - 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.
            hholzgra Hartmut Holzgraefe added a comment - See also: http://alexandernozdrin.blogspot.de/2012/09/mysql-56-handling-metadata-changes-in.html

            No longer repeatable after MDEV-5861

            serg Sergei Golubchik added a comment - No longer repeatable after MDEV-5861

            People

              shulga Dmitry Shulga
              elenst Elena Stepanova
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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