Foreign key constraints for InnoDB tables with upper-case letters disabled

[smisch]

Hello,

when a table name contains upper-case letters PMA doesn't show the form to create foreign key constraints under Stucture > Relation View even if Engine = InnoDB. It works fine when using lower-case table names.

Example were foreign key constraints can be created (form is shown):

CREATE TABLE `fkworks` ( `test` INT NOT NULL ) ENGINE = InnoDB;

Example were foreign key constraints cannot be created (form is hidden):

CREATE TABLE `FKfails` ( `test` INT NOT NULL ) ENGINE = InnoDB;

The reason is that PMA_Util::isForeignKeySupported only shows the form if $engine == 'INNODB', but since the table name contains upper-case letters, $engine is empty.

Table schematics are stored using a mb_strtolowered table name in PMA_DatabaseInterface::$_table_cache. This variable does contain the correct engine for both test cases.

To get the engine, PMA_Table::getStatusInfo gets invoked, but uses the original table name for the cache lookup, which fails, since the table schematics are stored lower-cased.

A quick fix that worked for me was to add mb_strtolower in PMA_Table::getStatusInfo:

$table = mb_strtolower($this->_name);

This shouldn't cause problems since on the system that has this problem you can't create tables which only differ in case i.e. Test and test cannot both exist in the same database.

Possibly related to http://sourceforge.net/p/phpmyadmin/bugs/2036/
Related stackoverflow question: http://stackoverflow.com/q/25616281/1220835

System: confirmed on OS X 10.9 and 10.10.
Apache: 2.4.10
PHP: 5.5.21
MySQL: 5.6.22
PMA: confirmed on 4.3.9 and 4.5.0-beta1 (most current)

lower_case_file_system = ON (default)
lower_case_table_names = 2 (default for OS X meaning tables get stored using their original names, but are compared case-insensitively)

[madhuracj]

Could you please provide the file and line number of what you refer by "Table schematics are stored using a mb_strtolowered table name in PMA_DatabaseInterface::$_table_cache"?

[smisch]

Sorry, I just inferred the mb_strotolower from the data that was present in PMA_DatabaseInterface::$_table_cache. The culprit here is actually the information_schema.TABLES table. The table contains the table names in lower-cased form regardless of the value of lower_case_table_names. Related Bugreport. In contrast if you query SHOW TABLES IN database you'll get the correct table names with upper-case letters.

PMA queries information_schema.TABLES to get the desired data for PMA_DatabaseInterface::$_table_cache in PMA_DatabaseInterface::getTablesFull. This results in a lower-cased table name being stored in PMA_DatabaseInterface::$_table_cache (i.e. "db.test").

To get the Engine for tbl_relation.php PMA_Table::getStatusInfo is called which then calls PMA_DatabaseInterface::getCachedTableContent in PMA_Table::getStatusInfo Line 324 using the non-lower-cased table name (i.e. "db.Test"). This results in the described cache miss.

[smisch]

Clarification: This issue does affect my OS X systems where lower_case_table_names = 2. It doesn't affect my AWS Linux where lower_case_table_names = 0. On AWS Linux information_schema.TABLES.TABLE_NAME provides the correct table names.

One curiosity I found with lower_case_table_names = 2 is that if you query information_schema.TABLES without any condition on TABLE_NAME you'll get the correct table names. If you then simply add WHERE TABLE_NAME = "Test" to your query, all information_schema.TABLES.TABLE_NAME get converted to lower-case. This seems to be the handling of lower_case_table_names = 2. To get around this strange behaviour you can use LIKE instead of = for comparison: WHERE TABLE_NAME LIKE "Test". This way I got the correct table name.

Comparisons using LIKE are case-insensitive so you might get more than one result on systems where lower_case_table_names = 0 when querying information_schema.TABLES with LIKE instead of =.

[smisch]

I think the best way to fix the issue is to not rely on information_schema.TABLES providing the correct TABLE_NAME and instead use the argument $table from PMA_DatabaseInterface::getTablesFull as a key to store data in PMA_DatabaseInterface::$_table_cache.

You will have to include a special case for lower_case_table_names = 2, possibly also for lower_case_table_names = 1 when you compare the result from information_schema.TABLES with $table to find the correct row, though. I don't see a way around that.

Edit: Since on systems with lower_case_table_names = 2 you can't have two tables which only differ by case (i.e. "Test" and "test") you can safely use WHERE TABLE_NAME LIKE "Test" when querying information_schema.TABLES. You'll only ever get one row. For systems with lower_case_table_names = 0 you can use WHERE TABLE_NAME = "Test" just like before without encountering any problems.

[madhuracj]

Thank you for debugging and your detailed report. I'd also like to know the value for configuration 'DisableIS'. I suppose it is false, the default value.
In that case the issue is with information_schema returning the correct table name and lower cased table name under different conditions.

[smisch]

Yes, the problem seems to be information_schema returning incorrect table names when lower_case_table_names = 2.

I did not change DisableIS on any of the systems. Setting it to true in my OS X system resolved the issue. I was able to assign foreign keys for tables containing upper-case letters.