Bug #46568 Non primary key fields not recorded in exceptions table in event of conflicts
Submitted: 5 Aug 2009 15:27 Modified: 5 Aug 2009 16:02
Reporter: Andrew Morgan Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Cluster: Replication Severity:S2 (Serious)
Version:MySQL Cluster 7.0.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: conflict, exception table, NDB$OLD, replication, same timestamp wins

[5 Aug 2009 15:27] Andrew Morgan
The documentation http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-conflict-resolution.html specifies that when using NDB$OLD conflict detection, the exceptions table can contain non-PK columns from the rejected write/update application on the slave. 

While it is possible to include those columns in the creation of the exception table, they are set to NULL when exceptions are actually added.

How to repeat:
Configure master and slave SQL nodes for multi-master replication and then...

mysql> CREATE TABLE mysql.ndb_replicationdb VARBINARY(63),table_name VARBINARY(63),server_id INT UNSIGNED,binlog_type INT UNSIGNED,conflict_fn VARBINARY(128),PRIMARY KEY USING HASH (db, table_name, server_id)) ENGINE=NDB PARTITION BY KEY(db,table_name);

insert into ndb_replication values ('test', 'tab1', 7, NULL, 'NDB$OLD(ts)');
mysql> use test;

mysql> create table tab1$EX (server_id INT UNSIGNED,master_server_id INT UNSIGNED,master_epoch BIGINT UNSIGNED,count INT UNSIGNED,sub_id int not null,name varchar(30),ts int unsigned, PRIMARY KEY (server_id, master_server_id, master_epoch, count)) engine=ndb;

mysql> create table tab1 (sub_id int not null primary key, name varchar(30), ts int unsigned) engine=ndb;

mysql> insert into tab9 values (1,'Frederick',0),(2,'Robert',0);

Stop the slave on the SQL nodes for both Clusters.

On one SQL node...

update tab1 set name='Fred',ts=1 where sub_id=1;

and on the other...

update tab1 set name='Freddy',ts=2 where sub_id=1;

Start the slave on both sites.

Check the exceptions table on each site and verify that 'name' and 'ts' are NULL rather than the correct values...

mysql> select * from tab1$EX;
| server_id | master_server_id | master_epoch   | count | sub_id | name | ts   |
|         1 |                2 | 86586540687364 |     1 |      1 | NULL | NULL | 

mysql> select * from tab1$EX;
| server_id | master_server_id | master_epoch   | count | sub_id | name | ts   |
|         2 |                1 | 87445534146578 |     1 |      1 | NULL | NULL | 

Suggested fix:
slave_set_resolve_fn should validate that the specified non-PK columns in the exceptions table matches those of the target table when that table is defined.

write_conflict_row should set these optional fields.

mtr should include tests for non-PK columns in the exceptions table.

If it isn't possible to fix the code then the documentation should be updated to not include the non-PK columns in the exception table and explain why that doesn impede the user or application figuring out how to resolve the conflict.
[5 Aug 2009 16:02] Andrew Morgan
1) only PKs must be there, by design 2) user can add own columns, such as exception was handled and when and by whom.

Therefore the code is behaving as designed.