MySQL Bugs: #29877: MySQL Connector/Net 5.0.7 Not Updating auto

Bug #29877	MySQL Connector/Net 5.0.7 Not Updating auto_increment Primary Key Column
Submitted:	18 Jul 2007 18:10	Modified:	13 Nov 2007 11:32
Reporter:	Matthew Bilek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.7	OS:	Windows
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
Having a problem with the 5.0.7 .Net connector not updating an auto_increment column when the .Update() method is called.  1.0.7 .Net connector would update auto_increment columns properly.

How to repeat:
Create a table with an auto_increment primary key.
Using the MySQL Connector/Net 5.0.7 call the <MySql.Data.MySqlClient.MySqlDataAdapter>.Update(<System.Data.DataSet>, Table) method to perform updates.

Using the MySQL Connector/Net 1.0.7 does provide the auto_increment primary keys after the .Update(...) method is called.

Hi Matthew and thanks for your report. Can you please attach small but complete test case showing this behavior?

Here is SQL to create the example table:

CREATE DATABASE IF NOT EXISTS mysql_bug_29877;

USE mysql_bug_29877

DROP TABLE IF EXISTS mysql_bug_29877;

CREATE TABLE IF NOT EXISTS mysql_bug_29877 (
  mysql_29877_id INTEGER UNSIGNED NOT NULL auto_increment,
  tinyint_value TINYINT UNSIGNED,
  smallint_value SMALLINT UNSIGNED,
  mediumint_value MEDIUMINT UNSIGNED,
  int_value INT UNSIGNED,
  bigint_value BIGINT UNSIGNED,
  datetime_value DATETIME,
  date_value DATE,
  timestamp_value TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  time_value TIME,
  year_value YEAR,
  char_value CHAR(10),
  varchar_value VARCHAR(10),
  binary_value BINARY(10),
  varbinary_value VARBINARY(10),
  tinyblob_value TINYBLOB,
  tinytext_value TINYTEXT,
  blob_value BLOB,
  text_value TEXT,
  mediumblob_value MEDIUMBLOB,
  mediumtext_value MEDIUMTEXT,
  longblob_value LONGBLOB,
  longtext_value LONGTEXT,
  enum_value ENUM('TINYINT','SMALLINT','MEDIUMINT','INT','BIGINT'),
  set_value SET('TINYINT','SMALLINT','MEDIUMINT','INT','BIGINT'),
  PRIMARY KEY  (mysql_29877_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

Sample .Net code used to test the 1.0.7 and 5.0.7 .Net Connector:

using System;
using System.Collections.Generic;
using System.Text;

namespace MySQLBug29877
{
    class Program
    {
        static private MySql.Data.MySqlClient.MySqlConnection MySqlDataMySqlClientMySqlConnection = null;
        static private MySql.Data.MySqlClient.MySqlCommand MySqlDataMySqlClientMySqlCommand = null;
        static private MySql.Data.MySqlClient.MySqlDataAdapter MySqlDataMySqlClientMySqlDataAdapter = null;
        static private MySql.Data.MySqlClient.MySqlCommandBuilder MySqlDataMySqlClientMySqlCommandBuilder = null;

        static private System.Data.DataSet SystemDataDataSet = null;
        static private System.Data.DataView SystemDataDataView = null;
        static private System.Data.DataRow SystemDataDataRow = null;

        static private System.Byte[] SystemBytes = new System.Byte[] { (byte)'a', (byte)'b', (byte)'c'};

        static void Main(string[] args)
        {
            try
            {
                Program.MySqlDataMySqlClientMySqlConnection = new MySql.Data.MySqlClient.MySqlConnection();
                Program.MySqlDataMySqlClientMySqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
                Program.MySqlDataMySqlClientMySqlDataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
                Program.MySqlDataMySqlClientMySqlCommandBuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder();

                Program.SystemDataDataSet = new System.Data.DataSet();
                Program.SystemDataDataView = new System.Data.DataView();

                Program.MySqlDataMySqlClientMySqlConnection.ConnectionString = "Data Source=localhost;Port=3306;Database=mysql_bug_29877;User Id=MySQLBug29877;Password=MySQLBug29877;";
                Program.MySqlDataMySqlClientMySqlConnection.Open();

                Program.MySqlDataMySqlClientMySqlCommand.CommandType = System.Data.CommandType.Text;
                Program.MySqlDataMySqlClientMySqlCommand.Connection = Program.MySqlDataMySqlClientMySqlConnection;
                Program.MySqlDataMySqlClientMySqlCommand.CommandText = "SELECT * FROM mysql_bug_29877";

                Program.MySqlDataMySqlClientMySqlDataAdapter.SelectCommand = Program.MySqlDataMySqlClientMySqlCommand;
                Program.MySqlDataMySqlClientMySqlDataAdapter.Fill(Program.SystemDataDataSet, "mysql_bug_29877");

                Program.MySqlDataMySqlClientMySqlCommandBuilder.DataAdapter = Program.MySqlDataMySqlClientMySqlDataAdapter;
                Program.MySqlDataMySqlClientMySqlCommandBuilder.DataAdapter.SelectCommand.CommandText = "SELECT * FROM mysql_bug_29877";
                Program.MySqlDataMySqlClientMySqlCommandBuilder.RefreshSchema();

                Program.SystemDataDataView = Program.SystemDataDataSet.Tables["mysql_bug_29877"].DefaultView;

                Program.SystemDataDataRow = Program.SystemDataDataView.Table.NewRow();
                SystemDataDataRow["tinyint_value"] = 1;
                SystemDataDataRow["smallint_value"] = 2;
                SystemDataDataRow["mediumint_value"] = 3;
                SystemDataDataRow["int_value"] = 4;
                SystemDataDataRow["bigint_value"] = 5;
                SystemDataDataRow["datetime_value"] = "2007-08-09 12:34:56";
                SystemDataDataRow["date_value"] = "2007-08-09";
//                SystemDataDataRow["timestamp_value"];
                SystemDataDataRow["time_value"] = "12:34:56";
                SystemDataDataRow["year_value"] = "2007";
                SystemDataDataRow["char_value"] = "abc";
                SystemDataDataRow["varchar_value"] = "abc";
                SystemDataDataRow["binary_value"] = "abc";
                SystemDataDataRow["varbinary_value"] = "abc";
                SystemDataDataRow["tinyblob_value"] = SystemBytes;
                SystemDataDataRow["tinytext_value"] = "abc";
                SystemDataDataRow["blob_value"] = SystemBytes;
                SystemDataDataRow["text_value"] = "abc";
                SystemDataDataRow["mediumblob_value"] = SystemBytes;
                SystemDataDataRow["mediumtext_value"] = "abc";
                SystemDataDataRow["longblob_value"] = SystemBytes;
                SystemDataDataRow["longtext_value"] = "abc";
                SystemDataDataRow["enum_value"] = "INT";
                SystemDataDataRow["set_value"] = "INT";

                Program.SystemDataDataView.Table.Rows.Add(Program.SystemDataDataRow);

                Program.MySqlDataMySqlClientMySqlDataAdapter.Update(Program.SystemDataDataSet, "mysql_bug_29877");
            }

            catch (System.Exception SystemException)
            {
                System.Console.WriteLine(SystemException.Message);
            }
        }
    }
}

1.0.7 Results

Attachment: MySQL Bug 29877 1.0.7.doc (application/msword, text), 273.00 KiB.

5.0.7 Results

Attachment: MySQL Bug 29877 5.0.7.doc (application/msword, text), 274.00 KiB.

Hi Matthew. I'm having hard time following your code. Can you please simplify (see my attached test case) and loose v 1.0.x from all descriptions / synopsis?

Simplified test caes

Attachment: B29877.txt (application/x-download, text), 3.86 KiB.

C# project

Attachment: _bug29877_vs2005.zip (application/not-important, text), 4.37 KiB.

Tonci,

I have tested your test case against the MySQL Connector/Net 1.0.7 and 5.0.7 and get the same results.  1.0.7 would show that SystemDataDataRow["id"] = 1, but 5.0.7 shows that SystemDataDataRow["id"] = null.

You should be able to use your test case to find and determine differences in the 1.0.7 and 5.0.7 versions.

Matthew, thank you. A colleague of mine reviewed the report and we decided that this *is* a bug.

Verified as described on latest 5.0 sources.

This is not a bug.  To get auto incremented values returned automatically, the user must set a property on the command builder.  By adding the following code, the test case works with 5.0.7.

cmdBuilder.ReturnGeneratedIdentifiers = true;

Thanks for clarification Reggie. Assigning to MC as this needs to be documented.

In our case it is a "bug" because it is not backward compatible with previous versions of the MySQL Connector/Net.

Is MySQL saying they don't support backward compatibility for the MySQL Connector/Net???

I did however confirmed that MySQL Connector/Net 5.0.7 does return the auto incremented primary key when then the code is modified to set .ReturnGeneratedIdentifiers = true.

This modification to the code works fine but is not backward compatable with legacy software.

After considering it further, we could set the default value of ReturnGeneratedIdentifers to true.  It is still important to expose this property to allow a user to turn it off.   We'll consider this for 5.0.8 and 5.1.3.

Thanks

Thanks!

I was talking to another peer developer which mentioned that some of our code does not use the command builder and still has the same problem.

Is there a solution or workaround to set this flag even when the command builder is not used?

I want to clarify the problem.  When the command builder is not used and a data adapter .Update() method is called the MySQL Connector/Net 1.0.7 would populate the auto increment column, but the MySQL Connector/Net 5.0.7 would not populate the auto increment column.

Setting to Documenting

The documentation has been updated with a new FAQ section, the first question of which details this issue and how to obtain the correct auto-increment-id in this situation.