Description:
When connecting to a MySQL server using Workbench, the "Server" => "Client Connections" button displays a list of MySQL connections. However, the query triggered by this button incorrectly shows the username for the "system user" thread and marks it as "foreground." For example, on a replica server, there are "system user" threads for the IO thread and SQL thread, which should be marked as "background." Instead, Workbench shows these threads with the username of the user who ran the "CHANGE MASTER TO" query and marks them as "foreground," which is inconsistent with the "SHOW PROCESSLIST" output.
How to repeat:
1. Connect to a replica server using MySQL Workbench.
2. Run the SHOW PROCESSLIST command:
mysql> SHOW PROCESSLIST;
+----+-------------+-----------------------+------+---------+-------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------------+------+---------+-------+----------------------------------------------------------+------------------+
| 14 | system user | | NULL | Query | 17 | Replica has read all relay log; waiting for more updates | NULL |
| 15 | system user | | NULL | Query | 18 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Query | 19 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Query | 20 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 52585 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 52585 | Waiting for an event from Coordinator | NULL |
| 20 | system user | | NULL | Connect | 52585 | Waiting for an event from Coordinator | NULL |
| 21 | system user | | NULL | Connect | 52585 | Waiting for an event from Coordinator | NULL |
| 22 | system user | | NULL | Connect | 52585 | Waiting for an event from Coordinator | NULL |
| 23 | ****** | ****** | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------------------+------+---------+-------+----------------------------------------------------------+------------------+
3. Use the "Server" => "Client Connections" button on Workbench, which triggers the following query:
SELECT t.PROCESSLIST_ID,IF (NAME = 'thread/sql/event_scheduler','event_scheduler',t.PROCESSLIST_USER) PROCESSLIST_USER,t.PROCESSLIST_HOST,t.PROCESSLIST_DB,t.PROCESSLIST_COMMAND,t.PROCESSLIST_TIME,t.PROCESSLIST_STATE,t.THREAD_ID,t.TYPE,t.NAME,t.PARENT_THREAD_ID,t.INSTRUMENTED,t.PROCESSLIST_INFO,a.ATTR_VALUE FROM performance_schema.threads t LEFT OUTER JOIN performance_schema.session_connect_attrs a ON t.processlist_id = a.processlist_id AND (a.attr_name IS NULL OR a.attr_name = 'program_name') WHERE t.TYPE <> 'BACKGROUND' /* statement may be truncated */
Result:
mysql> SELECT t.PROCESSLIST_ID,IF (NAME = 'thread/sql/event_scheduler','event_scheduler',t.PROCESSLIST_USER) PROCESSLIST_USER,t.PROCESSLIST_HOST,t.PROCESSLIST_DB,t.PROCESSLIST_COMMAND,t.PROCESSLIST_TIME,t.PROCESSLIST_STATE,t.THREAD_ID,t.TYPE,t.NAME,t.PARENT_THREAD_ID,t.INSTRUMENTED,t.PROCESSLIST_INFO,a.ATTR_VALUE FROM performance_schema.threads t LEFT OUTER JOIN performance_schema.session_connect_attrs a ON t.processlist_id = a.processlist_id AND (a.attr_name IS NULL OR a.attr_name = 'program_name') WHERE t.TYPE <> 'BACKGROUND' /* statement may be truncated */\G
*************************** 1. row ***************************
PROCESSLIST_ID: 7
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
PROCESSLIST_TIME: 52666
PROCESSLIST_STATE: Suspending
THREAD_ID: 31
TYPE: FOREGROUND
NAME: thread/sql/compress_gtid_table
PARENT_THREAD_ID: 1
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 2. row ***************************
PROCESSLIST_ID: 8
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 1
PROCESSLIST_STATE: NULL
THREAD_ID: 32
TYPE: FOREGROUND
NAME: thread/sql/one_connection
PARENT_THREAD_ID: 1
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 3. row ***************************
PROCESSLIST_ID: 9
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 1
PROCESSLIST_STATE: NULL
THREAD_ID: 33
TYPE: FOREGROUND
NAME: thread/sql/one_connection
PARENT_THREAD_ID: 1
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 4. row ***************************
PROCESSLIST_ID: 10
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 1
PROCESSLIST_STATE: NULL
THREAD_ID: 34
TYPE: FOREGROUND
NAME: thread/sql/one_connection
PARENT_THREAD_ID: 1
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 5. row ***************************
PROCESSLIST_ID: 11
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 2
PROCESSLIST_STATE: NULL
THREAD_ID: 35
TYPE: FOREGROUND
NAME: thread/sql/one_connection
PARENT_THREAD_ID: 1
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 6. row ***************************
PROCESSLIST_ID: 12
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 38
PROCESSLIST_STATE: NULL
THREAD_ID: 36
TYPE: FOREGROUND
NAME: thread/sql/one_connection
PARENT_THREAD_ID: 1
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 7. row ***************************
PROCESSLIST_ID: 14
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 37
PROCESSLIST_STATE: Replica has read all relay log; waiting for more updates
THREAD_ID: 38
TYPE: FOREGROUND
NAME: thread/sql/replica_sql
PARENT_THREAD_ID: 37
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 8. row ***************************
PROCESSLIST_ID: 15
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 38
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 39
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: CREATE INDEX k_7 ON sbtest7(k)
ATTR_VALUE: NULL
*************************** 9. row ***************************
PROCESSLIST_ID: 16
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 39
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 40
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: CREATE INDEX k_6 ON sbtest6(k)
ATTR_VALUE: NULL
*************************** 10. row ***************************
PROCESSLIST_ID: 17
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 40
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 41
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: BEGIN
ATTR_VALUE: NULL
*************************** 11. row ***************************
PROCESSLIST_ID: 18
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 52605
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 42
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 12. row ***************************
PROCESSLIST_ID: 19
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 52605
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 43
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 13. row ***************************
PROCESSLIST_ID: 20
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 52605
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 44
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 14. row ***************************
PROCESSLIST_ID: 21
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 52605
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 45
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 15. row ***************************
PROCESSLIST_ID: 22
PROCESSLIST_USER: azure_superuser
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 52605
PROCESSLIST_STATE: Waiting for an event from Coordinator
THREAD_ID: 46
TYPE: FOREGROUND
NAME: thread/sql/replica_worker
PARENT_THREAD_ID: 38
INSTRUMENTED: YES
PROCESSLIST_INFO: NULL
ATTR_VALUE: NULL
*************************** 16. row ***************************
PROCESSLIST_ID: 23
PROCESSLIST_USER: **********
PROCESSLIST_HOST: **********
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: executing
THREAD_ID: 117
TYPE: FOREGROUND
NAME: thread/sql/one_connection
PARENT_THREAD_ID: NULL
INSTRUMENTED: YES
PROCESSLIST_INFO: SELECT t.PROCESSLIST_ID,IF (NAME = 'thread/sql/event_scheduler','event_scheduler',t.PROCESSLIST_USER) PROCESSLIST_USER,t.PROCESSLIST_HOST,t.PROCESSLIST_DB,t.PROCESSLIST_COMMAND,t.PROCESSLIST_TIME,t.PROCESSLIST_STATE,t.THREAD_ID,t.TYPE,t.NAME,t.PARENT_THREAD_ID,t.INSTRUMENTED,t.PROCESSLIST_INFO,a.ATTR_VALUE FROM performance_schema.threads t LEFT OUTER JOIN performance_schema.session_connect_attrs a ON t.processlist_id = a.processlist_id AND (a.attr_name IS NULL OR a.attr_name = 'program_name') WHERE t.TYPE <> 'BACKGROUND'
ATTR_VALUE: mysql
16 rows in set (0.08 sec)
Suggested fix:
Modify the query triggered by the "Server" => "Client Connections" button in MySQL Workbench to correctly identify and classify "system user" threads and differentiate between "foreground" and "background" threads.
For example:
```
SELECT
t.PROCESSLIST_ID,
CASE
WHEN t.NAME IN ('thread/sql/event_scheduler', 'thread/sql/replica_io', 'thread/sql/replica_sql') THEN 'system user'
ELSE t.PROCESSLIST_USER
END AS PROCESSLIST_USER,
t.PROCESSLIST_HOST,
t.PROCESSLIST_DB,
t.PROCESSLIST_COMMAND,
t.PROCESSLIST_TIME,
t.PROCESSLIST_STATE,
t.THREAD_ID,
CASE
WHEN t.NAME IN ('thread/sql/replica_io', 'thread/sql/replica_sql', 'thread/sql/event_scheduler') THEN 'BACKGROUND'
ELSE t.TYPE
END AS TYPE,
t.NAME,
t.PARENT_THREAD_ID,
t.INSTRUMENTED,
t.PROCESSLIST_INFO,
a.ATTR_VALUE
FROM
performance_schema.threads t
LEFT OUTER JOIN
performance_schema.session_connect_attrs a
ON
t.processlist_id = a.processlist_id
AND (a.attr_name IS NULL OR a.attr_name = 'program_name')
WHERE
t.TYPE <> 'BACKGROUND'
OR t.NAME IN ('thread/sql/replica_io', 'thread/sql/replica_sql', 'thread/sql/event_scheduler');
```
just an example, maybe we can uses a CASE statement to correctly set the PROCESSLIST_USER for system threads like 'thread/sql/replica_io' and 'thread/sql/replica_sql' to 'system user'.