Pt-table-sync fails with certain compound primary keys

Polyglot Projects Percona Toolkit
1

I’ve noticed pt-table-sync getting into a loop, executing the same query over and over again.
All those tables have compound primary keys with an ENUM type.

For example:

CREATE TABLE `fbcompleteparse` (
  `ELEMENT` enum('location','organization') NOT NULL DEFAULT 'organization',
  `ID` int(10) unsigned NOT NULL DEFAULT 0,
  `DONESTAMP` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`ELEMENT`,`ID`)
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1

Running pt-table-sync as so:

pt-table-sync -vv --print --databases party_db --function MD5 --execute --sync-to-master h=localhost,u=root,p=***,D=party_db,t=fbcompleteparse

And this shows the same query over and over again:

# SELECT /*party_db.fbcompleteparse:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONCAT(LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(@crc, 1, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0'), LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(@crc := MD5(CONCAT_WS('#', `element`, `id`, `donestamp`)), 17, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0'))), 0) AS crc FROM `party_db`.`fbcompleteparse` FORCE INDEX (`PRIMARY`) WHERE ((((`element` > CAST('organization' AS UNSIGNED)) OR (`element` = CAST('organization' AS UNSIGNED) AND `id` > '10441')) AND ((`element` < CAST('organization' AS UNSIGNED)) OR (`element` = CAST('organization' AS UNSIGNED) AND `id` <= '10441'))))
# SELECT /*party_db.fbcompleteparse:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONCAT(LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(@crc, 1, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0'), LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(@crc := MD5(CONCAT_WS('#', `element`, `id`, `donestamp`)), 17, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0'))), 0) AS crc FROM `party_db`.`fbcompleteparse` FORCE INDEX (`PRIMARY`) WHERE ((((`element` > CAST('organization' AS UNSIGNED)) OR (`element` = CAST('organization' AS UNSIGNED) AND `id` > '10441')) AND ((`element` < CAST('organization' AS UNSIGNED)) OR (`element` = CAST('organization' AS UNSIGNED) AND `id` <= '10441'))))
# SELECT /*party_db.fbcompleteparse:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONCAT(LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(@crc, 1, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0'), LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(@crc := MD5(CONCAT_WS('#', `element`, `id`, `donestamp`)), 17, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0'))), 0) AS crc FROM `party_db`.`fbcompleteparse` FORCE INDEX (`PRIMARY`) WHERE ((((`element` > CAST('organization' AS UNSIGNED)) OR (`element` = CAST('organization' AS UNSIGNED) AND `id` > '10441')) AND ((`element` < CAST('organization' AS UNSIGNED)) OR (`element` = CAST('organization' AS UNSIGNED) AND `id` <= '10441'))))

I know groupby algorithm works, but is too slow on large tables.

Has anyone encounterwd this same problem and maybe solved it?

1 Like