The Boston Diaries

The ongoing saga of a programmer who doesn't live in Boston, nor does he even like Boston, but yet named his weblog/journal “The Boston Diaries.”

Go figure.

Wednesday, March 07, 2007

“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 (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.

The first pass involved writing an extention to MySQL to run an external program from a trigger. 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 is very difficult to get right, and it is very easy to cause serious problems with your application.

Catch-22 of the Active Database

Well … good, bad, I'm the guy with the compiler. And Bill Karwin's advice of polling the database 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.

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 …

Obligatory Picture

[The future's so bright, I gotta wear shades]

Obligatory Contact Info

Obligatory Feeds

Obligatory Links

Obligatory Miscellaneous

You have my permission to link freely to any entry here. Go ahead, I won't bite. I promise.

The dates are the permanent links to that day's entries (or entry, if there is only one entry). The titles are the permanent links to that entry only. The format for the links are simple: Start with the base link for this site:, then add the date you are interested in, say 2000/08/01, so that would make the final URL:

You can also specify the entire month by leaving off the day portion. You can even select an arbitrary portion of time.

You may also note subtle shading of the links and that's intentional: the “closer” the link is (relative to the page) the “brighter” it appears. It's an experiment in using color shading to denote the distance a link is from here. If you don't notice it, don't worry; it's not all that important.

It is assumed that every brand name, slogan, corporate name, symbol, design element, et cetera mentioned in these pages is a protected and/or trademarked entity, the sole property of its owner(s), and acknowledgement of this status is implied.

Copyright © 1999-2023 by Sean Conner. All Rights Reserved.