MySQL Bugs: #77293: Wrong processlist_user in threads table for replication threads

Bug #77293	Wrong processlist_user in threads table for replication threads
Submitted:	10 Jun 2015 9:42	Modified:	1 Oct 2019 9:49
Reporter:	Pavel Katiushyn	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.16	OS:	Linux (CentOS6.5)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
For replication threads the performance_schema.threads table on slave shows the name of user that started them. 
As the result it also affect statistics in the performance_schema accounts,hosts and users tables. And maybe some others.

information_schema.processlist shows "system user" instead for replication threads.

How to repeat:
[root@SRV11 ~]$ mysql -upavel.katiushyn -p
Enter password: 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_User: replitest
             Slave_IO_Running: No
            Slave_SQL_Running: No

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.threads where NAME like '%slave%'\G                                                                     *************************** 1. row ***************************
          THREAD_ID: 2417726
               NAME: thread/sql/slave_io
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: pavel.katiushyn
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 1104487
  PROCESSLIST_STATE: Waiting for master to send event
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 2416846
               ROLE: NULL
       INSTRUMENTED: YES
*************************** 2. row ***************************
          THREAD_ID: 3532155
               NAME: thread/sql/slave_sql
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: pavel.katiushyn
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 1
  PROCESSLIST_STATE: Slave has read all relay log; waiting for the slave I/O thread t
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 3529230
               ROLE: NULL
       INSTRUMENTED: YES
2 rows in set (0.00 sec)

Suggested fix:
Consider changing to "system user" as in information_schema.processlist or to NULL as for an internal threads.

The PROCESSLIST_USER show pavel.katiushyn for both threads, as replication was started by that user.

Hello, is there any update on this issue?

this is a bit more egregious with the performance_schema_show_processlist change (8.0.22+), maybe it's time to fix it

This also affects Workbench which uses performance_schema.threads for the "Client connections" management page

I see an issue similar between performance_schema.processlist and informance_schema.processlist in MYSQL 8.0.29

slave1 [localhost] {msandbox} ((none)) > select host,user from performance_schema.replication_conne
*************************** 1. row ***************************
host: 127.0.0.1
user: rsandbox
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > select * from information_schema.processlist where state like '%Coordinator%';
+----+-------------+------+------+---------+------+---------------------------------------+------+---------+-----------+---------------+
| ID | USER        | HOST | DB   | COMMAND | TIME | STATE                                 | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+----+-------------+------+------+---------+------+---------------------------------------+------+---------+-----------+---------------+
| 17 | system user |      | NULL | Connect |    3 | Waiting for an event from Coordinator | NULL |    3057 |         0 |             0 |
| 18 | system user |      | NULL | Connect |    3 | Waiting for an event from Coordinator | NULL |    3056 |         0 |             0 |
| 19 | system user |      | NULL | Connect |    3 | Waiting for an event from Coordinator | NULL |    3056 |         0 |             0 |
| 20 | system user |      | NULL | Connect |    3 | Waiting for an event from Coordinator | NULL |    3056 |         0 |             0 |
+----+-------------+------+------+---------+------+---------------------------------------+------+---------+-----------+---------------+
4 rows in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > select * from performance_schema.processlist where state like '%Coordinator%';
+----+----------+-----------+------+---------+------+---------------------------------------+------+---------+-----------+---------------+
| ID | USER     | HOST      | DB   | COMMAND | TIME | STATE                                 | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+----+----------+-----------+------+---------+------+---------------------------------------+------+---------+-----------+---------------+
| 17 | msandbox | localhost | NULL | Connect |    6 | Waiting for an event from Coordinator | NULL |    6272 |         0 |             0 |
| 18 | msandbox | localhost | NULL | Connect |    6 | Waiting for an event from Coordinator | NULL |    6272 |         0 |             0 |
| 19 | msandbox | localhost | NULL | Connect |    6 | Waiting for an event from Coordinator | NULL |    6271 |         0 |             0 |
| 20 | msandbox | localhost | NULL | Connect |    6 | Waiting for an event from Coordinator | NULL |    6271 |         0 |             0 |
+----+----------+-----------+------+---------+------+---------------------------------------+------+---------+-----------+---------------+
4 rows in set (0.00 sec)

<m performance_schema.threads where NAME like '%replica%'\G                    
*************************** 1. row ***************************
          THREAD_ID: 53
               NAME: thread/sql/replica_io
               TYPE: FOREGROUND
     PROCESSLIST_ID: 15
   PROCESSLIST_USER: msandbox
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
   PROCESSLIST_TIME: 927
  PROCESSLIST_STATE: Waiting for source to send event
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 52
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 84950
     RESOURCE_GROUP: SYS_default

As per,
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-processlist-table.html

https://dev.mysql.com/doc/refman/8.0/en/information-schema-processlist-table.html

USER

The MySQL user who issued the statement. A value of system user refers to a nonclient thread spawned by the server to handle tasks internally, for example, a delayed-row handler thread or an I/O or SQL thread used on replica hosts. For system user, there is no host specified in the Host column. unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet occurred. event_scheduler refers to the thread that monitors scheduled events

HOST

The host name of the client issuing the statement (except for system user, for which there is no host). The host name for TCP/IP connections is reported in host_name:client_port format to make it easier to determine which client is doing what.

DB

The default database for the thread, or NULL if none has been selected.

documentation description for performance_schema.processlist and informance_schema.processlist not matching for USER and HOST.

Expectation:
It would be good if the replication user and host values could be fixed as below, 

USER : system user

HOST: localhost

One of the cases in which having current USER and HOST name for these replica threads is when we need to kill USER connections, which will also kill replica process thread ID, assuming that the user runs it.

performance_schema.processlist and performace_schema.threads show incorrect user information for replication threads.

Start MySQL 8.0.35 with at least one replica.
Run the following on the replica and observe the results:
SHOW PROCESSLIST;
SHOW WARNINGS;
SELECT * FROM information_schema.processlist ORDER BY id;
SHOW WARNINGS;
SELECT * FROM performance_schema.processlist ORDER BY id;
SELECT thread_id,name,type,processlist_id,processlist_user,processlist_host,processlist_state FROM performance_schema.threads WHERE name LIKE '%replica%';
STOP REPLICA;
START REPLICA;
SHOW PROCESSLIST;
SELECT * FROM information_schema.processlist ORDER BY id;
SELECT * FROM performance_schema.processlist ORDER BY id;
SELECT thread_id,name,type,processlist_id,processlist_user,processlist_host,processlist_state FROM performance_schema.threads WHERE name LIKE '%replica%';