boosten.org

Using triggers in MySQL

S call me a geek for having several different machines at home, but I like to split different functions on different machines.

So I have a web server whose solely purpose it is to serve web pages (for 4 different external domains, and some test sites as well), which is completely separated from my MySQL server. Yet another server is doing my email, but also is used for some special purposes, like generating graphs.

Up till now I had this last machine generating graphs, which were rsynced once every hour to the web server. There was no trigger for generating the graph, so I had scheduled a cronjob once a day to generate those graphs.

One of these graphs is created from data input via a web form, which usually happens around 7 AM, and the graph is created at 9 AM, and the rsyncing takes place at 9:05 AM (actually again at 10:05 AM, and so on).

The problem with this method is that whenever the web form gets filled after 9 AM, the correct graphs don’t show up until the day after, and generating the same graphs more times a day was not an option for me.

So I found a way to synchronize all these actions by using a trigger in MySQL. I will be referring to my servers by the following names: Graph, MySQL and Web.

What are triggers? A trigger in MySQL is able to perform a task whenever a row in a table is inserted, updated, or deleted, and it can do that just before the row is actually altered, or after this joyful event.

Let’s start with the syntax:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

Normally, a trigger runs with the authorizations of the user inserting, deleting or updating the table the trigger runs on, but you’re able to modify this behavior with the DEFINER clause. I’m not going to use this one, but made sure the authorizations of the user are in order.

The trigger_time can have two possible values, either BEFORE or AFTER. I want my actions taking place after the data is inserted.

The trigger_event can hold the values INSERT, UPDATE or DELETE.

The trigger_stmt (statement) is the action that gets executed when this trigger is activated.

I found in my quests, that it’s not possible, using standard MySQL, to have this trigger perform a system call, thus executing an external command. There are ways to do so, but it requires complex programs, calling named pipes or other ways. Since my applications aren’t time critical, I can live with a minute delay (the minute delay comes from cron, I will describe that below).

For this purpose I created an extra table in my database, to hold the action that needs to be performed:

CREATE TABLE `work` (
  `name` varchar(25) DEFAULT NULL
)

Nothing fancy, it’s just to hold the action, until it has been performed.

Now for the trigger:

CREATE TRIGGER nutstrigger AFTER INSERT ON nuts
FOR EACH ROW
BEGIN
  insert into work values ('nuts');
END

Don’t pay attention to the word ‘nuts’. In the Netherlands we call the utility companies (the ones that deliver power, gas and water to our houses) nuts, and I keep statistics on those on a daily bases by noting down the actual meter readings (don’t ask me why, I just like numbers).

I don’t know why we call those companies nuts, but I know I’m going nuts whenever they send a bill .

Back to the trigger: AFTER a new row is inserted into table nuts, insert the word ‘nuts’ into table work. If I weren’t to do nothing with this table work, then the word ‘nuts’ would be added every day to this table.

But that’s not my intention.

Therefore, on server Graph I wrote the following shell script, that is executed every minute (cron).

#!/bin/sh
 
TASKS=`/usr/local/bin/mysql -BNe 'select distinct(name) from work'`
day=`date +%d`
 
for TASK in ${TASKS}; do
  case ${TASK} in
  nuts) /usr/local/bin/mysql -BNe 'delete from work where name="nuts"'
	   /home/peter/bin/nuts2.sh
	   if [ "$day" = "01" ] 
	   then
		/home/peter/bin/nutsmonthly.sh
	   fi
        /usr/local/bin/mysql -BNe 'insert into work values ("sync")'
	   ;;
  esac
done

It’s no rocket science, but let me explain:
TASKS is filled with a list of unique (distinct) actions it finds in table work. Distinct, because there’s no point in running the action more than once within a minute. These actions are separated by blanks, so make sure you don’t define any actions with blanks in them.

The day variable I need to do some special actions on the first of the month, but that could have been solved another way.

With the for loop, I loop through these different actions. The case selector then checks whether an action has been defined for this server. In this case, it’s only programmed to react on ‘nuts’. If it actually finds this action, I remove it from the table, or otherwise it would find it minute after minute.

I then run the nuts2.sh script, which basically performs a query in MySQL, and creates a graph of it, using GnuPlot. Depending on the day of the month I run the nutsmonthly.sh script, which does the same, but generates monthly graphs.

The last MySQL inserts another action into the work table, for another server, Web. This server copies the generated graphs from Graph. Remember, I do this on separate servers to keep the web server as clean as possible.

The script on Web looks like this:

#!/bin/sh
 
TASKS=`/usr/local/bin/mysql -BNe 'select distinct(name) from work' peter`
day=`date +%d`
 
for TASK in ${TASKS}; do
  case ${TASK} in
  sync) /usr/local/bin/mysql -BNe 'delete from work where name="sync"'
	   /home/peter/bin/resync.sh
	   ;;
  esac
done

Same story here, but it checks on another action (‘sync’), and does something else. resync.sh holds two rsync commands.

The last script is actually no longer related to the trigger, but it shows how servers can be daisy changed if they have to perform some action on the same trigger.

This entry was posted on Sunday, November 1st, 2009 at 11:11 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.