pt-query-digest 3.0.11 changed `checksum` from BIGINT UNSIGNED to CHAR(32)
- Dominant language
- JavaScript
- Stars
- 1.4k
- Forks
- 306
- PR merge metrics
- No merged PRs in 30d
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.
https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html#cmdoption-pt-query-digest-history
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](https://github.com/box/Anemometer/blob/1a4cca940046a465065e7388d4789cfc0d424485/README.md) with pt-query-digest >= 3.0.11.
# Suggested Fix
[install.sql](https://github.com/box/Anemometer/blob/1a4cca940046a465065e7388d4789cfc0d424485/install.sql) should be changed datatype to `CHAR(32)` .
And some behaviors (ex. using `translate_checksum` ) have to be fixed
Contributor guide
Assessment
This issue has not been assessed yet.