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

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

#201

Hi all,

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

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

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.

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

Navigation Menu

Repository navigation

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

Desription

How to repeat

Suggested Fix

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Desription

How to repeat

Suggested Fix

Activity

ashatrov commented on Mar 12, 2019

koleo commented on Mar 15, 2019

wavelet123 commented on Sep 23, 2019

hummermania commented on Oct 25, 2019

hummermania commented on Oct 25, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

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