MySQL Bugs: #21507: I can't create a unique hash index in NDB

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

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

Category update

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

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.

Lars,

It should be "visible", are you sure you use the correct name
  (can be found e.g. ndb_show_tables)

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

and how do you call getIndex (or similar)

I use getNdbIndexOperation

myTransaction->getNdbIndexOperation("ip_index","clients")

i think the index is called "ip_index$unique"

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

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

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

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

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.