MySQL Bugs: #73686: Creating a unique index for a column involved in a foreign key constraint fails

Bug #73686	Creating a unique index for a column involved in a foreign key constraint fails
Submitted:	22 Aug 2014 11:09	Modified:	22 Dec 2014 14:51
Reporter:	Simon Cruise	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	MySQL Cluster 7.3.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	foreign key ndb, unique key ndb

Description:
If you have a column involved in a foreign key constraint then this can not be used when adding a unique constraint.

You can however add a unique constraint for columns that are not involved in a foreign key constraint and subsquently add the foreign key constraint.

When adding unique after foreign you get :

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

How to repeat:
CREATE TABLE `terminal_payment_page_layout` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `pp_layout_id` int(10) NOT NULL,
  `terminal_id` int(10) NOT NULL,
  `default_template` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_terminal_pp_layout_pp_layout` (`pp_layout_id`),
  KEY `fk_terminal_pp_layout_terminal` (`terminal_id`)
) ENGINE=ndbcluster

alter table terminal_payment_page_layout add CONSTRAINT `fk_terminal_pp_layout_pp_layout` FOREIGN KEY(`pp_layout_id`) REFERENCES `payment_page_layout` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `terminal_payment_page_layout` ADD CONSTRAINT `terminal_pp_layout_uq_terminal_layout` UNIQUE (`pp_layout_id`, `terminal_id`);

Adding a foreign key for terminal_id only without pp_layout_id and then adding unique works.

Also if terminal_id fk is added then unique you are allowed add pp_layout_id fk

id in referenced tables is identical for both.

Could this be related to order in unique key?

Hello Simon,

Thank you for the report.
I could not repeat this issue at my end, could you please provide complete repeatable test case(tables involved etc) to reproduce this issue?

with 7.3.5/7.3.6
=================

// 7.3.6

[root@cluster-repo mysql-cluster-7.3.6]# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.19-ndb-7.3.6-cluster-commercial-advanced-log MySQL Cluster Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS `payment_page_layout`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `payment_page_layout` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=ndbcluster;
Query OK, 0 rows affected (0.96 sec)

mysql> DROP TABLE IF EXISTS `terminal_payment_page_layout`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `terminal_payment_page_layout` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `pp_layout_id` int(10) NOT NULL,
    ->   `terminal_id` int(10) NOT NULL,
    ->   `default_template` tinyint(1) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
    ->   KEY `fk_terminal_pp_layout_pp_layout` (`pp_layout_id`),
    ->   KEY `fk_terminal_pp_layout_terminal` (`terminal_id`)
    -> ) ENGINE=ndbcluster;
Query OK, 0 rows affected (1.75 sec)

mysql> alter table terminal_payment_page_layout add CONSTRAINT `fk_terminal_pp_layout_pp_layout` FOREIGN KEY(`pp_layout_id`) REFERENCES `payment_page_layout` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `terminal_payment_page_layout` ADD CONSTRAINT `terminal_pp_layout_uq_terminal_layout` UNIQUE (`pp_layout_id`, `terminal_id`);
Query OK, 0 rows affected (0.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `terminal_payment_page_layout` ADD CONSTRAINT `terminal_pp_layout_uq_terminal_layout` UNIQUE (`pp_layout_id`, `terminal_id`);
Query OK, 0 rows affected (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table payment_page_layout\G
*************************** 1. row ***************************
       Table: payment_page_layout
Create Table: CREATE TABLE `payment_page_layout` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table terminal_payment_page_layout\G
*************************** 1. row ***************************
       Table: terminal_payment_page_layout
Create Table: CREATE TABLE `terminal_payment_page_layout` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `pp_layout_id` int(10) NOT NULL,
  `terminal_id` int(10) NOT NULL,
  `default_template` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `terminal_pp_layout_uq_terminal_layout` (`pp_layout_id`,`terminal_id`),
  KEY `fk_terminal_pp_layout_pp_layout` (`pp_layout_id`),
  KEY `fk_terminal_pp_layout_terminal` (`terminal_id`),
 CONSTRAINT `fk_terminal_pp_layout_pp_layout` FOREIGN KEY(`pp_layout_id`) REFERENCES `payment_page_layout` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Thanks,
Umesh

I tried to recreate in test schema but failed. It must be something to do with my other schema table structures then that is causing this. Exact same steps on test schema work fine.

So I created all related database tables in test and tried the same steps on both schemas and it still works on test schema.

I dumped the schema that is having this issue and re-created but the issue is still there. If I change it all to InnoDB then it works as expected.

It must be something to do with other fk's configured on related tables. I'll do a deeper dive and get back, I can't share our schema publicly unfortunately but I'll attempt to recreate on a fresh schema with a script that I'll upload to not waste any more of your time.

Really appreciate you looking into this.

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".