We encountered this, and circumvented this by defining our fields as
DOUBLE(x,y) to ensure that a standard precision set is used.
but yes, it was (and still is) an issue.
B.
David Dindorp wrote:
>> 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.
>
>
