processlist showing '%' as host from trigger

I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:

CREATE TRIGGER access_insert_trg BEFORE INSERT ON access
FOR EACH ROW 
set NEW.hostname = (select SUBSTRING_INDEX(host,':',1)
                    from information_schema.processlist 
                    WHERE ID=connection_id())
;

I end up getting % as the hostname even though when I manually run the same query on the processlist table I get an actual hostname. I've checked the connection_id() value and it's correctly the id of a row in processlist with a real hostname:port value in the host column.

I can't figure out how or why it's being translated to a generic %: value. Any ideas on what's going on & how to fix it?

I'm using MySQL 5.1.57 if that changes anything.

Are you doing the insert that fires the trigger from inside a stored procedure?

Apparently, when inside a stored procedure, the row matching your thread in information_schema.processlist is populated (albeit somewhat half-heartedly) with the DEFINER username and hostname if the calling user is not the same as the definer.

This is somewhat unexpected, so I'll document it a bit more, below...

But first, your fix:

SET NEW.hostname = SUBSTRING_INDEX(user(),'@',-1);

If this works, it's definitely a better approach -- it's a much more lightweight way to get the information you're looking for, and seems to return the correct answer where the other doesn't. Making the call to information_schema.processlist is expensive, since apparently the entire table is rendered each time you call for it.

Now, to replicate the behavior for the benefit of those who (me included) whouldn't have believed it without seeing it in action:

DELIMITER $

DROP PROCEDURE IF EXISTS `bizarre` $
CREATE DEFINER=`super_dev`@`%` PROCEDURE `bizarre`()
BEGIN

SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();

END $

DELIMITER ;

Now to test the SP. Note that I am not connecting as super_dev, I am connecting as a different super user. If I connect as super_dev (the DEFINER user), this works the way we expect.

mysql> call bizarre();
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
| ID    | USER          | HOST    | DB         | COMMAND | TIME | STATE     | INFO                                                                    |
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
| 17261 | super_dev     | %:49730 | dev_testsv | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

My best guess, this is an artifact of the environment changes that need to happen internally when a stored procedure is called.

StackExchange Database Administrators Q#28636, answer score: 6