Can I get MySQL to call Ruby?
February 26th, 2008 4:07:47 pm pst by Sterling CamdenMySQL and I are acquainted, but we have yet to establish an intimate relationship. She’s a cheap date who’s always available and doesn’t make a scene. But lately she’s been eluding my desire for something more exciting. Maybe she’s jealous because I want to bring Ruby in on one of her triggers.
Maybe you can help me to be more persuasive. I need to execute a Ruby script whenever a row is inserted or deleted on a specific table. Ideally, this should occur as immediately as possible. It would make sense to do this from within a MySQL trigger function, but there doesn’t seem to be any easy way to get there from here. I searched the MySQL forums and found three similar requests, but no answers.
Guy Naor posted exactly what I need except that it uses Postgres’ LISTEN/NOTIFY mechanism, which is sadly lacking in MySQL. Unfortunately, we don’t have the option of changing database servers.
Baron published an approach using advisory locks as a notification mechanism. But commenter Arjen Lentz said “I’ve tended to advise against using MySQL’s advisory locking functions, there are enough pitfalls to make it undesirable in terms of relying on it for an application” — without specifying exactly what those pitfalls are. Besides, the idea of using something intended as a lock in place of a message seems like it’s bound to get, um, locked up.
We’re currently considering using a polling mechanism instead. The trigger function would insert a row into a new table that acts as a queue, and the polling process would read the queue and handle the notifications. But that does introduce a delay of up to a little more than our polling interval, and it also seems just a bit Mousetrappish. I’m hoping that some of my readers who have taken MySQL and Ruby around the block a few more times than I have (glances with puppy dog eyes in the direction of Assaf, lower lip protruding and trembling slightly) might come up with a better idea (pppplease).
Posted in Coding...OK?, Wildly popular | 2 Comments » RSS 2.0
Subscribe by Email
I can tell you what we’re doing: polling and no logic in the database.
To poll you just look for changes in the table you’re monitoring, there’s always a created_at and updated_at index. No secondary table necessary. That works pretty well for asynchronous stuff, and no need to maintain queues.
For synchronous stuff, you do it synchronously. If you need two actions done together, you write the code to do actions, but you don’t split the code in different places using a trigger or stored procedures.
Thanks for responding, Assaf. I expect we’ll stick with polling then.
I originally thought it would be a good idea to call this code from the clients that do the inserts/updates, but those clients are somewhat uncontrolled.