pt-query-digest 3.0.11 changed checksum from BIGINT UNSIGNED to CHAR(32) #200
Description
Desription
pt-query-digest 3.0.11 and later calculates query-checksum as 32 chars (was 16 chars and cast to BIGINT UNSIGNED)
Starting from Percona Toolkit 3.0.11, the checksum function has been updated to use 32 chars in the MD5 sum. This causes the checksum field in the history table will have a different value than in the previous versions of the tool.
And then, query-checksums which are generated by t-query-digest 3.0.11 and later couldn't be stored correctly.
This makes to break query aggregation.
mysql> INSERT INTO `slow_query_log`.`global_query_review`
-> (checksum, fingerprint, sample, first_seen, last_seen)
-> VALUES('ADD660F67EF8C8E84629F9AE383EA2F1', 'insert into t? select ? from t?', 'INSERT INTO t1 SELECT 0 FROM t1', COALESCE('2019-01-29 10:32:15', NOW()), COALESCE('2019-01-29 10:33:13', NOW()))
-> ON DUPLICATE KEY UPDATE
-> first_seen = IF(
-> first_seen IS NULL,
-> COALESCE('2019-01-29 10:32:15', NOW()),
-> LEAST(first_seen, COALESCE('2019-01-29 10:32:15', NOW()))),
-> last_seen = IF(
-> last_seen IS NULL,
-> COALESCE('2019-01-29 10:33:13', NOW()),
-> GREATEST(last_seen, COALESCE('2019-01-29 10:33:13', NOW())));
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'ADD660F67EF8C8E84629F9AE383EA2F1' for column 'checksum' at row 1 |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
How to repeat
Following steps in README.md with pt-query-digest >= 3.0.11.
Suggested Fix
install.sql should be changed datatype to CHAR(32) .
And some behaviors (ex. using translate_checksum ) have to be fixed