MySQL Bugs: #32081: TableAdapter does not update schema properly if database columns are deleted

Bug #32081	TableAdapter does not update schema properly if database columns are deleted
Submitted:	3 Nov 2007 22:14	Modified:	10 Jul 2008 21:31
Reporter:	Morgan Soley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.1.4GA	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	TableAdapter, XSD

Description:
This bug is present in at least 5.1.2 and 5.1.3.

If you delete columns from a table, the corresponding TableAdapter does not find the changes if it is loaded using a "SELECT * FROM" query.  Columns that do not exist are kept in the schema.

Additionally, if you alter a column such that it has the same name but different characteristics, the column is added to the schema with "1" appended to the end of it.  For e.g.  "countryID" column gets altered, the TableAdapter is refreshed and now it shows as having columns "countryID" and "countryID1".

How to repeat:
Create a simple table in a mysql database, like 
CREATE TABLE simple (test1 int, test2 int);

Create a new web site project in visual studio.

Add a connection string to the simple database to the web.config file.

Add a new "Dataset" item to the project's App_Code directory, called "SimpleDatabase".  The XSD file will be created.

Using the dataset wizard, create a new TableAdapter that connects to the simple table.  Use the query "SELECT * FROM simple".   When the wizard finishes, note that the schema is correct in the XSD designer.

Using a MySQL command prompt, drop a column and add a new column in the simple table.

Now regenerate the table adapter schema by running through the configuration wizard again.  Notice that the dropped columns remain in the schema, while the new columns have been added.

Hi Morgan and thanks for your report. Verified just as described, "ghost" field is even present in "Data preview" window.

Morgan, you do not have to hide non-sensitive comments, they might help others. So, if you wish, please unhide it.

Now for your questions:
 - ...manually create a keyPair.snk file...
Please make a note of AssemblyInfo.cs file and make necessary adjustments:

//
// In order to sign your assembly you must specify a key to use. Refer to the 
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing. 
//
// Notes: 
//   (*) If no key is specified, the assembly is not signed.
//   (*) KeyName refers to a key that has been installed in the Crypto Service
//       Provider (CSP) on your machine. KeyFile refers to a file which contains
//       a key.
//   (*) If the KeyFile and the KeyName values are both specified, the 
//       following processing occurs:
//       (1) If the KeyName can be found in the CSP, that key is used.
//       (2) If the KeyName does not exist and the KeyFile does exist, the key 
//           in the KeyFile is installed into the CSP and used.
//   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
//       When specifying the KeyFile, the location of the KeyFile should be
//       relative to the project output directory which is
//       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
//       located in the project directory, you would specify the AssemblyKeyFile 
//       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
//   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
//       documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
#if FINAL && !NET20
[assembly: AssemblyKeyFile("")]
[assembly: AssemblyKeyName("ConnectorNet")]
#endif

 - ...It wasn't clear that I had to download Visual Studio SDK...
Don't know answer to this one, never built VS plug-in myself... Maybe I should try. Anyway, my path would be "C:\Program Files\Visual Studio 2005 SDK\2007.02\VisualStudioIntegration". Afais, it's needed for creating installation package.

This is not a bug.  This is handled by the VS table adapter configuration system, not us.  And SQL Server behaves exactly the same way.  I tested it with VS 2008.