List:MySQL and .NET« Previous MessageNext Message »
From:David Dindorp Date:October 27 2006 9:41am
Subject:RE: New bugs category for Visual Studio plugin
View as plain text  
> 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.

Thread
New bugs category for Visual Studio pluginReggie Burnett25 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp26 Oct
  • Re: New bugs category for Visual Studio pluginMartin MC Brown26 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp26 Oct
  • RE: New bugs category for Visual Studio pluginReggie Burnett26 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp26 Oct
  • RE: New bugs category for Visual Studio pluginReggie Burnett26 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp27 Oct
  • Re: New bugs category for Visual Studio pluginBarry Zubel27 Oct
  • RE: New bugs category for Visual Studio pluginReggie Burnett27 Oct
    • MySQL not taking a Double.MaxValueChris Herridge19 Feb
      • RE: MySQL not taking a Double.MaxValueJohan Steyn19 Feb
      • RE: MySQL not taking a Double.MaxValueReggie Burnett21 Feb
RE: New bugs category for Visual Studio pluginYvan Rodrigues27 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp27 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp28 Oct
  • RE: New bugs category for Visual Studio pluginReggie Burnett29 Oct
RE: New bugs category for Visual Studio pluginDavid Dindorp9 Nov
  • RE: New bugs category for Visual Studio pluginReggie Burnett15 Nov
RE: MySQL not taking a Double.MaxValueChris Herridge19 Feb