Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted
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.

share|improve this answer
    
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] –  Zitrax Feb 13 '10 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

share|improve this answer
    
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. –  Zitrax Feb 13 '10 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. –  Andrew Cholakian Feb 14 '10 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.

share|improve this answer

If you know C++ you can use the MySQL Replication Listener library to do Change Data Capture: https://launchpad.net/mysql-replication-listener

share|improve this answer
1  
We 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. –  Chopper3 Jan 13 '11 at 11:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.