pt-table-sync print update sql for one row of table but they are totally same between master and slave

[Alber]

Mysql version : 5.5.32-log MySQL Community Server (GPL)
percona toolkit version : 2.2.11

pt-table-sync --print --charset=utf8 --no-check-triggers --function=MD5 --no-check-slave --no-foreign-key-checks -d *** -t check_table h=Master_IP,u='pt_user',p='****' h=****

UPDATE `***`.`check_table` SET `docketno`='392933', `visitedunitnumber`='65602496000200001', `timesheetid`='322937', `ah(total mins)`='120', `bh(total mins)`='1020', `avgmanhourperunit`=6.333333333333333, `activitytype`='M', `employeeid`='226714/78416', `employeename`='Fatih Aydin/Fatih Akoglu', `buildingname`='KERVANSARAY LARA GÜZELOBA TRT KAMPI YANI KUNDU
', `createddate`='2015-10-19 16:15:04', `arrivaldate`='2015-10-06 09:00:00', `branchid`='914', `structureid`='2', `oliyear`='2015', `olimonth`='10', `oliday`='25', `versionno`='TR20151025_P', `processingdate`='2015-10-25' WHERE `id`='396179' LIMIT 1 /*percona-toolkit src_db:*** src_tbl:check_table src_dsn:A=utf8,h=Master_IP,p=...,u=pt_user dst_db:*** dst_tbl:check_table dst_dsn:A=utf8,h=****,p=...,u=pt_user lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:4100 user:root host:SEAME-ServiceRDB*/;

mysql> show create table check_table\G;
*************************** 1. row ***************************
       Table: check_table
Create Table: CREATE TABLE `check_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DocketNo` varchar(36) DEFAULT NULL,
  `VisitedUnitNumber` varchar(100) DEFAULT NULL,
  `TimesheetID` int(10) DEFAULT NULL,
  `AH(Total Mins)` int(10) DEFAULT NULL,
  `BH(Total Mins)` int(10) DEFAULT NULL,
  `AvgManhourPerUnit` double DEFAULT NULL,
  `ActivityType` varchar(30) DEFAULT NULL,
  `EmployeeID` varchar(255) DEFAULT NULL,
  `EmployeeName` varchar(150) DEFAULT NULL,
  `BuildingName` varchar(255) DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ArrivalDate` datetime DEFAULT NULL,
  `BranchID` int(10) DEFAULT NULL,
  `StructureID` int(10) DEFAULT NULL,
  `OLIYear` int(11) NOT NULL,
  `OLIMonth` int(11) NOT NULL,
  `OLIDay` int(11) NOT NULL,
  `VersionNo` varchar(50) DEFAULT NULL,
  `ProcessingDate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `processingDate` (`ProcessingDate`),
  KEY `BranchID` (`BranchID`)
) ENGINE=InnoDB AUTO_INCREMENT=579631 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> desc check_table;
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| DocketNo | varchar(36) | YES | | NULL | |
| VisitedUnitNumber | varchar(100) | YES | | NULL | |
| TimesheetID | int(10) | YES | | NULL | |
| AH(Total Mins) | int(10) | YES | | NULL | |
| BH(Total Mins) | int(10) | YES | | NULL | |
| AvgManhourPerUnit | double | YES | | NULL | |
| ActivityType | varchar(30) | YES | | NULL | |
| EmployeeID | varchar(255) | YES | | NULL | |
| EmployeeName | varchar(150) | YES | | NULL | |
| BuildingName | varchar(255) | YES | | NULL | |
| CreatedDate | datetime | YES | | NULL | |
| ArrivalDate | datetime | YES | | NULL | |
| BranchID | int(10) | YES | MUL | NULL | |
| StructureID | int(10) | YES | | NULL | |
| OLIYear | int(11) | NO | | NULL | |
| OLIMonth | int(11) | NO | | NULL | |
| OLIDay | int(11) | NO | | NULL | |
| VersionNo | varchar(50) | YES | | NULL | |
| ProcessingDate | date | YES | MUL | NULL | |
+-------------------+------------------+------+-----+---------+----------------+

data in slave:
mysql> select * from check_table where id=396179\G;
*************************** 1. row ***************************
               id: 396179
         DocketNo: 392933
VisitedUnitNumber: 65602496000200001
      TimesheetID: 322937
   AH(Total Mins): 120
   BH(Total Mins): 1020
AvgManhourPerUnit: 6.333333333333333
     ActivityType: M
       EmployeeID: 226714/78416
     EmployeeName: Fatih Aydin/Fatih Akoglu
     BuildingName: KERVANSARAY LARA GÜZELOBA TRT KAMPI YANI KUNDU

      CreatedDate: 2015-10-19 16:15:04
      ArrivalDate: 2015-10-06 09:00:00
         BranchID: 914
      StructureID: 2
          OLIYear: 2015
         OLIMonth: 10
           OLIDay: 25
        VersionNo: TR20151025_P
   ProcessingDate: 2015-10-25
