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