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

    XMLWordPrintable

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

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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