| Bug #1939 | Wrong case sensitivity for table names when in "ANSI" mode | ||
|---|---|---|---|
| Submitted: | 24 Nov 2003 16:08 | Modified: | 19 May 2009 22:36 |
| Reporter: | Troels Arvin | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[26 Nov 2003 13:35]
Sergei Golubchik
I would not classiy it as critical either as in most applications table is often referred to by the same name, I mean case-sensitively "same". Anyway, this breaks the standard, agree. You can use --lower-table-names switch to make table names case insenstive. But it will also cause tables name to be creates in lowercase only. Thus old tables with uppercase characters in names will be unaccessible :( This is the reason why we didn't turn lower-table-names automatically in the ANSI mode. Unfortunately we can fix it the safe way in the version 5.0 only :(
[30 Mar 2005 22:31]
Troels Arvin
I never realized that the bug also existed in non-ansi mode; that changes things a bit (the bug is consistent). I have lowered severity, as the bug is not triggered by the ansi-mode, as I previously thought. By the way: it doesn't look like v. 5.0 will fix the bug :-(
[3 Nov 2010 10:29]
Vlad Safronov
test. ignore.
[27 May 2013 12:03]
Justin Jacob
add to my.cnf->[mysqld] lower_case_table_names=1 it will solve the problem
Description: When running MySQL in "ANSI" mode, MySQL's handling of table names (possibly also other identifiers) becomes very non-ISO/ANSI: create table testtab(id int not null); mysql> create table testtab(id int not null); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO TESTTAB VALUES(4); ERROR 1146: Table 'troels.TESTTAB' doesn't exist According to [1] and [2], table names are to be handled case insensitively (implictly converted to upper case whenever used) as long as they are not delimited. This error potentially breaks heaps of fine SQL and is very annoying. In my opinion, the ANSI-mode becomes impossible to use in practice because of this. That's why I classify it as Critical. But as very few people use the feature, I gave it "Low" as Priority. References: 1: Melton J, Simon A R: "SQL 1999 - Understanding Relational Language Components" ISBN 1-55860-456-1 URL: http://books.elsevier.com/uk/mk/uk/subindex.asp?isbn=1558604561 2: http://dbazine.com/gulutzan5.html How to repeat: See description.