Bug #54863 Bug in MySqlDataAdapter update method
Submitted: 28 Jun 2010 16:38 Modified: 13 Jul 2010 9:56
Reporter: Attila Aszalos Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.2.3 OS:Windows
Assigned to: Tony Bedford CPU Architecture:Any
Tags: MySqlDataAdapter; Update method; UpdatedRowSource.FirstReturnedRecord

[28 Jun 2010 16:38] Attila Aszalos
Description:
After calling the Update method of the DataAdapter on a DataTable, which has a newly inserted row, the RowState gets "Modified" (it should be "Unchanged"). This causes an exeption when I try to delete the newly inserted row.

The DataTable's  primary key is an autoincrementing integer value, which is updated after the call of the DataAdapter's Update method using the UpdatedRowSource property set to "FirstReturnedRecord". When I try to delete the newly inserted record it gives an exception with the following message: "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.".

If I call the datatable.AcceptChanges() method after the data adapters update command (which performed the insert) everything works properly.

Why do I need to call the AcceptChanges method? The documentation from MSDN says that the Update method of the DataAdapter automatically calls the DataTable.AcceptChanges() method: http://msdn.microsoft.com/en-us/library/system.data.idataadapter.update.aspx, in the 7th step AcceptChanges  is called.

How to repeat:
CREATE TABLE `bugtable` (
  `id_auto` int(11) NOT NULL AUTO_INCREMENT,
  `field` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_auto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insertbugtable`(
    in p_field varchar(50))
BEGIN
    INSERT INTO bugtable(`field`) VALUES(p_field);
    SELECT * FROM bugtable WHERE id_auto=@@IDENTITY;
END

private static void testMySql()
{
	MySqlConnection conn = new MySqlConnection("server=localhost;user=username;database=dbname;password=pswd;");
	MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM bugtable", conn);
	
	da.InsertCommand = conn.CreateCommand();
	da.InsertCommand.CommandText = "sp_insertbugtable";
	da.InsertCommand.CommandType = CommandType.StoredProcedure;
	da.InsertCommand.Parameters.Add("p_field", MySqlDbType.VarChar, 50, "field");
	da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

	da.DeleteCommand = conn.CreateCommand();
	da.DeleteCommand.CommandText = "DELETE FROM bugtable WHERE id_auto=@id_auto";
	da.DeleteCommand.Parameters.Add("@id_auto", MySqlDbType.Int32, 4, "id_auto");

	DataSet ds = new DataSet();
	da.Fill(ds, "bugtable");

	BindingSource bs = new BindingSource();
	bs.DataSource = ds;
	bs.DataMember = "bugtable";

	bs.AddNew();
	((DataRowView)bs.Current).Row["field"] = "bugrow";
	bs.EndEdit();
	
	da.Update(ds.Tables["bugtable"]);

	bs.RemoveCurrent();
	da.Update(ds.Tables["bugtable"]); // here there will be a concurrencyviolation exception
}

Suggested fix:
The AcceptChanges method of the DataTable object (given as parameter to the DataAdapter.Update method) should be called at the end of the method according to the documentation at the MSDN.
[1 Jul 2010 16:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112698

821 Vladislav Vaintroub	2010-07-01
      Bug#54863 : several datadapter.Update()s  with DataTable changes in
       between can result into ConcurrencyException
      
      To fix, call DataTable.AcceptChanges() at the end of MySqlDataAdapter.Update()
      (this is the documented behavior which DBDataAdapter surprisingly does not implement)
[1 Jul 2010 22:59] Vladislav Vaintroub
pushed to 6.0, 6.1, 6.2, 6.3
[2 Jul 2010 7:19] Tonci Grgin
Thanks Attila for your report and Wlad for his quick fix.
[13 Jul 2010 9:56] Tony Bedford
An entry has been added to the 6.0.7, 6.1.5, 6.2.4, and 6.3.3 changelogs:

Several calls to datadapter.Update() with intervening changes to DataTable resulted in ConcurrencyException exceptions being generated.
[7 Aug 2010 23:56] Pavel Bazanov
Hello,
Despite what MSDN is saying I don't see that SqlDataAdapter and OleDbDataAdapter call AcceptChanges. Why did you decide to call it in MySqlDataAdapter?

Also, this may be a breaking change for the users. For example, I don't need AcceptChanges() to be called. It breaks my code and I need to rewrite several methods...
[10 Dec 2010 8:13] Tonci Grgin
Continued in Bug#58853.
[14 Dec 2010 20:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126845

854 Reggie Burnett	2010-12-14
      - removed extra call to Update in MySqlDataAdapter introduced with bug fix to bug #54863.
        It seems to not be necessary (bug #58853)