mysql-trigger-logger
mysql-trigger-logger.sql
demonstrates how to use MySQL triggers to log the timestamp, user, SQL and a custom note for unexpected changes ("heisenbugs") from UPDATE
or INSERT
statements to a logging table according to custom conditional statements.
Getting Started
- Download
mysql-trigger-logger.sql
- Edit the trigger definitions logic after line 22 for your use case (you should not need to update the stored procedure)
- Test in your development environment first
- Ask your DBA to install it in other environments. Note that adding triggers will interfere with trigger-based schema change tools like
pt-online-schema-change
- Matching SQL queries will be inserted into the
debug_log
table. Either check that table periodically manually, or use one of the provided cron jobs likenotify-trigger-logger.pl
to send alert emails automatically. - Don't forget to disable the trigger(s) when you're done!
Cron Jobs
The following cron scripts (tested on Linux) are provided to read the logging table, debug_log
, for new alerts (alerted='N'
), send the alert email, then toggle that row to alerted='Y'
:
notify-trigger-logger.pl
(written in Perl)notify-trigger-logger.py
(written in Python)notify-trigger-logger.rb
(written in Ruby)notify-trigger-logger.sh
(written in bash. avoid embedded tabs in the query and note columns for best results.)
There are minimal dependencies:
- the mysql client package for your OS, on Redhat/CentOS typically installed by
sudo yum -y install mysql-client
- for Perl, a mailer compatible with the
sendmail
program name from either the sendmail or postfix packages andsudo cpan install DBI DBD::mysql
- for Python, a mailer compatible with the
sendmail
program name from either the sendmail or postfix packages andsudo pip install MySQLdb
- for bash, the
mailx
command
Security
- No temporary files are created, and no files are read or written.
- The scripts can be run as a non-privileged OS user and made read-only to that user to make the database password private or ...
- You can move the login details to
/etc/notify-trigger-logger.ini
to centralize credentials. - A limited-privilege MySQL database user can be created with
GRANT SELECT, UPDATE on debug_log to 'debug_log'@'127.0.0.1';
- The default email alerts contain raw SQL statements, which could contain PII or PHI. You may want to omit or mask the queries for compliance reasons.
- If HTML can be injected from an end-user, then you should use the Perl or Python scripts and escape the query text. bash scripts are impractical to harden against unknown input strings.
Notes
- Adding triggers to busy tables will impact performance since triggers are executed for all rows, even if your logic refers to a subset of rows.
- It's recommended to try simpler debugging methods first, like grepping SQL statements in source code or reading application logs, before installing triggers in your production database. This use of triggers is a last resort to narrow down the source of an intermittent problem.
- MySQL has a limit of one trigger type,
INSERT
orUPDATE
, per table. AdditionalCREATE TRIGGER
statements will be skipped. - The provided cron scripts send one alert per log entry, all at once. If your triggers log many rows, then you will get many alert emails. There are several ways to customize the alerting behavior:
- add a counter in the inner loop of the script and exit after say 10 alerts per run, or use
LIMIT 10
in theSELECT
statement - change the
SET alerted='Y' WHERE id=
tonote=
oralerted='N'
to mark multiple items as already alerted - do
SELECT id, ts, user, query, note, COUNT(*) cnt FROM debug_log WHERE alerted='N' GROUP BY note
to send one alert email per note string with a count per note string.
Alert Email Sample
From: root@localhost
To: me@apple.com
Date: 2018-11-05 17:59
Subject: notify-trigger-logger.pl: 1: unexpected value in db.client.intervals
Time: 2018-11-05 17:58:53
User: prod@127.0.0.1
Query:
INSERT INTO client SET intervals = 15 WHERE id = 1;
Schema for debug_log
CREATE TABLE `debug_log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`query` varchar(1024) NOT NULL DEFAULT '',
`user` varchar(32) NOT NULL DEFAULT '',
`note` varchar(1024) NOT NULL DEFAULT '',
`alerted` char(1) NOT NULL DEFAULT 'N',
PRIMARY KEY (`id`),
KEY `idx_alerted` (`alerted`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
License
MIT License