mysql database listener
Solution 1
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.
http://dev.mysql.com/doc/refman/5.0/en/handler.html
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.
Solution 2
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
Related videos on Youtube
Zitrax
Updated on September 17, 2022Comments
-
Zitrax almost 2 years
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.
-
Zitrax over 14 yearsHandlers 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]
-
Zitrax over 14 yearsCurrently 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.
-
Andrew Cholakian over 14 yearsWell, 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.
-
Chopper3 over 13 yearsWe 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.