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