pt-table-sync quotes float values, can't sync

[Damon]

SQL:
DELETE FROM `tkhk`.`cache_tendersummary_loc` WHERE `officeid`='28401' AND `branchid`='20401' AND `tendertypeid`='1' AND `tenderclassificationid`='4' AND `tenderprem`='85' AND `tenderunit`='882' AND `tendernum`='87' AND `tendervalue`='5578720' AND `tendernummonth`='0' AND `tendervaluemonth`='0' AND `targetmonth`=6966.67 AND `tendernumyear`='0' AND `tendervalueyear`='0' AND `targetyear`=27866.7 AND `opportunityprem`='251' AND `opportunityunit`='2232' AND `opportunitynum`='298' AND `opportunityvalue`='4585270' AND `alltimetendernum`='303' AND `alltimetendervalue`='99798240' AND `alltimewonnum`='38' AND `alltimewonvalue`='6103773' AND `alltimelostnum`='178' AND `alltimelostvalue`='88115744' AND `alltimeclosednum` IS NULL AND `alltimeclosedvalue` IS NULL AND `tenderclosedmonth` IS NULL AND `valueclosedmonth` IS NULL AND `tenderclosedyear` IS NULL AND `valueclosedyear` IS NULL AND `tendercreatedmonth`='3' AND `valuecreatedmonth`='54920' AND `tendercreatedyear`='11' AND `valuecreatedyear`='590720' AND `valuewonmonth`='0' AND `valuelostmonth`='0' AND `valueunresolvedmonth`='5560950' AND `valuewonyear`='0' AND `valuelostyear`='0' AND `valueunresolvedyear`='5560950' AND `unitwonmonth`='0' AND `unitlostmonth`='0' AND `unitunresolvedmonth`='816' AND `unitwonyear`='0' AND `unitlostyear`='0' AND `unitunresolvedyear`='816' AND `tenderwonmonth`='0' AND `tenderlostmonth`='0' AND `tenderunresolvedmonth`='86' AND `tenderwonyear`='0' AND `tenderlostyear`='0' AND `tenderunresolvedyear`='86' LIMIT 1 /*percona-toolkit src_db:tkhk src_tbl:cache_tendersummary_loc src_dsn:A=utf8,h=192.168.1.153,p=...,u=pttool dst_db:tkhk dst_tbl:cache_tendersummary_loc dst_dsn:A=utf8,h=192.168.1.154,p=...,u=pttool lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:65398 user:root host:Damon*/;

Pt-table-sync version: 2.2.11

issue sql :
AND `targetmonth`=6966.67
AND `targetyear`=27866.7

targetmonth and targetyear type: float

[Damon (894615808-b)]

Command : pt-table-sync --execute --charset=utf8 --no-check-triggers --no-check-slave --no-foreign-key-checks --databases tkhk --tables cache_tendersummary_loc h=192.168.1.153,u='xxxx',p='xxxxxxx' h=192.168.1.154,u='xxxxx',p='xxxxxxxxxx'

[Frank Cizmich (frank-cizmich)]

Hello Damon,

What indexes/keys does this table have? Can you please post the result of "SHOW CREATE TABLE"?

It seems pt-table-sync is trying to match floating point values, and that usually is problematic due to rounding issues and the machine's differing internal representation of these numbers across systems.

Regards

[Damon (894615808-b)]

Hi Frank

I can find the record in my databases , I did't find the float data have different between delete sql and the record . so i think shouldn't rounding issues . there is "Show create table" following.

