processlist showing '%' as host from trigger

Question

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 %:<port> value. Any ideas on what's going on & how to fix it?

I'm using MySQL 5.1.57 if that changes anything.

Answer 1

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.