Bug #83253 performance_schema digests the same query to multiple different digests
Submitted: 4 Oct 2016 14:02 Modified: 20 Dec 2016 19:27
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S1 (Critical)
Version:8 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance_schema

[4 Oct 2016 14:02] Justin Swanhart 
Description:
Looking at sys.statement_analysis I see the same query 'SET NAMES `UTF8` ;' reported multiple times with multiple digests.

How to repeat:
MySQL [(none)]> select distinct db,query,digest from sys.statement_analysis where query = 'SET NAMES `UTF8` ;';
+-------------+---------------------+----------------------------------+
| db          | query               | digest                           |
+-------------+---------------------+----------------------------------+
| NULL        | SET NAMES `UTF8` ;  | 4de6d3c80ee7ebf9d06738595660c343 |
| NULL        | SET NAMES `UTF8` ;  | 54ce498e03a5f904bf4f022646bf08e3 |
| NULL        | SET NAMES `UTF8` ;  | a33152a77b945f338da09c97071fbe89 |
| NULL        | SET NAMES `UTF8` ;  | afd800e623b90886ac7e3562538c1c7e |
| NULL        | SET NAMES `UTF8` ;  | bdb8cb2e9d9b839639220c0759fbb008 |
| NULL        | SET NAMES `UTF8` ;  | c3a8a2851bf6e550836a94c6f3391f7d |
| NULL        | SET NAMES `UTF8` ;  | d04f62588ce57fd4a6b9b0c1fa5e8cd7 |
| NULL        | SET NAMES `UTF8` ;  | e17b03877786e54bad08bb4315c36c77 |
| NULL        | SET NAMES `UTF8` ;  | e9e52b844b0eb70b1ffc664c3b524e41 |
| ps_history  | SET NAMES `UTF8` ;  | 69164082e872fc72a9ac111a088b4edf |
| sys_history | SET NAMES `UTF8` ;  | 4de6d3c80ee7ebf9d06738595660c343 |
| sys_history | SET NAMES `UTF8` ;  | 54ce498e03a5f904bf4f022646bf08e3 |
| sys_history | SET NAMES `UTF8` ;  | 66825d4c750a2ff10991276a23209e2d |
| sys_history | SET NAMES `UTF8` ;  | 6f5955e015bc958176b2eb6f21928a46 |
| sys_history | SET NAMES `UTF8` ;  | a33152a77b945f338da09c97071fbe89 |
| sys_history | SET NAMES `UTF8` ;  | bdb8cb2e9d9b839639220c0759fbb008 |
| sys_history | SET NAMES `UTF8` ;  | c9c9d096d70f11dbc29ac2ecf18e48e6 |
| sys_history | SET NAMES `UTF8` ;  | ea79492ec2e2a826f0baf7e9bf1f37c3 |
| test        | SET NAMES `UTF8` ;  | a33152a77b945f338da09c97071fbe89 |
| test        | SET NAMES `UTF8` ;  | bdb8cb2e9d9b839639220c0759fbb008 |
| test        | SET NAMES `UTF8` ;  | d04f62588ce57fd4a6b9b0c1fa5e8cd7 |
| test        | SET NAMES `UTF8` ;  | e9e52b844b0eb70b1ffc664c3b524e41 |
+-------------+---------------------+----------------------------------+
22 rows in set (0.04 sec)
[30 Nov 2016 6:29] Mayank Prasad 
Hi Justin, 

Thanks for reporting the issue. I am wondering about the ';' in digest text. 

Please let us know the reproduction steps.

A repro test case would be helpful.

Thanks
-Mayank
[7 Dec 2016 15:03] Justin Swanhart 
I think you can reproduce this if you install PS_History numerous times.  It uses:
DELIMITER ;;

It seems that the P_S is stripping off the first ; but not the second ;
[8 Dec 2016 15:09] Justin Swanhart 
Here is a related problem: the digest of the SET NAMES statement is apparently not case insensitive:
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select distinct db,query,digest from sys.statement_analysis ; 
+------+-------------------------------------+----------------------------------+
| db   | query                               | digest                           |
+------+-------------------------------------+----------------------------------+
| NULL | SELECT @@`version_comment` LIMIT ?  | bb3f69453119b2d7b3ae40673a9d4c7c |
+------+-------------------------------------+----------------------------------+
1 row in set (0.01 sec)

mysql> set names `UTF8`;
Query OK, 0 rows affected (0.00 sec)

mysql> set names `utf8`;
Query OK, 0 rows affected (0.00 sec)

mysql> select distinct db,query,digest from sys.statement_analysis ;
+------+-------------------------------------------------------------------+----------------------------------+
| db   | query                                                             | digest                           |
+------+-------------------------------------------------------------------+----------------------------------+
| NULL | SELECT @@`version_comment` LIMIT ?                                | bb3f69453119b2d7b3ae40673a9d4c7c |
| NULL | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | bb6fa1813faeb702c69ee1dc761a372f |
| NULL | SET NAMES `UTF8`                                                  | 96767f5518674c82d9050a5ee44585cb |
| NULL | SET NAMES `utf8`                                                  | 497c378a9ba204f8fadb1306e6b0ca83 |
+------+-------------------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
[10 Dec 2016 14:06] Justin Swanhart 
This particular bug happens because statements are not properly delimited by the client specified delimiter.  It happened because of a typo, but here is how to reproduce it:
mysql> select distinct db,query,digest from sys.statement_analysis ;
+------+-------------------------------------+----------------------------------+
| db   | query                               | digest                           |
+------+-------------------------------------+----------------------------------+
| NULL | SELECT @@`version_comment` LIMIT ?  | bb3f69453119b2d7b3ae40673a9d4c7c |
+------+-------------------------------------+----------------------------------+
1 row in set (0.01 sec)

mysql> delimiter ;;
mysql> set names utf8;
    -> select 1;
    -> ;;
Query OK, 0 rows affected (0.00 sec)

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select distinct db,query,digest from sys.statement_analysis ;;
+------+-------------------------------------------------------------------+----------------------------------+
| db   | query                                                             | digest                           |
+------+-------------------------------------------------------------------+----------------------------------+
| NULL | SELECT @@`version_comment` LIMIT ?                                | bb3f69453119b2d7b3ae40673a9d4c7c |
| NULL | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | bb6fa1813faeb702c69ee1dc761a372f |
| NULL | SET NAMES `utf8` ;                                                | e6fcd985d5dcc6736089e6a112baf974 |
| NULL | SET NAMES `utf8` ;                                                | 9fbc4780e3de0cd62941f9c4872a5759 |
+------+-------------------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
[12 Dec 2016 8:50] Mayank Prasad 
Hello Justin,
Thanks for the steps. I am working on it.
[20 Dec 2016 19:27] Paul DuBois 
Posted by developer:
 
Noted in 5.6.36, 5.7.18, 8.0.1 changelogs.

Semicolon (;) characters within or between statements could cause
distinct digests to be generated from identical statements.