MySQL Bugs: #37591: Connector 5.2.2 Foreign Key Constraints cause table adpater error in .NET

Bug #37591	Connector 5.2.2 Foreign Key Constraints cause table adpater error in .NET
Submitted:	23 Jun 2008 17:29	Modified:	15 Aug 2008 14:59
Reporter:	Matthew Hill	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ErrorCode:-1 creating tableadapter, Source: Microsoft.VSDesigner

Description:
Trying to create table adpaters in datasets in Visual Studio .net gives and error Source: Microsoft.VSDesigner, ErrorCode:-1 whenever you try to create a tableadapter to a table with a foreign key constraint inthe same dataset.

Example.

tbladdress

tblstate

FK_tbladdress_tblstate from field stateid in tbladdress to stateid primary key field in tblstate.

tbladdress tableadapter creates fine when attempting to add tblstate tableadapter to the same dataset the error occurs..attempting to create the same tableadapter in a seperate dataset doesnt create the error.

My assumption is that the dataset tableadpater autocode genration is unable to create the FK constraints in the dataset uysing the 5.2.2 connector.

How to repeat:
Using the infomration above you can easily recreate the issue.

create any two tables with a foreign key constraint in table A pointing at tbl B's primary key field.

Then in Visual Studio 2005 or 2008 using the 5.2.2 my sql connector create a c# project. Probably best to create a Common Library project type. in that project add a Dataset and in the satset first add a table adapter to Table A and add a tablea dapater to Table B.

It will creat ethe error every time.

Suggested fix:
I wouldnt know the fix..currently the only 2 possible workarounds are

1. only have one table adpater per dataset (hardly and efficient use of Datasets.

2. remove the FK COnstraints when you add the tables then readd teh FK's when after the table adpaters are created..problem with the second methodlolgy is that any shema changes in order to update to the dataset you have to remove the FK and readd it again after updating the tableadpater.

I also ran into this problem with Visual Studio 2008.  The only way I can figure out how to create a DataSet from the MySql database is to select "Data" in the menu bar, select "Add New Data Source", select "Database", select the data connection to your MySql database, save your connection string, and finally select the tables for your DataSet.  When adding the DataSet, VS2008 will give an error that says "Failed to add relation(s).  Unexpected Error", but the tables will be added.  Then, you must go in and manually add the relations to the new DataSet to match the relations in your MySql schema.

This can be really time consuming if you have many relations.

Any other way of adding MySql data seems to create even more errors (specifically to the TableAdapters) that must be manually fixed.

In addition, when auto increment is set to true in MySql, it is not moved over to Visual Studio 2008 when the data is added from MySql.  Auto increment has to be manually modified in the VS2008 DataSet before the relations are re-added.

Hi Matthew and thanks for your report.

Can you please add some info so I can check it:
 - MySQL server / OS / VS .net versions
 - create table statements

MY SQL server 5.0.51b

Visual Studio 2005 and VS 2008

I dont know what you mean by create table statements.

The fucntionality is inside a dataset object right click and select new table adapater...adding a table adapter that has a FK constraint to another table in the Dataset object gives the error.

Matthew, thanks for answering my first question.

Please add statements used to create tables tbladdress and tblstate. If you can not repeat the problem with empty tables, please use mysqldump and dump some data for those tables and attach resulting file to the report too.

tblblock sql dump

Attachment: tblblock.sql (text/plain), 983 bytes.

tblsublock

Attachment: tblsubblock.sql (text/plain), 967 bytes.

test.sql

Attachment: test.sql (text/plain), 1.06 KiB.

here is something strange..the tblblock and tblsubblock are tables form the database that is giving me trouble...

I attempted to recreate the problem witha new database (the test.sql dump file) and could not recreate it..im not sue what the difference is between teh two tables relationships and fk contraints from one db to another that woudl cause this problem.

Matthew

I'm very interested in this but have so much to do that I am waiting to hear back from you what the differences are between those two databases before I dig in.  I hope you understand.

An example that will cause the errors I described above.

Attachment: elogbook_mysql.sql (application/octet-stream, text), 2.49 KiB.

Thanks Matthew. We're checking this out.

VS2005 snapshot

Attachment: Bug37591.JPG (image/jpeg, text), 83.57 KiB.

Hi Matthew. As you can see from attached image, I have no problems with your database...
I am using VS2005Pro on WinXP Pro SP2 localhost running MySQL server 5.0.68pb and c/NET 5.2.2 (debug). I even added table 'activities' once more (manually) without any problem and with all relations drawn correctly.

Ideas?

Im at a loss..

The database that is havign the problem was migrated using the MySQL Migration Toolkit.

Any new databse I create doesnt have the problem...I cant seem to find anythign that would cause the probem. Frustrating because recreating the databse from scratch seems to be the answer but it isnt really an option..

I have a workaround that seems to be working ...a sql script that removes the FK's and the puts tham back in place when I need to work with the tableadapters. I guess you can just close the issue.

Thanks for your time.

I'm not inclined to close the issue just yet.  Are these tables on the same server?  Are there any differences at all besides the SQL of the table?

This is also causing a problem in VS2008.  I posted a SQL script above to make a simple database.  If you add that to VS2008 you get the same error.  Can you confirm that you are also seeing the same problem?

This is verified though it works perfectly in VS2005. I've emailed some of my contacts in Microsoft to help me with this one.  Hopefully will know something soon.

This worked fine in VS 2005 and VS 2008 SP1 so we are closing it as  a bug in VS 2008.  Since VS 2008 SP1 is a free upgrade this should not pose a problem.

Hello. 

I have the same problem. 

I'm using Visual Studio 2008 Full Edition (Spanish version), Windows XP SP3 connecting to MySQL 5.1, all on the same computer. I 

design my database with MySQL Workbench and export to MySQL Server 5.1. 

I tried installing the 6.0.3 version of the connector. NET but I have the bug #44460 and I'm waiting solutions for it in future 

compiled versions.

I have uninstalled version 6.0.3 and have installed the version 5.2.6. 

But with this version I have the same problem: "Add New Data Source", select "Database", select the data connection to your MySql
database, save your connection string, and finally select the tables for your DataSet. 
When adding the DataSet, VS2008 will give an error that says "Failed to add relation(s). 
Unexpected Error", but the tables will be added.

Please, help me.