Bug #50326 Reverse Engineering missing table relationships

• Submitted: 14 Jan 2010 11:47
• Modified: 15 Jan 2010 12:14
• Reporter: tom corcoran
• Status: Not a Bug
• Category: MySQL Workbench
• Severity: S3 (Non-critical)
• Version: 5.2.11 OSS Beta
• OS: Windows (XP Pro SP2)
• CPU Architecture: Any
• Tags: reverse engineer

[14 Jan 2010 11:47] tom corcoran

Description:
My MySql install is 5.1.32 Community. I downloaded WB 5.2.11 OSS Beta and did a Create EER Model from Existing Databases. I ended up with an EER Diagram for all the 86 tables but with no table relationships even though foreign keys exist.

Background: 

Here's and example picture of the indexes with no foreign key relationships: http://i49.tinypic.com/16h4sc5.jpg

Mike Lischke helped on the forum and suggested I open a bug here
Ref: http://forums.mysql.com/read.php?153,298198,298198#msg-298198

How to repeat:
The MySQl database was created from a DTS from Sqol Server. 

I generated the sql script for the indexes (primary and foreign) separately and converted it to mysql compatible(lost some indexes due to BLOB/TEXT column 'x' used in key specification without a key length error). 

I ran this script on the MySql database prior to reverse enginnering the EER Model from Existing Databases

[14 Jan 2010 13:08] Johannes Taxacher

Hi Tom,

i don't see any Foreign Key constraints defined in the sql dump you supplied. if they are not defined in the database model, workbench cannot reverse engineer them. was this the model you used to rev.eng.?

[14 Jan 2010 14:18] tom corcoran

Indexes which gave problems

Attachment: index inno issues.txt (text/plain), 3.74 KiB.

[14 Jan 2010 14:21] tom corcoran

I ran the attached index script on the database once it had been converted to innodb. In also include the indexes which did not run as they were not mysql compatible - the script was generated from a sql server database. 

Are the indexes at the end not foreign keys?

[14 Jan 2010 15:06] Valeriy Kravchuk

Indexes are required by InnoDB on foreign key columns (and are created automatically if not exist), but foreign keys should be explicitly declared in CREATE TABLE or ALTER TABLE. Read http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for the details.

[14 Jan 2010 15:20] tom corcoran

Ok. I am dealing with a historic Jira v3.4.1 database. I would have thought it would have come with foreign keys but apparently not...

[15 Jan 2010 7:50] Valeriy Kravchuk

So, this problem was not a result of any bug in MySQL Workbench.

[15 Jan 2010 12:14] tom corcoran

Ok, thanks for clarifying this.