P_S showing same digest for all select statements using views

General

Escalation

General

Escalation

Description

The table performance_schema.events_statements_history showing the same digest value for the SELECTS on views.

mysql> create table percona.test_digest_table (cola int, colb int);
Query OK, 0 rows affected (0.22 sec)mysql> create view percona.test_digest_view 
    -> as
    -> select cola, colb
    -> from percona.test_digest_table;
Query OK, 0 rows affected (0.01 sec)mysql> select cola from percona.test_digest_view;
Empty set (0.00 sec)mysql> select * from percona.test_digest_view where colb = 2;
Empty set (0.00 sec)mysql> show create table percona.test_digest_view;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View             | Create View                                                                                                                                                                                                     | character_set_client | collation_connection |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test_digest_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_digest_view` AS select `test_digest_table`.`cola` AS `cola`,`test_digest_table`.`colb` AS `colb` from `test_digest_table` | latin1               | latin1_swedish_ci    |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)mysql> select sql_text,digest,digest_text
    -> from performance_schema.events_statements_history 
    -> where sql_text like '%percona.test_digest_view%';
+-------------------------------------------------------------------------------------------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_text                                                                                  | digest                                                           | digest_text                                                                                                                                         |
+-------------------------------------------------------------------------------------------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| show create table percona.test_digest_view                                                | 0ae8e625e79b6ed0ef4f9bfe638092edfbaf40c094f32709ffd4abe5679830ce | SELECT `percona` . `test_digest_table` . `cola` AS `cola` , `percona` . `test_digest_table` . `colb` AS `colb` FROM `percona` . `test_digest_table` |
| create view percona.test_digest_view 
as
select cola, colb
from percona.test_digest_table | 3cd56fc1f7db95795f33109a503176924293c6fe118b626dffa933c684e8bc9b | CREATE VIEW `percona` . `test_digest_view` AS SELECT `cola` , `colb` FROM `percona` . `test_digest_table`                                           |
| select cola from percona.test_digest_view                                                 | 0ae8e625e79b6ed0ef4f9bfe638092edfbaf40c094f32709ffd4abe5679830ce | SELECT `percona` . `test_digest_table` . `cola` AS `cola` , `percona` . `test_digest_table` . `colb` AS `colb` FROM `percona` . `test_digest_table` |
| select * from percona.test_digest_view where colb = 2                                     | 0ae8e625e79b6ed0ef4f9bfe638092edfbaf40c094f32709ffd4abe5679830ce | SELECT `percona` . `test_digest_table` . `cola` AS `cola` , `percona` . `test_digest_table` . `colb` AS `colb` FROM `percona` . `test_digest_table` |
+-------------------------------------------------------------------------------------------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.13 sec)

The same happens with CREATE VIEW ALGORITHM=TEMPTABLE|MERGE as well. This seems to happen in all version and the above was tested in 8.0.15 Percona server.

This is related with upstream bug - https://bugs.mysql.com/bug.php?id=89559

Environment

None

AFFECTED CS IDs

255480

Activity

Show:

puneet.kaushik

February 22, 2022 at 6:06 AM

Bug Fix verified in PS 8.0.27 , now table performance_schema.events_statements_history showing the different digest value for the SELECTS on views.

George Lorch

December 20, 2021 at 7:18 PM

[2 Jul 17:49] Paul DuBois
Posted by developer:
 
Fixed in 5.7.36, 8.0.27.

For all SELECT statements on a view, the query digest was based on
the view definition. As a result, different queries had the same
digest and aggregated together in the Performance Schema
events_statements_summary_by_digest table, so statistics in that
table were not usable for distinguishing distinct SELECT statements.

The query digest for each SELECT statement on a view now is based on
the SELECT, not the view definition. This enables distinguishing
distinct SELECT statements in the events_statements_summary_by_digest
table. However, tools that use query digests may need some adjustment
to account for this change. For example, MySQL Enterprise Firewall
and query rewrite plugins rely on query digests and existing rules
for them that are associated with views may need to be updated.

Resize issue view side panel

Done

Details

Assignee

Unassigned

Reporter

Vinodh Krishnaswamy

Labels

Priority

Medium

Created May 26, 2019 at 2:15 PM

Updated March 6, 2024 at 12:07 PM

Resolved December 20, 2021 at 7:19 PM

P_S showing same digest for all select statements using views

Description

Environment

AFFECTED CS IDs

Activity

Details

Assignee

Reporter

Labels

Upstream Bug URL

Fix versions

Affects versions

Priority

Flag notifications

Something's gone wrong

Something's gone wrong