MySQL Bugs: #31321: Partial transaction results with INSERT ... SELECT and cluster replication

Bug #31321	Partial transaction results with INSERT ... SELECT and cluster replication
Submitted:	1 Oct 2007 17:00	Modified:	6 Dec 2007 17:31
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	count(), ndb, replication

Description:
Simple 2 node, 2 replica cluster replication setup, using default configuration values.

On the master i create a table 

  CREATE TABLE t1 (id int primary key auto_increment, t text) engine=ndb;

and populate it with 

  INSERT INTO t1 VALUES (0, NULL);

then repeated

  BEGIN; INSERT INTO t1 SELECT 0, NULL FROM t1; COMMIT;

until it reports 16384 lines inserted, so that the table t1 has 32768 rows in total.

Then i set up the slave and start it. While it catches up with the master i do repeated 

  SELECT COUNT(*) FROM t1; 

calls on the slave.

I would expect to only see 1, 2, 4, 8, ... 16384, 32768 as count results, but between 16384 and 32768 there is one moment where the SELECT blocks for a while instead of returning the result right away, and the result returned is something like 23617, 23695, 23956, ... 

The next SELECT COUNT(*) FROM t1 will then return the expected 32768

ndb_use_exact_count is ON and the tx_isolation level is REPEATABLE-READ

  

How to repeat:
see above

Suggested fix:
make sure only real transaction results are visible, do not expose intermediate counts

out select count(*) is does only support dirty_read
i.e you should be able to get same result on master.

a program doing read with locks (shared/exclusive) should
  not be able to see "half" epochs...

but this is not "provable" using count(*)....

Changed category to Docs, reassigned to myself following discussion with Hartmut.

Discussed with Jonas, updated 5.1/6.0 Cluster Limitations with info he provided.

any idea when it should be 'fixed'? maybe a option allowing dirt/non dirt reads (serialized?)