MySQL Bugs: #60309: MySQL 5.5.9 for Mac OSX has bug with foreign key constraints

Bug #60309	MySQL 5.5.9 for Mac OSX has bug with foreign key constraints
Submitted:	3 Mar 2011 0:01	Modified:	6 May 2011 23:53
Reporter:	Robert Morse	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.9	OS:	MacOS
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
Did a clean install of 5.5.9 on a brand new MacBook Pro (i7) under Mac OSX 10.6.6 without any issues.  Created the databases and the tables looked fine including all foreign keys and indexes.  When trying to load data, MySql complained with several foreign key violation errors.  The syntax of the inserts was correct, and the foreign key definitions were correct.

We tried the 32 and 64 bit versions with the same results.

Uninstalled MySQL, and installed version 5.5.8.  Everything works fine.

How to repeat:
See above

Suggested fix:
No clue

we'll need to see a table structure for the involved tables as well as an insert that works in 5.5.8 but fails in 5.5.9.  thanks,

Here is just one example.  Insertions into the PRODUCT table worked without error.  Inserting any row into DATA_CREDENTIAL failed with a foreign key violation referencing the ID in PRODUCT.  The inserts follow the table description.

-- ----------------------------
--  Table structure for `PRODUCT`
-- ----------------------------
DROP TABLE IF EXISTS `PRODUCT`;
CREATE TABLE `PRODUCT` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ARCHIVED` bit(1) DEFAULT NULL,
  `CREATE_DATE` datetime DEFAULT NULL,
  `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ENABLED` bit(1) DEFAULT NULL,
  `RESTRICTED` bit(1) DEFAULT NULL,
  `VERSION` int(11) DEFAULT NULL,
  `IS_ACTIVE` bit(1) DEFAULT NULL,
  `AGE_NEAREST_BIRTHDAY` bit(1) DEFAULT NULL,
  `DEACTIVATE_DATE` datetime DEFAULT NULL,
  `MARKETING_NAME` varchar(128) COLLATE utf8_bin NOT NULL,
  `MAX_AGE` int(11) DEFAULT NULL,
  `MIN_AGE` int(11) DEFAULT NULL,
  `POLICY_PRODUCT_TYPE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `DESCRIPTION` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
  `PRODUCT_LOGO` longblob,
  `PRODUCT_NAME` varchar(128) COLLATE utf8_bin NOT NULL,
  `PRODUCTION_RULE_URL` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `TEST_RULE_URL` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `COMPANY_ID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK185958CFC238DDA8` (`COMPANY_ID`),
  CONSTRAINT `FK185958CFC238DDA8` FOREIGN KEY (`COMPANY_ID`) REFERENCES `COMPANY` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
--  Table structure for `DATA_CREDENTIAL`
-- ----------------------------
DROP TABLE IF EXISTS `DATA_CREDENTIAL`;
CREATE TABLE `DATA_CREDENTIAL` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ARCHIVED` bit(1) DEFAULT NULL,
  `CREATE_DATE` datetime DEFAULT NULL,
  `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ENABLED` bit(1) DEFAULT NULL,
  `RESTRICTED` bit(1) DEFAULT NULL,
  `VERSION` int(11) DEFAULT NULL,
  `ACCOUNT_NUMBER` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `BRANCH_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `CA_CERT_LOCATION` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `CA_CERT_PASSWORD` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `CLIENT_CERT_LOCATION` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `CLIENT_CERT_PASSWORD` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `CUSTOMER_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `CUSTOMER_NAME` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `DATA_PROVIDER` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ENCRYPTION_KEY` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `MIB_COMPANY_SYMBOL` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `PASSWORD` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `PASSWORD_EXPIRATION` date DEFAULT NULL,
  `PREVIOUS_PASSWORD` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `PURPOSE_CODE` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `SECURITY_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `SERVICE_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `SERVICE_URL` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `SITE_ID` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `IS_TEST` bit(1) DEFAULT NULL,
  `USER_ID` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `PRODUCT_ID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FKA3BDB40CDECB61C9` (`PRODUCT_ID`),
  CONSTRAINT `FKA3BDB40CDECB61C9` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `PRODUCT` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

This insert into PRODUCT works fine:

insert into PRODUCT(ID, VERSION, CREATED_BY, RESTRICTED, ENABLED, ARCHIVED, PRODUCT_NAME, MARKETING_NAME, DESCRIPTION, MIN_AGE, MAX_AGE, AGE_NEAREST_BIRTHDAY, IS_ACTIVE, DEACTIVATE_DATE, COMPANY_ID, PRODUCTION_RULE_URL, TEST_RULE_URL) values (1, 0, 'public', false, true, false, 'Test', 'Test', 'Test', 16, 65, true, true, null, 1, 'test', 'test');

This insert fails with a foreign key violation:

