| Bug #55222 | Mysqldump table names case bug in REFERENCES clause | ||
|---|---|---|---|
| Submitted: | 13 Jul 2010 16:52 | Modified: | 10 Jan 2011 22:40 |
| Reporter: | Artem Mikhmel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.x, 5.1.49-bzr | OS: | Any (Windows 7, Mac OS X) |
| Assigned to: | Kevin Lewis | CPU Architecture: | Any |
| Tags: | foreign key reference case, mysqldump | ||
[13 Jul 2010 18:11]
Valeriy Kravchuk
Verified just as described on Mac OS X:
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.49-debug Source distribution
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> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 2 |
+------------------------+-------+
2 rows in set (0.01 sec)
mysql> create table `Table1`(c1 int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> create table `Table2`(c1 int primary key, c2 int) engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> alter table `Table2` add constraint fk1 foreign key(c2) references `Table1`(c1);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table `Table2`\G
*************************** 1. row ***************************
Table: Table2
Create Table: CREATE TABLE `Table2` (
`c1` int(11) NOT NULL,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `fk1` (`c2`),
CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `table1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
See also bug #46941.
While this is documented to some extent (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_lower_case_tabl...):
"If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase."
I consider it only as a documented workaround, not a real solution.
[6 Oct 2010 22:00]
Kevin Lewis
When lower_case_table_names=2, the referenced tables are supposed to be stored in case sensitive, but compared in case insensitive. Innodb usually relies on the server to enforce this setting, except on Windows in which it always puts table names in lower case. (for example, see normalize_table_name() in ha_innodb.cc). But since Innodb has to parse and store referential contraints itself, it needs to check this server variable to know whether to make table names lower case. (See dict_scan_table_name() in dict0dict.cc). However, InnoDB only uses a booean TRUE of FALSE for its copy of lower_case_table_names (see srv_lower_case_table_names). This means that it interprets lower_case_table_names=2 to be lower_case_table_names=1. It does this when storing and looking up referenced table name. When lower_case_table_names=2, InnoDB aught to store the table name as supplied, but use only the lower case name for lookups into its dictionary. There are several ways to do this. I am investigating what the best way should be.
[6 Oct 2010 22:02]
Kevin Lewis
When lower_case_table_names=2, the referenced tables are supposed to be stored in case sensitive, but compared in case insensitive. Innodb usually relies on the server to enforce this setting, except on Windows in which it always puts table names in lower case. (for example, see normalize_table_name() in ha_innodb.cc). But since Innodb has to parse and store referential contraints itself, it needs to check this server variable to know whether to make table names lower case. (See dict_scan_table_name() in dict0dict.cc). However, InnoDB only uses a booean TRUE of FALSE for its copy of lower_case_table_names (see srv_lower_case_table_names). This means that it interprets lower_case_table_names=2 to be lower_case_table_names=1. It does this when storing and looking up referenced table name. When lower_case_table_names=2, InnoDB aught to store the table name as supplied, but use only the lower case name for lookups into its dictionary. There are several ways to do this. I am investigating what the best way should be.
[23 Nov 2010 21:05]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124772 3231 kevin.lewis@oracle.com 2010-11-23 Bug#55222 - InnoDB does not attempt to handle lower_case_table_names == 2 when looking up foreign table names and referenced table name. It turned that server vaiable into a boolean and ignored the possibility of it being '2'. lower_case_table_names == 2 means that it should be stored and displayed in mixed case as given, but compared internally in lower case. Normally the server deals with this since it stores table names. But InnoDB stores referential constrainsts for the server, so it need to keep track of both lower case and given names. This solution creates two table name pointers for each foreign and referenced table name. One to display the name, and one to look it up. Both pointers point to the same allocated string unless this setting is 2. So the overhead added is not too much. Two functions are created in dict0mem.c to populate the ..._lookup versions of these pointers. Both dict_mem_set_foreign_table_name_lookup() and dict_mem_set_referenced_table_name_lookup() are called 5 times each.
[24 Nov 2010 17:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124900 3231 kevin.lewis@oracle.com 2010-11-24 Bug#55222 - InnoDB does not attempt to handle lower_case_table_names == 2 when looking up foreign table names and referenced table name. It turned that server vaiable into a boolean and ignored the possibility of it being '2'. lower_case_table_names == 2 means that it should be stored and displayed in mixed case as given, but compared internally in lower case. Normally the server deals with this since it stores table names. But InnoDB stores referential constrainsts for the server, so it need to keep track of both lower case and given names. This solution creates two table name pointers for each foreign and referenced table name. One to display the name, and one to look it up. Both pointers point to the same allocated string unless this setting is 2. So the overhead added is not too much. Two functions are created in dict0mem.c to populate the ..._lookup versions of these pointers. Both dict_mem_foreign_table_name_lookup_set() and dict_mem_referenced_table_name_lookup_set() are called 5 times each.
[30 Nov 2010 18:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125545 3245 kevin.lewis@oracle.com 2010-11-30 Bug#55222 - RB://517 - Approved by Sunny InnoDB does not attempt to handle lower_case_table_names == 2 when looking up foreign table names and referenced table name. It turned that server variable into a boolean and ignored the possibility of it being '2'. The setting lower_case_table_names == 2 means that it should be stored and displayed in mixed case as given, but compared internally in lower case. Normally the server deals with this since it stores table names. But InnoDB stores referential constraints for the server, so it needs to keep track of both lower case and given names. This solution creates two table name pointers for each foreign and referenced table name. One to display the name, and one to look it up. Both pointers point to the same allocated string unless this setting is 2. So the overhead added is not too much. Two functions are created in dict0mem.c to populate the ..._lookup versions of these pointers. Both dict_mem_foreign_table_name_lookup_set() and dict_mem_referenced_table_name_lookup_set() are called 5 times each.
[30 Nov 2010 19:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125564 3345 kevin.lewis@oracle.com 2010-11-30 [merge] Merge Bug#55222 from mysql-5.5-innodb
[30 Nov 2010 20:45]
Kevin Lewis
Pushed to mysql-5.5-innodb and mysql-trunk-innodb. Approved by Sunny on RB://517
[1 Dec 2010 2:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125590 3246 kevin.lewis@oracle.com 2010-11-30 Fix compiler warning for Bug#55222 patch.
[1 Dec 2010 8:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125616
[1 Dec 2010 8:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/125615
[7 Dec 2010 23:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126272 3257 kevin.lewis@oracle.com 2010-12-07 Bug#55222 - Previous patch had a bug in unused code which was reactivated in mysql-trunk-innodb in rev revno: 3367 timestamped Tue 2010-12-07 02:25:25-0800. The crash happens only when lower_case_table_names=2, such as on MacOS, when running the new testcase innodb-system-table-view. Specifically, it crashes when any query is made against the INFORMATION_SCHEMA.INNODB_SYS_FOREIGN table.
[7 Dec 2010 23:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126273 3368 kevin.lewis@oracle.com 2010-12-07 [merge] Merge Bug#55222 bugfix to mysql-trunk-innodb
[9 Dec 2010 15:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/126448 3258 kevin.lewis@oracle.com 2010-12-09 Bug#55222 - Previous patch had a bug in unused code which was reactivated in mysql-trunk-innodb in rev revno: 3367 timestamped Tue 2010-12-07 02:25:25-0800. The crash happens only when lower_case_table_names=2, such as on MacOS, when running the new testcase innodb-system-table-view. Specifically, it crashes when any query is made against the INFORMATION_SCHEMA.INNODB_SYS_FOREIGN table. The function dict_process_sys_foreign_rec() is only used for displaying SYS_FOREIGN records so it does not need a lookup version of those names to be allocated. In this patch, those new function calls are deleted.
[22 Dec 2010 21:31]
Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101222212842-y0t3ibtd32wd9qaw) (version source revid:alexander.nozdrin@oracle.com-20101222212842-y0t3ibtd32wd9qaw) (merge vers: 5.6.1) (pib:24)
[8 Jan 2011 15:10]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:vasil.dimov@oracle.com-20110108150508-gpanhz48z8069qot) (version source revid:vasil.dimov@oracle.com-20110108150048-b1y9m8xe72hay0ch) (merge vers: 5.5.9) (pib:24)
[10 Jan 2011 22:40]
John Russell
Added to changelog: When the lowercase_system_names variable was set to 2, InnoDB could fail to restore a mysqldump dump of a table with foreign key constraints involving case-sensitive names.
[18 Jan 2011 19:25]
Lallement Thomas
I reproduce the problem with mysql 5.5.8.
Server setting:
lower_case_table_names = 2
A SIMPLE TEST YOU CAN TRY (with Mysql Workbench 5.2.31a)
-------------------------
1. Create a diagram with 2 tables and a foreign key (ex.: ROLE, USER)
2. Synchronize the model (creation of the schema in mysql). There is no problem here, in the preview database changes to be applied, I have a constraint with table name in upper case:
CONSTRAINT `FK_role`
FOREIGN KEY (`ID_ROLE` )
REFERENCES `dbtest`.`ROLE` (`ID_ROLE` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
3. Close the current model
4. Use Reverse Engineer to create the diagram from database. There is no more relationship between the tables.
See the file attached.
[18 Jan 2011 19:27]
Lallement Thomas
Show the problem (with mysql workbench)
Attachment: bug_mysql.PNG (image/png, text), 22.80 KiB.
[18 Jan 2011 20:17]
Calvin Sun
The fix is in 5.5.9.
[15 May 2019 10:40]
Dmitry Lenev
Posted by developer: Bug #11744975 / #6555 "DUMPING A TABLE WITH A FOREIGN KEY IGNORES --LOWER_CASE_TABLE_NAMES=0" has been marked as duplicate of this bug.
Description: There is a mysql server v5.1.32-community some config: version | 5.1.32-community lower_case_table_names | 2 Suppose, I have following tables in my DB: CREATE TABLE `Webmasters` ( `webmasterId` int(11) NOT NULL AUTO_INCREMENT, `email` char(200) COLLATE utf8_unicode_ci NOT NULL, `password` char(32) COLLATE utf8_unicode_ci NOT NULL, `active` tinyint(1) DEFAULT NULL, PRIMARY KEY (`webmasterId`) ) ENGINE=InnoDB; and CREATE TABLE `Hits` ( `hitId` int(11) NOT NULL AUTO_INCREMENT, `webmasterId` int(11) NOT NULL, `sessionId` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`hitId`) ) ENGINE=InnoDB; Lets add one Foreign Key: alter table Hits add constraint FK_Reference_1 foreign key (webmasterId) references Webmasters (webmasterId) on delete restrict on update restrict; Now, we have 2 tables bound with FK constraint. Lets dump them with mysqldump: // some irrelevant output skipped mysqldump -uroot -p --opt test Hits > test.sql -- MySQL dump 10.13 Distrib 5.1.32, for Win32 (ia32) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.1.32-community DROP TABLE IF EXISTS `Hits`; CREATE TABLE `Hits` ( `hitId` int(11) NOT NULL AUTO_INCREMENT, `webmasterId` int(11) NOT NULL, `sessionId` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`hitId`), KEY `FK_Reference_1` (`webmasterId`), CONSTRAINT `FK_Reference_1` FOREIGN KEY (`webmasterId`) REFERENCES `webmasters` (`webmasterId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; There is a problem with > CONSTRAINT `FK_Reference_1` FOREIGN KEY (`webmasterId`) REFERENCES `webmasters` (`webmasterId`) As you see, table name `webmasters` is in lower case. The same output is seen after 'Show create table Hits;' sql is executed: CREATE TABLE `Hits` ( `hitId` int(11) NOT NULL AUTO_INCREMENT, `webmasterId` int(11) NOT NULL, `sessionId` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`hitId`), KEY `FK_Reference_1` (`webmasterId`), CONSTRAINT `FK_Reference_1` FOREIGN KEY (`webmasterId`) REFERENCES `webmasters` (`webmasterId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 It results in a problem, when this dump is restored at any mysql server with case sensitive mysql-server settings, like lower_case_table_names = 0 and any record about to insert into table Hits. mysql considers there is no table named `webmasters` (lower case) and issues an error, like 1452: Cannot add or update a child row: a foreign key constraint fails... How to repeat: Server settings must be lower_case_table_names = 2 Execute following SQL: CREATE TABLE `Webmasters` ( `webmasterId` int(11) NOT NULL AUTO_INCREMENT, `email` char(200) COLLATE utf8_unicode_ci NOT NULL, `password` char(32) COLLATE utf8_unicode_ci NOT NULL, `active` tinyint(1) DEFAULT NULL, PRIMARY KEY (`webmasterId`) ) ENGINE=InnoDB; CREATE TABLE `Hits` ( `hitId` int(11) NOT NULL AUTO_INCREMENT, `webmasterId` int(11) NOT NULL, `sessionId` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`hitId`) ) ENGINE=InnoDB; alter table Hits add constraint FK_Reference_1 foreign key (webmasterId) references Webmasters (webmasterId) on delete restrict on update restrict; Then SHOW CREATE TABLE Hits; You will see result: CREATE TABLE `Hits` ( `hitId` int(11) NOT NULL AUTO_INCREMENT, `webmasterId` int(11) NOT NULL, `sessionId` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`hitId`), KEY `FK_Reference_1` (`webmasterId`), CONSTRAINT `FK_Reference_1` FOREIGN KEY (`webmasterId`) REFERENCES `webmasters` (`webmasterId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 with lower case table name `webmasters`. execute mysqldump -u -p database Hits DROP TABLE IF EXISTS `Hits`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `Hits` ( `hitId` int(11) NOT NULL AUTO_INCREMENT, `webmasterId` int(11) NOT NULL, `sessionId` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`hitId`), KEY `FK_Reference_1` (`webmasterId`), CONSTRAINT `FK_Reference_1` FOREIGN KEY (`webmasterId`) REFERENCES `webmasters` (`webmasterId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Suggested fix: Output table name in FK reference according to lower_case_table_names settings value. In case of lower_case_table_names = 2 on Windows, table name case should be exactly as it was specified when creating foreign key.