Bug #21507 I can't create a unique hash index in NDB
Submitted: 8 Aug 2006 14:32 Modified: 14 Dec 2006 5:19
Reporter: Lars Torstensson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:MySQL cluster 5.0.24 OS:Linux (Redhat Linux 2.6.9-34.ELsmp #1 )
Assigned to: Martin Skold CPU Architecture:Any

[8 Aug 2006 14:32] Lars Torstensson
Description:
When using a MySQL cluster table I can't create a unique hash index on a column that does not have a "not null" constrain. 

How to repeat:
create table clients ( 
        mac char(6) NOT NULL, 
        ip integer unsigned, 
        primary key (mac)
)engine=NDB; 

mysql> create unique index ip_index using hash on clients(ip);
ERROR 1121 (42000): Column 'ip' is used with UNIQUE or INDEX but is not defined as NOT NULL
[8 Aug 2006 14:33] Lars Torstensson
Category update
[8 Aug 2006 15:55] Hartmut Holzgraefe
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php
[11 Aug 2006 11:32] Lars Torstensson
I got a workaround from Jonas Oreland that makes it possible to create a unique hash index but unfortunately I can’t test if the workaround improves the performance because the index isn’t visible in the NDB API.

Aug 11 12:12:03 fe2 nexus[2023]: DBAPI: API ERROR: 4243 Index not found, Details: no details specified Status: PermanentError, Classification: 1 ApplicationError: Error in application program, Failed to open clients ip_index in database.
[23 Aug 2006 13:29] Jonas Oreland
Lars,

It should be "visible", are you sure you use the correct name
  (can be found e.g. ndb_show_tables)
[24 Aug 2006 8:04] Lars Torstensson
Yes a ndb_show_tables gives this

id    type                 state    logging database     schema  name
9     UniqueHashIndex      Online   Yes     nexus        def     ip_index$unique

And the servers using the ndb-api say this.

Aug 24 09:59:22 nlfe nexus[8892]: SERVER1-3-DBAPI: API ERROR: 4243 Index not found, Details: no details specified Status: PermanentError, Classification: 1 ApplicationError: Error in application program, Failed to open clients ip_index in database
[24 Aug 2006 8:07] Jonas Oreland
and how do you call getIndex (or similar)
[24 Aug 2006 9:12] Lars Torstensson
I use getNdbIndexOperation

myTransaction->getNdbIndexOperation("ip_index","clients")
[24 Aug 2006 10:05] Jonas Oreland
i think the index is called "ip_index$unique"
[24 Aug 2006 11:58] Lars Torstensson
Humm… tried with ip_index$unique and got the same result.

Aug 24 13:51:29 nlfe nexus[9620]: SERVER-5-DBAPI: API ERROR: 4243 Index not found, Details: no details specified Status: PermanentError, Classification: 1 ApplicationError: Error in application program
[3 Nov 2006 9:54] 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/14793

ChangeSet@1.2282, 2006-11-03 10:53:44+01:00, mskold@mysql.com +4 -0
  bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash only indexes with NULL valued attributes, but any NULL valued access will become full table scan with pushed condition on index attribute values
[7 Nov 2006 15:39] 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/14973

ChangeSet@1.2282, 2006-11-07 16:38:37+01:00, mskold@mysql.com +4 -0
  bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash only indexes with NULL valued attributes, but any NULL valued access will become full table scan with pushed condition on index attribute values
[13 Nov 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/15220

ChangeSet@1.2320, 2006-11-13 12:56:45+01:00, mskold@mysql.com +3 -0
  bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash only indexes with NULL valued attributes, but any NULL valued access will become full table scan with pushed condition on index attribute values. 5.1 re-implementation
[14 Dec 2006 5:19] 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 fix/feature enhancement for 5.0.32 and 5.1.14.