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
Triage: D4 (Minor)

[1 Oct 2007 17: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 5: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 10:00] Jon Stephens
Changed category to Docs, reassigned to myself following discussion with Hartmut.
[6 Dec 2007 17:31] Jon Stephens
Discussed with Jonas, updated 5.1/6.0 Cluster Limitations with info he provided.
[3 Mar 2011 23:00] Roberto Spadim
any idea when it should be 'fixed'? maybe a option allowing dirt/non dirt reads (serialized?)