Bug #80372 Built-in mysql functions are case sensitive when used in query digest hash
| Submitted: | 15 Feb 2016 12:58 | Modified: | 16 Feb 2016 0:47 |
|---|---|---|---|
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.0-m17-enterprise-commercial-advanced | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[16 Feb 2016 0:47]
MySQL Verification Team
Thank you for the bug report.
miguel@tikal ~/dbs/5.8 $ bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.8.0-m17-debug Source distribution
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>
mysql>
mysql> use performance_schema;
truncate events_statements_summary_by_digest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%';
select GET_LOCK('abcdef', 10);
select GET_LOCK('abc', 10);
select get_lock('abcn', 10);
select get_lock('abcn2', 10);
select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G
Database changed
mysql> truncate events_statements_summary_by_digest;
Query OK, 0 rows affected (0,00 sec)
mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%';
Empty set (0,00 sec)
mysql> select GET_LOCK('abcdef', 10);
+------------------------+
| GET_LOCK('abcdef', 10) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0,00 sec)
mysql> select GET_LOCK('abc', 10);
+---------------------+
| GET_LOCK('abc', 10) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0,00 sec)
mysql> select get_lock('abcn', 10);
+----------------------+
| get_lock('abcn', 10) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0,00 sec)
mysql> select get_lock('abcn2', 10);
+-----------------------+
| get_lock('abcn2', 10) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0,00 sec)
mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G
*************************** 1. row ***************************
SCHEMA_NAME: performance_schema
DIGEST: f9adb7466b20afb5d684a86c66f80b96
DIGEST_TEXT: SELECT `GET_LOCK` (...)
COUNT_STAR: 2
SUM_TIMER_WAIT: 146510000
MIN_TIMER_WAIT: 67902000
AVG_TIMER_WAIT: 73255000
MAX_TIMER_WAIT: 78608000
SUM_LOCK_TIME: 0
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 2
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2016-02-15 22:45:35
LAST_SEEN: 2016-02-15 22:45:35
*************************** 2. row ***************************
SCHEMA_NAME: performance_schema
DIGEST: 5b8047c8a72ce408986e9aad8aa60b4f
DIGEST_TEXT: SELECT `get_lock` (...)
COUNT_STAR: 2
SUM_TIMER_WAIT: 143761000
MIN_TIMER_WAIT: 66465000
AVG_TIMER_WAIT: 71880000
MAX_TIMER_WAIT: 77296000
SUM_LOCK_TIME: 0
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 2
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2016-02-15 22:45:35
LAST_SEEN: 2016-02-15 22:45:35
2 rows in set (0,00 sec)
mysql>
[19 Feb 2016 6:02]
Erlend Dahl
Posted by developer: It's the same all the way back to 5.6 (the feature doesn't exist in 5.5).
[18 Jun 2016 21:36]
Omer Barnir
Posted by developer: Reported version value updated to reflect release name change from 5.8 to 8.0
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When using built-in functions (i.e. get_lock) the digest hashing depends on the case used in the function. This makes it much harder to use a query rewrite rule, since there needs to be one-rule per case. How to repeat: The last select returns 2 rows. Ideally it should return 1: use performance_schema; truncate events_statements_summary_by_digest; select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'; select GET_LOCK('abcdef', 10); select GET_LOCK('abc', 10); select get_lock('abcn', 10); select get_lock('abcn2', 10); select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G Example: mysql> truncate events_statements_summary_by_digest; Query OK, 0 rows affected (0.01 sec) mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'; Empty set (0.00 sec) mysql> select GET_LOCK('abcdef', 10); +------------------------+ | GET_LOCK('abcdef', 10) | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec) mysql> select GET_LOCK('abc', 10); +---------------------+ | GET_LOCK('abc', 10) | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> select get_lock('abcn', 10); +----------------------+ | get_lock('abcn', 10) | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) mysql> select get_lock('abcn2', 10); +-----------------------+ | get_lock('abcn2', 10) | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec) mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G *************************** 1. row *************************** SCHEMA_NAME: performance_schema DIGEST: ee7c2b19baee272d6a3df36aff4ab009 DIGEST_TEXT: SELECT `GET_LOCK` (...) COUNT_STAR: 2 SUM_TIMER_WAIT: 334159000 MIN_TIMER_WAIT: 135749000 AVG_TIMER_WAIT: 167079000 MAX_TIMER_WAIT: 198410000 SUM_LOCK_TIME: 0 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 2 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-02-15 12:51:44 LAST_SEEN: 2016-02-15 12:51:52 *************************** 2. row *************************** SCHEMA_NAME: performance_schema DIGEST: 0007fbbcc280ace574bcf43940f522da DIGEST_TEXT: SELECT `get_lock` (...) COUNT_STAR: 2 SUM_TIMER_WAIT: 298418000 MIN_TIMER_WAIT: 131014000 AVG_TIMER_WAIT: 149209000 MAX_TIMER_WAIT: 167404000 SUM_LOCK_TIME: 0 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 2 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-02-15 12:52:01 LAST_SEEN: 2016-02-15 12:52:03 2 rows in set (0.00 sec) Suggested fix: Built-ins should be insensitive similar to tokens like SELECT being insensitive.