Bug #47422 MySQLDataAdapter can't work for delete operation

• Submitted: 17 Sep 2009 21:37
• Modified: 21 Sep 2009 6:22
• Reporter: Xiong He
• Status: Can't repeat
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 5.0
• OS: Any
• 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.