launch a process off a mysql row insert

Question

I need to launch a server side process off a mysql row insert. I'd appreciate some feedback/suggestions. So far I can think of three options:

1st (least attractive): My preliminary understanding is that I can write a kind of "custom trigger" in C that could fire off a row insert. In addition to having to renew my C skills this would requite a (custom?) recompile of MySQl ... yuck!

2nd (slightly more attractive): I could schedule a cron task server side of a program that I write that would query the table for new rows periodically. This has the benefit of being DB and language independent. The problem with this is that I suffer the delay of the cron's schedule.

3rd (the option I'm leading with): I could write a multi threaded program that would query the table for changes on a single thread, spawning new threads to process the newly inserted rows as needed. This has all the benefits of option 2 with less delay.

I'll also mention that I'm leaning towards python for this task, as easy access to the system (linux) commands, as well as some in house perl scripts, is going to be very very useful.

I'd appreciate any feedback/suggestion

Thanks in advance.

Answer 1

Write an insert trigger which duplicates inserted rows to a secondary table. Periodically poll the secondary table for rows with an external application/cronjob; if any rows are in the table, delete them and do your processing (or set a 'processing started' flag and only delete from the secondary table upon successful processing).

This will work very nicely for low to medium insert volumes. If you have a ton of data coming at your table, some kind of custom trigger in C is probably your only choice.

Answer 2

I had this issue about 2 years ago in .NET and I went with the 3rd approach. However, looking back at it, I'm wondering if looking into Triggers with PhpMyAdmin & MySQL isn't the approach to look into.