Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I know little about MySQL, but I guess it'll be the host permission '%' that allowed the connection –  Phil Nov 13 '12 at 23:31
    
It seemed likely initially that it could be a permissions problem, but in my test I logged in as the same user that is inserting into the table & the identical query found in the trigger returns a valid hostname (Not '%') so it seems like that user has the right access. - stumped - –  pmckinstry Nov 13 '12 at 23:38
    
From the same host though? I'm thinking it's the permissions match you're seeing –  Phil Nov 13 '12 at 23:46
    
Good thought. But ya, even on the same host. –  pmckinstry Nov 14 '12 at 8:55
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Your explanation makes sense and the fix to use user() instead of the processlist works perfectly. Thanks! –  pmckinstry Nov 14 '12 at 9:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.