Bug #26043 UNIQUE INDEX create always fails after constraint violation
Submitted: 2 Feb 2007 20:14 Modified: 9 Jan 2009 9:31
Reporter: Bernd Ocklin Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.14 OS:Linux (SUSE 10.1)
Assigned to: Assigned Account CPU Architecture:Any

[2 Feb 2007 20:14] Bernd Ocklin 
Description:
If a UNIQUE INDEX creation failed once due to constraint violation any following attempt will also fail.

How to repeat:
CREATE TABLE t (a INT PRIMARY KEY, b INT) ENGINE = ndb;
INSERT INTO t VALUES (1, 12);
INSERT INTO t VALUES (2, 12);
ALTER TABLE t ADD UNIQUE KEY ui_t (b);

--> fails with constraint violation

DELETE FROM t;
ALTER TABLE t ADD UNIQUE KEY ui_t (b);

--> fails with unknown error

Suggested fix:
allow to unique key creation after cause of constraint violation was removed.
[6 Feb 2007 10:07] Hartmut Holzgraefe 
verified, mysqltest test case will be upladed later

additional note: with InnoDB the first ALTER TABLE failes with error 1062 instead of 1022, shouldn't both fail with the same error code?
[6 Feb 2007 11:30] Hartmut Holzgraefe 
only affects 5.1, 5.0 works fine

the actual error produced by 5.1 on the 2nd ALTER TABLE is 

  ERROR 1296 (HY000): Got error 156 'Unknown error code' from NDBCLUSTER

with error 156 being 

  #define HA_ERR_TABLE_EXIST       156  /* The table existed in storage engine */
[6 Feb 2007 11:38] Hartmut Holzgraefe 
on my note above the 1022 and 1062 error code was from a 5.0 cluster, 
with 5.1 the error codes are 

  ERROR 1169 (23000): Can't write, because of unique constraint, to table 't'

for cluster and

  ERROR 1582 (23000): Duplicate entry '12' for key 'ui_t'

for InnoDB instead
[6 Feb 2007 11:53] Hartmut Holzgraefe 
mysqltest test case (requires mysqltest from at least 5.1.6)

Attachment: bug26043.tgz (application/x-gtar, text), 1.09 KiB.
[11 Oct 2007 13:23] Morten Tryfoss 
It's worth mention that the unique key is actually beeing created (you can't insert any more rows that are non-unique after the failed create).
[23 Apr 2008 13:57] li zhou 
The bug has been fixed with WL3600.
Although there is a relative bug#35599, this bug should be closed.
[9 Dec 2008 9:29] li zhou 
This bug have already fixed in telco-6.4.
So the bug should be closed.
[9 Dec 2008 9:33] Jonas Oreland 
is it also fixed in 6.2 and 6.3? cause we can't just defer this to 6.4...
[9 Dec 2008 10:05] li zhou 
Fixed in 6.4.0 and 6.2.16

6.3.19 still fail.
[10 Jan 2009 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".