Kevin Yeandel’s Random Access Memory

Mysql triggers are not the best in the world and it seems you can’t call something external from the db – like, for example, running a script.

Well, I needed to know if an update had occurred on a table and to fire off a remote command to another server.

The solution was:

1. Add a field to the table called action – default it to NULL
2. Perl script to check the table for rows which contained default=NULL
3. If such a row existed then do some file system action
4. Update the table so that action is no longer null
5. Make the whole thing reliable

Step 1. Create a shell script

check_relay.sh
#!/bin/bash
echo “running application”;
while [ 1 ]; do
echo “running”
perl /home/user/relay2.pl
sleep 3
done

2. create a perl script to query the db table we spoke off earlier (relay2.pl – in my app)

my $database = ‘relay’;
my $host = ‘localhost’;
my $user = ‘relay_user’;
my $pass = ‘relay_pass’;
my $dbh = DBI->connect(“DBI:mysql:database=$database;host=$host”,”$user”,”$pass”) or die “Can’t open DB: $!”;
my $sth = $dbh->prepare(q{SELECT tr_id, actioned FROM relay where actioned is null});
$sth->execute() or die $dbh->errstr;
my $actioned;
my $tr_id; # should use a transaction id so you know which row was updated in case there is more than one.
$sth->bind_columns(\$actioned,\$tr_id);

while($sth->fetch()) {
#here we do stuff on the os, e.g. system(“php ./relay2.php $field_from_db_etc”);
#now update the db so the row no longer requires a trigged type event
my $newQ2 = “UPDATE transactions SET actioned =true WHERE tr_id=$tr_id”;
my $sth3 = $dbh->prepare($newQ2);
$sth3->execute() or die $dbh->errstr;
$sth3->finish;
}
$sth2->finish;
}
$sth->finish;
$dbh->disconnect;

3. create a perl script to check the process is actually running (which it wont be unless you ran it by hand)

#!/usr/bin/perl
open PROS, “ps -ef|grep check_relay.sh |”;
while ($line = <PROS>){
unless ($line =~ m/grep/){
#print “is already running\n”;
exit;
}
}
print “check_relay was not running. Trying to start it.\n”;
exec ‘nohup /home/user/./check_relay.sh &’;
4. Make sure the damn thing boots up about 60 seconds after the system starts and check it is still running every 60 seconds.

Edit cron for the user under which this script will run -

(crontab -e)

* * * * * /home/user/ps_check.pl

If you have done everything right then kill the process and see that it launches again within 60 secs which is the resolution of cron.

Obviously make sure mysql is configured to autostart at the correct run levels if the machine is booted up.

(if I remember correctly on centos, chkconfig mysqld on –level 35)