CREATE TABLE `cache_tendersummary_loc` (
  `OfficeID` int(10) DEFAULT NULL,
  `BranchID` int(10) DEFAULT NULL,
  `TenderTypeID` int(10) DEFAULT NULL,
  `TenderClassificationID` int(10) DEFAULT NULL,
  `TenderPrem` int(10) DEFAULT NULL,
  `TenderUnit` int(10) DEFAULT NULL,
  `TenderNum` int(10) DEFAULT NULL,
  `TenderValue` decimal(22,0) DEFAULT NULL,
  `TenderNumMonth` int(10) DEFAULT NULL,
  `TenderValueMonth` int(10) DEFAULT NULL,
  `TargetMonth` float DEFAULT NULL,
  `TenderNumYear` int(10) DEFAULT NULL,
  `TenderValueYear` int(10) DEFAULT NULL,
  `TargetYear` float DEFAULT NULL,
  `OpportunityPrem` int(10) DEFAULT NULL,
  `OpportunityUnit` int(10) DEFAULT NULL,
  `OpportunityNum` int(10) DEFAULT NULL,
  `OpportunityValue` decimal(22,0) DEFAULT NULL,
  `AllTimeTenderNum` int(10) DEFAULT NULL,
  `AllTimeTenderValue` decimal(22,0) DEFAULT NULL,
  `AllTimeWonNum` decimal(10,0) DEFAULT NULL,
  `AllTimeWonValue` decimal(22,0) DEFAULT NULL,
  `AllTimeLostNum` decimal(10,0) DEFAULT NULL,
  `AllTimeLostValue` decimal(22,0) DEFAULT NULL,
  `AllTimeClosedNum` decimal(10,0) DEFAULT NULL,
  `AllTimeClosedValue` decimal(22,0) DEFAULT NULL,
  `TenderClosedMonth` decimal(10,0) DEFAULT NULL,
  `ValueClosedMonth` decimal(22,0) DEFAULT NULL,
  `TenderClosedYear` decimal(10,0) DEFAULT NULL,
  `ValueClosedYear` decimal(22,0) DEFAULT NULL,
  `TenderCreatedMonth` decimal(10,0) DEFAULT NULL,
  `ValueCreatedMonth` decimal(22,0) DEFAULT NULL,
  `TenderCreatedYear` decimal(10,0) DEFAULT NULL,
  `ValueCreatedYear` decimal(22,0) DEFAULT NULL,
  `ValueWonMonth` decimal(22,0) DEFAULT NULL,
  `ValueLostMonth` decimal(22,0) DEFAULT NULL,
  `ValueUnresolvedMonth` decimal(22,0) DEFAULT NULL,
  `ValueWonYear` decimal(22,0) DEFAULT NULL,
  `ValueLostYear` decimal(22,0) DEFAULT NULL,
  `ValueUnresolvedYear` decimal(22,0) DEFAULT NULL,
  `UnitWonMonth` decimal(22,0) DEFAULT NULL,
  `UnitLostMonth` decimal(22,0) DEFAULT NULL,
  `UnitUnresolvedMonth` decimal(22,0) DEFAULT NULL,
  `UnitWonYear` decimal(22,0) DEFAULT NULL,
  `UnitLostYear` decimal(22,0) DEFAULT NULL,
  `UnitUnresolvedYear` decimal(22,0) DEFAULT NULL,
  `TenderWonMonth` decimal(22,0) DEFAULT NULL,
  `TenderLostMonth` decimal(22,0) DEFAULT NULL,
  `TenderUnresolvedMonth` decimal(22,0) DEFAULT NULL,
  `TenderWonYear` decimal(22,0) DEFAULT NULL,
  `TenderLostYear` decimal(22,0) DEFAULT NULL,
  `TenderUnresolvedYear` decimal(22,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thanks and regards,
Damon

[Frank Cizmich (frank-cizmich)]

Hi Damon,

There are two different issues at play here. Both are related to floating point.

First issue:

To detect differences in floating point columns, the tool converts first to string, but due to rounding errors and internal representation of data , it might still detect differences (even when not apparent with a simple manual select).
This can be alleviated using the --float-precision option:
http://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html#cmdoption-pt-table-sync--float-precision

Second issue:

The tool prefers to match rows using primary or unique keys, but this table has none; so it resorts to trying to match every single column value.
Again, this bring up the floating point issue. Matching floating point columns is very difficult due to the arbitrary precision and internal representation.
The best solution for this is to add a (non float) primary key for this table. It is also good practice.
If this is not an option you could try converting the problematic floats to decimal type. Decimal types do indeed match precisely across versions and platforms.

Check out this link for more info about the problems of matching floating point values:
http://dev.mysql.com/doc/refman/5.6/en/problems-with-float.html

Cheers,
   Frank

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1268