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

[yoku0825]

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 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

[ashatrov]

#201

[koleo]

Hi all,

I just pushed a new PR #202 with a more complete patch than #201.

[wavelet123]

if clicked the checksum link,there will be "Unknown checksum" error,is this related with this field type change?

[hummermania]

if clicked the checksum link,there will be "Unknown checksum" error,is this related with this field type change?

Because we also have another yet transform for 'checksum' field:
See config/sample.config.inc.php around this

'callbacks' => array(
'table' => array(
'date' => function ($x) { $type=''; if ( date('N',strtotime($x)) >= 6) { $type = 'weekend'; } return array($x,$type); },
'checksum' => function ($x) { return array(dec2hex($x), ''); }
)
)

But quickly replace checksum callback to return checksum field without changing - has no effect.
And we quickly try durty fix it in views/report_result.php:

           if (isset($callbacks)) {
               foreach ($callbacks as $fxname => $fx) {
                  if ($fxname == 'checksum') {       <---
                       continue;
                   }    
                   if (array_key_exists($fxname, $row)) {

But this is not tested and we don't know - maybe this fix can breake another places.

[hummermania]

Hmmm no, maybe more cleaner - to disable/remove callback for 'checksum' field.