Bug #98498 P_S view status_by_thread does not reflect correct results

• Submitted: 6 Feb 2020 9:41
• Modified: 6 Feb 2020 15:44
• Reporter: Oli Sennhauser
• Status: Verified
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0
• OS: Any (n.a.)
• CPU Architecture: Any (n.a.)

[6 Feb 2020 9:41] Oli Sennhauser

Description:
P_S view status_by_thread does not reflect correct results

How to repeat:
We want to see other connection:

SELECT thread_id, processlist_id AS 'Processlist Id', processlist_user as User, processlist_host as Host
     , processlist_db as db, processlist_command as Command, processlist_time as Time
  FROM performance_schema.threads
 WHERE type = 'FOREGROUND'
   AND processlist_id != CONNECTION_ID()
   AND processlist_command != 'Daemon'
   AND processlist_command = 'Sleep'
   AND processlist_time > 1
;
+-----------+----------------+------+-----------+------+---------+------+
| thread_id | Processlist Id | User | Host      | db   | Command | Time |
+-----------+----------------+------+-----------+------+---------+------+
|      4305 |           4280 | app  | localhost | test | Sleep   |    7 |
+-----------+----------------+------+-----------+------+---------+------+

SELECT variable_name, variable_value
  FROM performance_schema.status_by_thread
 WHERE thread_id = (SELECT thread_id
                      FROM performance_schema.threads
                     WHERE processlist_id = 4280
                   )
  AND (variable_name IN ('Queries', 'Questions') OR variable_name LIKE 'Com\_%')
;
+--------------------+----------------+
| variable_name      | variable_value |
+--------------------+----------------+
| Com_stmt_reprepare | 0              |
| Queries            | 12991          |
| Questions          | 26             |
+--------------------+----------------+

--> This is completely wrong! Com_% are missing. (why is com_stmt_prepare there then?) queries we did never this amount. must be global! questions seems to be correct.

We had no overflows:

SHOW GLOBAL STATUS LIKE 'performance_schema_thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
+------------------------------------------+-------+

But we did for sure some queries in other connection:

SELECT event_id, sql_text, current_schema
  FROM performance_schema.events_statements_history
 WHERE thread_id = 4305
 ORDER BY event_id DESC
;
+----------+------------------------------------------------------+----------------+
| event_id | sql_text                                             | current_schema |
+----------+------------------------------------------------------+----------------+
|       26 | select * from test limit 20                          | test           |
|       25 | update test set data = 'bla'  where id = 18          | test           |
|       24 | update test set data = 'bla' from test where id = 18 | test           |
|       23 | select * from test limit 20                          | test           |
|       22 | NULL                                                 | test           |
|       21 | SELECT DATABASE()                                    | test           |
|       20 | select USER()                                        | test           |
|       19 | select @@version_comment limit 1                     | test           |
|       18 | NULL                                                 | test           |
|       17 | NULL                                                 | test           |
+----------+------------------------------------------------------+----------------+

Suggested fix:
fix session status counter

[6 Feb 2020 15:47] MySQL Verification Team

Hi Mr. Sennhauser,

Thank you for your bug report.

I have got results very similar to yours with latest 5.7 and latest 8.0 releases.

Here they are, in the same order as in your test case, although with different queries ....

+-----------+----------------+--------+-----------+------+---------+------+
| thread_id | Processlist Id | User   | Host      | db   | Command | Time |
+-----------+----------------+--------+-----------+------+---------+------+
|        63 |             20 | sinisa | localhost | test | Sleep   |   55 |
+-----------+----------------+--------+-----------+------+---------+------+

+--------------------+----------------+
| variable_name      | variable_value |
+--------------------+----------------+
| Com_stmt_reprepare | 0              |
| Queries            | 36             |
| Questions          | 2              |
+--------------------+----------------+

+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
+------------------------------------------+-------+

+----------+----------------------------------+----------------+
| event_id | sql_text                         | current_schema |
+----------+----------------------------------+----------------+
|        9 | show processlist                 | test           |
|        8 | show processlist                 | test           |
|        7 | show processlist                 | test           |
|        6 | select @@version_comment limit 1 | test           |
|        5 | NULL                             | test           |
|        3 | show tables                      | test           |
|        1 | show databases                   | test           |
+----------+----------------------------------+----------------+

Verified as reported.