insert into DATA_CREDENTIAL(ID, VERSION, CREATED_BY, RESTRICTED, ENABLED, ARCHIVED, PRODUCT_ID, DATA_PROVIDER, USER_ID, PASSWORD, PASSWORD_EXPIRATION, PREVIOUS_PASSWORD, PURPOSE_CODE, ENCRYPTION_KEY, SERVICE_URL, ACCOUNT_NUMBER, SITE_ID, SERVICE_NAME, CUSTOMER_CODE, SECURITY_CODE, CUSTOMER_NAME, BRANCH_CODE, MIB_COMPANY_SYMBOL, CLIENT_CERT_LOCATION, CA_CERT_LOCATION, CLIENT_CERT_PASSWORD, CA_CERT_PASSWORD, IS_TEST) values (1, 0, 'public', false, true, false, 1, 'TEST', null, 'TEST', null, null, null, null, 'TEST', 'TEST', null, null, null, null, null, null, null, null, null, null, null, true );

Inserts into some other tables that have a foreign key to PRODUCT work fine.

Hope this helps.

Please, send the output of:

show variables like 'lower%';

This can be related to/a duplicate of bug #60196.

Works as expected for me with 5.5.11 (current mysql-5.5 from bzr) on Mac OS X.

Hello,
Unfortunately, I removed 5.5.9, and just have 5.5.8 running.  If I have time this weekend, I'll re-install and send the variables.  I will look at 60196.

I second this problem.  I'd be happy to provide the case where it fails.

Ken,

Please, send your test case and the output of:

show variables like 'lower%';

from your environment.

I also received a new MBP and reinstalled MySQL (mysql-5.5.10-osx10.6-x86_64). Finally I came across the same problem as described above. So here is the query result and what I did to solve it.

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

Dropped database, created the file /etc/my.cnf with the following content:

[mysqld]
lower_case_table_names=1

Restarted the MySQL daemon and repeated the query:

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

I recreated the tables and everything works fine.

Same issue for me.   5.5.8 works, 5.5.9 fails with foreign key constraint violations.

I need to stick with lower_case_table_names=2 to be dump compatible with other systems that use that setting.

This issue is similar to Bug#60196 but not quite a duplicate.  In that case, InnoDB did an extra case sensitive comparison with the table name that it should not have done when lower_case_table_names == 2.  

Both of these bugs have the peculiar condition that if you do all the SQL in the same session, the problem does not occur.  You have to create both tables and fill the referenced table, shutdown and start a new instance before trying to insert a record into the table with the foreign key.

I tried the SQL provided by Robert Morse above, but that did not work as given since it referenced a 'COMPANY' table that was not provided.  Nevertheless, it does show the problem when you take the table the constraint referencing 'COMPANY' out of the CREATE TABLE PRODUCT statement.  But then, only if the last INSERT into 'DATA_CREDENTIAL' is done after a reboot.

After debugging this issue, I found that this problem shows another hole in the fix for Bug#55222.  This testcase is different from that for Bug#60196 in that the referenced table contains a secondary key.  When the engine is restarted, the referenced table is opened by the purge thread, which does not notice that lower_case_table_names == 2.

So this is a similar problem as 60196 in that the referenced table is opened in a back-door way.  But this is a separate bug.

For reference, here is a shorter version of the SQL needed to reproduce this bug;

DROP TABLE IF EXISTS `DATA_CREDENTIAL`;
DROP TABLE IF EXISTS `PRODUCT`;

CREATE TABLE `PRODUCT` (
  `ID` int(20),
  `COMPANY_ID` int(20),
  PRIMARY KEY (`ID`),
  KEY `FK1` (`COMPANY_ID`)
) ENGINE=InnoDB ;

CREATE TABLE `DATA_CREDENTIAL` (
  `ID` int(20),
  `PRODUCT_ID` int(20),
  PRIMARY KEY (`ID`),
  KEY `FK2` (`PRODUCT_ID`),
  CONSTRAINT `FK2` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `PRODUCT` (`ID`)
) ENGINE=InnoDB;

insert into PRODUCT(ID, COMPANY_ID) values (1, 1);

### Reboot the server

insert into DATA_CREDENTIAL(ID, PRODUCT_ID) values (1, 1);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`data_credential`, CONSTRAINT `FK2` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `PRODUCT` (`ID`))

I'm experiencing the same issue as well. Switched to 5.1.49 on my iMac and the same schema worked perfectly.
Snow 10.6.7 on core i7 macbook
mysql  Ver 14.14 Distrib 5.5.11, for osx10.6 (i386) using readline 5.1

Added to changelog for 5.5.13, 5.6.3:

Similar problem to the foreign key error in bug #11831040 / 60196 /
60909, but with a different root cause and occurring on Mac OS X.
With the setting lower_case_table_names=2, inserts into InnoDB tables
covered by foreign key constraints could fail after a server restart.

+1 for lower_case_table_names=1. Works perfectly