MySQL Bugs: #27642: Bug at use MySqlDataAdapter.Update(table) from mysql-connector-net

Bug #27642	Bug at use MySqlDataAdapter.Update(table) from mysql-connector-net
Submitted:	4 Apr 2007 10:37	Modified:	11 May 2007 16:37
Reporter:	Mikhail Slivchenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	MySqlDataAdapter, UPDATE

Description:
By a call of a method "Update" is not synchronized between the table in a database on a mysql server and the datatable in the program a column described as:
id int (8) unsigned primary key unique auto_increment 

How to repeat:
At the server:
create table test (id int(8) unsigned primary key unique auto_increment, name text) ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;

At the program (C# .net 2.0 Microsoft Visual Studio 2005):
private DataSet dataset;
private MySqlCommand myCommand1;
private MySqlDataAdapter adapter1;
private MySqlCommandBuilder MySqlCB1;

string connStr = String.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};", "localhost", 3306, "test", "test", "test");
try 
{
  myConnection = new MySqlConnection( connStr );
  myConnection.Open();
}
catch (MySqlException ex) 
{
  MessageBox.Show( "Error connecting to the server: " + ex.Message );
}

dataset = new DataSet();
myCommand1 = new MySqlCommand();
myCommand1.Connection = myConnection;
adapter1 = new MySqlDataAdapter();
MySqlCB1 = new MySqlCommandBuilder();

MySqlCB1.DataAdapter = adapter1;
myCommand1.CommandText = "select * from test order by name";
adapter1.SelectCommand = myCommand1;
adapter1.Fill(dataset, "test");
dataGridView1.DataSource = dataset.Tables["test"];

If now in a column "name" tables dataset.Tables ["test"] add any value (for example "abc") and to cause a method adapter1.Update(dataset. Tables ["test"]) that the column "id" tables dataset.Tables ["test"] will not be updated and will contain empty value.

If to add some identical values in a column "name" tables dataset.Tables ["test"] (for example "abc") and to cause a method adapter1.Update(dataset.Tables ["test"]), and then remove one of these added lines of the table and to cause a method adapter1.Update(dataset.Tables ["test"]) that we shall receive a error: “Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.” Thus the chosen line from a database will not leave.

In MySQL Connector Net 1.0.8 (1.0.9) given problem was absent. At addition of a line in the table in a similar way, after a call of a method adapter1.Update(dataset.Tables["test"]) a column "id" tables dataset.Tables["test"] was automatically updated and in it corresponding value "id" from the table test databases of a mysql-server was substituted.

Hi Mikhail and thanks for your report. I appologize for the delay.

I am examining this report together with Bug#27410 and the work, from my side,
will be done by the end of this week.

Hi Mikhail. Can you please check on sample provided in C:\Program Files\MySQL\MySQL Connector Net 5.0.6\Samples\Table Editor\cs? I believe it describes your problem and proper way to code it. On my machine it works with create table bug27642(
id int(8) unsigned primary key unique auto_increment, 
name text) 
ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;

What do you think?

"
[8 Apr 18:13] Tonci Grgin

Hi Mikhail. Can you please check on sample provided in C:\Program
Files\MySQL\MySQL Connector Net 5.0.6\Samples\Table Editor\cs? I
believe it describes your problem and proper way to code it. On my
machine it works with create table bug27642(
id int(8) unsigned primary key unique auto_increment, 
name text) 
ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;

What do you think?
"

Re:

What I do:

mysql -u root -h host -p password

