Bug #30041 CHECKSUM TABLE reports different results for tables with FLOAT/DOUBLE columns
Submitted: 25 Jul 2007 14:16 Modified: 4 Feb 2008 19:42
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.44 OS:Any
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: bfsm_2007_08_02

[25 Jul 2007 14:16] Mark Leith
Using CHECKSUM TABLE ... EXTENDED across replication set ups with statement based replication causes unpredictable results, with the checksum value created returning differently for otherwise exact copies of tables. 

For instance, on 'replication server one', with some debug code, we can see output such as:

OTHER DCF99F42 len=4

crc = 1694539836

And on 'replication server two' for the same row, we see:

OTHER DBF99F42 len=4

crc = 1677762620

F4FD1F42 versus F6FD1F42 corresponding to 39.998008728027344 vs 39.99800109863281 within IEEE-754 format. 

As is seen, this affects the checksum of the column (and hence row). The actual value displayed on both servers for the above row is:

mysql> select amount from t1 limit 179,1;
| amount |
| 39.998 |
1 row in set (0.00 sec)

The tables also dump to a file from mysqldump that report no diff's across the replication servers, so the data is otherwise 'precise'.

How to repeat:
Only reproduced in debugging, see follow up discussion.

Suggested fix:
For FLOAT/DOUBLE columns, truncate/round the values to their default or configured precision, and then take the CRC check of the column value, instead of taking the checksum on the full float precision.
[31 Jul 2007 22:08] Sergei Golubchik
Just to comment, if CHECKSUM TABLE reports tables as different, they *are* different, and there could be queries that will return different results on these tables. This behavior is of course intentional, and it's why CHECKSUM TABLE was created in the first place - to be able to determine whether two tables are identical or different.
[2 Aug 2007 19:29] Alexey Botchkov
These tables are really different, and it's right that the checksum shows
this difference. So it's not a bug.
Though the problem is that these tables have to be equal.
Now we plan to solve it in 5.2.
[13 Dec 2007 10:30] Sergei Golubchik
I expect this to be fixed by the patch for wl#2934 (already pushed)
[14 Dec 2007 7:24] Alexey Botchkov
Fixed in 6.0 whith the patch to wl#2934.
[3 Feb 2008 16:59] Alexey Botchkov
The problem is that tables that are same from the 'SQL' point of view can differ on 'binary' level as same numbers can be differently represented in DOUBLE field. So the same tables can have different checksums. It is a bug, just not a bug in the checksum calculation but in the way we handle doubles.
That was fixed in 6.0 with the special functions to avoid it.
[4 Feb 2008 19:42] Paul DuBois
Noted in 6.0.5 changelog.

For tables with FLOAT or DOUBLE columns, CHECKSUM TABLE could report
different results on master and slave servers.