MySQL Bugs: #54895: Bug in MySqlDataAdapter update method

Bug #54895	Bug in MySqlDataAdapter update method
Submitted:	29 Jun 2010 14:13	Modified:	13 Jul 2010 10:37
Reporter:	Attila Aszalos	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.2.3.0	OS:	Windows
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	MySqlDataAdapter.Update; ConcurrencyException; update command

Description:
When I update a value in a DataRow and call the DataAdapters Update method it gives a "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." 
The UpdateCommand is a stored procedure which contains the update command and a select command for the updated row, so that I can get the updated values back in the client application. For this the UpdatedRowSource property is set to FirstReturnedRecord.
If I remove the select command from the end of the update stored procedure it works fine. I think it should when the select command is present.

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 AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getall_bugtable`()
BEGIN
	select * from bugtable;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_updatebugtable`(
    in p_id_auto int,
    in p_field varchar(50))
BEGIN
    update bugtable set `field`=p_field where id_auto=p_id_auto;
    select * from bugtable where id_auto=p_id_auto; // if this is commented out it doesn't give the exception, but I need to refresh the updated row
END

private static void testUpdate()
{
	const string SELECT_CMD = "sp_getall_bugtable"; 
	const string UPDATE_CMD = "sp_updatebugtable";
	const string TABLE_NAME = "bugtable";

	MySqlConnection conn = new MySqlConnection("server=servername;user=username;database=dbname;password=pswd;");

	DataSet ds = new DataSet();

	MySqlDataAdapter da = new MySqlDataAdapter();

	da.SelectCommand = conn.CreateCommand();
	da.SelectCommand.CommandType = CommandType.StoredProcedure;
	da.SelectCommand.CommandText = SELECT_CMD;

	da.UpdateCommand = conn.CreateCommand();
	da.UpdateCommand.CommandType = CommandType.StoredProcedure;
	da.UpdateCommand.CommandText = UPDATE_CMD;
	da.UpdateCommand.Parameters.Add("p_id_auto", MySqlDbType.Int32, 4, "id_auto");
	da.UpdateCommand.Parameters.Add("p_field", MySqlDbType.VarChar, 4, "field");
	da.UpdateCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

	da.Fill(ds, TABLE_NAME);
	ds.Tables[TABLE_NAME].Rows[0]["field"] = "newvalue"; // a value differnt from the old value => the Rows[0].RowState value changes to "Modified"

	da.Update(ds.Tables[TABLE_NAME]);// Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
}

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/112965

823 Vladislav Vaintroub	2010-07-06
      Make sure MySqlDataAdapter.Update() works for custom stored procedure driven update commands 
      that make use of UpdateRowSource.FirstReturnedRecord ( Bug#54895)

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/113176

823 Vladislav Vaintroub	2010-07-08
      Make sure MySqlDataAdapter.Update() works for custom stored procedure driven update commands 
      that make use of UpdateRowSource.FirstReturnedRecord ( Bug#54895)

pushed to 6.0.7, 6.1.5, 6.2.4, 6.3.3

An entry has been added to the 6.0.7, 6.1.5, 6.2.4, and 6.3.3 changelogs:

MySqlDataAdapter.Update() generated concurrency violations for custom stored procedure driven update commands that used UpdateRowSource.FirstReturnedRecord.