Is there any way to attach some kind of a listener to a table in mysql to monitor changes ? I have tried to search for this but not found anything. I would like to do this to create a ui with live updates.
show table status like 'tablename'
will show details about when the table was last updated.
Also, if you table has an incrementing primary key (auto_increment, timestamp, etc), then the HANDLER interface can be useful for tracking new rows inserted into the table - blocking until new data arrives.
As has been mentioned in other answers, adding triggers to the table is probably the most flexible solution. Your trigger could update an audit table recording the changes, and then your app could poll the audit table to reflect any changes.
Handlers did sound promising and I tried it now. But it is very slow. I have a table with around a million rows, using SELECT on it the reported time for fetching what I want is 0.00 sec , but using a handler it takes 5.04 sec. What I really tried to find out was if I could avoid polling, but it seems that I perhaps just have to poll using SELECT and report to the ui when something changes. [SELECT * from tbl WHERE alfa = 2 ORDER BY beta LIMIT 1] VS [HANDLER tbl READ key LAST WHERE alfa = 2]– ZitraxFeb 13, 2010 at 20:47
There are a couple ways you may be able to accomplish this, but my guess is that you'd be better off doing this in app code than in the database.
If you do want to do this in the DB you have a couple options, MySQL Proxy and MySQL triggers. I'm not sure if MySQL triggers can do anything other than execute SQL, but that could be sufficient, you could use them to create an 'updates' table and regularly poll that. If you don't want to do that you could probably write a MySQL Proxy script in Lua to do what you need, though that would likely be a lot more difficult.
MySQL manual dealing with triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html
An intro to MySQL Proxy: http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html
Currently polling the main table seem to be pretty fast so using triggers just seem to add some complexity. I was just curious if I could avoid polling at all. I have not investigated proxies yet though.– ZitraxFeb 13, 2010 at 20:52
Well, you can give MySQL proxy a shot, but from my experience it's hard to work with, and has mediocre documentation. If you can get away with not using it I'd say spare yourself the headache. Feb 14, 2010 at 2:04
Would a timestamp_inserted column in the table help?
If the new column timestamp_inserted defaulted to NOW() the code changes might not be too severe if you added that. I think in MySQL you don't have to specify columns that have defaults when you INSERT.
With the triggers that Andrew mentioned you can handle more events, like update or delete.
If you know C++ you can use the MySQL Replication Listener library to do Change Data Capture: https://launchpad.net/mysql-replication-listener
1We have a rule about self promotion on one's own products/services but as this is GPL I'm going to leave this post alone but please be aware.– Chopper3Jan 13, 2011 at 11:26