MySQL Bugs: #12054: ON DUPLICATE KEY UPDATE fails in ndb table

Bug #12054	ON DUPLICATE KEY UPDATE fails in ndb table
Submitted:	20 Jul 2005 13:44	Modified:	1 Aug 2005 12:16
Reporter:	Scott Tully	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	4.1.12 (max)	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Using the INSERT... ON DUPLICATE KEY UPDATE ... syntax in an NDB table resulted in ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from ndbcluster

How to repeat:
Create a table and insert a row. Then insert the same row again using on duplicate key update.

mysql> INSERT INTO zone_queue SET dog = 'Y', master_id = '9', zone_id = '3' ON DUPLICATE KEY UPDATE dog = 'Y';
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO zone_queue SET dog = 'Y', master_id = '9', zone_id = '3' ON DUPLICATE KEY UPDATE dog = 'Y';
ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from ndbcluster

I was able to reproduce this bug using the following create table statement:

CREATE TABLE `zone_queue` (
  `dog` char(1) NOT NULL,
  `master_id` int(11) default NULL,
  `zone_id` int(11) default NULL,
  PRIMARY KEY  (`dog`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This is the table structure that created the error for me...

CREATE TABLE `zone_queue` (
  `queue_id` int(11) NOT NULL auto_increment,
  `zone_id` int(11) NOT NULL default '0',
  `master_id` int(11) NOT NULL default '0',
  `dans` enum('N','Y') NOT NULL default 'N',
  `dog` enum('N','Y') NOT NULL default 'N',
  `exim` enum('N','Y') NOT NULL default 'N',
  `squid` enum('N','Y') NOT NULL default 'N',
  `cd` enum('N','Y') NOT NULL default 'N',
  `exequeue` enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (`queue_id`),
  UNIQUE KEY `zone_id` (`zone_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ;

Got another error from this table

MySQL Error: 1062Duplicate entry '58' for key 1
 INTO zone_queue SET zone_id = '1',
                                master_id  = '13',
                                dans  = 'Y',
                                dog  = 'N',
                                exim  = 'N',
                                squid  = 'N',
                                cd  = 'N',
                                exequeue  = 'Y';

Duplicate entry '59' for key 1
If i rerun the query it increment the auto queue_id in memmory, but not in the db itself... the highest queue_id was 51 at this time, not 58, or 59.  It seems to be releated to the UNIQUE key.... that's what is duplicated, not the primary key. But the REPLACE or ON DUPLICATE.... statements fail.

Got another error from this table

MySQL Error: 1062Duplicate entry '58' for key 1
 INTO zone_queue SET zone_id = '1',
                                master_id  = '13',
                                dans  = 'Y',
                                dog  = 'N',
                                exim  = 'N',
                                squid  = 'N',
                                cd  = 'N',
                                exequeue  = 'Y';

Duplicate entry '59' for key 1
If i rerun the query it increment the auto queue_id in memmory, but not in the db itself... the highest queue_id was 51 at this time, not 58, or 59.  It seems to be releated to the UNIQUE key.... that's what is duplicated, not the primary key. But the REPLACE or ON DUPLICATE.... statements fail.

Got another error from this table

MySQL Error: 1062Duplicate entry '58' for key 1
RELACE INTO zone_queue SET zone_id = '1',
                                master_id  = '13',
                                dans  = 'Y',
                                dog  = 'N',
                                exim  = 'N',
                                squid  = 'N',
                                cd  = 'N',
                                exequeue  = 'Y';

Duplicate entry '59' for key 1
If i rerun the query it increment the auto queue_id in memmory, but not in the db itself... the highest queue_id was 51 at this time, not 58, or 59.  It seems to be releated to the UNIQUE key.... that's what is duplicated, not the primary key. But the REPLACE or ON DUPLICATE.... statements fail.

This is a known limitation in cluster.
Internally cluster cannot ignore unique
key violations at the moment (that is, not 
without aborting transaction at constraint violation).
Problem exists for primary key updates
as well, but there an extra read is done.
The same could be done for ignoring
unique key violation, but it would result in
having to do multiple reads (one for each
key) and make all such update statements 
extremely slow.
A real solution that allows ignoring all unique
violations without an extra read requires some
implementation work.

This bug is not scheduled to be fixed at this time.