Bug #5225 | DOUBLE datatype rounding causes .Net crash and severe data corruption | ||
---|---|---|---|
Submitted: | 26 Aug 2004 13:13 | Modified: | 6 Feb 2008 19:44 |
Reporter: | d di (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | OS: | Any (All) | |
Assigned to: | CPU Architecture: | Any | |
Tags: | .net crash, data corruption, double precision, floating point, mysqldump, qc, Rounding |
[26 Aug 2004 13:13]
d di
[1 Sep 2004 16:44]
MySQL Verification Team
Thank you for the bug report ans its test case. I was able to repeat: C:\code-for-bugs\bug5225>bug5225 Current data: DecayRate, 1st row: 0,666666666666667 DecayRate, 2nd row: 0,666666666666667 Updating first row... OK! Updating second row... Exception occured! System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at TestClient.Main(String[] args)
[6 Jun 2005 5:37]
Christopher Gruber
I am writing a client application in Visual Studio 2003.NET with C# and am using the MySQL .NET Connector 1.04 and I have been plagued with a DBConcurrency violation exception upon executing a MySqlDataAdapter.update(dataset, "tablename") command after my datagrid changes a column that is a MySQL.float data type. I saw this thread and have tested it with MySQL 4.0.24 and MySQL 4.1 with the results already posted in this thread. I saw this bug was reported last August 2004 and was wondering if it will be fixed soon.
[20 Dec 2005 15:41]
d di
This is the SQL log from the Microsoft SQL server, showing what .Net is doing behind the scenes: ------------------- Login set quoted_identifier on set implicit_transactions off set cursor_close_on_commit off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set language us_english set dateformat mdy set datefirst 7 SQL SELECT * FROM testtable RPC exec sp_reset_connection SQL SET NO_BROWSETABLE ON RPC declare @P1 int set @P1=-1 exec sp_prepare @P1 output, NULL, N'SELECT * FROM testtable', 1 select @P1 SQL SET NO_BROWSETABLE OFF RPC exec sp_special_columns N'testtable', NULL, NULL, N'V', N'T', N'O', @ODBCVer = 3 RPC exec sp_special_columns N'testtable', NULL, NULL, N'V', N'T', N'O', @ODBCVer = 3 RPC exec sp_executesql N'UPDATE testtable SET DecayRate = @P1 WHERE ((Id = @P2) AND (DecayRate = @P3))', N'@P1 float,@P2 tinyint,@P3 float', 1.234500000000000e+004, 1, 6.666666666666670e-001 RPC exec sp_unprepare 1 RPC exec sp_reset_connection RPC exec sp_executesql N'UPDATE testtable SET DecayRate = @P1 WHERE ((Id = @P2) AND (DecayRate = @P3))', N'@P1 float,@P2 tinyint,@P3 float', 1.234500000000000e+004, 2, 6.666666666666666e-001 ------------------- See MySQL log in the original bug description for a comparison. As can be seen in the Microsoft SQL Server log, .NET here requests to update columns with two different values: 6.666666666666670e-001 and 6.666666666666666e-001. This differs from .NET accessing a MySQL database, where the requested value is (wrongly) 6.66666666666666960e-001 in _both_ update commands. According to the recent addition by Christopher Gruber, .NET sees the wrong value when using Connector/NET in addition to Connector/ODBC. That would suggest that my guess from August 2004 is probably wrong and that there is in fact something wrong with the way the MySQL server returns DOUBLE values, not the client. For testing that, here's a quick run using the command line clients for both products. MySQL: ------------------- C:\>mysql -h <server hostname> -D TestDb -u root --password=<sa password> mysql> select * from testtable; +----+-------------------+ | Id | DecayRate | +----+-------------------+ | 1 | 0.666666666666667 | | 2 | 0.666666666666667 | +----+-------------------+ 2 rows in set (0.00 sec) ------------------- Microsoft SQL Server: ------------------- C:\>osql -S <server hostname> -d TestDB -U sa -P <sa password> 1> select * from testtable 2> go Id DecayRate ---- ------------------------ 1 0.66666666666666696 2 0.66666666666666663 (2 rows affected) ------------------- Above: Results from the MySQL server are wrong in the command line client too, while results from Microsoft SQL Server are correct (albeit being rounded in a fashion that looks a little odd). "Wrong" in the above sentence means that values returned from MySQL are too heavily rounded. Copy/pasting the value that MySQL returns in the above into a SELECT statement will only yield half of the results expected (namely the entry with Id=1). Obviously this is very similar to the problem that .NET developers are facing, because .NET does exactly this behind your back. Just for laughs and giggles, here's a CLI run that shows that the server itself sees the correct value when doing the SELECT in one big swoop using a subquery. I don't know the internals, for those who do, this may not be surprising. Here it is anyway, just in case anyone else thought of doing this experiment: mysql> SELECT * FROM testtable WHERE DecayRate IN (SELECT DecayRate FROM testtable); +----+-------------------+ | Id | DecayRate | +----+-------------------+ | 1 | 0.666666666666667 | | 2 | 0.666666666666667 | +----+-------------------+ 2 rows in set (0.00 sec) What .NET does: mysql> SELECT * FROM testtable WHERE DecayRate=0.666666666666667; +----+-------------------+ | Id | DecayRate | +----+-------------------+ | 1 | 0.666666666666667 | +----+-------------------+ 1 row in set (0.00 sec) Does anyone know *why* the DOUBLE values returned by SELECT are so aggressively rounded by MySQL?
[9 Nov 2006 12:41]
d di
This bug also causes severe data corruption when restoring backups made with 3rd party mysql backup systems or with mysqldump. To reproduce, run mysqldump on the "testtable" table from this bug report, and then restore the table with another name (eg. "restored"): $ mysqldump testdb testtable > db_dump.sql $ sed 's/testtable/restored/g' -i db_dump.sql $ mysql -D testdb < db_dump.sql Then compare the output of fx. a SELECT command on the original table and the restored table respectively: mysql> SELECT * FROM testtable WHERE DecayRate=0.666666666666667; +----+-------------------+ | Id | DecayRate | +----+-------------------+ | 1 | 0.666666666666667 | +----+-------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM restored WHERE DecayRate=0.666666666666667; +----+-------------------+ | Id | DecayRate | +----+-------------------+ | 1 | 0.666666666666667 | | 2 | 0.666666666666667 | +----+-------------------+ 2 rows in set (0.00 sec)
[23 Nov 2006 20:21]
Bogdan Degtyariov
David, I got the response from developers regarding this bug. They considered this record their TODO, but the fix gets priority dependent on how serious the bug is and how many customers are affected by it. As it is not associated with any support issue, the priority of this bug is lower than ones with support assignments. Unfortunately, I cannot provide any time estimates when it is going to be fixed.
[13 Sep 2007 14:01]
d di
Might I suggest that this bug is fixed and the fixed mode of operation get designated a new SQL_MODE flag? Examples could be "ROUND_ON_INSERT", "NO_ROUND_ON_SELECT" or even "NO_RESULT_ROUNDING". This seems to be how similar MySQL issues are fixed, while remaining backwards compatible with any theoretically existent systems that would depend on the old, broken behaviour. (I'm assuming that is why this bug has been lingering for several years even though it seems to be a one-liner..)
[6 Feb 2008 13:45]
Tonci Grgin
I might not be the best person for closing this report as I opted clearly there is *no* bug here at all... I am not about to quote IEEE specifications and the way floats are stored in CPU. As for .NET framework (and for that matters *all* of MS stuff) DOUBLE is TRUNCATED on MAX 15 decimals when mapped to MS types: http://msdn2.microsoft.com/en-us/library/ae382yt8(VS.80).aspx and I suggest to end this discussion with MS recommendation: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798634&SiteID=1, "Please use DECIMAL". Anyway, our developers decided to "fix" this behavior in 6.0 and it works as David expects: C:\mysql60\bin>mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 6.0.5-alpha-nt-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE testtable ( -> Id TINYINT (3), -> DecayRate DOUBLE NOT NULL, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO testtable (Id, DecayRate) VALUES (1, 0.666666666666667); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO testtable (Id, DecayRate) VALUES (2, 0.6666666666666666); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM testtable WHERE DecayRate = 0.6666666666666666; +----+--------------------+ | Id | DecayRate | +----+--------------------+ | 2 | 0.6666666666666666 | +----+--------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM testtable WHERE DecayRate IN (SELECT DecayRate FROM testtab le); +----+--------------------+ | Id | DecayRate | +----+--------------------+ | 1 | 0.666666666666667 | | 2 | 0.6666666666666666 | +----+--------------------+ 2 rows in set (0.03 sec) mysql> UPDATE testtable SET DecayRate = 0.88888888888888 WHERE DecayRate = 0.666 6666666666666; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM testtable; +----+-------------------+ | Id | DecayRate | +----+-------------------+ | 1 | 0.666666666666667 | | 2 | 0.88888888888888 | +----+-------------------+ 2 rows in set (0.00 sec) mysql>
[6 Feb 2008 14:35]
d di
Tonci, I think you might have misunderstood what is the underlying issue. It is not about DOTNET at all. My fault probably, as the first example I used was a piece of C# code. The reason for that was simply that we experienced massive problems in a number of systems coded in C# when they were switched to use MySQL Server. Anyway, the real issue is that MySQL Server breaks the client's expectations by storing numbers in a higher precision than the client is allowed to see. The rounding is not performed in DOTNET as you suggest, but in MySQL Server. (The simplest explanation I can think of is to say that MySQL "rounds on SELECT", as opposed to every other DBMS on the planet I've seen which does "round on INSERT".) If you need non-DOTNET related evidence, you can choose other examples than the breakage caused to optimistic concurrency. For example, using the test case table, do a mysqldump, restore from the dump. Then compare the results of the "SELECT [...] 0.666666666666667" query against these two tables, and you'll get different results! The "round on SELECT" behaviour causes backups created with mysqldump to contain less data than was in the original database. Reopened for practical reasons, to keep track of when this issue is fixed in a release. About the "bug" word: I agree with you that this is not an implementation bug. Not sure what to call it when a product is designed in a way that causes users of that product to fail. A chocolate teapot being made of chocolate is not a "bug" per se, but there definitely is a design issue, at least if you'd like to drink tea out of it ;-). (Maybe we could dub this a behavioural or design bug^H^H^Hissue or something. All we'd need then would be an appropriate place to mark it as such in the issue tracker...)
[6 Feb 2008 14:38]
d di
By the way, a big THANK YOU to the developer(s) who have finally nailed this one ! :-)
[6 Feb 2008 17:43]
Tonci Grgin
David, I didn't misunderstood anything, I was just being thorough and I think I've addressed all of your questions/concerns. 1) (The simplest explanation I can think of is to say that MySQL "rounds on SELECT", as opposed to every other DBMS on the planet I've seen which does "round on INSERT".) - Fixed in 6.0.5, as you can see from my example. 2) If you need non-DOTNET related evidence, you can choose other examples than the breakage caused to optimistic concurrency. For example, using the test case table, do a mysqldump, restore from the dump. Then compare the results of the "SELECT [...] 0.666666666666667" query against these two tables, and you'll get different results! The "round on SELECT" behaviour causes backups created with mysqldump to contain less data than was in the original database. - Fixed in 6.0.5, tested, works: C:\mysql60\bin>mysqldump --opt -uroot -p test > tst.sql C:\mysql60\bin>mysql -uroot -p test < tst.sql mysql> select * from testtabledmp; +----+--------------------+ | Id | DecayRate | +----+--------------------+ | 1 | 0.666666666666667 | | 2 | 0.6666666666666666 | +----+--------------------+ 2 rows in set (0.00 sec) mysql> select * from testtable; +----+--------------------+ | Id | DecayRate | +----+--------------------+ | 1 | 0.666666666666667 | | 2 | 0.6666666666666666 | +----+--------------------+ 2 rows in set (0.00 sec) mysql> select * from testtable -> union all -> select * from testtabledmp; +----+--------------------+ | Id | DecayRate | +----+--------------------+ | 1 | 0.666666666666667 | | 2 | 0.6666666666666666 | | 1 | 0.666666666666667 | | 2 | 0.6666666666666666 | +----+--------------------+ 4 rows in set (0.00 sec) mysql> select * from testtable -> union -> select * from testtabledmp GROUP BY DecayRate; +----+--------------------+ | Id | DecayRate | +----+--------------------+ | 1 | 0.666666666666667 | | 2 | 0.6666666666666666 | +----+--------------------+ 2 rows in set (0.00 sec) 3) I agree with you that this is not an implementation bug. - IMO using FLOAT(s) this way is a bug in itself and I will not elaborate that. Now, I would like to know why you reopened this report? Will "Fixed in release == 6.0.5" be sufficient for your tracking purposes as I can not keep "bug" open when there's nothing to be fixed? Also, I am always subscribed to all bug reports I've worked on so no need for you to set bug report to "Open" to draw my attention.
[6 Feb 2008 19:44]
d di
> I didn't misunderstood anything, I was just being thorough Oh, okay. I misunderstood the motivation for the dotnet speech, then ;-P. > IMO using FLOAT(s) this way is a bug in itself Perhaps the complaint should be lodged at Microsoft for using FLOAT fields in conjunction with optimistic concurrency. (To their defense, it does work with other DBMSs.) > and I will not elaborate that. Fair enough, issue's fixed, everybody's happy! > Now, I would like to know why you reopened this report? "not a bug" seemed a bit incorrect-ish. I don't have all the options you have mind you - as far as changing status goes, I can pretty much only reset the case to open. (Plus I had this idea that issues that are fixed should "end" with a commit message quoted, but then maybe not). I'll just set the case to "closed" (my other status option ;-)), hope we're all happy with that..
[7 Feb 2008 9:43]
Tonci Grgin
David, thanks for being constructive and patient. We do appreciate you input very much.