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

[29 Aug 2006 9:08] Yuan WANG
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
[29 Aug 2006 12:51] Valeriy Kravchuk
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.