CODEWITHSUNDEEP
phpmysql
BlackMouse
BlackMouse

Reputation: 4552

How to log a specific query?

Is it possible to log a specific query?

I have some matches in a game that quits unexpected. And the only way a match can quit is if an UPDATE query set the value of "matchStatus" to 2 in a row. Is it possible to log all the queries that do this? In that case, how do you do it?

e.g 

$query = "UPDATE active_matches SET matchStatus = '2' WHERE match_id='$match_id'";

Thanks

Upvotes: 1

Views: 89

Answers (2)

eggyal
eggyal

Reputation: 125835

An alternative solution, which is a little more complicated to implement, but may or may not be more suited to your needs, is to create a log table into which a trigger inserts entries upon the execution of such update queries:

CREATE TABLE AugitLog (
  `Timestamp`  TIMESTAMP,
  `User`       VARCHAR(16),
  `Host`       VARCHAR(255),
  `MatchID`    INT
);

DELIMITER ;;

CREATE TRIGGER audit_match_termination AFTER UPDATE ON active_matches FOR EACH ROW
IF OLD.matchStatus <> 2 AND NEW.matchStatus = 2 THEN
  INSERT INTO AuditLog VALUES (
    NOW(),                          -- timestamp
    SUBSTRING_INDEX(USER(),'@', 1), -- username
    SUBSTRING_INDEX(USER(),'@',-1), -- hostname
    NEW.match_id                    -- the match that was terminated
  );
END IF;;

DELIMITER ;

Upvotes: 1

Incognito
Incognito

Reputation: 20765

Inside your my.conf file add the line

log = /var/log/mysql/someLog.log

Restart mysqld and you'll have logs of these queries put here.

Later you can simply grep values out of the log file as per your needs:

grep 'UPDATE active_matches' someLog.log Matches all lines with *UPDATE active_matches*, and outputs, and obviously you can change the pattern if you want to check for anything else.

Upvotes: 2

Related Questions