Bug #5051 GetSchema not working correctly

• Submitted: 14 Aug 2004 1:06
• Modified: 26 Aug 2004 6:07
• Reporter: Matthew Aznoe
• Status: Can't repeat
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: ByteFX 0.76
• OS: Windows (Windows XP, MySQL 4.0.20)
• Assigned to: Reggie Burnett
• CPU Architecture: Any

[14 Aug 2004 1:06] Matthew Aznoe

Description:
The GetSchema() function in MySqlDataAdapter is not correctly pulling column information out of the database.  In my example below, I have a column that has auto_increment enabled, but when you examine the DataTable structure returned after the call to GetSchema(), it says that auto_increment is false for that column.  This then causes the DataTable.Rows.Add() function to fail because "Column 'id' does not allow nulls".

This same problem also occurs when the DataTable is initialized with the Fill() function.

How to repeat:
using ByteFX.Data.MySqlClient;
using System;
using System.Data;

namespace DBTester
{
   /// <summary>
   /// Summary description for Class1.
   /// </summary>
   class Class1
   {
      [STAThread]
      static void Main(string[] args)
      {
         MySqlConnection      clConnection      = new MySqlConnection( "Data Source=localhost;User ID=root;Database=dbtest" );
         MySqlCommand         clCommand         = new MySqlCommand();
         MySqlDataAdapter      clAdapter         = new MySqlDataAdapter();
         MySqlCommandBuilder   clCommandBuilder   = new MySqlCommandBuilder( clAdapter );
         string               sCommand            = "SELECT * FROM testtable";
         DataTable            dtDataTable         = new DataTable();

         try
         {
            clCommand.Connection = clConnection;

            // Get data from the database.
            clConnection.Open();
   
            clCommand.CommandText = sCommand;
            clAdapter.SelectCommand = clCommand;
            clAdapter.FillSchema( dtDataTable, SchemaType.Source );

            DataRow clRow = dtDataTable.NewRow();

            clRow["name"] = "Bob";
            clRow["salary"] = 10;

            dtDataTable.Rows.Add(clRow);

            clCommand = new MySqlCommand();
            clCommand.Connection = clConnection;
            clCommand.CommandText = sCommand;
            clAdapter = new MySqlDataAdapter();
            clCommandBuilder = new MySqlCommandBuilder( clAdapter );
            clAdapter.SelectCommand = clCommand;
            clAdapter.Update(dtDataTable);
            clConnection.Close();
            Console.WriteLine("Data updated successfully.");
         }
         catch( Exception ex )
         {
            Console.WriteLine(ex.Message);
            clConnection.Close();
         }
         Console.ReadLine();
      }
   }
}

/*
create table testtable
(
   id         int unsigned auto_increment not null,
   name      varchar(255) not null,
   salary   float not null,
   primary key( id )
);
insert into testtable values( null, "Dave", 9.55 );
*/

Suggested fix:
The GetSchema() function should be fixed so that the Unique and AutoIncrement fields are set properly according to the settings in the database.

[26 Aug 2004 6:07] Reggie Burnett

I was able to verify that IsUnique was not set right from GetSchema but was not able to reproduce your problem with auto increment, both against MySql 4.0 and 4.1

I have created a test case related to this bug however.

[20 Apr 2005 16:10] Matt Bilek

I have the same problem with properties and get the same error message.  I can recreate this problem at will.

[29 May 2006 0:45] Darren Wurf

Hi,

I'm still seeing this problem with MySql 5.0.18-nt and Connector/Net v1.0.6.15336 (WinXP Pro).

Data type of the column (as described by DESCRIBE `tablename`) is:
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| sched_id    | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
+-------------+------------------+------+-----+------------+----------------+

Under C# .NET (v1.1), 
Columns["sched_id"].Unique == true (Appears to be fixed)
Columns["sched_id"].AutoIncrement == false (Not fixed?)

Manually setting AutoIncrement to true fixes the issue (but at 30 tables it starts to get painful :)