MySQL - adding constraint once adds it twice with diffent case

Question

I have this weird behaviour when I add a constraint to a MySQL table:

[...] add constraint qrtz_blob_triggers_ibfk_1 foreign key [...]

Now SHOW CREATE TABLE shows the constraint twice:

CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY [...]
CONSTRAINT `QRTZ_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY [...]

Here is the complete session:

mysql> DROP DATABASE IF EXISTS my_test; CREATE DATABASE my_test default character set utf8 collate utf8_bin;
Query OK, 2 rows affected (0.07 sec)

Query OK, 1 row affected (0.00 sec)

mysql> use my_test;
Database changed
mysql> 
mysql> DROP TABLE IF EXISTS `QRTZ_TRIGGERS`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE TABLE `QRTZ_TRIGGERS` (
       `TRIGGER_NAME` varchar(80) NOT NULL,
       `TRIGGER_GROUP` varchar(80) NOT NULL,
       PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

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

mysql> 
mysql> create table `QRTZ_BLOB_TRIGGERS` (`TRIGGER_NAME` varchar(80) not null, `TRIGGER_GROUP` varchar(80) not null, `BLOB_DATA` longblob, primary key (`TRIGGER_NAME`, `TRIGGER_GROUP`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> alter table `QRTZ_BLOB_TRIGGERS`  add index qrtz_blob_triggers_ibfk_1 (`TRIGGER_NAME`, `TRIGGER_GROUP`), add constraint qrtz_blob_triggers_ibfk_1 foreign key (`TRIGGER_NAME`, `TRIGGER_GROUP`) references `QRTZ_TRIGGERS` (`TRIGGER_NAME`, `TRIGGER_GROUP`) ON UPDATE RESTRICT ON DELETE RESTRICT;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table QRTZ_BLOB_TRIGGERS \G
*************************** 1. row ***************************
       Table: QRTZ_BLOB_TRIGGERS
Create Table: CREATE TABLE `QRTZ_BLOB_TRIGGERS` (
  `TRIGGER_NAME` varchar(80) NOT NULL,
  `TRIGGER_GROUP` varchar(80) NOT NULL,
  `BLOB_DATA` longblob,
  PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `qrtz_blob_triggers_ibfk_1` (`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY (`TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`TRIGGER_NAME`, `TRIGGER_GROUP`),
  CONSTRAINT `QRTZ_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY (`TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Using

[...] add constraint QRTZ_BLOB_TRIGGERS_ibfk_1 foreign key [...]

works fine.

I am using Ubuntu 12.04 LTS with MySQL 5.5:

mysql-server-5.5     5.5.32-0ubuntu0.12.04.1

Does anyone have a clue what's going on there?

I have lower_case_table_names=0 as suggested by the MySQL manual: "Use lower_case_table_names=0 on Unix and [...]" — Timo
Maybe are you on a case-insensitive file system? (I'm not even sure if this would impact somehow constrain identifiers... :°) — Sylvain Leroux

RandomSeed RandomSeed · Accepted Answer · 2013-08-13T16:19:34

This is a known bug, fixed in the upcoming release:

InnoDB would rename a user-defined foreign key constraint containing the string “ibfk” in its name, resulting in a duplicate constraint."

The bug report advises a fix in versions 5.1.72, 5.5.32, 5.6.14, 5.7.2, but I suspect a typo for the 5.5 branch. Considering the release dates, I would expect the fix in v5.5.34.

MySQL - adding constraint once adds it twice with diffent case

1 Answers