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

Wednesday, November 21, 2012

Systems Administrator at the Broker Exchange Network, LLC, Available Now.





We need another Sys Admin!

   Position responsibilities include networking with Dell and Cisco devices, Astaro and Watchgard firewalls, Barracuda load-balancers. Also systems administration and building for mostly Dell based servers including Fusion-IO cards, DAS, NAS and other solutions for performance. These systems run Linux varieties, primarily CentOS. These include web servers and database servers running Linux, Apache, MySQL, Php (LAMP). Other languages, such as Java, may be used on some of these servers also. There is also a few Microsoft servers in office for file sharing and a few other tasks.




Check out our pages on facebook and our primary websites!