Bug #30077 MySqlDataAdapter.Update() exception due to date field format
Submitted: 26 Jul 2007 15:51 Modified: 7 Aug 2007 7:20
Reporter: Franco A. Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.0.7 OS:Windows
Assigned to: CPU Architecture:Any
Tags: MySqlDataAdapter

[26 Jul 2007 15:51] Franco A. 
Description:
I'm using MySqlDataAdapter with MySqlCommandBuilder on a table with a DATE field in primary key.
When trying to perform an Update on the data adapter I get an exception (translated in English is something like: "Concurrency violation: UpdateCommand modified 0 of 1 records").
I looked at the mysql query log and the update command is:

UPDATE `test`.`test2` SET `cod` = 6 WHERE ((`cod` = 1) AND (`dt` = '2006-01-01 00:00:00'))

As dt is a DATE field MySql doesn't find the row because of the H:M:S part in the generated sql. Changing the statement to:

UPDATE `test`.`test2` SET `cod` = 6 WHERE ((`cod` = 1) AND (`dt` = '2006-01-01'))

it works perfectly

How to repeat:
Create this table:

CREATE TABLE test2 (cod INT, dt DATE, PRIMARY KEY(cod, dt));
INSERT INTO test2 (cod, dt) VALUES (1, '2006-1-1');
INSERT INTO test2 (cod, dt) VALUES (2, '2006-1-2');
INSERT INTO test2 (cod, dt) VALUES (3, '2006-1-3');
INSERT INTO test2 (cod, dt) VALUES (4, '2006-1-4');

Then use this simple test program:

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      MySqlConnection cn = new MySqlConnection();
      cn.ConnectionString = "Server=YOUR_HOST;database=test;user=YOUR_USER;pwd=YOUR_PWD";
      MySqlCommand cmd = cn.CreateCommand();
      cmd.CommandText = "SELECT * FROM test2 ORDER BY cod";
      MySqlDataAdapter da = new MySqlDataAdapter();
      da.SelectCommand = cmd;
      MySqlCommandBuilder bld = new MySqlCommandBuilder(da);
      bld.ConflictOption = ConflictOption.OverwriteChanges;
      DataSet ds = new DataSet();
      Console.WriteLine("Fill...");
      da.Fill(ds);
      Console.WriteLine("Modifying row 0...");
      ds.Tables[0].Rows[0]["cod"] = 6;
      Console.WriteLine("New value = " + ds.Tables[0].Rows[0]["cod"].ToString());
      Console.WriteLine("Updating...");
      try
      {
        da.Update(ds);
      }
      catch (Exception ex)
      {
        Console.WriteLine("Caught exception: " + ex.Message);
      }
      Console.ReadLine();
    }
  }
}

Suggested fix:
The H:M:S part in the generated sql should be removed for DATE fields
[30 Jul 2007 22:45] 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/31858
[30 Jul 2007 22:45] Reggie Burnett 
Fixed in 5.0.8 and 5.1.3
[30 Jul 2007 22:48] 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/31859
[7 Aug 2007 7:20] MC Brown 
A note has been added to the 5.0.8 and 5.1.3 changelogs: 

A DATE field would be updated with a date/time value, causing a MySqlDataAdapter.Update() exception.
[16 Aug 2007 14:52] 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/32645