| Bug #92049 | bogus data when ordering results from variables_by_thread | ||
|---|---|---|---|
| Submitted: | 17 Aug 2018 5:06 | Modified: | 27 Sep 2018 18:09 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S1 (Critical) |
| Version: | 5.7.22, 5.7.23, 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Aug 2018 6:01]
MySQL Verification Team
Hello Shane,
Thank you for the report and test case.
Verified as described with 5.7.23 build.
- Other GA versions checked
8.0.12 - not affected
5.6 - Not checked, variables_by_thread is implemented and available since 5.7.6+
- lowest version checked 5.7.9 - affected
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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 * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout') ORDER BY VARIABLE_NAME,THREAD_ID;
+-----------+----------------------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+----------------------------+----------------+
| 28 | insert_id | 0 |
| 28 | net_buffer_length | 16384 |
| 28 | net_retry_count | 10 |
| 28 | updatable_views_with_limit | YES |
+-----------+----------------------------+----------------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout');
+-----------+---------------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------------+----------------+
| 28 | interactive_timeout | 28800 |
| 28 | net_read_timeout | 30 |
| 28 | net_write_timeout | 60 |
| 28 | wait_timeout | 28800 |
+-----------+---------------------+----------------+
4 rows in set (0.00 sec)
Sincerely,
Umesh
[27 Sep 2018 18:09]
Paul DuBois
Posted by developer: Fixed in 5.7.25, 8.0.14. Mishandling of deprecated system variables could cause output from queries on the Performance Schema variables_by_thread table to be incorrect.
Description: Trying to get some connections variable values to debug a timeout issue, and I'm seeing nonsensical results. mysql> SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout') ORDER BY VARIABLE_NAME,THREAD_ID; +-----------+----------------------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+----------------------------+----------------+ | 29 | insert_id | 0 | | 29 | net_buffer_length | 16384 | | 29 | net_retry_count | 10 | | 29 | updatable_views_with_limit | YES | +-----------+----------------------------+----------------+ 4 rows in set (0.00 sec) It is returning rows I didn't ask for! How to repeat: SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout') ORDER BY VARIABLE_NAME,THREAD_ID; SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout');