Bug #77457 Cannot add unique constraint on set of fields with existing foreign key

• Submitted: 23 Jun 2015 14:56
• Modified: 24 Aug 2015 16:19
• Reporter: Vladislav Belogrudov
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 7.4.6-1.el7.x86_64, 7.4.7
• OS: Oracle Linux (7.1)
• Assigned to: Lakshmi Narayanan Sreethar
• CPU Architecture: Any
• Tags: ndb

[23 Jun 2015 14:56] Vladislav Belogrudov

Description:
I try to run OpenStack with MySQL Cluster and while running migration scripts I receive 

ERROR 1296 (HY000): Got error 4243 'Index not found' from NDBCLUSTER 

How to repeat:
CREATE TABLE networks (
tenant_id VARCHAR(255),
id VARCHAR(36) NOT NULL,
name VARCHAR(255),
status VARCHAR(16),
admin_state_up BOOL,
shared BOOL,
PRIMARY KEY (id)
);

CREATE TABLE ports (
tenant_id VARCHAR(255),
id VARCHAR(36) NOT NULL,
name VARCHAR(255),
network_id VARCHAR(36) NOT NULL,
mac_address VARCHAR(32) NOT NULL,
admin_state_up BOOL NOT NULL,
status VARCHAR(16) NOT NULL,
device_id VARCHAR(255) NOT NULL,
device_owner VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(network_id) REFERENCES networks (id)
);

ALTER TABLE ports ADD CONSTRAINT uniq_ports0network_id0mac_address UNIQUE (network_id, mac_address);

[23 Jun 2015 14:57] Vladislav Belogrudov

log of api node

Attachment: mysql.log (text/x-log), 5.17 KiB.

[24 Jun 2015 5:53] MySQL Verification Team

Hello Vladislav,

Thank you for the report and test case.

Thanks,
Umesh

[25 Jun 2015 8:16] Magnus Blåudd

Suggested workaround is to create the index and foreign key separately.

mysql> CREATE TABLE networks (
-> tenant_id VARCHAR(255),
-> id VARCHAR(36) NOT NULL,
-> name VARCHAR(255),
-> status VARCHAR(16),
-> admin_state_up BOOL,
-> shared BOOL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0,68 sec)
mysql> CREATE TABLE ports (
-> tenant_id VARCHAR(255),
-> id VARCHAR(36) NOT NULL,
-> name VARCHAR(255),
-> network_id VARCHAR(36) NOT NULL,
-> mac_address VARCHAR(32) NOT NULL,
-> admin_state_up BOOL NOT NULL,
-> status VARCHAR(16) NOT NULL,
-> device_id VARCHAR(255) NOT NULL,
-> device_owner VARCHAR(255) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0,90 sec)
mysql> CREATE UNIQUE INDEX uniq_ports0network_id0mac_address on ports (network_id, mac_address);
Query OK, 0 rows affected (0,82 sec)
mysql> ALTER TABLE ports ADD FOREIGN KEY(network_id) REFERENCES networks (id);
Query OK, 0 rows affected (0,50 sec)
Records: 0 Duplicates: 0 Warnings: 0

[24 Aug 2015 16:19] Jon Stephens

Documented fix as follows in the NDB 7.3.11 and 7.4.8 changelogs:

    Adding a unique key to an NDB table failed when the table
    already had a foreign key. Prior to upgrading, you can work
    around this issue by creating the unique key first, then adding
    the foreign key afterwards, using a separate ALTER TABLE
    statement.

Closed.