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 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"

share|improve this question
    
That's a wild question, the likes of which I have never heard before. +1 for such outside-the-box thinking. –  RolandoMySQLDBA Nov 18 '11 at 16:25

2 Answers 2

up vote 4 down vote accepted

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)

share|improve this answer
    
Haha, a truly nice solution, wouldn't have thought about that. I couldn't fully test it yet (too small thread stack), but each of the parts work perfectly. Thank you! –  Beat Nov 18 '11 at 17:29
    
It is very nice, though I am curious on the performance impact of a write-heavy table :) –  Derek Downey Nov 18 '11 at 21:42
    
I'll use an archive table, I hope that will limit the performace impact –  Beat Nov 18 '11 at 22:53
    
That should be fine. Keep in mind that the ARCHIVE storage engine has no indexing. Only INSERTs and SELECTs via full table scans are available. –  RolandoMySQLDBA Nov 18 '11 at 22:55
    
That shouldn't be a problem: I won't access the logs on a regular basis –  Beat Nov 18 '11 at 23:26
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
share|improve this answer
    
you can access the previous record using OLD.colName and you can use new updated record using NEW.colName –  stephan de jager Nov 26 '13 at 9:41
    
example "IF OLD.eventName <> NEW.eventName THEN /*log my query*/" –  stephan de jager Nov 26 '13 at 9:42

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.