MySQL Bugs: #9691: UPDATE fails on attempt to update primary key

Bug #9691	UPDATE fails on attempt to update primary key
Submitted:	6 Apr 2005 18:13	Modified:	21 Apr 2005 9:20
Reporter:	Ted Schundler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	4.1.10	OS:	FreeBSD (FreeBSD 5.4)
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
Primary keys cannot be updated on ndb tables if there is also a unique index setup. It complains up duplicate entries. The same operation works fine my MyIASM tables.

How to repeat:
Run the following SQL:

CREATE TABLE `keytest` (
  `pri` int(11) NOT NULL default '0',
  `sec` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pri`),
  UNIQUE KEY `sec` (`sec`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

INSERT INTO `keytest` VALUES (1,1),(2,2),(3,3);

UPDATE keytest SET pri=4 WHERE pri=1;

Get the following error:
ERROR 1062 (23000): Duplicate entry '4' for key 1

NdbCluster currently doesn't really support in-place update of primary keys.
This functionality is implemented by insert+delete, changing this to delete+insert 
should do the trick.

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/internals/24133