Bug #70351 ALTER TABLE ADD CONSTRAINT xxx FOREIGN KEY adds two constraints

• Submitted: 16 Sep 2013 12:55
• Modified: 17 Sep 2014 5:26
• Reporter: Søren Andersen
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.5.32,5.1.71,5.5.33, 5.6.13, 5.7.2-m12
• OS: FreeBSD (9.0-RELEASE-p7)
• CPU Architecture: Any
• Tags: regression

[16 Sep 2013 12:55] Søren Andersen

Description:
Adding a named foreign key constraint makes the DB add two constraints.

On my production servers I could not insert into the table after the change:
Cannot add or update a child row: a foreign key constraint fails (`nshop`.`module_payment_provider__transaction__xyzasdf`, CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CA...
I am certain the the shop ID existed.
However I cannot reproduce the insert error with the example below.

It works as expected on 5.5.29.

How to repeat:
CREATE TABLE `test_shop` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO test_shop values (1);

CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
	`id`       int(10) unsigned NOT NULL AUTO_INCREMENT,
	`shop_id`  int(10) unsigned NOT NULL,
	PRIMARY KEY (`id`),
	KEY `shop_id` (`shop_id`)
) ENGINE=InnoDB;

ALTER TABLE `module_payment_provider__transaction__xyzasdf`
	ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id`
		FOREIGN KEY (`shop_id`)
		REFERENCES `test_shop` (`id`)
		ON DELETE CASCADE
		ON UPDATE CASCADE;

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';

-- Output shows two rows:
-- One for CONSTRAINT_NAME mpp__transaction_log__nordpay_ibfk_shop_id
-- The other for module_payment_provider__transaction__xyzasdf_ibfk_shop_id 

Suggested fix:
Accept the given symbol.

[16 Sep 2013 14:34] Valeriy Kravchuk

Same with 5.6.13 on Windows, two constraints are added:

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`
) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `module_payment_provider__transaction__xyzasdf_ibfk_shop_id` FOREIG
N KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASC
ADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.13-log |
+------------+
1 row in set (0.06 sec)

[17 Sep 2013 8:07] MySQL Verification Team

Hello Søren,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

[17 Sep 2013 8:09] MySQL Verification Team

// Looks like most of the GA versions in 5.1/5.5/5.6 are affected... 
// Checked one of the lowest version in 5.5.x series and observed that it is not affected (5.5.23)
// Regression

// 5.5.32(reported version) -- affected

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_shop` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test_shop values (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `module_payment_provider__transaction__xyzasdf` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`shop_id`  int(10) unsigned NOT NULL,PRIMARY KEY (`id`),KEY `shop_id` (`shop_id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> ALTER TABLE `module_payment_provider__transaction__xyzasdf` ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `module_payment_provider__transaction__xyzasdf_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                                            | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME                                    | REFERENCED_TABLE_NAME |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| def                | test              | mpp__transaction_log__nordpay_ibfk_shop_id                 | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
| def                | test              | module_payment_provider__transaction__xyzasdf_ibfk_shop_id | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
2 rows in set (0.00 sec)

// 5.7.2-m12 -- affected

mysql> select version();
+------------------------------------------+
| version()                                |
+------------------------------------------+
| 5.7.2-m12-enterprise-commercial-advanced |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_shop` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test_shop values (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `module_payment_provider__transaction__xyzasdf` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`shop_id`  int(10) unsigned NOT NULL,PRIMARY KEY (`id`),KEY `shop_id` (`shop_id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `module_payment_provider__transaction__xyzasdf` ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `module_payment_provider__transaction__xyzasdf_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                                            | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME                                    | REFERENCED_TABLE_NAME |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| def                | test              | mpp__transaction_log__nordpay_ibfk_shop_id                 | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
| def                | test              | module_payment_provider__transaction__xyzasdf_ibfk_shop_id | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
2 rows in set (0.00 sec)

[17 Sep 2013 8:11] MySQL Verification Team

// 5.6.13  -- affected

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.13-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_shop` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO test_shop values (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `module_payment_provider__transaction__xyzasdf` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`shop_id`  int(10) unsigned NOT NULL,PRIMARY KEY (`id`),KEY `shop_id` (`shop_id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `module_payment_provider__transaction__xyzasdf` ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `module_payment_provider__transaction__xyzasdf_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                                            | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME                                    | REFERENCED_TABLE_NAME |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| def                | test              | mpp__transaction_log__nordpay_ibfk_shop_id                 | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
| def                | test              | module_payment_provider__transaction__xyzasdf_ibfk_shop_id | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
2 rows in set (0.01 sec)

// 5.5.33  -- affected

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.5.33-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_shop` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test_shop values (1);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE `module_payment_provider__transaction__xyzasdf` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`shop_id`  int(10) unsigned NOT NULL,PRIMARY KEY (`id`),KEY `shop_id` (`shop_id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `module_payment_provider__transaction__xyzasdf` ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `module_payment_provider__transaction__xyzasdf_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                                            | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME                                    | REFERENCED_TABLE_NAME |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| def                | test              | mpp__transaction_log__nordpay_ibfk_shop_id                 | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
| def                | test              | module_payment_provider__transaction__xyzasdf_ibfk_shop_id | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
2 rows in set (0.00 sec)

[17 Sep 2013 8:11] MySQL Verification Team

// 5.1.71  -- affected

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.71-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_shop` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test_shop values (1);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE `module_payment_provider__transaction__xyzasdf` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`shop_id`  int(10) unsigned NOT NULL,PRIMARY KEY (`id`),KEY `shop_id` (`shop_id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE `module_payment_provider__transaction__xyzasdf` ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `module_payment_provider__transaction__xyzasdf_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                                            | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME                                    | REFERENCED_TABLE_NAME |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| NULL               | test              | mpp__transaction_log__nordpay_ibfk_shop_id                 | NULL                      | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
| NULL               | test              | module_payment_provider__transaction__xyzasdf_ibfk_shop_id | NULL                      | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
+--------------------+-------------------+------------------------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
2 rows in set (0.00 sec)

// 5.5.23  -- Not affected

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.5.23-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_shop` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO test_shop values (1);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE `module_payment_provider__transaction__xyzasdf` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`shop_id`  int(10) unsigned NOT NULL,PRIMARY KEY (`id`),KEY `shop_id` (`shop_id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `module_payment_provider__transaction__xyzasdf` ADD CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table module_payment_provider__transaction__xyzasdf\G
*************************** 1. row ***************************
       Table: module_payment_provider__transaction__xyzasdf
Create Table: CREATE TABLE `module_payment_provider__transaction__xyzasdf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_id` (`shop_id`),
  CONSTRAINT `mpp__transaction_log__nordpay_ibfk_shop_id` FOREIGN KEY (`shop_id`) REFERENCES `test_shop` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS where TABLE_NAME = 'module_payment_provider__transaction__xyzasdf';
+--------------------+-------------------+--------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                            | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME                                    | REFERENCED_TABLE_NAME |
+--------------------+-------------------+--------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
| def                | test              | mpp__transaction_log__nordpay_ibfk_shop_id | def                       | test                     | PRIMARY                | NONE         | CASCADE     | CASCADE     | module_payment_provider__transaction__xyzasdf | test_shop             |
+--------------------+-------------------+--------------------------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------------------------------------+-----------------------+
1 row in set (0.00 sec)

[17 Sep 2014 5:26] Erlend Dahl

Fixed in 5.5.34, 5.6.14, 5.7.2. See bug#69707, bug#69693.