MySQL 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).