1 row in set (0.00 sec)

ERROR:
No query specified

data in master:
mysql> select * from check_table where id=396179\G;
*************************** 1. row ***************************
               id: 396179
         DocketNo: 392933
VisitedUnitNumber: 65602496000200001
      TimesheetID: 322937
   AH(Total Mins): 120
   BH(Total Mins): 1020
AvgManhourPerUnit: 6.333333333333333
     ActivityType: M
       EmployeeID: 226714/78416
     EmployeeName: Fatih Aydin/Fatih Akoglu
     BuildingName: KERVANSARAY LARA GÜZELOBA TRT KAMPI YANI KUNDU

      CreatedDate: 2015-10-19 16:15:04
      ArrivalDate: 2015-10-06 09:00:00
         BranchID: 914
      StructureID: 2
          OLIYear: 2015
         OLIMonth: 10
           OLIDay: 25
        VersionNo: TR20151025_P
   ProcessingDate: 2015-10-25
1 row in set (0.00 sec)

ERROR:
No query specified

[Frank Cizmich (frank-cizmich)]

Hi Alber,

could you try using this option:

--float-precision 5

https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html#cmdoption-pt-table-sync--float-precision

if that doesn't work, could you check if the charset and collation are the same on both tables?

show create table check_table;
show table status where Name='check_table'\G

[Alber (yaolei3)]

Hi Frank,

thanks very much for your quick response!

it doesn't work even applied the parameter you suggested.

also i double confirm the table structure of master and slave, they are same,only the value of auto-increment is different.

Slave:
mysql> show table status where Name='check_table'\G;
*************************** 1. row ***************************
           Name: check_table
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 432629
 Avg_row_length: 224
    Data_length: 97124352
Max_data_length: 0
   Index_length: 14712832
      Data_free: 5242880
 Auto_increment: 596779
    Create_time: 2015-07-20 21:30:50
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.10 sec)

Master:
mysql> show table status where Name='check_table'\G;
*************************** 1. row ***************************
           Name: check_table
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 431160
 Avg_row_length: 225
    Data_length: 97124352
Max_data_length: 0
   Index_length: 14712832
      Data_free: 5242880
 Auto_increment: 600462
    Create_time: 2014-09-30 16:05:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.08 sec)

mysql> show create table check_table\G;
*************************** 1. row ***************************
       Table: check_table
Create Table: CREATE TABLE `check_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DocketNo` varchar(36) DEFAULT NULL,
  `VisitedUnitNumber` varchar(100) DEFAULT NULL,
  `TimesheetID` int(10) DEFAULT NULL,
  `AH(Total Mins)` int(10) DEFAULT NULL,
  `BH(Total Mins)` int(10) DEFAULT NULL,
  `AvgManhourPerUnit` double DEFAULT NULL,
  `ActivityType` varchar(30) DEFAULT NULL,
  `EmployeeID` varchar(255) DEFAULT NULL,
  `EmployeeName` varchar(150) DEFAULT NULL,
  `BuildingName` varchar(255) DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ArrivalDate` datetime DEFAULT NULL,
  `BranchID` int(10) DEFAULT NULL,
  `StructureID` int(10) DEFAULT NULL,
  `OLIYear` int(11) NOT NULL,
  `OLIMonth` int(11) NOT NULL,
  `OLIDay` int(11) NOT NULL,
  `VersionNo` varchar(50) DEFAULT NULL,
  `ProcessingDate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `processingDate` (`ProcessingDate`),
  KEY `BranchID` (`BranchID`)
) ENGINE=InnoDB AUTO_INCREMENT=596779 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Master:
mysql> show create table check_table\G;
*************************** 1. row ***************************
       Table: check_table
Create Table: CREATE TABLE `check_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DocketNo` varchar(36) DEFAULT NULL,
  `VisitedUnitNumber` varchar(100) DEFAULT NULL,
  `TimesheetID` int(10) DEFAULT NULL,
  `AH(Total Mins)` int(10) DEFAULT NULL,
  `BH(Total Mins)` int(10) DEFAULT NULL,
  `AvgManhourPerUnit` double DEFAULT NULL,
  `ActivityType` varchar(30) DEFAULT NULL,
  `EmployeeID` varchar(255) DEFAULT NULL,
  `EmployeeName` varchar(150) DEFAULT NULL,
  `BuildingName` varchar(255) DEFAULT NULL...

[Frank Cizmich (frank-cizmich)]

Hi Abel

I really expected the --float-precision to do the trick.

That "6.333333333333333" value looks exactly like the kind of thing that might cause this.

Could you try with a lower value so we can rule it out definitely? like "--float-precision=2" ?

[Shahriyar Rzayev (rzayev-sehriyar)]

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