MySQL table names come out as case insensitive when reflecting foreign keys

[Former user]

attached is an example of the issue against the trunk.

it autoloads two tables from a mysql database, and then maps them to some domain classes. the mapping itself seems to have issues with resolving the foriegnkeys and tosses an error about not being able to infer the relations.

sqlalchemy.exceptions.ArgumentError: Error determining primary and/or secondary join for relationship 'person' between mappers 'Mapper|Teacher|Teacher' and 'Mapper|Person|Person'. You should specify the 'primaryjoin' (and 'secondaryjoin', if there is an association table present) keyword arguments to the relation() function (or for backrefs, by specifying the backref using the backref() function with keyword arguments) to explicitly specify the join conditions. Nested error is "Cant find any foreign key relationships between 'Teacher' and 'Person'"

the underlying issue seems to be that the name picked up by the autoload on the fk is always lowercase, and that the fk resolution, will autoload a the table with the lower case name, and then the corresponding_column check fails.. because its the dictionary keys are column objects and their hash value is based on the object instance, and there fore won't match. but the underlying issue is the metadata should be able to retrieve the tables by normalizing the name during fk resolution.

the issue disappears if the autoload table object is given a lower case name.

[Former user]

ddl for example

[Former user]

A small program demonstrating the issue

[Mike Bayer]

i need to go over this more closely to further understand it, but if youd like to jump in on #155, which is all about case folding/quoting and quite possibly encapsulates this problem, that would be helpful.

[Former user]

Kapil, what version of mysql are you running this test against?

[Mike Bayer]

Ok, ive looked at this a little bit. Someone needs to tell me exactly what they think the solution should be, because if MySQL identifier names are case sensitive, then this is a MySQL bug that I dont exactly see any way around.

Heres why.

Create the two tables, exactly as the DDL attached shows. Note that the foreign key from Teacher to Person uses the correct case.

now say do a SHOW CREATE TABLE:

mysql> show create table Teacher;

Teacher | CREATE TABLE `Teacher` (
  `teacher_id` int(11) NOT NULL auto_increment,
  `person_id` int(11) NOT NULL,
  `total_salary` decimal(10,0) default NULL,
  PRIMARY KEY  (`teacher_id`),
  KEY `Teacher_FKIndex1` (`person_id`),
  CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL blows away the case on the table name of "person". So theres not much we can do here, if we are to remain "case sensitive" on the SA side (which we generally are), since MySQL itself is not relaying the proper casing to us.

Also, I just tried creating a second table called "teacher" and it says "Table 'teacher' already exists". So it would appear MySQL, at least this version 5 that im running, is case insensitive.

This would imply that the solution here, if youre working with a case insensitive MySQL, is to use lower case table names as you did...the only other way would be to make SA's behavior support "case insensitive" behavior with regards to MetaData, i.e. being able to look up tables without casing.

can we please identify how MySQL is either case sensitive or insensitive (since aaron is giving me conflicting results on his database), and the best we can do is add a "case_sensitive=False" flag to MySQL dialect.

[Former user]

On my server the following happens.

aaron@cyril:~$ mysql --version
mysql  Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i486) using readline 5.1

aaron@cyril:~$ mysql -e "show create table teacher;" aaron
ERROR 1146 (42S02) at line 1: Table 'aaron.teacher' doesn't exist

aaron@cyril:~$ mysql -e "show create table Teacher;" aaron
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                      |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Teacher | CREATE TABLE `Teacher` (
  `teacher_id` int(11) NOT NULL auto_increment,
  `person_id` int(11) NOT NULL default '0',
  `total_salary` decimal(10,0) default NULL,
  PRIMARY KEY  (`teacher_id`),
  KEY `Teacher_FKIndex1` (`person_id`),
  CONSTRAINT `Teacher_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `Person` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

[Former user]

http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in most varieties of Unix, and not case sensitive in Windows.

[Former user]

cf. table of values/meanings for lower_case_table_names at http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html

aaron@cyril:~$ mysql -e "show variables like 'lower_case_table_names';" aaron
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

[Mike Bayer]

well i put something of a hack to fix this. the side effect is that while the test program now works, it puts each Table into the MetaData twice, once under the case sensitive name and another under the case insensitive name, when it detects the case insensitive condition via the system variable. i dont see any way of doing this without some effect on the MetaData; either a "case_insensitive" flag would be set on it, or the mixed case version of the table removed, or this. when using autoload i dont see any way to hide the case sensitivity from the metadata side of things.

the fix is in changeset:1838.

marking this as "fixed" to attract attention. feel free to suggest further strategies.