MySQL Bugs: #47981: Reverse Engineer DB does not import foreign keys

Bug #47981	Reverse Engineer DB does not import foreign keys
Submitted:	11 Oct 2009 23:40	Modified:	13 Oct 2009 14:57
Reporter:	Wade Little	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.4	OS:	MacOS (10.6.1)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	case sensitive, foreign keys

Description:
Using the workbench I do a reverse engineer on my DB.  After the process completes it generates four warnings.  The big problem is that it does not import my foreign keys because the table name is Sites but the workbench shows the FK as sites.

Here is the warnings from workbench:
---
WARNING: Table `Dashboard`.`products` not found. Stub was created.
WARNING: Table `Dashboard`.`ECOtoProducts` : Foreign key `ProductFK` : Referred column `Dashboard`.`products`.`idProducts` not found. Stub was created.
WARNING: Table `Dashboard`.`sites` not found. Stub was created.
WARNING: Table `Dashboard`.`ECOtoSites` : Foreign key `Site1FK` : Referred column `Dashboard`.`sites`.`idSites` not found. Stub was created.
-------

How to repeat:
Do reverse engineer with the following setup:

Here are my tables:
--------
mysql> show tables;
+---------------------+
| Tables_in_dashboard |
+---------------------+
| PartstoSites        |
| Sites               |
+---------------------+
---
Here is the foreign key working as expected;

mysql> delete from Sites where idSites=46;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dashboard`.`partstosites`, CONSTRAINT `SiteFK` FOREIGN KEY (`idSites`) REFERENCES `sites` (`idSites`))

Suggested fix:
Fix the case sensitive problem with workbench on reverse engineer.

Just an FYI:

I did not list all the tables in my DB so the warnings that workbench shows includes warnings for other tables but the with the exact same problem.

It must be the table validator that removes invalid foreign keys. 5.2.4 was still missing a patch addressing this problem - the patch was merged from 5.1 branch after release.
But to be sure it's not a separate problem, it's better to have an SQL script for reverse-engineering that allows to reproduce the misbehavior.

mysqldump of database reversetest

Attachment: reversetest.sql (application/octet-stream, text), 2.87 KiB.

Create database reversetest and then use the attached .sql file to step up the database.  Once you have done this use Workbench on OSX and perform a reverse-engineer and you will see the warnings when it is complete and notice the foreign keys are not imported.

There are identifiers in the script that differ only in casing. `Site1FK` foreign key references `sites` table, while the latter is referred to as `Sites` in `create table` statement.
To resolve that you can set `SqlIdentifiersCS` option to 0 if you want to treat identifiers case-insensitively (ATM the only way to do it is to manually edit wb_options.xml located in MySQL subdir of your home directory. You have to close Workbench application before editing it.) or you may alter your original script to remove varieties in casing of identifiers - it's a good practice to always use identifiers as if they were treated case sensitively.

There is still a problem here...I agree 100% that when creating the table all items should be created with case sensitive.

I did the create table statement with it case sensitive but as you will see below I lose the case sensitive right after REFERENCES in the FOREIGN KEY.  

So the problem appears to possibly not be with Workbench but rather with MySQL itself?  I performed this same test on my Solaris box and it did not lose the case sensitive for 'sites' so the problem seems limited to OSX.

So should this bug be assigned to DDL?

------------
mysql> CREATE TABLE `ECOtoSites` (
    ->   `idECOtoSites` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `idECO` int(11) NOT NULL,
    ->   `idSites` int(11) NOT NULL,
    ->   PRIMARY KEY (`idECOtoSites`),
    ->   UNIQUE KEY `ECOSites` (`idECO`,`idSites`),
    ->   KEY `Sites` (`idSites`),
    ->   CONSTRAINT `Site1FK` FOREIGN KEY (`idSites`) REFERENCES `Sites` (`idSites`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table ECOtoSites;+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ECOtoSites | CREATE TABLE `ECOtoSites` (
  `idECOtoSites` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `idECO` int(11) NOT NULL,
  `idSites` int(11) NOT NULL,
  PRIMARY KEY (`idECOtoSites`),
  UNIQUE KEY `ECOSites` (`idECO`,`idSites`),
  KEY `Sites` (`idSites`),
  CONSTRAINT `Site1FK` FOREIGN KEY (`idSites`) REFERENCES `sites` (`idSites`)
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

mysql> show tables;
+-----------------------+
| Tables_in_reversetest |
+-----------------------+
| ECOtoSites            |
| Sites                 |
+-----------------------+

There are differences in case sensitiveness of identifiers for all 3 major platforms. Please refer to documentation regarding case sensitivity in identifiers on different platforms: http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

Briefly, name comparisons are not case sensitive in OS X, but Workbench by default treats identifiers with regard to letter case. Note, you can change either value of `lower_case_table_names` variable of DBMS or `SqlIdentifiersCS` parameter (see my previous comment) in Workbench to resolve that contradiction.

Potentially Workbench could check for `lower_case_table_names` variable value before starting reverse-engineering and adopt `SqlIdentifiersCS` as needed, but that migh lead to undesirable mix of casing in the model. For example several schemata were reverse-engineered from different DBMS that use different value for `lower_case_table_names`. So user has to explicitly set `SqlIdentifiersCS` parameter in Workbench and apply same rule set every time.