pt-table-checksum and differences exponentials windows/linux

[ErikEngerd]

Hi,


I am using a slave on linux Centos 6.2 (mysql 5.1.61) with the master on windows (5.0.82-community-nt-log). I would like to reverse the roles of master and slave and insert a centos DVD into the windows machine and reinstall it, but.. the windows machine is running the production database so I would like to be certain that the data is identical (enough).

Now I am running into this bug http://bugs.mysql.com/bug.php?id=12860

I can see it my just typing: 'select 1e-7' in the mysql command-line on both servers and this leads to three digits (1e-007) on windows and two (1e-07) on linux.

Do you know of any fix for this problem? Also, if I would fix this for pt-table-checksum then I would perhaps also need a similar for for pt-table-sync.

Any ideas on how to work around this problem?

Cheers
Erik

[ErikEngerd]

I should mention that I was already using the --float-precision 6 option to pt-table-checksum.
Also, selecting a single row which is different (according to pt-table-sync --print only shows a difference in the formatting of this single floating point value. But rounding the value on both windows and linux leads to the same result.

Could it be that the problem is in pt-table-sync instead?


What could be wrong here?

[ErikEngerd]

I have now found one table for which there is only one difference reported from pt-table-checksum. Using pt-table-sync I can print the SQL that would update the row and extract the primary key of that row. If I then select this row from both the master and the slave in a mysql command prompt I see no differences. I verified this by cutting and pasting the output of the command on master and server and then using standard unix diff to check for differences.

What could be the problem here? All I can think of is some compatibility issue between windows and linux.

Any ideas?

[ErikEngerd]

Also the hashing function does not have any effect. Next to the default I also used MD5 and SHA1. I am using percona toolkit 2.1.2-1 by the way.

[ErikEngerd]

Hmmm... I was troubleshooting the hashing query as output from pt-table-sync and I found that it is just a floating point rounding issue.

What would be needed is accurate replication of floating point numbers. The rounding solution is also problematic at best. For one particular table I have found that rounding to 8 decimals is the sweet spot giving no differences, but using 6 or 10 give even more differences.

What would be needed is a hash function that results in the same value of any two floats for which abs(f1-f2) < eps but I think that that is a logical impossibility because if abs(f2-f3) < eps also holds then f1, f2, and f3 should get the same hash value, basically forcing a hash function that results in a constant value for any float (equivalent to ignoring the float value).

The current linux slave also has another (remote) linux slave and the replication between these two seems not to be affected by rounding issues as I found no differences between these two hosts.