Bug #6555 | Dumping a table with a FOREIGN KEY ignores --lower_case_table_names=0 | ||
---|---|---|---|
Submitted: | 10 Nov 2004 17:43 | Modified: | 15 May 2019 10:37 |
Reporter: | Federico Chiesa | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 6.1,4.021 | OS: | Windows (Windows (XP)) |
Assigned to: | CPU Architecture: | Any |
[10 Nov 2004 17:43]
Federico Chiesa
[11 Nov 2004 20:49]
Heikki Tuuri
Hi! To fix this, we need a MySQL function: get_table_name_in_proper_case_on_windows(char* lower_case_table_name) Internally, InnoDB keeps all table names in lower case on Windows. In SHOW CREATE TABLE, we could look at the name of the .frm file to get the real case of the table name. -- Workaround for the user: set lower_case_table_names=1 in my.cnf also in the (Unix) server where you are importing. Then the case of table names is ignored also on Unix. Regards, Heikki
[12 Nov 2004 9:12]
Heikki Tuuri
Hi! Actually, we need a function get_database_and_table_name_for_a_lower_case_name() because we ned to know also the case of the database name. On Windows, InnoDB puts all database names and table names to lower case. Regards, Heikki
[6 Sep 2006 9:55]
Heikki Tuuri
Changing the catogory to 'MySQL Server' because to fix this bug we need MySQL to provide a way to retrieve the correct case of a table name in an .frm file.
[17 Oct 2006 13:55]
Kristofer Pettersson
Random notes while investigating: While parsing the CREATE TABLE command we call the function dict_scan_table_name to extract a table name from our SQL statement. If this function finds a pattern that matches our expectations we always get a lower case result (table name): #ifndef __WIN__ if (srv_lower_case_table_names) { #endif /* !__WIN__ */ /* The table name is always put to lower case on Windows. */ innobase_casedn_str(ref); #ifndef __WIN__ } #endif /* !__WIN__ */ Note: Without the innobase_casedn_str(ref) the function would return the correct representation on the windows platform, ie motherTable instead of mothertable. Why can't we use that value?
[31 Jan 2007 16:26]
Heikki Tuuri
Hi! Inside InnoDB, table names are always case sensitive, whereas in MySQL on Windows treats them like file names, and they are case insensitive. The problem originates from 2001 when MySQL did not give the table name in a consistent case on Windows. The case that MySQL passed to InnoDB depended on the case that a database client had used to access the table after a mysqld startup. I solved the problem by always lower-casing InnoDB's table names on Windows. A question is, if we could remove the lower-casing code of InnoDB on Windows? Hmm... we could do that if: 1) we knew that the modern MySQL-5.1 always passes the table name case consistently to storage engines; and 2) we would add to InnoDB code, that would look for a table name from InnoDB's data dictionary both in the original case AND in lower case (so that we would find also old tables where InnoDB lower-cased the table name). The above would solve this bug report for newly created tables. For older tables we would still need the function get_table_name_in_original_case(). Actually, we could probably write the function get_table_name_in_original_case() within InnoDB. It is not that hard to find the .frm file of a table from the database directory. No need for MySQL to implement that function. But we need to check that SHOW CREATE TABLE does not retrieve table names from the 5.1 information schema tables, where the case of the table name can also be wrong. I am reclassifying this to an 'InnoDB bug'. Regards, Heikki
[16 Apr 2007 11:43]
Heikki Tuuri
Reclassifying as a MySQL bug, since storage of foreign key clauses in the .frm file will solve this bug in the long run.
[20 Jul 2008 9:06]
Brian Birtle
Is there a solution to this or is there an ETA on the solution? It is more than four years old! We develop on Windows and then use Linux test and production servers. This is causing havoc in our process to synchronize all our various development, test, and production databases. Is there at least a nice work around?
[29 Sep 2008 8:43]
Leif Inge Sandberg
Hope this will be fixed soon (6.1 sounds far away?). The bug is still present in MySQL 6.0.6. A "side effect" is that it seems to cause NetBeans to fail importing tables into Java entity classes (http://www.netbeans.org/issues/show_bug.cgi?id=146940). The issue isn't critical or very bad, but it's a bit annoying and wastes some time on manual fixing of the entity class import. I don't see why the database shouldn't respect my choice of letter case, i.e. I would have defaulted the lower_case_table_names option to zero. If I name a table JobType in my create table script, it's because I actually want it to be named JobType. If anyone refers to the table as jobtype, it's a user/programmer mistake and should produce some "Table not found" error.
[5 Jan 2010 14:48]
Yousuf Ahmed
Hi, We are using MySql version 5.1.38 on Windows. I see this problem exists and reported earlier. Appreciating the efforts to fix the problem, i would like to know the status of the fix. Is it fixed in the newer versions of MySql. Please reply as soon as possible. Thanks, Yousuf Ahmed
[31 Jan 2012 9:41]
Jamie Garton
This bug has been open for over 7 years now, come on MySQL it needs to be fixed! I have no control over how 3rd party databases are created so I am stuck with camel case table names. This needs to be fixed.
[11 Nov 2017 18:54]
Federico Razzoli
Seems to be fixed in (or before) 8.0.3.
[15 May 2019 10:36]
Dmitry Lenev
Posted by developer: Hello! First of all I need to point out that using --lower-case-table-names=0 on case-insensitive file system (e.g. on Windows) is not (and was not) supported. Here is what 5.1 manual says about this: " You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or OS X). It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption.". Yes, the above text doesn't mention problems with foreign keys specifically, but its main point that this is an unsupported combination and that problems can occurr if one tries to use it. It can't cover all possible problems that will follow. Recent 8.0 versions will simply refuse to start in such a case. E.g. in addition to the above 8.0 manual page describing lower_case_table_names variable says (see section "5.1.8 Server System Variables"): "An error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.". Now if you are on Windows (or other system with case-insensitive filesystem) and want to preserve case of table and database names you can use --lower-case-table-name=2 option, which is supported. However, in 5.1 --lower-case-table-names=2 mode on Windows is affected by the problem similar to the above. The name of referenced table in dumps doesn't preserve its original case. This problem was reported as bug #11762610 / #55222 "MYSQLDUMP TABLE NAMES CASE BUG IN REFERENCES CLAUSE", and was fixed in MySQL 5.5.9. Taking into account the above I am closing this bug report as duplicate of bug #11762610 / #55222.