Monday, November 26, 2012

Tracking Mod-Queries for Mysql Table

Ok, so I needed to track down a ten year old query that is dynamically generated from "who knows!".
  1. Step One!
    1. Find where the database is getting updated from!!!!


You can log all queries via a global general log.
Downfall is that it slows down the database due to the additional operations that are performed.

SET GLOBAL GENERAL_LOG='ON';
SET GLOBAL LOG_OUTPUT='TABLE';

Query the log via…

Select * from mysql.general_log where argument LIKE ‘start of query%’;


Make sure to shut off the log when done!

SET GLOBAL GENERAL_LOG='OFF';

Hope this helps!



This does not work, but if you have an idea of how to improve it, let me know.
This sets up a trigger that will log any queries that modify trackTableName in the DBNAME database.


<<<<<< SQL

create table if not exists DBNAME.trigger_log LIKE INFORMATION_SCHEMA.PROCESSLIST;

delimiter |

DROP TRIGGER IF EXISTS clickTrigger|
CREATE TRIGGER clickTrigger BEFORE INSERT ON DBNAME.trackTableName
  FOR EACH ROW BEGIN
    INSERT INTO DBNAME.trigger_log SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();
  END;
|

delimiter ;



delimiter |

DROP TRIGGER IF EXISTS clickTriggerUpdate|
CREATE TRIGGER clickTriggerUpdate BEFORE UPDATE ON DBNAME.trackTableName
  FOR EACH ROW BEGIN
    INSERT INTO DBNAME.trigger_log SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();
  END;
|

delimiter ;




delimiter |

DROP TRIGGER IF EXISTS clickTriggerDelete|
CREATE TRIGGER clickTriggerDelete BEFORE Delete ON DBNAME.trackTableName
  FOR EACH ROW BEGIN
    INSERT INTO DBNAME.trigger_log SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();
  END;
|

delimiter ;
>>>>>> /SQL

---- update -----
This is not what I expected.
It is logging the trigger sql, not the query that does the update/insert/delete.

This may work?
http://stackoverflow.com/a/10654126/1267277