MySQL Bugs: #36042: MySQL Connector/Net 5.2.1 Not Updating auto

Bug #36042	MySQL Connector/Net 5.2.1 Not Updating auto_increment Primary Key Column
Submitted:	13 Apr 2008 20:37	Modified:	16 Apr 2008 7:56
Reporter:	Mikhail Slivchenko	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, MySqlDataAdapter, UPDATE

Description:
Having a problem is described in Bug #27642 and Bug #29877.

.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.

Reggie Burnett wrote:

"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;"

At connector 5.2.1 cmdBuilder.ReturnGeneratedIdentifiers = true BY DEFAULT, but not updating auto_increment primary key column

How to repeat:
from command line:

mysql -u root -h host -p password

create database bug;
use bug;
create table bug(id int(8) primary key unique auto_increment,
name text) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;
exit;

then:

run C:\Program Files\MySQL\MySQL Connector Net 5.2.1\Samples\Table
Editor\cs\bin\Debug\TableEditor.exe

then:
write in textbox Server: "host", User Id: "root", Password: "password" and click on
"Connect" button

then in combobox "Databases" select database "bug"
then in combobox "Tables" select table "bug"

In second column of datagrid I insert value "abc", then press button "Update", into the
table "bug" database's "bug" at the server "host" new row aded with name="abc" and
CORRECT id (for example="1") BUT into the first row of datagrid value of id="null" (NOT
"1"!!!!!!!!)

Repeat this procedures some times (for example 3).

We have at server 3 rows with values:
(1,"abc")
(2,"abc")
(3,"abc")

At program we have 3 rows with values:
("null","abc")
("null","abc")
("null","abc")

If at c# code in events "click" for button "update" add line MessageBox.Show(cb.ReturnGeneratedIdentifiers.ToString());
for example:
private void updateBtn_Click(object sender, System.EventArgs e)
{
  DataTable changes = data.GetChanges();
  MessageBox.Show(cb.ReturnGeneratedIdentifiers.ToString());
  da.Update( changes );
  data.AcceptChanges();
}
at clicking "Update" I take a Message "true"!!! But auto_increment primary key column NOT UPDATING!

Hi Mikhail and thanks for your report.

I am unable to verify it on latest 5.2.1 sources.

Explanation:
  Taken in consideration that cmdBuilder.ReturnGeneratedIdentifiers = true; is now default, following test case works as expected:

namespace MySQLBug29877
{
	class Program
	{
		static private MySql.Data.MySqlClient.MySqlConnection
			conn = null;
		static private MySql.Data.MySqlClient.MySqlCommand
			cmd = null;
		static private MySql.Data.MySqlClient.MySqlDataAdapter
			da = null;
		static private MySql.Data.MySqlClient.MySqlCommandBuilder
			cmdBuilder = 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
			{
				conn = new MySql.Data.MySqlClient.MySqlConnection();
				cmd = new MySql.Data.MySqlClient.MySqlCommand();
				da = new MySql.Data.MySqlClient.MySqlDataAdapter();
				cmdBuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder();

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

				conn.ConnectionString = "Data Source=localhost;Port=3306;Database=test;User Id=root;Password=*****";
				conn.Open();

                MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug29877", conn);
                cmdCreateTable.CommandTimeout = 0;
                cmdCreateTable.ExecuteNonQuery();
                cmdCreateTable.CommandText = "CREATE TABLE bug29877 (" +
                    "id INTEGER UNSIGNED NOT NULL auto_increment," +
                    "tinyint_value TINYINT UNSIGNED," +
                    "PRIMARY KEY  (id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
                cmdCreateTable.ExecuteNonQuery();

                cmd.CommandType=System.Data.CommandType.Text;
				cmd.Connection= conn;
                cmd.CommandText = "SELECT * FROM bug29877";
			    da.SelectCommand =cmd;
                da.Fill(SystemDataDataSet, "bug29877");
				cmdBuilder.DataAdapter = da;
                cmdBuilder.DataAdapter.SelectCommand.CommandText = "SELECT * FROM bug29877";
				cmdBuilder.RefreshSchema();

                SystemDataDataView = SystemDataDataSet.Tables["bug29877"].DefaultView;

				SystemDataDataRow = SystemDataDataView.Table.NewRow();
				SystemDataDataRow["tinyint_value"] = 1;

                SystemDataDataView.Table.Rows.Add(SystemDataDataRow);

               
				System.Console.WriteLine("ID before update: " + SystemDataDataRow["id"]);
                da.Update(SystemDataDataSet, "bug29877");
				System.Console.WriteLine("ID after update: "  + SystemDataDataRow["id"]);
                System.Console.ReadKey();
			}

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

I'm using MySQL server 5.0.58PB & c/NET 5.2.1 from trunk.

source and binaries from connector 5.2.1 (Table Editor)

Attachment: Table Editor.zip (application/zip, text), 164.22 KiB.

from command line:

mysql -u root -h host -p password

create database bug;
use bug;
create table bug(id int(8) primary key unique auto_increment,
name text) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;
exit;

then:

- unzip archive Table Editor.zip
- go "table editor\cs\bin\debug"
- run tableeditor.exe
- enter server, user, password than press "Connect"
- select database "bug"
- select table "bag"
- add new value at text column
- press "Update"
- id column IS NULL!!!!!!!

I think this a bug!

Mikhail, as I read it, you are actually arguing that TableEditor demo is not functioning, right? For as I see c/NET and code I attached is working well.