box / box/Anemometer

pt-query-digest 3.0.11 changed `checksum` from BIGINT UNSIGNED to CHAR(32)

Open
#200 5 comments 1 reaction 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.