Can a trigger access the query string?

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"

I have a working theory (LaForge would say to Capt Picard)...

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
Make it so (Capt Picard would quip back to LaForge)

StackExchange Database Administrators Q#8108, answer score: 5