pt-table-checksum's --float-precision does not work for very small / large values

Reported by Johannes W on 2013-03-17
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

Hello,

we use pt-table-checksum for a fairly large database containing measured values stored as DOUBLE. For very large or small values the ROUND() MySQL function used by --float-precision does not work as expected.
E.g. on the master it maps very small values to "0.0", while on the slave it maps them to "-0.0" (resulting in a different crc32). Or for large values, e.g. on the master and slave the significant digits of the mantissa are different after ROUND().

We have a patch that introduces a new command-line option to activate a different rounding algorithm that solves both problems. However, it is slower than the simple ROUND(), for that reason and backwards-compatibility a new option is probably better:

    IF($result = 0, 0, SIGN($result) * ROUND(LOG2(ABS($result)), $float_precision))

Would you be willing to accept such a patch in general? On which branch should it be based? If there is a chance to get it integrated upstream, we can provide concrete examples and further explanation.

Regards,

Johannes Weißl and Sebastian Egner

Daniel Nichter (daniel-nichter) wrote :

Hello Johannes,

Yes it's possible we could include this patch or change how --float-precision works. You can branch https://code.launchpad.net/~percona-toolkit-dev/percona-toolkit/2.2

See a concrete example would help. If the new algo is only a little slower but fixes a common problem, it's probably worth the tradeoff.

tags: added: float-precision
removed: double float large precision round small values
Changed in percona-toolkit:
status: New → Confirmed
Johannes W (jmuc) wrote :

Hello Daniel,

thanks for the response! The replication is between MySQL 5.1.52 (Windows, Master)
and 5.1.66 (Linux, Slave). Here are two examples:

If I insert A = -5.32907051820075e-015 and B = -1.1655233978208e+024 into a double
column on the master, they are not binary equal on the slave, i.e. they have a
different CRC32().

ROUND(col, 2) on the master results in A' = 0.00 and B' = -1165523397820799900000000.00,
while on the slave it results in A'' = -0.00 and B'' = -1165523397820799938199552.00, so
they have still a different CRC32().

With the new "rounding-with-logarithm" method both on the master and slave
A_log = 47.42 and B_log = -79.95.

The patch is available for merging at lp:~jmuc/percona-toolkit/float-log-precision.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers