Bug #111335 SHOW FULL PROCESSLIST shows '%' in Host IP section instead of the correct IP

Description:
When you create a procedure using definer for a particular user, while the procedure is called using another user. The SHOW FULL PROCESSLIST shows '%' in Host IP section instead of the correct IP of the source.

Ex:

mysql> show full processlist;
+-------+----------+-------------------+------+---------+------+------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+-------------------+------+---------+------+------------+-----------------------+
| 13500 | rdsadmin | localhost | NULL | Sleep | 1 | | NULL |
| 16377 | master | 172.31.1.67:44640 | d1 | Sleep | 4751 | | NULL |
| 16390 | master | 172.31.1.67:46490 | d1 | Query | 0 | starting | show full processlist |
| 16396 | master | %:51588 | d1 | Query | 7 | User sleep | SELECT SLEEP (30) |
+-------+----------+-------------------+------+---------+------+------------+-----------------------+
4 rows in set (0.00 sec)

Note: Host entry %:51588

The issue was first test in AWS RDS MySQL 5.7.38 and was also reproducible on MySQL community 5.7.38 in Ubuntu based EC2 (Ubuntu 22.04.2 LTS).

More occurrences were reported on 8.0.22 as well.
https://forums.percona.com/t/for-mysql-show-processlist-host-name-shows-like-5462-not-able...

How to repeat:
Steps to Reproduce: (following are sample steps for RDS MySQL 5.7.38, but similar steps would suffice for MySQL community as well).

STEP 1: Created a procedure from master user using Definer:

DELIMITER $$
CREATE DEFINER = 'master'@'%'
PROCEDURE testpro1()
BEGIN
SELECT SLEEP (30);
END$$
DELIMITER ;

STEP 2: Created an alternative user ‘admin’ with master privileges:

CREATE USER 'admin'@'%' IDENTIFIED BY '1234567890';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO 'admin'@'%' WITH GRANT OPTION;

STEP 3: Called the Procedure using the newly created admin user:

call testpro1();

STEP 4: Ran Show full processlist from ‘master’ user;

mysql> show full processlist;
+-------+----------+-------------------+------+---------+------+------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+-------------------+------+---------+------+------------+-----------------------+
| 13500 | rdsadmin | localhost | NULL | Sleep | 1 | | NULL |
| 16377 | master | 172.31.1.67:44640 | d1 | Sleep | 4751 | | NULL |
| 16390 | master | 172.31.1.67:46490 | d1 | Query | 0 | starting | show full processlist |
| 16396 | master | %:51588 | d1 | Query | 7 | User sleep | SELECT SLEEP (30) |
+-------+----------+-------------------+------+---------+------+------------+-----------------------+
4 rows in set (0.00 sec)

HI Mr. Khan,

The current behaviour is there for security reasons.

We are now in the process of checking on whether it can be changed or not.

We shall contact you once that we determine whether this behaviour can be changed or not .....

Hi,

We have consulted our Development team in charge of this server functionality.

The conclusion is that this is expected and intended behaviour. What we are presented in the output is actually the effective user at this point. In your case, the effective user is a definer. That also stems from your SQL SECURITY definition ...... Since you have not specified any, the default is presumed.

Not a bug.