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)
