Bug #61617 Saving table after adding Foreign Key results in error
Submitted: 23 Jun 2011 19:55 Modified: 13 Jul 2011 14:10
Reporter: Alex Beer Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.3.7 and 6.4.1 OS:Microsoft Windows (Windows 7 (x64))
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any
Tags: foreign key, MySQL Connecter .NET, references, visual studio 2010

[23 Jun 2011 19:55] Alex Beer
Description:
I created two tables within Visual Studio 2010 (SP 1) using the Table-Designer.
To be able to use Foreign Keys, the tables are set to "InnoDB" Storage Engine.

To keep it simple, TableA only contains 2 columns named "ID", "Foreign_ID" and TableB table only contains 1 column named "ID". Both "ID" fields are set as Primary Keys in both tables.

Then I added a Foreign Key via Table-Deisgner --> Relationships...
from TableA.Foreign_ID to TableB.ID. No issue so far.

Now, when trying to save the table, the following error occurs:
"Unable to save object with error: You have an error in your SQL syntax; check the manual that corresponds with your MySQL server version for the right syntax to use near ') REFERENCES 'TableA '()' at Line 1"

After checking the Foreign Key in the Relationships tab again, the two drop down lists for selecting the columns to map are empty.

I tried a work around by adding the Foreign Key relation via phpMyAdmin.
But when I then tried to open the table containing the Forein Key information in Visaul Studio an "Object reference not set to an instance" exception.

Same error occurs with version 6.3.7 of MySQL Connector .NET

Infos:
MySQL Server Version: 5.1.43-nmm4-log
Protocol version: 10
MySQL-Encoding: UTF-8 Unicode (utf8)
MySQL-Client-Version: 5.1.43
phpMyAdmin Version:  3.3.9

How to repeat:
- Create two tables TableA, TableB within Visual Studio 2010 using Table-Designer
- Add two columns ID, Foreign_ID to TableA, both of type int
- Mark TableA.ID as primary key
- Add one column ID to TableB of type int and mark it as primary key.
- Save both tables
- Select TableA and choose Table-Designer --> Relations... from VS menu
- Add a Forein Key from TableA.Foreign_ID to TableB.ID
- Close the tab.
- Try to save TableA --> error
- Open Table-Designer -> Relations... --> column mapping is empty

- Create the Foreign Key relation via any other tool (e.g. phpMyAdmin)
- Try to open TableA within Visual Studio -> error

Suggested fix:
There seems to be a problem with the Foreign Key columns drop down list on the Table-Designer --> Relations... tab within VS 2010 SP 1
[23 Jun 2011 20:01] Alex Beer
Create FK

Attachment: Create_FK.PNG (image/png, text), 34.15 KiB.

[23 Jun 2011 20:01] Alex Beer
Saving the table results in error

Attachment: Saving_Error.PNG (image/png, text), 53.98 KiB.

[23 Jun 2011 20:02] Alex Beer
Columns drop down lists empty on Relations tab

Attachment: Foreign_Columns_Empry.PNG (image/png, text), 23.66 KiB.

[24 Jun 2011 12:01] Alex Beer
- changed Version
[13 Jul 2011 14:10] Fernando Gonzalez.Sanchez
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed on 6.4.4, 6.3.9, 6.2.6