MySQL Bugs: #21897: Query in a transaction returns no result if the table is altered by others

Bug #21897	Query in a transaction returns no result if the table is altered by others
Submitted:	29 Aug 2006 9:08	Modified:	29 Aug 2006 12:51
Reporter:	Yuan WANG	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.25-BK, 5.0.22	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE

Description:
For InnoDB tables, if a client start a transaction, and then another client modifies the table using 'ALTER TABLE ... ADD COLUMN', the first client will see empty result for the modified table. 

How to repeat:
CREATE TABLE t(a int) ENGINE = INNODB;
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);

Then suppose there are two clients: C1 and C2, operating as the follows:

C1: BEGIN;
C1: SELECT * FROM t;
  -- Ok, fetch two rows
C2: ALTER TABLE t ADD b INT DEFAULT 0;
C1: SELECT * FROM t;
  -- Error, no data returned

Thank you for a problem report. It is not a bug, as this behaviour is clearly described in the manual. Read http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html for the details.