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 …
