Derek Neely

After some reading of consolidation options for Apache logs, I ran across mod_log_sql (we are hating spread) which will take Apache logs and log them off to a MySQL database. Sounded great! We could then run scripts to go through and parse the values and run statistics on. Twas perfect for our needs. RIGHT!!!

At my company we run about 12 vhosts over about 5-6 load balanced web servers. All of them were configured to log to our loganalysis server which is a pretty beefy machine. The logging all in all worked well with a few major exceptions listed below.

1) I used the directive:

LogSQLRequestIgnore .gif .jpg .css .ico .png .js

This directive is supposed to be used to ignore any pages ending with that extension. This did not work at all and I had to create a script to actually delete those before analyzing the logs. Bummer but not that big of a deal.

2) While working with the server to optimize the database, there were various times when I would need to restart the MySQL service and a few times I needed to reboot the server. During these periods of time, the web servers were unable to log to the database which brought them to their knees. The inability of the module to handle a database outage gracefully was a major deal breaker for us. I feel this issue is a result of intense disk IO when the database is down. The server is logging to its Apache logs, the the backup SQL logs, and to the Apache error logs for every failed request. This becomes emense with thousands of requests per second. Should this server die or needing maintenance would have ultimately brought our company to a hault.

Long story short, we’re scrapping mod_log_sql and going with an NFS mount out to all the web services which we can then parse and run statistics on using some custom scripts and/or AWStats or Splunk.

I know I’m probably in the past on this, but I recently discovered MySQL triggers and the great power and convenience behind them. Below, I’m trying to capture some basic examples of how to use them.

Triggers are actions that are performed when another action occurs. Specifically on any INSERT, UPDATE, or DELETE. One brief example would be, when you delete a row/record from one table, you can automatically delete any reference to that record in other tables.

First create two (2) tables:

CREATE TABLE a (a_id int(11) NOT NULL auto_increment, a_value varchar(100) NOT NULL, PRIMARY KEY (a_id))

CREATE TABLE b (b_id int(11) NOT NULL auto_increment, b_value varchar(100) NOT NULL, a_id int(11) NOT NULL, PRIMARY KEY (b_id))

We will use/assume the relationship of 1-to-many (a-to-b). In the b-table we have a column ‘a_id’ which will be a logical foreign key back to the a-table. (I know you can specify foreign keys on the tables but for this tutorial/example that isn’t exactly necessary.)

Now, on to the triggers…

There are 6 trigger events associated as BEFORE or AFTER of INSERT, UPDATE, or DELETE:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

The general syntax for creating a trigger is:

delimiter //
CREATE TRIGGER
[trigger_name]
[BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON
[table_action_perfomed_on]
FOR EACH ROW BEGIN
[sql_statement_of_what_to_do];
END;
//

DELETES: This trigger will run upon a row being deleted.

delimiter //
CREATE TRIGGER
a_table_on_delete
BEFORE DELETE ON
a
FOR EACH ROW BEGIN
DELETE FROM b WHERE a_id=OLD.a_id;
END;
//

Once you have created your trigger, you can view your triggers using SHOW TRIGGERS:

mysql> show triggers\G;
*************************** 1. row***************************
Trigger: a_table_on_delete
Event: DELETE
Table: a
Statement: BEGIN
DELETE FROM b WHERE a_id=OLD.a_id;
END
Timing: BEFORE
Created: NULL
sql_mode:
Definer: root@localhost

Also, be sure to reset your delimter for standard SQL statements.

mysql> delimiter ;