* * * * *

                 “Good, bad, I'm the one with the compiler.”

I've been doing some heavy programming lately, dealing with schlepping bits
into and out of QuickBooks [1] (which I'll name Project “Pacino” which is
related to the actual name of the project) and my part so far is with getting
data into and out of MySQL [2].

The first pass involved writing an extention to MySQL to run an external
program from a trigger [3]. One guy, however, kept popping up on the various
threads advising against this:

> I always recommend against doing things like this. Calling an external
> processes from a trigger or UDF (User Defined Function) is very difficult
> to get right, and it is very easy to cause serious problems with your
> application.
>

“Catch-22 of the Active Database [4]”

Well … good, bad, I'm the guy with the compiler [5]. And Bill Karwin's advice
of polling the database [6] doesn't sit well with me. Heck, polling in
general doesn't sit well with me (“Are we there yet?” “Are we there yet?”
“Are we there yet?” “Are we there yet?” “Are we there yet?”). But I can see
why Bill is cautious, and I wrote the code with his problems in mind [7].

It works, but there are cases when the notification doesn't go through (the
program we invoke collects the updated record to update another, non- MySQL
database (else we'd just use MySQL replication) and if we can't update the
other database, the notification goes unnoticed). So it was back the drawing
board for another approach. This time, the approach is for MySQL to log the
changes to another table and to notify a daemon of the update. The daemon
then pulls the changes from the table MySQL updated and does whatever with
them. Since MySQL also logs the changes, if the daemon isn't running, the
changes just pile up in a queue for later processing.

Now, as I'm working on attempt two, I did get permission to release the code
for calling an external program from MySQL. The code is pretty simple and
while it does work, it's quite simple (only MySQL strings are supported for
instance) and could hammer a server pretty hard, depending upon the size of
the update. It's not public yet, but if anyone were interested in the code, I
can give it out …

[1] gopher://gopher.conman.org/0Phlog:2006/12/29.1
[2] http://www.mysql.com/
[3] http://mysql.meetup.com/137/boards/view/viewthread?thread=2280640
[4] http://karwin.blogspot.com/2006/10/catch-22-of-active-
[5] http://www.everything2.com/index.pl?node_id=471021
[6] http://karwin.blogspot.com/2006/10/catch-22-of-active-
[7] gopher://gopher.conman.org/0Phlog:2007/03/07/double-fork.x-html

Email author at [email protected]