Bug #31869 Concurrency violation: the UpdateCommand affected 0 of the expected 1 records
Submitted: 26 Oct 2007 8:23 Modified: 9 Jan 2008 2:01
Reporter: tudder tiger Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.1.3 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: UpdateCommand

[26 Oct 2007 8:23] tudder tiger
Description:
1.Creat a talbe:
CREATE TABLE `testTable` (
  `id` char(20) NOT NULL,
  `test` float(9,3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;

2.When I want to update a record folowing exception has been appeared:
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records
(when testTable.test < -25000.001)

run MySQL ConnectorNet\Sample\Table Editor, the same error has been appeared.

How to repeat:
1.Creat a talbe:
CREATE TABLE `testTable` (
  `id` char(20) NOT NULL,
  `test` float(9,3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;

2.When I want to update a record folowing exception has been appeared:
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records
(when testTable.test < -25000.001)

run MySQL ConnectorNet\Sample\Table Editor, the same error has been appeared.

Suggested fix:
no
[4 Jan 2008 16:08] Marc Puig
We have same problem. 

It always happens with updates in float columns. 
The problem could be in MysqlCommandBuilder?  It can be related to the local regional settings (we use the comma instead of point to separate the decimal places)

Please confirm if you could reproduce it.
[6 Jan 2008 9:14] Marc Puig
We have solved the problem.
It was due to the aproximation of FLOAT columns. MySqlCommandBuilder composes the UPDATE command using WHERE col=?1 AND col=?2 ...
In FLOAT columns you'll never find an exact value in this way.

For example, if you have a FLOAT column with value 10.3, and you perform a SELECT * FROM table WHERE floatcolumn=10.3, you won't get any result. Instead, you can use  SELECT * FROM table WHERE floatcolumn BETWEEN 10.2 AND 10.4 and you'll get the row.

So, there's only one solution. Use DECIMAL column instead of FLOAT, and it works well.

Hope this helps you.

Marc Puig
[9 Jan 2008 2:01] Reggie Burnett
Thanks for letting us know.   You are correct that using float or double columns for any type of index or unique column is tough and not recommended.
[30 Jan 2008 14:33] Johnson Oluwadele
There schould be a solution to this quickly. This is a terrible error and has to be fixed.
[30 Jan 2008 15:44] Tonci Grgin
Hi Johnson. Can you please elaborate your statements in regards to how CPU stores floating point values. From my part, I don't see a bug here.
[14 May 2010 21:57] Matthew Bilek
Never had problems with my code executing until I added a float column to the table and modified the code to save column value. Then I started to get intermittent 'Concurrency violation: the UpdateCommand affected 0 of the expected 1 records' errors.