MySQL Bugs: #63714: Table design is failed in Visual Studio 2010

Bug #63714	Table design is failed in Visual Studio 2010
Submitted:	10 Dec 2011 18:53	Modified:	3 Feb 2012 22:07
Reporter:	Radik Nuriev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.4.4	OS:	Windows (XP,7)
Assigned to:	Javier Treviño	CPU Architecture:	Any
Tags:	table design

Description:
Table design does not work for Visual Studio 2010. I get error "Object refrence not set to an instance of an object" when i click design on context menu for any table within my database. I used connector 6.3.6 and Win XP Prp OS all was fine. Once a day, i got this error (i can't say what did precede to this error). Then i installed .net connector 6.4.3. It did not fix the problem. Then i reinstllaed all on Windows 7 clean machine (mysegl server 5.5, connector 6.4.3). However it did not fix problem too.
It is interesting...When i try to launch design of table and get error, right after if i try to refresh connection is hanged.
I have several pics of this bag

How to repeat:
click design table in Visual Studio

Hi Radik,

The problem you have reported sounds a bit too obvious to be overlooked by developers. I tried doing the same setup with my Visual Studio 2010 in Windows 7 and everything worked well.

Which edition of Visual Studio 2010 you are using? 
Please note that MySQL plugins for Visual Studio work correctly only with Visual Studio Professional or higher. The limitation which prevents extending Visual Studio Express with 3-rd party plugins has been placed by Microsoft and cannot be workarounded without violating the end user agreement.

Hi Bogdan!
I am using VS 2010 Ultimate. I have just connected perfomance_schema db of mysql. And there is no issue with design option. However my own db is failed to be designed via VS interface. It was fine earlier...And I'm in doubt what can cause "Object refrence not set to an instance of an object". An i have just found out if i try design tables that are generated by asp.memebrship it is fine too...So it means issue is related to my own tables.

I did further investigation by creating new db from scratch that consisted of two tables. 
1. Table room has two fields ID(pk) and  name;
2. Table lesson has three fields :id(pk), name androomId(FK);
Then i connected to DB via VS and tried to launch design. So it was fine with table room, it was failed with table lesson that had FK refrencing to room table.
And it was also fine for both room and lesson tables to design via VS if relationship beetween tables was removed.
SO my guess is there is somethingwrong with FK in .NET connector.

Still investigating.
I  dropped relation beetween two table. Connected to db via VS and launched design mode. I tried to create FK in VS however i was failed again. When i created FK in VS it generated script: ALTER TABLE `lesson` ADD FOREIGN KEY `FK_lesson_room`() REFERENCES `room`(). ANd as fas as i know this is wrong script for mysql. I tried to launch this script in mysql cmd client and got an error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ') REF
ERENCES `room`()' at line 1

Hi Radik,

Thanks for your reply.
Can you send the CREATE TABLE statements with FK to help me repeat the problem?

I was able to repeat the problem with the following two tables.

CREATE TABLE `table1_pk` (
  `id2` int(11) NOT NULL DEFAULT '0',
  `txt` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table1_fk` (
`id`  int NULL ,
`id2`  int NULL ,
PRIMARY KEY (`id`),
CONSTRAINT `fkey` FOREIGN KEY (`id2`) REFERENCES `table1_pk` (`id2`) ON DELETE CASCADE ON UPDATE NO ACTION
);

In addition to that, VS2010 editor cannot create FK constraint when columns in two tables have the same name (`id2` as above).

Setting the bug as Verified.

Bogdan, hello!

Thanks for reply. I guess you do not need me to send you scrip already, doesn't it?
Can you pls tell me what way i will informed about bug status?
Thanks in advance!

This bug has been fixed with some fixes we did to our foreign key support in 6.3.8.  Those fixes have been merged up to 6.4 and will appear in our 6.4.5 maintenance release

Verified as well this is working in the latest 6.4 version this is not duplicated (I did duplicate this in 6.4.4).  Thanks Reggie.

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

Fix was already released with 6.3.8, 6.4.5 and 6.5.0.

Added to changelog for 6.3.8, 6.4.5, 6.5.0: 

Visual Studio 2010 Table Designer could give an error "Object
reference not set to an instance of an object" for schemas with
certain combinations of column names and foreign key references. The
SQL syntax was incorrect for the ALTER TABLE statement generated by
the Table Designer.