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

[6 Apr 2005 18:13] Ted Schundler
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
[8 Apr 2005 10:22] Martin Skold
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.
[19 Apr 2005 9:23] 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/internals/24133