> Can you provide a test case that fails using the MySQL CLI?
Yeah. Here's an example using MySQL Server 5.0.26 on Windows.
First, we create a test table that has a floating point field.
We will also add an integer PK field, just for good measurement.
================================
CREATE TABLE testcase (
Id TINYINT,
DecayRate DOUBLE,
PRIMARY KEY(Id)
);
================================
Now add some test data that we know triggers the issue.
================================
INSERT INTO testcase VALUES (1, 0.666666666666667);
INSERT INTO testcase VALUES (2, 0.6666666666666666);
================================
We are trying to simulate for example a DOTNET DataGrid browsing these
data. First it needs to present data to the user, so it does a SELECT.
================================
SELECT * FROM testcase;
+----+-------------------+
| Id | DecayRate |
+----+-------------------+
| 1 | 0.666666666666667 |
| 2 | 0.666666666666667 |
+----+-------------------+
2 rows in set (0.00 sec)
================================
Notice in the above that MySQL Server has rounded one value.
The user now edits some random value in the 2nd row in the DataGrid.
Upon saving the edited data, the standard CommandBuilder found in
DOTNET will make sure that we're not overwriting an update made by
another user. It does this by using optimistic concurrency to make
sure that noone else has modified the data that it's about to write.
In effect, DOTNET includes not only the primary key, but also other
fields (in this case DecayRate) in it's UPDATE query.
================================
UPDATE testcase SET Id=Id WHERE Id='2' AND
DecayRate='0.666666666666667';
Query OK, 0 rows affected (0.00 sec)
================================
Notice in the above that DecayRate matches what the DataGrid saw
via it's initial SELECT. Also notice that 0 rows were affected.
DOTNET expected record number 2 to be updated, but it wasn't.
DOTNET throws an exception, telling the application/user that someone
else has modified data while the user wasn't looking:
System.Data.DBConcurrencyException:
Concurrency violation:
the UpdateCommand affected 0 records.
(A typical application would restart whatever job it was doing, and in
effect retry the operation later. That will cause it to loop
endlessly,
because this is not really a concurrency exception.)
What it really is is MySQL Server storing more digits in the floating
point number than it's telling the client. But when the SELECT happens,
MySQL Server actually *uses* those digits in it's comparison,
effectively
making sure that optimistic concurrency is forever broken on MySQL.
