Bug #73193 Related MySQL tables and views are detected only in a single direction

• Submitted: 4 Jul 2014 0:43
• Modified: 31 Jul 2014 23:42
• Reporter: Javier Treviño
• Status: Closed
• Category: MySQL for Windows: MySQL for Excel
• Severity: S3 (Non-critical)
• Version: 1.3.0
• OS: Any
• CPU Architecture: Any

[4 Jul 2014 0:43] Javier Treviño

Description:
When importing multiple DB objects, related tables and views are only detected when the originally selected DB object (shown at the left side of the dialog) has a MySQL Foreign Key explicitly declared, but are not detected if the selected DB object is actually the foreign object.  Meaning only a 1 direction relationship is detected by MySQL for Excel.

For Example if we have a relationship like this:
City <-n----1-> Country

And the foreign key constraint was defined on the City table where the related foreign table is Country, MySQL for Excel detects Country as related when originally selecting City; but it does not detect any related tables when originally selecting Country.  It should detect City as related to Country in the latter scenario.

How to repeat:
1. Open MySQL for Excel
2. Select a schema that contains tables or views related by foreign key
constraints (e.g. sakila)
3. Select the City table and right-click on it
4. From the pop-up menu select Import Selected and Related DB Objects
5. Note City appears in the left list, and Country appears in the right one.
6. Click Cancel.
7. Select the Country table and right-click on it
8. From the pop-up menu select Import Selected and Related DB Objects
5. Note no Country appears in the left list, nothing appears in the right one. City should have appeared on the right list.

[11 Jul 2014 2:45] Javier Treviño

Posted by developer:
 
Rewrote the code that handles tables relationships in the Import Multiple dialog, relationships are detected for all tables imported to Excel (both the ones in the original selection from the DB objects selection panel and their related ones flagged by the user to be imported).  The relationships are now detected within the DbTable class in 2 directions:
"Normal" where the foreign keys are declared on the DbTable and other DbTable objects represent the foreign MySQL tables.
"Reverse" where the foreign keys are declared on other DbTables (or other foreign MySQL tables) and they point to the current DbObject.

Now all relationships are created correctly in the Excel data model between the imported MySQL tables.

[31 Jul 2014 23:42] Philip Olson

Fixed as of the upcoming MySQL for Excel 1.3.2 release, and here's the changelog entry:

When importing multiple database objects, related tables and views were
only detected when the originally selected database object (shown at the
left side of the dialog) had a MySQL Foreign Key explicitly declared, but
they were not detected if the selected database object was actually the
foreign object. In other words, only a 1-directional relationship was
detected.

Thank you for the bug report.