From: kevin.lewis Date: March 7 2011 3:42pm Subject: bzr commit into mysql-5.5-innodb branch (kevin.lewis:3343) Bug#60196 Bug#11831040 List-Archive: http://lists.mysql.com/commits/132549 X-Bug: 60196,11831040 Message-Id: <20110307154216.8E294C9C648@kevin-lewis-macbook.local> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0330280351==" --===============0330280351== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///Users/kevinlewis/Work/Mysql/Bug60196/mysql-5.5-innodb/ based on revid:vasil.dimov@stripped 3343 kevin.lewis@stripped 2011-03-07 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. added: mysql-test/suite/innodb/r/innodb_bug60196.result mysql-test/suite/innodb/t/innodb_bug60196-master.opt mysql-test/suite/innodb/t/innodb_bug60196.test modified: storage/innobase/dict/dict0load.c === added file 'mysql-test/suite/innodb/r/innodb_bug60196.result' --- a/mysql-test/suite/innodb/r/innodb_bug60196.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb/r/innodb_bug60196.result revid:kevin.lewis@stripped @@ -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; === added file 'mysql-test/suite/innodb/t/innodb_bug60196-master.opt' --- a/mysql-test/suite/innodb/t/innodb_bug60196-master.opt 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb/t/innodb_bug60196-master.opt revid:kevin.lewis@stripped @@ -0,0 +1 @@ +--lower-case-table-names=2 === added file 'mysql-test/suite/innodb/t/innodb_bug60196.test' --- a/mysql-test/suite/innodb/t/innodb_bug60196.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb/t/innodb_bug60196.test revid:kevin.lewis@stripped @@ -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; + === modified file 'storage/innobase/dict/dict0load.c' --- a/storage/innobase/dict/dict0load.c revid:vasil.dimov@stripped +++ b/storage/innobase/dict/dict0load.c revid:kevin.lewis@stripped @@ -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; } --===============0330280351== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/kevin.lewis@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: kevin.lewis@stripped # target_branch: file:///Users/kevinlewis/Work/Mysql/Bug60196/mysql-\ # 5.5-innodb/ # testament_sha1: f52605d9299b2fcea6f290492044265a308104c8 # timestamp: 2011-03-07 09:42:15 -0600 # base_revision_id: vasil.dimov@stripped\ # kutqg71nw6v1dzq0 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWeDSf/cABstfgEAyf3f//3+v /+D////4YA28fLyzSW7bbS9bdeuQni1bAVl3YDo1J66r0NKNN6thJRQaTT9U9T1PU2p4nqajammh o0ABo0AAAACSRDTEJmgpkyp4p6n6KeoaeoGgGmhoGgBoBKEyCin6NU/Kj1HlB5R5Q9QAADIA0AAA kSImibTVT20m1JmRomp+TSh6T009RqPTUep6h5QG9UNlDjI0yYmgyZMJpkDIaA0Bpk0MAJoDCRII CMSm0yBlPRNI09TT0U9NqJ6jIaGmmTygaJpStWRpjXzUgsfql+OXj+Xft4c0ZpxG29KcW1wX6tPo 18qqDGqXy47LTpGchoYWFoX3Cz+T3pVuTCWA2xee2mkmY4JUXZVewvgULhUFcpMZBpJb84Nwt3oy 5CzUjQYt1kJc9YFgzWwjCAmQbS6UyYy8eEsYl2S/NoVmVVOcSiOiDLVG7huspglaxLz2izYMaeSj AI3jxP41d4K0GJgmeJmGMZSFmRuRI4xi/sEMOSbw24Kqhs+gaXtxxOjGg77hlobfaOFnhWiVX2sd yhBoKl51s25NR6wD1dQVJBIkMDDCVxhKTYNfkLWLAdwtBVUQRnD6DdNB7hHTXYLuAtBFx3ZhiMkQ HIoBoWlJBdMa97PwBVFWSLhZGZ0gGBQsNzGzE/Qy3Hp2OXeFJEpaX3tLTFJ9NIrzhKRKcNKt5Ykt /zK0Z5FYmZd5JJG9qypYSXJOX69hGBM9i+Vsj/NcqzDIcpwPXoMDZPmrqLLT3lZDWYaRjrgx6x54 kZXBXMd9w65aUko9ELEipNrCoXFXxSb35LVVSimEmdcoGx6j1DNQd+aLaeOe4jPPC0p5CQZSg6ki EkdmzEfmJa7rSobR58j7/W/vsLV19ycl4vxi2odMtvltEUYlqY2tgQxsbGwbG02h6AZoWE6+2aqa ElQwxOoC1gaDNgITaCCcz4BThZCYM4H1aj2smIKk4IJEjNIvy/KBuIgfXABcXFwxjGMsKzJWcUU4 dIWiRqWbz9Bx6+I5gZWeZIF2fYv+WDEClTCzhvKMAphu3bCrOi18hWB9etCQk4+Iv60FHg2JnKVj tg+nm83ImFUBrUdp0b5NCzZuFzfZ3bGFmElepkGM7TfkV3YefXWBbTGci5XFWovzEiFQE0ICqi9b 7JIR9cyNx+yfFYVTtGCuEGOsmX0U0YCksVj+u8dXSkjzrmD2eCCSnlzfwChC1Z/EksIFfVwTEwO8 i626pbokZeblMvPkEi9750TXBgxylq0dh1VqovsEx2SxkIInT6VMkaw770IDGRUcA3AxBC5CZnD6 T0W/NcS4wZn8zd7KwJkcEaAYMUuJ0ckC5cLwW4L762Skt/fBIKrtO+R0VMcaX4sROBAFHBRtWsmU 7sHXMlQLfQ35oeIMTxUju838/QjaXBjZ3hbCNW0Inyy0pWuIExhVKl4ySalV+I5RVcx1Q41L5MZl ZmWlDlmEMoMlV7BgPYFVgcgzCi4JiSSB0mTKkEVIvQVLkxME8ZYzlbF9wllOhJPqGoHdhjmMWZwU rxV+DDgW4xqT/byPfvwLi5aO8gyBUNyrqVsoevIrBAI7U0rMsjvEHkgYkDPJa8c1b6OWEdk616qk HGVH6VsGzo3CpESCHSdZUn8RhKs+VeucNwLBFmMTSlgguIGyyCsgcTOauV1WeYs2LyRuqIzisFyT qyAKvQgrzVUgdVHdG+OBldS6ZQoQJxPhVMuAbKQrRls4iWaj3KeZgWG4FbrdkhJrxwCKjsJvgTH5 bCh7UaHUMrpCimqWd7ro7PPMVYE7XRFErLXapqdo/LNj2BMvJBMkmk2ExlKQRHcj+5Hu3LgJQ23D GbAYQupfeLj9gWGFev2q8hd4g/sF2C+YkeApAtoqxPEZBg03AtogniWtiA/BMBo/UFALxAdTbGKk RCXkLwSvgpCyKgqmAv5i0BMUECgUBeKotcxGRISvEWCcBIXpAmVgh5/gIgKZEVQvOAeoWsD7QV6o n7CG3zTAPuU1SgqUCZDGwDkhjMUROFEWQvUKQI1CsBhgRof8Jy+4WUElCClthAsQIgYIxhziDCAZ Q8zEEfIRmL/pAsxMVgExMWAYEgRMTEBIN4I2CPiZkyIgiOkUxLQW0IGTuBZitk0M3gbzqKmo1C2G oWRle3gWizA4C0EdALYXFBIwEy0YIxGMYxkgV4jYCPiMWQqFhRmQigXghhrBimIKlwwYjeTBGglS 5omCJhAhai0gLRBcJhyP/HpAS8Q5DQNgxoH9K5L7yCBtuBREEQMbGQRF83DKZT6+5B+i7iXVrDqh QA/Y4A8fypMCaGM2GZmmPQJFDcB3jGfyGQg/jU0xEVgL6D7S4/K0A3YsziZE8/b5sP337xB8iKxo ET8X7887yBBLnjNL7ztBx6BXn6+QgbAtv0LlkpRx6y4u/orPxpZ0AsjFLUsgUinSkdqmK1D3sZgt sg9BJtxHndKaBeJtR0tYmv4EyZrrMvGQMGLG4Z5LcKEU4CkFcCOVMvs8OVBULtGuFa9IqiFabUs1 EaLaVLg2jx7Rk5A+deZM2kfnjas6k4NRTv4TjBTnBgkSJtASouuDYgNsWB25yINhNK8Bq+CFwH4O eKgT2E1IGxkwhdZvv7qHznEx4GIrRXkTQRYrzqHn2rjwnAjguFpJ9aqRef1pxnmv3I/HXsZkjsWR LrwBcTafQZ89OCqcLN8ecYcFQcKZ83ap2G634k+TO28UmiGSJYO7C6g0w+mtb4JyxGQgL2DO7zjO xz2MlfQLQXE9YYhBXd6Nh4Qik4DBDFGXLLAzHbzM0fx4jAODfcNX5wXKmvPLLTLJnhIlEHOOyH09 lmo6VzayTSNVtHjug6aT81NcGae0vPhuLrZo6ExXvxUjBtjCcLayUn1TRq6C/UYwuliNTyZB4qUL R8+hrPS0p+4mjMVbxoQpgSQsRYw4R4EGxWtAVAxIYY5fvObcuJ4l565es5+JUtKHhxJ6HeS9ZavD 6rS8XMdGoyI3NZ1+gWB0g0YD6oQSa6WYmX1FAtIahpHWpQAlQEMB0jK0hnGC9WYTb6E+gQ14TD24 ETYI1xSR73EiQ0iVi0A3yPo+Z6/QI9XOgf6b7fyqi9IxUzAqAZLcagVC+5TW7BkbiFhVRTJchs7Z JmGUEhJfexMYxkrimIaNpuOck5bOct6GnvQtwUSpjVNQURBafVd0yDbxUjuy4G5aOmbGyGCwxA5g t6XlBRWCMGG5rmu0KBW43gmTPQxxUkGAy0ZCNEBAXKSgGIl8xB6wYd2oWmlqY9k6mwd7QaTpm94R IV7lfIA8VYrAW0gv4HplEW5AXapSFvvOFYuGtXAspYiC0rKpzCwbioxVKuBcPGxYWaMM4iYjCQ5b MoNZsorF8Kl0lFxKQLcK4sklNVILsC1VS9qVEiwtuBD3s10WFo4Gi4ghNia8l3d+1iGAYAdKWShI 3HqBS18i6iQSXLTUI8p0A5Xak+V8DVSPkgKJS4EfaVBa5LZGguc7CpsWl6yMCWiXeKS3wZMsexgY TuD6k9HHwIOWe86CwtNbbpBhoYXLgme8xXMjIDJliaZEEMYx+qErE1JATaVTbM1KdIgl8+v8PQQU Y0ihYkSWD0WdOupEIyBdZLxLlgPJrG13FTJqwYYGZrZGpcuagj3Fis9oyAXf1eRFnA4VlV8jhzUI RMPc1ejulJkg4klRC0ZpzEDJ3GeEIIuodWKhb2K+0obWqBRXKhglb9dQ1LY1S295o92aS1jJsdyC 5kRbhxlk2By0InpMR6tYmSudaFApqMXiY7rJko3TstXEkYMrMLZhF/cBeWFdYtlVG1DKIKExlR5T LbssYq33qKrbgclrPgSA832GQYM1SEmvvobFhcYFH+JIFAZY/OMA9V2J4+KlAqMhQdjzbq2douhA 0gbG0hjSGNDMBjN9rx4y6TIVK603cO2RGzVeQq95eVQV2y1eeAKHQqRFyEcxJUlfJjILRkwGK8hN EDGbDD76HcNGJ1wC7TaQhHPRQD0glIgAf0PrTBPDnRTHhYjoInuGw7FFlDsWJzXUbf+LuSKcKEhw aT/7gA== --===============0330280351==--