Bug #47422 MySQLDataAdapter can't work for delete operation
Submitted: 17 Sep 2009 21:37 Modified: 21 Sep 2009 6:22
Reporter: Xiong He Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: MySqlClientFactory, MySqlDataAdapter

[17 Sep 2009 21:37] Xiong He
Description:
here is the source code:
using System;
using System.Collections.Generic;
using System.Text;

using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Common;

namespace SybaseUtilTest
{
    class Program
    {
        static void testDataAdapter()
        {
            try
            {
                MySqlClientFactory factory = MySqlClientFactory.Instance;
                DbConnection conn = factory.CreateConnection();
                conn.ConnectionString = string.Format("server={0};user id={1}; password={2}; database={3}; port={4}; pooling=false",
                            "localhost", "root", "******", "test", 3306);
                conn.Open();

                DbDataAdapter da = factory.CreateDataAdapter();

                da.SelectCommand = conn.CreateCommand();
                da.SelectCommand.CommandText = "select * from t12345";

                da.DeleteCommand = conn.CreateCommand();
                da.DeleteCommand.CommandText = "delete from t12345 where id = @id";

                DbParameter param = factory.CreateParameter();
                param.ParameterName = "@id";
                param.DbType = DbType.Int32;
                param.SourceColumn = "id";
                param.SourceVersion = DataRowVersion.Current;

                da.DeleteCommand.Parameters.Add(param);
                da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

                DataTable dt = new DataTable("t12345");
                da.Fill(dt);

                int index = 0;
                foreach ( DataRow o in dt.Rows )
                {
                    if (o["id"].Equals(4))
                    {
                        Console.WriteLine(String.Format("index={0}, to delete id = 4, col2 = {1}" , index, o["col2"]));
                        break;
                    }
                    index++;
                }
                dt.Rows[index].Delete();
                da.Update(dt);
                dt.AcceptChanges();

                da.Dispose();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Source + "\n"
                    + ex.Message + "\n"
                    + ex.StackTrace);
            }
            
        }
        
        static void Main(string[] args)
        {
            testDataAdapter();
        }
    }
}

there will be exception here, "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records"

table t12345 is defined as (id int primary key, col2 varchar(32))

How to repeat:
as the source code

Suggested fix:
No.
[18 Sep 2009 8:58] Tonci Grgin
Hi Xiong and thanks for your report.

Does this error happen when there is a row satisfying the condition? Can you please do following things:
  o Attach SQL script (with DDL/DML) that I can import to MySQL server.
  o Upgrade c/NET to, at least, 5.2 and retest. 5.0 is very very old.
  o Tell me your MySQL server version and SQL_MODE you're using.
[21 Sep 2009 6:22] Tonci Grgin
Xiong, good, so it works with c/NET 5.2.7. I will consult others but I don't think we'll be fixing bugs in c/NET 5.0.

There is a misconception here. Any *connector* version (c/J, c/ODBC. c/NET ...) is in no way related to MySQL server version! So you should always use latest connector versions.

Closing the report as new connector version doesn't exhibit the problem.