Bug #27044 replicated with unique field ndb table allows duplicate key inserts
Submitted: 12 Mar 2007 11:47 Modified: 2 Jun 2007 14:35
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Replication Severity:S3 (Non-critical)
Version:5.1.17 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[12 Mar 2007 11:47] Andrei Elkin 
Description:
 starting cluster for replication with mysql-test-run.pl --start-and-exit --debug --mysqld=--binlog-format=row t/rpl_ndb_basic.test
 1. create table with primary key
 2. populate
 3. trying to insert on master -> errror with dup key

 However, on slave:

 mysql> show create table t3;
 +-------+-----------------------------------------------------------------+
 | Table | Create Table                                                                                                                           |
 +-------+-----------------------------------------------------------------+
 | t3    | CREATE TABLE `t3` (
   `a` int(11) NOT NULL,
   `b` int(11) DEFAULT NULL,
   PRIMARY KEY (`a`)
 ) ENGINE=ndbcluster

 mysql> select * from t3;
 +---+------+
 | a | b    |
 +---+------+
 | 3 |    3 | 
 | 1 |    1 | 
 | 2 |    2 | 
 +---+------+
 3 rows in set (0.01 sec)

the same
 mysql> insert into t1 values (1,0),(2,0),(3,0);
 
completes (from the result file of How to repeat test)

select * from t1;
a       b
3       0
1       0
2       0

I noticed that repeating attempts to insert into unique the table starts rejecting.

How to repeat:
mysql-test-run.pl --record --mysqld=--binlog-format=row t/rpl_row_ndb_bug.test

cat <<. >t/rpl_row_ndb_bug.test
-- source include/have_ndb.inc
let $type= 'NDB' ;

-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc

connection master;
--disable_warnings
drop table if exists  t1;
--enable_warnings
eval CREATE TABLE t1 (a int primary key, b int) ENGINE= $type;
insert into t1 values (1,1),(2,2),(3,3);

--error ER_DUP_KEY
insert into t1 values (1,0),(2,0),(3,0);
select * from t1;

sync_slave_with_master;
select * from t1;
insert into t1 values (1,0),(2,0),(3,0);
select * from t1;
.

Suggested fix:
To start from looking inside

log_event.cc's  Write_rows_log_event::do_before_row_operations, do_after
make manipulations with table handle.
[12 Mar 2007 19:11] Andrei Elkin 
Checked other engine to conclude that ndb only is vulnerable.
The patch is coming soon.
[13 Mar 2007 10:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21790

ChangeSet@1.2472, 2007-03-13 12:18:57+02:00, aelkin@dsl-kpogw6-fe8ddc00-181.dhcp.inet.fi +3 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is lack of resetting of extra() [see files' comments]
[13 Mar 2007 10:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21791

ChangeSet@1.2472, 2007-03-13 11:38:06+02:00, aelkin@dsl-kpogw6-fe8ddc00-181.dhcp.inet.fi +3 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is lack of resetting of extra() [see files' comments]
[15 Mar 2007 18:36] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22043

ChangeSet@1.2472, 2007-03-15 20:35:36+02:00, aelkin@dsl-hkibrasgw1-ff1ec100-106.dhcp.inet.fi +3 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is wrong usage of ndb->extra:s.
  
  Fixed with leaving in do_before only
  table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE); 
  and reseting in do_after.
[19 Mar 2007 8:36] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22233

ChangeSet@1.2472, 2007-03-19 10:35:20+02:00, aelkin@dsl-hkibrasgw1-ff1ec100-106.dhcp.inet.fi +3 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is wrong usage of ndb->extra:s.
  
  Fixed with resetting flags in do_before.
  There is open issue with symmetrical resetting
     table->file->extra(HA_EXTRA_NO_IGNORE_NO_KEY)
  which i had to omit due to the issue reported as bug#27077
[22 Mar 2007 12:04] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22625

ChangeSet@1.2447, 2007-03-22 13:17:08+01:00, tomas@whalegate.ndb.mysql.com +2 -0
  BUG#27044 bug fix of 27320 fixes this, added test case
[2 May 2007 9:44] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25891

ChangeSet@1.2472, 2007-05-02 12:44:26+03:00, aelkin@dsl-hkibras1-ff18c300-219.dhcp.inet.fi +3 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is wrong usage of ndb->extra:s.
  
  Fixed with resetting flags at do_after.
  There is open issue with symmetrical resetting
     table->file->extra(HA_EXTRA_NO_IGNORE_NO_KEY)
  which i had to hand to bug#27077.
[2 May 2007 14:27] Andrei Elkin 
The last committed patch is for review.
[27 May 2007 12:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27406

ChangeSet@1.2472, 2007-05-27 15:20:20+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +1 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is wrong usage of ndb->extra:s.
  
  Fixed with resetting flags at do_after.
  There is open issue with symmetrical resetting
     table->file->extra(HA_EXTRA_NO_IGNORE_NO_KEY)
  which i had to hand to bug#27077.
  The test for the current bug was committed in a cset for bug#27320.
[29 May 2007 13:36] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27573

ChangeSet@1.2472, 2007-05-29 16:36:05+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +1 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is wrong usage of ndb->extra:s.
  
  Fixed with resetting flags at do_after.
  There is open issue with symmetrical resetting
     table->file->extra(HA_EXTRA_NO_IGNORE_NO_KEY)
  which i had to hand to bug#27077.
  The test for the current bug was committed in a cset for bug#27320.
[29 May 2007 14:08] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27582

ChangeSet@1.2472, 2007-05-29 15:53:03+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +1 -0
  Bug#27044 replicated with unique field ndb table allows duplkey inserts
  The bug in that slave version of a table with unique field still was
  able to execute INSERT query as replace whereas it's impossible on master.
  
  The reason of this artifact is wrong usage of ndb->extra:s.
  
  Fixed with resetting flags at do_after.
  There is open issue with symmetrical resetting
     table->file->extra(HA_EXTRA_NO_IGNORE_NO_KEY)
  which i had to hand to bug#27077.
  The test for the current bug was committed in a cset for bug#27320.
[1 Jun 2007 19:21] Bugs System 
Pushed into 5.1.20-beta
[2 Jun 2007 14:35] Jon Stephens 
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.20 changelog.