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

Bug #64574	Stored procedure doesn't not notice ALTER TABLE, might cause replication abort
Submitted:	6 Mar 2012 23:54	Modified:	20 Jun 2012 16:17
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

'How to repeat' section contains two test cases. The first one is for the base use case: 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.

A workaround could be to run FLUSH TABLES after ALTER.

How to repeat:
# 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

I am sorry if it is a duplicate -- it seems to be an old problem, so must have been already filed, but it is hard to find as it could be described in so many different ways, there is no definitive signature. 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.

Please note that the suggested workaround is partial, it is only applicable to certain scenarios, different from the ones provided in 'How to repeat' (e.g. to cases with a trigger).

Thank you for the bug report.

i found a comment in find_field_in_tables()

if (item->cached_table)
  {
    /*
      This shortcut is used by prepared statements. We assume that
      TABLE_LIST *first_table is not changed during query execution (which
      is true for all queries except RENAME but luckily RENAME doesn't
      use fields...) so we can rely on reusing pointer to its member.
      With this optimization we also miss case when addition of one more
      field makes some prepared query ambiguous and so erroneous, but we
      accept this trade off.

Noted in 5.6.6 changelog.

Failure of a stored program to notice metadata changes in objects accessed within the program could cause replication to fail.