7

I'm looking for a solution similar to the inotify method of watching files for changes. I'm aware that I could watch the binlog file of the mysql database and run queries to pick out the new results but that seems very inefficient and inelegant; as does simply doing masses of queries in a loop waiting for new results.

Drake
  • 1,938
  • 3
  • 18
  • 26
  • 7
    http://stackoverflow.com/questions/3501346/how-do-i-hook-into-an-event-triggered-once-a-mysql-query-is-true – daxim Dec 11 '10 at 20:44

3 Answers3

6

If you add a TRIGGER to the table(s) you're interested in, you can use that to alert the watching application. You could do that in a number of ways:

  1. Create an audit table in the database, and have the trigger write the relevant info there; and have your watching application poll the audit table for new entries. You're still polling, but in a controlled way which won't hit the server too hard.
  2. Have the trigger call an external app through a UDF.
Vince Bowdren
  • 8,326
  • 3
  • 31
  • 56
  • 3
    That's what [I commented already 6 days ago](http://stackoverflow.com/q/4418598#comment-4820725) as already existing answer. Enjoy your bounty. – daxim Dec 17 '10 at 13:59
  • 2
    FWIW, I added this answer before I'd noticed your comment (which I did upvote); and the first of my suggested solutions is different from yours. I genuinely wasn't plagiarising. – Vince Bowdren Dec 17 '10 at 16:33
  • 3
    @daxim: If you wanted the bounty - you should have posted your comment as an answer! ;-) – Brad Dec 20 '10 at 20:34
1

As far as MyISAM tables go you can watch information_schema.TABLES.UPDATE_TIME. That would save you from polling all tables you're interested in. For InnoDB, watching binlog is the best I can think of.

Mchl
  • 61,444
  • 9
  • 118
  • 120
0

Another approach is to do a push/signal instead of a DB poll. Have whatever process updates the database, notify your Perl code that an update was done via any IPC you pick (heck, a log file being appended to with name of table being changed might do the trick well enough).

This is especially effective if the updates a fairly rare/low volume yet the reaction time to them must be quick.

An additional benefit is portability - works for any MySQL backend or for any other DB engine.

DVK
  • 126,886
  • 32
  • 213
  • 327
  • This is unfortunately not an option as the database is updated by some other 3rd party program I am using and the perl script I am writing does not do the updating of the database. – Drake Dec 11 '10 at 20:22