Bug #31321 Partial transaction results with INSERT ... SELECT and cluster replication
Submitted: 1 Oct 2007 19:00 Modified: 6 Dec 2007 18:31
Reporter: Hartmut Holzgraefe
Status: Closed
Category:Server: Docs Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: Jon Stephens Target Version:
Tags: ndb, replication, count()
Triage: D4 (Minor)

[1 Oct 2007 19:00] Hartmut Holzgraefe 
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
[2 Oct 2007 7:58] Jonas Oreland 
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(*)....
[2 Oct 2007 12:00] Jon Stephens 
Changed category to Docs, reassigned to myself following discussion with Hartmut.
[6 Dec 2007 18:31] Jon Stephens 
Discussed with Jonas, updated 5.1/6.0 Cluster Limitations with info he provided.