create database test;
use test;
create table bug27642(id int(8) unsigned 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.0.6\Samples\Table Editor\cs\bin\Debug\TableEditor.exe

(Important! To not interrupt work of the program TableEditor.exe! All operations to carry out in one session! )

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

then in combobox "Databases" select database "test"
then in combobox "Tables" select table "bug27642"

In second column of datagrid I insert value "abc", then press button "Update", into the table "bug27642" database's "test" 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")

But there should be real values of id taken of the table "bug27642" from a server "host"

After these operations I allocate one of lines of the datagrid (in program TableEditor.exe) containing values (null,"abc") , press "Delete" on keyboard and then press "Update" button......

And take: "Unhandled exception has occured in your application..... Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."

And it "Details" section:
"
See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.DBConcurrencyException: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at TableEditor.Form1.updateBtn_Click(Object sender, EventArgs e) in C:\Program Files\MySQL\MySQL Connector Net 5.0.6\Samples\Table Editor\cs\Form1.cs:line 341
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
TableEditor
    Assembly Version: 1.0.2656.14267
    Win32 Version: 1.0.2656.14267
    CodeBase: file:///C:/Program%20Files/MySQL/MySQL%20Connector%20Net%205.0.6/Samples/Table%20Editor/cs/bin/Debug/TableEditor.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
MySql.Data
    Assembly Version: 5.0.6.0
    Win32 Version: 5.0.6.0
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/MySql.Data/5.0.6.0__c5687fc88969c44d/MySql.Data.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
"

Hi Mikhail.

"In second column of datagrid I insert value "abc", then press button "Update",
into the table "bug27642" database's "test" 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"!!!!!!!!)"
&
"And take: "Unhandled exception has occured in your application..... Concurrency
violation: the DeleteCommand affected 0 of the expected 1 records."

are both the same problem. You are unable to fetch LAST_INSERT_ID from database and get it into datatable. Further more, as this is sample project, you are getting (null) in AI field which means that all of AI column properties are not set, not even AutoIncrement(!)
	da.Fill( data );
        data.Columns[0].AllowDBNull = true;
        data.Columns[0].DefaultValue = null;
        data.Columns["id"].AutoIncrement = true;
        data.Columns["id"].AutoIncrementSeed = -1;
        data.Columns["id"].AutoIncrementStep = 1;
I think this is a bug.

I have searched entire manual/BugsDB in order to find a solution but found none.
Maybe using SP returning LAST_INSERT_ID for InsertCommand and DataRow to assign that value to DataTable column...

There are objective dificulties with AI columns too. Looking into NET specs I've found that:
"Please be aware that the DataColumn class in .NET 1.0 and 1.1 does not allow columns with type of UInt16, UInt32, or UInt64 to be autoincrement columns. If you plan to use autoincremement columns with MySQL, you should consider using signed integer columns." is true for NET 2.0 too,
and
"Using the data source to populate an Identity or Autonumber column for a new row added to a DataSet creates a unique situation because the DataSet has no direct connection to the data source. As a result, the DataSet is unaware of any values generated automatically by the data source. However, with a data source that can create stored procedures with output parameters, such as ..., you can specify the automatically generated values, such as a new identity value, as an output parameter and use the DataAdapter to map that value back to the column in the DataSet."

Hi Tonci!

You wrote:
"
"In second column of datagrid I insert value "abc", then press button
"Update",
into the table "bug27642" database's "test" 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"!!!!!!!!)"
&
"And take: "Unhandled exception has occured in your application.....
Concurrency
violation: the DeleteCommand affected 0 of the expected 1 records."

are both the same problem.
"

It's I understand...

And you wrote too:
"
There are objective dificulties with AI columns too. Looking into NET
specs I've found that:
"Please be aware that the DataColumn class in .NET 1.0 and 1.1 does not
allow columns with type of UInt16, UInt32, or UInt64 to be autoincrement
columns. If you plan to use autoincremement columns with MySQL, you
should consider using signed integer columns." is true for NET 2.0
too,
and
"Using the data source to populate an Identity or Autonumber column for
a new row added to a DataSet creates a unique situation because the
DataSet has no direct connection to the data source. As a result, the
DataSet is unaware of any values generated automatically by the data
source. However, with a data source that can create stored procedures
with output parameters, such as ..., you can specify the automatically
generated values, such as a new identity value, as an output parameter
and use the DataAdapter to map that value back to the column in the
DataSet."
"

But with MySQL Connector Net 1.0.8 (and 1.0.9) all works perfect!

And I think bug into Connector 5.x.x

"Please be aware that the DataColumn class in .NET 1.0 and 1.1 does not
allow columns with type of UInt16, UInt32, or UInt64 to be autoincrement
columns. If you plan to use autoincremement columns with MySQL, you
should consider using signed integer columns." is true for NET 2.0
too"

I think, that the autoincremement column in the datatable (in C# program) i=
s not necessary to me.

In fact in time from data recive and addition of a new row someone  could a=
dd data from other client place.
Simply connector should receive last_insert_id() from the table for each ad=
ded line and to place it in the datatable (in C# program)

May be this can help you... I did in my solution and it worked

C#
/// mytable is a DataTable, sqladp is a MySqlAdapter and cd is a 
/// MySqlCommandBuilder

mytable = new DataTable();
sqladp = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM comandos_in",dbconn);
cb = new MySqlCommandBuilder(sqladp);
sqladp.Fill(mytable);
newrow = mytable.NewRow();
newrow["numdnp"] = numdnp; /// This is a simple String
newrow["accion"] = accion; /// This is a simple String
newrow["fecha"] = System.DateTime.Now;
newrow["ejecutado"] = 0;
mytable.Rows.Add(newrow); ///i don't know if this line is needed 
DataTable changes = mytable.GetChanges();
sqladp.Update(changes);
mytable.AcceptChanges();

Is just like the example show.

Regards
Jorge De Vega

Connector/Net v5.1.0 - still the same problem: My application maintains data locally and updates a database by using datasets, tables with auto-increment primary keys and the data-adapter update-method (at this time implemented for SQL-Server). Tried to implement this on MySql - without success. The reason: When inserting new rows into the database, the primary keys maintained by the database are not updated in the dataset (as they are with SQL Server) and therefore I don't get the "real" primary keys back to my application within the returned dataset.

Looking forward to have this bug solved.
Regards
Ewald

Just found a way to get the primary keys (auto increment) for new records out of the MySql-database back into the dataset(Connector/Net v5.1.0): I added the following SQL-statement to the CommandText of the InsertCommand and all works fine:

<data-adapter>.InsertCommand.CommandText += "; SELECT <id-column> FROM <table> WHERE <id-column> = LAST_INSERT_ID()";

Hope someone can use that.
Ewald

This has already been handled.  The sample has not been updated to use the new technique.  Please see my blog post on this at http://www.bytefx.com/blog/PermaLink,guid,90a3e805-2d0a-4409-96fc-3a13519b033a.aspx

I'm also 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.

I also have the same problem. It is a big bug!!!

Anybody actually tried Reggie's suggestions? Read the part where differences between fw's 1. and 2. are explained? Bear in mind, MS writes standards to accommodate their own SW not caring for functionality others provide...

Hello I have the same problem in a different context

This is the function I call when the user explicitely ask uptade for it's change
The dataset is the datasource of a datagridview

The first time the function is called it works fine

But if it is called a second time with the same datagrid view I got the error
{"Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."}
But if I make another select between the two call : no error

    private void do_update()
    {

      MySqlCommandBuilder cb = new MySqlCommandBuilder(adaAddr);

      DataSet ch = new DataSet();
      ch = dsAddr.GetChanges(DataRowState.Deleted);
      if (null != ch)
      {
        int del=adaAddr.Update(ch);
        lbl_delcnt.Text = del.ToString();
      }

      ch = dsAddr.GetChanges(DataRowState.Modified);
      if (ch != null)
      {
        int upd=adaAddr.Update(ch);
        lbl_updcnt.Text = upd.ToString();
      }

      ch = dsAddr.GetChanges(DataRowState.Added);
      if (ch != null)
      {
        int add=adaAddr.Update(ch);
        lbl_addcnt.Text = add.ToString();
      }

      MySqlCommand cmd = new MySqlCommand("DELETE from tours where addrID not in ( select addrID from address );", conn);
      int tourdel=cmd.ExecuteNonQuery();
      lbl_deltourcnt.Text = tourdel.ToString();
       
      return;

Reggie Burnett's link doesn't seem to work. Does anyone know a) an alternative link location and b) if that actually solved the problem? I notice that the bug is down as Closed, but I don't actually see that it's been resolved anywhere.

Here's Reggie's post, hope he doesn't mind me uploading it.

Attachment: Retrieving autoincrement field values.pdf (application/save, text), 77.72 KiB.

For now, bug#40529 was marked as duplicate of this one.

Tonci, thanks for the post to Reggie's explanation. The PDF explains that this new ReturnGeneratedIdentifiers property, when set to true, should return the newly created IDs. Howvere when I looked at this property, it defaults to being true anyway and I found the reverse to work - setting it to False. Is that right?