Bug #29337 when actualize a float value throw exception "concurrency violation"
Submitted: 25 Jun 2007 14:02 Modified: 3 Aug 2007 19:51
Reporter: Guillermo Theler Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.1.1 OS:Windows (XP)
Assigned to: CPU Architecture:Any
Tags: UPDATE ERROR

[25 Jun 2007 14:02] Guillermo Theler
Description:
I discover the error in my code but when execute the sample code 'table edit' do the same thing: when actualize a float value throw exception "concurrency violation"

How to repeat:
execute sample code ´table edit´ with a float row
[26 Jul 2007 9:27] Tonci Grgin
Hi Guillermo and thanks for your report. I'm wondering why is it marked as S1 (Critical)? This is just a sample program not suitable for any real use...

Verified as described on:
 - MySQL server 5.0.44BK running on WinXP Pro SP2 
 - NET fw 2.0 with c/NET 5.1.3 (from trunk)

private void updateBtn_Click(object sender, System.EventArgs e)
{
	DataTable changes = data.GetChanges();
	da.Update( changes );
	data.AcceptChanges();
}

DataTable changes:
		HasErrors	true	bool

changes.Rows[0].RowError
		RowError	"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."	string

data.Rows[1].ItemArray[1] << modified, correct
		HasErrors	false	bool
		RowState	Modified	System.Data.DataRowState

Failing update:
6 Query       UPDATE `test`.`bug29337` SET `DecFld` = 5.59 WHERE ((`Id` = 2) AND ((0 = 1 AND `DecFld` IS NULL) OR (`DecFld` = 6.47)) AND ((0 = 1 AND `FltFld` IS NULL) OR (`FltFld` = 11.4567)) AND ((0 = 1 AND `DateFld` IS NULL) OR (`DateFld` = '2007-07-09 00:00:00')) AND (`TSFld` = '2007-07-26 10:20:17'))

Server version: 5.0.44-max-nt-log Source distribution

mysql> create table bug29337 (
    -> Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> DecFld DECIMAL (12,2) DEFAULT 0.00,
    -> FltFld FLOAT DEFAULT 0.00,
    -> DateFld DATE,
    -> TSFld TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO bug29337 VALUES (NULL, 5.36, 12.5678, "2007-07-10", NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bug29337 VALUES (NULL, 6.47, 11.4567, "2007-07-09", NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bug29337;
+----+--------+---------+------------+---------------------+
| Id | DecFld | FltFld  | DateFld    | TSFld               |
+----+--------+---------+------------+---------------------+
|  1 |   5.36 | 12.5678 | 2007-07-10 | 2007-07-26 10:19:53 |
|  2 |   6.47 | 11.4567 | 2007-07-09 | 2007-07-26 10:20:17 |
+----+--------+---------+------------+---------------------+
[3 Aug 2007 19:51] Reggie Burnett
Don't know how to fix this right now.  Other products on the market show the same behavior.  The core problem is that MySQL does float comparisons using every bit of precision instead of just using the display precision.

The best option right now is to either use decimal columns instead of float or use the CommandBuilder.ConflictOption.OverwriteChanges