this_crc is 0 in master and slave , and the table have diff record but pt-table-sync and pt-table-checksums no check out that.

[Damon]

Master Result:
+----------+--------+------------------------+
| TenderID | UnitID | TenderClassificationID |
+----------+--------+------------------------+
| 12190 | 17924 | 3 |
| 12190 | 17925 | 3 |
| 12190 | 17926 | 3 |
| 12190 | 17927 | 3 |
| 12191 | 17924 | 2 |
| 12191 | 17925 | 2 |
| 12191 | 17926 | 2 |
| 12191 | 17927 | 2 |
+----------+--------+------------------------+

Slave result:
+----------+--------+------------------------+
| TenderID | UnitID | TenderClassificationID |
+----------+--------+------------------------+
| 12190 | 17924 | 3 |
| 12190 | 17925 | 3 |
| 12190 | 17926 | 3 |
| 12190 | 17927 | 3 |
| 12191 | 17924 | 3 |
| 12191 | 17925 | 3 |
| 12191 | 17926 | 3 |
| 12191 | 17927 | 3 |
+----------+--------+------------------------+

Table structure:
Create Table: CREATE TABLE `tender_unit` (
  `TenderID` int(10) unsigned NOT NULL DEFAULT '0',
  `UnitID` int(10) unsigned NOT NULL DEFAULT '0',
  `TenderClassificationID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`TenderID`,`UnitID`),
  KEY `UnitID` (`UnitID`),
  KEY `TenderClassificationID` (`TenderClassificationID`),
  CONSTRAINT `tender_unit_ibfk_1` FOREIGN KEY (`TenderClassificationID`) REFERENCES `tenderclassification` (`id`),
  CONSTRAINT `tender_unit_ibfk_2` FOREIGN KEY (`TenderID`) REFERENCES `tender` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Command:
pt-table-sync --print --charset=utf8 --no-check-triggers --no-check-slave --no-foreign-key-checks --databases tksing --tables tender_unit h=10.126.0.120,u='pttool',p='9CrxYWm3a' h=10.126.0.118,u='pttool',p='9CrxYWm3a'

mysql> select COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `TenderID`, `UnitID`,`TenderClassificationID`)) AS UNSIGNED)), 10, 16)), 0) AS crc from tender_unit tu where tu.TenderID=12190 or tu.TenderID=12191;
+------+
| crc |
+------+
| 0 |
+------+

checksum result:
+--------+-------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+--------+-------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| tksing | tender_unit | 1 | 0.09623 | NULL | NULL | NULL | dd3ad519 | 51415 | dd3ad519 | 51415 | 2015-05-13 17:24:19 |
+--------+-------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

[Frank Cizmich (frank-cizmich)]

Hello Damon,

I've tried the sql command on the sample data you provide and the crc does not return 0.
You are trying to sync two independent servers , right? (not master - slave)

If the problem persists, can you please run again and attach PTDEBUG output log file?

(running like this)
PTDEBUG=1 [command] 2> logfile

[Damon (894615808-b)]

Hi Frank

those server is master and slave , and i run this sql command in other slave server but it always return 0 to me .

i've already put debug log in there , Thanks

Thanks a lot,
Damon

[Frank Cizmich (frank-cizmich)]

Thanks for the log data.

If they are master and slave, the recommended way to sync them is using the --sync-to-master option

In this case it would be (supposing 10.126.0.120 is the slave, otherwise use 10.126.0.118) :

pt-table-sync --print --charset=utf8 --no-check-triggers --no-foreign-key-checks --databases tksing --tables tender_unit --sync-to-master h=10.126.0.120,u='pttool',p='9CrxYWm3a'

Checkout
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html#usage

Also note that the --print option will make the tool just print the queries needed to run on master to sync the servers, not actually execute them <- you probably were aware of this, but I mention it just in case :-)

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

[Damon (894615808-b)]

Hi Frank, thank you suggestion.

10.126.0.118 is slave server, so i run in master(10.126.0.120) server and used command:
pt-table-sync --print --charset=utf8 --no-check-triggers --no-foreign-key-checks --databases tksing --tables tender_unit --sync-to-master h=10.126.0.118,u='pttool',p='9CrxYWm3a'

but still can't print the different record, and the different record always exist in master and slave.

Thanks,
Damon

[Frank Cizmich (frank-cizmich)]

Hi Damon,

After quite a while I managed to reproduce the issue.
Wow, seems like you ran into some really unfortunate data combination that indeed threw the default checksum function off (CRC32). Any change in the data made it work (adding or deleting a row, changing any value).

Please try adding this option: --function=MD5

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

This made it work for me. You can also use SHA1

[Damon (894615808-b)]

Hi Frank

As you suggestion, I can print the different record when used pt-table-sync. and pt-table-checksum can use this option like pt-table-sync right ?

Thank you very much for solving my puzzles for trouble .

Thank a lot,
Damon

[Frank Cizmich (frank-cizmich)]

Yes, pt-table-checksum has the same option.

https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--function

If you use both tools in combination (first pt-table-checksum and then pt-table-sync with --replication option) be sure to use the same option on both.

Cheers!

[Shahriyar Rzayev (rzayev-sehriyar)]

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