I'm thinking about using triggers as a logging mechanism in MySQL (v. 5.1) and therefore I'd like my trigger to get the query string in order to store it in another tabe.
I couldn't find anything like that in the MySQL documentation so I expect the answer to be no – but I still hope that I've overlooked something.
PS: this question is related to IcarusNM's answer to my question "Log MySQL DB changing queries and users"
Since you are using MySQL 5.1, you have access to the table INFORMATION_SCHEMA.PROCESSLIST.
You also have access to the ID of the current process the trigger is running on. The function to get that process ID is CONNECTION_ID.
You could try to fish out the query like this:
SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();
Keep in mind that the info is LONGTEXT:
mysql> show create table information_schema.processlist\G
*************************** 1. row ***************************
Table: PROCESSLIST
Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
`ID` bigint(4) NOT NULL DEFAULT '0',
`USER` varchar(16) NOT NULL DEFAULT '',
`HOST` varchar(64) NOT NULL DEFAULT '',
`DB` varchar(64) DEFAULT NULL,
`COMMAND` varchar(16) NOT NULL DEFAULT '',
`TIME` int(7) NOT NULL DEFAULT '0',
`STATE` varchar(64) DEFAULT NULL,
`INFO` longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Within the trigger, you could use a local variable to hold the query
DECLARE original_query VARCHAR(1024);
and then capture the query
SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();
Give it a Try !!!
If it works, then
CONNECTION_ID() does change, but it looks like the current id is always that of the select? Should I try and save the CONNECTION_ID() earlier in the process?
DROP TRIGGER IF EXISTS DEBUG_TIGGER;
CREATE TRIGGER DEBUG_TIGGER
AFTER UPDATE ON my_table
FOR EACH ROW
BEGIN
INSERT INTO log_my_table(query)
SELECT argument
FROM mysql.general_log
WHERE thread_id = connection_id()
ORDER BY event_time DESC
LIMIT 5; /*gives last 5 queries(this will help you get idea of what logical path was taken)*/
INSERT log_my_table
(query)
VALUES('-----------------');/*empty place holder*/
END