2

I am writing a trigger that suppose to fire on inserts for a table and log the exact query used to insert.

However, using below

SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();

Always return in original_query variable this above query not the one firing the trigger and count in processlist is 1.

I am in MySQL 5.6.18.

This is the whole trigger deffinition

DROP TRIGGER IF EXISTS affiliate_revenue_i_log$$
CREATE TRIGGER affiliate_revenue_i_log BEFORE INSERT ON cds_affiliate_revenues
    FOR EACH ROW
    BEGIN
      DECLARE original_query VARCHAR(1024);        
      SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();
      INSERT INTO cds_affiliate_revenues_log (action,id,added_date,original_query) VALUES('insert',NEW.id,NOW(),original_query);
    END$$
  • I'm not sure (don't think so) that you can log by table. But what you can do is activate the general log - general_log = on in [mysqld] section of my.cnf. Warning - only do this on dev/test systems - on production you run the risk of crippling your system! – Vérace Jul 16 '15 at 12:30
1

You could use log_output and general_log (USE CAREFUL), for those blessed with MySQL >= 5.1.12:

Steps:

I've created a new table called new_table:

CREATE TABLE new_table (
  id int(11) NOT NULL,
  text varchar(45) DEFAULT NULL,
  text2 varchar(45) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I've created a new log table called new_table_log:

CREATE TABLE test.new_table_log LIKE new_table;

ALTER TABLE test.new_table_log 
CHANGE COLUMN text2 text2 VARCHAR(1024) NULL DEFAULT NULL COMMENT '' ;

Trigger:

USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.new_table_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`new_table_AFTER_INSERT` AFTER INSERT ON `new_table` FOR EACH ROW
BEGIN
    DECLARE vOriginal_query VARCHAR(1024); 
    DECLARE vLAST_LO VARCHAR(15) DEFAULT 'NONE';
    DECLARE vLAST_GL ENUM('ON','OFF') DEFAULT 'OFF';
    SET vLAST_LO=@@log_output;
    SET vLAST_GL=IF(@@general_log=0,'OFF','ON');
    SET GLOBAL log_output = 'TABLE';
    SET GLOBAL general_log = 'ON';
    SELECT argument INTO vOriginal_query FROM mysql.general_log WHERE argument LIKE 'INSERT%' AND argument NOT LIKE '%NEW.%' AND thread_id=CONNECTION_ID() ORDER BY event_time DESC LIMIT 0,1;
    INSERT INTO new_table_log(id,text,text2)VALUES(NEW.id,'INSERT',vOriginal_query);
    SET GLOBAL log_output = vLAST_LO;
    SET GLOBAL general_log = vLAST_GL;
END$$
DELIMITER ;

Insert into new_table:

INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST');

Test:

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE new_table (
    ->   id int(11) NOT NULL,
    ->   text varchar(45) DEFAULT NULL,
    ->   text2 varchar(45) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test.new_table_log LIKE new_table;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE test.new_table_log 
    -> CHANGE COLUMN text2 text2 VARCHAR(1024) NULL DEFAULT NULL COMMENT '' ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test.new_table;
+----+------+-------+
| id | text | text2 |
+----+------+-------+
|  1 | Lel  | TEST  |
+----+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.new_table_log;
+----+--------+-----------------------------------------------------------------+
| id | text   | text2                                                           |
+----+--------+-----------------------------------------------------------------+
|  1 | INSERT | INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST') |
+----+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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