diff options
author | unknown <kevin.lewis@oracle.com> | 2011-03-07 09:42:07 -0600 |
---|---|---|
committer | unknown <kevin.lewis@oracle.com> | 2011-03-07 09:42:07 -0600 |
commit | 08f128f63323924666caf4f7cb0d2b3c39d2b017 (patch) | |
tree | cc9c2374edd188996ec64dbc6bfab5ba15d2f032 | |
parent | 85ea5564bd96c35ab55225285d4e80ee133d67d4 (diff) | |
download | mariadb-git-08f128f63323924666caf4f7cb0d2b3c39d2b017.tar.gz |
Bug#60196 / Bug#11831040
Setting lowercase_table_names to 2 on Windows causing Foreign Key problems
This problem was exposed by the fix for Bug#55222. There was a codepath in dict0load.c,
dict_load_foreigns() that made sure the table name matched case sensitive in order to
load a referenced table into the dictionary as needed. If an engine is rebooted which
accesses a table with foreign keys, and lower_case_table_names=2, then the table with
foreign keys will get an error when it is changed (insert/updated/delete).
Once the referenced tables are loaded into the dictionary cache by a select statement
on those tables, the same change would succeed because the affected code path would
not get followed.
-rwxr-xr-x | mysql-test/suite/innodb/r/innodb_bug60196.result | 76 | ||||
-rwxr-xr-x | mysql-test/suite/innodb/t/innodb_bug60196-master.opt | 1 | ||||
-rwxr-xr-x | mysql-test/suite/innodb/t/innodb_bug60196.test | 91 | ||||
-rw-r--r-- | storage/innobase/dict/dict0load.c | 8 |
4 files changed, 173 insertions, 3 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_bug60196.result b/mysql-test/suite/innodb/r/innodb_bug60196.result new file mode 100755 index 00000000000..296c5ce7e1d --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug60196.result @@ -0,0 +1,76 @@ +DROP TABLE IF EXISTS Bug_60196_FK1 ; +DROP TABLE IF EXISTS Bug_60196_FK2 ; +DROP TABLE IF EXISTS Bug_60196 ; +CREATE TABLE `Bug_60196_FK1` (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE `Bug_60196_FK2` (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE `Bug_60196` ( +FK1_Key INT NOT NULL, +FK2_Key INT NOT NULL, +PRIMARY KEY (FK2_Key, FK1_Key), +KEY FK1_Key (FK1_Key), +KEY FK2_Key (FK2_Key), +CONSTRAINT FK_FK1 FOREIGN KEY (FK1_Key) +REFERENCES Bug_60196_FK1 (Primary_Key) +ON DELETE CASCADE +ON UPDATE CASCADE, +CONSTRAINT FK_FK2 FOREIGN KEY (FK2_Key) +REFERENCES Bug_60196_FK2 (Primary_Key) +ON DELETE CASCADE +ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO Bug_60196_FK1 VALUES (1), (2), (3), (4), (5); +INSERT INTO Bug_60196_FK2 VALUES (1), (2), (3), (4), (5); +INSERT INTO Bug_60196 VALUES (1, 1); +INSERT INTO Bug_60196 VALUES (1, 2); +INSERT INTO Bug_60196 VALUES (1, 3); +INSERT INTO Bug_60196 VALUES (1, 99); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug_60196`, CONSTRAINT `FK_FK2` FOREIGN KEY (`FK2_Key`) REFERENCES `Bug_60196_FK2` (`Primary_Key`) ON DELETE CASCADE ON UPDATE CASCADE) +INSERT INTO Bug_60196 VALUES (99, 1); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug_60196`, CONSTRAINT `FK_FK1` FOREIGN KEY (`FK1_Key`) REFERENCES `Bug_60196_FK1` (`Primary_Key`) ON DELETE CASCADE ON UPDATE CASCADE) +SELECT * FROM bug_60196_FK1; +Primary_Key +1 +2 +3 +4 +5 +SELECT * FROM bug_60196_FK2; +Primary_Key +1 +2 +3 +4 +5 +SELECT * FROM bug_60196; +FK1_Key FK2_Key +1 1 +1 2 +1 3 +# Stop master server +# Restart server. +# +# Try to insert more to the example table with foreign keys. +# Bug60196 causes the foreign key file not to be found after +# the resstart above. +# +SELECT * FROM Bug_60196; +FK1_Key FK2_Key +1 1 +1 2 +1 3 +INSERT INTO Bug_60196 VALUES (2, 1); +INSERT INTO Bug_60196 VALUES (2, 2); +INSERT INTO Bug_60196 VALUES (2, 3); +SELECT * FROM Bug_60196; +FK1_Key FK2_Key +1 1 +1 2 +1 3 +2 1 +2 2 +2 3 + +# Clean up. +DROP TABLE Bug_60196; +DROP TABLE Bug_60196_FK1; +DROP TABLE Bug_60196_FK2; diff --git a/mysql-test/suite/innodb/t/innodb_bug60196-master.opt b/mysql-test/suite/innodb/t/innodb_bug60196-master.opt new file mode 100755 index 00000000000..c0a1981fa7c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug60196-master.opt @@ -0,0 +1 @@ +--lower-case-table-names=2
diff --git a/mysql-test/suite/innodb/t/innodb_bug60196.test b/mysql-test/suite/innodb/t/innodb_bug60196.test new file mode 100755 index 00000000000..70da220d68a --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug60196.test @@ -0,0 +1,91 @@ +# Bug#60196 - Setting lowercase_table_names to 2 on Windows causing +# Foreign Key problems after an engine is restarted. + +# This test case needs InnoDB. +-- source include/have_innodb.inc + +# +# Precautionary clean up. +# +--disable_warnings +DROP TABLE IF EXISTS Bug_60196_FK1 ; +DROP TABLE IF EXISTS Bug_60196_FK2 ; +DROP TABLE IF EXISTS Bug_60196 ; +--enable_warnings + +# +# Create test data. +# +CREATE TABLE `Bug_60196_FK1` (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE `Bug_60196_FK2` (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE `Bug_60196` ( + FK1_Key INT NOT NULL, + FK2_Key INT NOT NULL, + PRIMARY KEY (FK2_Key, FK1_Key), + KEY FK1_Key (FK1_Key), + KEY FK2_Key (FK2_Key), + CONSTRAINT FK_FK1 FOREIGN KEY (FK1_Key) + REFERENCES Bug_60196_FK1 (Primary_Key) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT FK_FK2 FOREIGN KEY (FK2_Key) + REFERENCES Bug_60196_FK2 (Primary_Key) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO Bug_60196_FK1 VALUES (1), (2), (3), (4), (5); +INSERT INTO Bug_60196_FK2 VALUES (1), (2), (3), (4), (5); +INSERT INTO Bug_60196 VALUES (1, 1); +INSERT INTO Bug_60196 VALUES (1, 2); +INSERT INTO Bug_60196 VALUES (1, 3); +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO Bug_60196 VALUES (1, 99); +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO Bug_60196 VALUES (99, 1); + +SELECT * FROM bug_60196_FK1; +SELECT * FROM bug_60196_FK2; +SELECT * FROM bug_60196; + +--echo # Stop master server + +# Write file to make mysql-test-run.pl wait for the server to stop +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +# Send a shutdown request to the server +-- shutdown_server 10 + +# Call script that will poll the server waiting for it to disapear +-- source include/wait_until_disconnected.inc + +--echo # Restart server. + +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +# Turn on reconnect +--enable_reconnect + +# Call script that will poll the server waiting for it to be back online again +--source include/wait_until_connected_again.inc + +# Turn off reconnect again +--disable_reconnect + +--echo # +--echo # Try to insert more to the example table with foreign keys. +--echo # Bug60196 causes the foreign key file not to be found after +--echo # the resstart above. +--echo # +SELECT * FROM Bug_60196; +INSERT INTO Bug_60196 VALUES (2, 1); +INSERT INTO Bug_60196 VALUES (2, 2); +INSERT INTO Bug_60196 VALUES (2, 3); +SELECT * FROM Bug_60196; + +--echo +--echo # Clean up. +DROP TABLE Bug_60196; +DROP TABLE Bug_60196_FK1; +DROP TABLE Bug_60196_FK2; + diff --git a/storage/innobase/dict/dict0load.c b/storage/innobase/dict/dict0load.c index 14490980bb6..37bf4a1ad59 100644 --- a/storage/innobase/dict/dict0load.c +++ b/storage/innobase/dict/dict0load.c @@ -2258,10 +2258,12 @@ loop: /* Since table names in SYS_FOREIGN are stored in a case-insensitive order, we have to check that the table name matches also in a binary string comparison. On Unix, MySQL allows table names that only differ - in character case. */ - - if (0 != ut_memcmp(field, table_name, len)) { + in character case. If lower_case_table_names=2 then what is stored + may not be the same case, but the previous comparison showed that they + match with no-case. */ + if ((srv_lower_case_table_names != 2) + && (0 != ut_memcmp(field, table_name, len))) { goto next_rec; } |