MySQL Bugs: #60196: Setting lowercase_table_names to 2 on Windows causing Foreign Key problems

Bug #60196	Setting lowercase_table_names to 2 on Windows causing Foreign Key problems
Submitted:	22 Feb 2011 3:15	Modified:	29 Apr 2011 22:33
Reporter:	Nathan Taylor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.9	OS:	Windows (lower_case_table_names=2)
Assigned to:	Kevin Lewis	CPU Architecture:	Any
Tags:	foreign key, lower_case_table_names, regression

Description:
After upgrading to MySQL Server 5.5.9 on Windows I am experiencing constraint errors with foreign keys when inserting rows with valid inputs. The issue seems to be related to setting lower_case_table_names=2.

Given a 3 table configuration, where 2 tables each define a single-column primary key and the 3rd defines a multi-column primary key made up of foreign keys referencing the other two tables, attempts to insert valid records will fail succeed initially, but then fail indefinitely with constraint validation errors after the MySQL server is restarted.

IMPORTANT: The MySQL server service *MUST* be restarted for this behavior to occur. 

After the error occurs, deleting the 'violated' foreign key and recreating it fixes the problem- until the server is restarted again. 

---

I have tested this with 100% repro success using MySQL Server 5.59 32-bit and 64-bit on Windows 2003 (32-bit) and Windows 7 (64-bit).

How to repeat:
****************************************
** my.ini > lower_case_table_names=2; **
****************************************

CREATE DATABASE FkBugCheck;
COMMIT;

USE FkBugCheck;
COMMIT;

CREATE TABLE `FK1` (
  `Key` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Key`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
COMMIT;

CREATE TABLE `FK2` (
  `Key` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Key`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
COMMIT;

CREATE TABLE `Example` (
  `FK1Key` int(11) NOT NULL,
  `FK2Key` int(11) NOT NULL,
  PRIMARY KEY (`FK2Key`,`FK1Key`),
  KEY `FK1Key` (`FK1Key`),
  KEY `FK2Key` (`FK2Key`),
  CONSTRAINT `FK_FK1` FOREIGN KEY (`FK1Key`) REFERENCES `FK1` (`Key`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_FK2` FOREIGN KEY (`FK2Key`) REFERENCES `FK2` (`Key`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
COMMIT;

INSERT INTO FK1 VALUES (1), (2), (3), (4), (5);
COMMIT;

INSERT INTO FK2 VALUES (1), (2), (3), (4), (5);
COMMIT;

INSERT INTO Example VALUES (1, 1)
COMMIT;

-- ALL OF THE ABOVE QUERIES SHOULD SUCCEED WITHOUT ERROR

*******************************
** RESTART THE MYSQL SERVICE **
*******************************

INSERT INTO Example VALUES (1, 2)
COMMIT;

-- ERROR: Cannot add or update a child row: a foreign key constraint fails (`fktest`.`example`, CONSTRAINT `FK_FK2` FOREIGN KEY (`FK2Key`) REFERENCES `FK2` (`Key`) ON DELETE CASCADE ON UPDATE CASCADE)

Paragraph 2 should read:

Given a 3 table configuration, where 2 tables each define a single-column primary key and
the 3rd defines a multi-column primary key made up of foreign keys referencing the other
two tables, attempts to insert valid records will succeed initially, but then fail
indefinitely with constraint validation errors after the MySQL server is restarted.

I wonder if this is a regression related to the fix of bug #55222 in 5.5.9.

Verified just as described on Windows XP:

...
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 --host=19
2.168.0.1 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use FkBugCheck;
Database changed
mysql> INSERT INTO Example VALUES (1, 2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`fkbugcheck`.`example`, CONSTRAINT `FK_FK2` FOREIGN KEY (`FK2Key`) REFEREN
CES `FK2` (`Key`) ON DELETE CASCADE ON UPDATE CASCADE)

It worked with 5.5.8, so we have a regression bug here.

I found the problem in dict0load.c, dict_load_foreigns().  There was a code path that compared the table name using case sensitive when a table is loaded into the dictionary cache while evaluating referenced tables.

This problem was exposed by the fix for Bug#55222.

The funny thing is that if the referenced tables had previously been loaded into the dictionary cache, then this same code path would not get followed and everything would work fine.  So the simple workaround for this bug is to do a select on each of the referenced tables first before doing the insert/update/delete on the table with foreign keys.

A fix in on the way...

@Kevin Lewis

Something I failed to mention earlier is that foreign keys which I created *before* upgrading the server 5.5.9 seem to be unaffected by this behavior.

Answer to the previous comment which noted; "foreign keys which I created *before* upgrading the server 5.5.9 seem to be unaffected by this behavior".

That makes sense.  Before the fix for Bug#55222, InnoDB would always store those referenced table names in lower case on windows. InnoDB stores these foreign and referenced table names in its own dictionary in the system tables since MySQL does not store foreign key info.  The fix for Bug#55222 makes current code compare in lower case but store these referenced table names in the case given.  In this one spot, though, it is comparing the lower case version of the referenced table name against what is actually stored. So for previously created foreign key relationships which were stored in lower case, the code works correctly.  But this bug exists for tables created after 55222.

Bug #60909 was marked as a duplicate of this one.

Added to 5.6.3 and 5.5.12 changelog:

With the setting lower_case_table_names=2, inserts into InnoDB tables
covered by foreign key constraints could fail after a server restart.