Bug #21072 Duplicate key error in NDB references wrong key
Submitted: 14 Jul 2006 22:51 Modified: 14 Dec 2007 20:32
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.2 OS:Any
Assigned to: Martin Skold
Tags: auto_increment, cluster, duplicate key, ndb, primary key, unique
Triage: D2 (Serious)

[14 Jul 2006 22:51] Kolbe Kegel
Description:
When inserting a row into an NDB table with a duplicate value for a non-PK UNIQUE key on column col1, the error issued will reference the wrong key.

How to repeat:
create table t1 (id int unsigned auto_increment primary key, intUsername varchar(255) default '', unique(intUsername)) engine=myisam;
create table t2 (id int unsigned auto_increment primary key, intUsername varchar(255) default '', unique(intUsername)) engine=ndbcluster;
insert into t1 values ();
insert into t1 values ();
insert into t2 values ();
insert into t2 values ();

mysql 5.0.22-max (root) [test]> create table t1 (id int unsigned auto_increment primary key, intUsername varchar(255) default '', unique(intUsername)) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql 5.0.22-max (root) [test]> create table t2 (id int unsigned auto_increment primary key, intUsername varchar(255) default '', unique(intUsername)) engine=ndbcluster;
Query OK, 0 rows affected (1.13 sec)

mysql 5.0.22-max (root) [test]> insert into t1 values ();
Query OK, 1 row affected (0.00 sec)

mysql 5.0.22-max (root) [test]> insert into t1 values ();
ERROR 1062 (23000): Duplicate entry '' for key 2
mysql 5.0.22-max (root) [test]> insert into t2 values ();
Query OK, 1 row affected (0.00 sec)

mysql 5.0.22-max (root) [test]> insert into t2 values ();
ERROR 1062 (23000): Duplicate entry '2' for key 1

Suggested fix:
NDB should reference the correct key, as MyISAM does.
[15 Jul 2006 20:10] Geert Vanderkelen
Same happens in latest 5.1bk.
[19 Jul 2006 9:18] Hartmut Holzgraefe
also verified on 4.1
[19 Jul 2006 9:47] Hartmut Holzgraefe
This actually seems to be an off-by-one error,
reporting a violation of key #1 when it is actually key #2
and so on, only when violiting key #1 itself the returned
message is actually ok
[19 Jul 2006 10:11] Hartmut Holzgraefe
mysqltest test case

Attachment: bug21072.tgz (application/x-gtar, text), 884 bytes.

[19 Jul 2006 11:52] Hartmut Holzgraefe
i was wrong with my off-by-one theory, its always key #1 that's returned
[19 Jul 2006 13:17] Hartmut Holzgraefe
Actually (uint)-1 seems to be the right thing to return here in case
of non-PK violations, this will produce the more generic 

  ERROR 1022 (23000): Can't write; duplicate key in table '...'

without mentioning key numbers and values at all.

Fixing things this way and adding an entry in the Cluster Limitations
documentation section might be enough to close this for now.
[19 Jul 2006 13: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/9335
[19 Jul 2006 13:32] Hartmut Holzgraefe
new mysqltest test case for the proposed fix

Attachment: bug21072.tgz (application/x-gtar, text), 708 bytes.

[19 Jul 2006 16:26] Hartmut Holzgraefe
See also http://bugs.mysql.com/bug.php?id=5312 tough ...
[21 Sep 2006 14:49] 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/12346

ChangeSet@1.2566, 2006-09-21 16:49:07+02:00, mskold@mysql.com +5 -0
  Bug #21072  Duplicate key error in NDB references wrong key: use MAX_KEY to signal unknown key
[21 Sep 2006 14:55] 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/12347

ChangeSet@1.2567, 2006-09-21 16:54:54+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: wrong indent for code block
[16 Oct 2006 9:57] 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/13723

ChangeSet@1.2283, 2006-10-16 11:54:54+02:00, mskold@mysql.com +2 -0
  Bug #21072  Duplicate key error in NDB references wrong key: re-generated test results
[16 Oct 2006 10:19] 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/13725

ChangeSet@1.2314, 2006-10-16 12:14:38+02:00, mskold@mysql.com +4 -0
   Bug #21072  Duplicate key error in NDB references wrong key: Added new printout for duplicate key error when key is unknown
[16 Oct 2006 12:27] 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/13730

ChangeSet@1.2315, 2006-10-16 14:20:05+02:00, mskold@mysql.com +1 -0
   Bug #21072  Duplicate key error in NDB references wrong key: Added new printout for duplicate key error when key is unknown
[19 Oct 2006 12: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/13977

ChangeSet@1.2532, 2006-10-19 14:41:14+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: use MAX_KEY to signal unknown key: Added string initialization
[19 Oct 2006 13: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/13978

ChangeSet@1.2568, 2006-10-19 14:57:04+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: use MAX_KEY to signal unknown key: Added string initialization
[19 Oct 2006 13:25] 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/13983

ChangeSet@1.2318, 2006-10-19 15:22:50+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: use MAX_KEY to signal unknown key: Added string initialization
[25 Oct 2006 9:19] 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/14329

ChangeSet@1.2569, 2006-10-25 11:18:52+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: Re-wrote string usage to avoid valgrind warnings
[25 Oct 2006 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/14331

ChangeSet@1.2569, 2006-10-25 11:44:40+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: Re-wrote string usage to avoid valgrind warnings
[25 Oct 2006 13:42] 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/14354

ChangeSet@1.2329, 2006-10-25 15:34:47+02:00, mskold@mysql.com +1 -0
  Bug #21072  Duplicate key error in NDB references wrong key: Re-wrote string usage to avoid valgrind warnings
[1 Nov 2006 14:26] Jonas Oreland
pushed into 4.1.22
[1 Nov 2006 14:41] Jonas Oreland
pushed into 5.0.29
[1 Nov 2006 14:53] Jonas Oreland
pushed into 5.1.13
[2 Nov 2006 8:11] 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 for 4.1.22/5.0.29/5.1.12.
[4 Nov 2006 3:17] Jon Stephens
*Fix for 5.0 documented in 5.0.30 Release Notes.*
[21 Feb 2007 11:49] Hartmut Holzgraefe
changed to "to be fixed later" further improvements on the error message should be done for MySQL 5.2
[6 Dec 2007 16:15] 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/39430

ChangeSet@1.2494, 2007-12-06 17:15:21+01:00, mskold@mysql.com +13 -0
  bug#21072 Duplicate key error in NDB references wrong key: Return correct key for non-batching inserts
[7 Dec 2007 9:33] 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/39499

ChangeSet@1.2495, 2007-12-07 10:33:50+01:00, mskold@mysql.com +3 -0
  bug#21072 Duplicate key error in NDB references wrong key: Post-review fixes
[7 Dec 2007 10:05] 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/39504

ChangeSet@1.2588, 2007-12-07 11:05:19+01:00, mskold@mysql.com +7 -0
  bug#21072 Duplicate key error in NDB references wrong key: Post-merge fixes
[7 Dec 2007 11:19] 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/39514

ChangeSet@1.2589, 2007-12-07 12:20:04+01:00, mskold@mysql.com +1 -0
  bug#21072 Duplicate key error in NDB references wrong key: regenerated result
[14 Dec 2007 17:08] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 17:08] Bugs System
Pushed into 5.0.56
[14 Dec 2007 17:09] Bugs System
Pushed into 6.0.5-alpha
[14 Dec 2007 20:32] 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 (improved) fix in 5.0.56, 5.1.23, and 6.0.5 changelogs.