monitoring mysql for changes

Question

I have a Java app using a MySQL database through hibernate. The database is really used as persistence layer: The database is read at the initial load of the program, and the records are then maintained in memory.

However, we are adding extra complexity, where another process may change the database as well, and it would be nice for the changes to reflect on the Java app. Yet, I don't particularly like pulling mechanisms to query the database every few seconds, especially that the database is rarely updated.

Is there a way to have a callback to listen to database changes? Would triggers help?

Answer 1

Or change both applications so the Java app is truly the owner of the MySQL database and exposes it as a service. You're coupling the two apps at the database level by doing what you're proposing.

If you have one owner of the data you can hide schema changes and such behind the service interface. You can also make it possible to have a publish/subscribe mechanism to alert interested parties about database changes. If those things are important to you, I'd reconsider letting another application access MySQL directly.

Answer 2

Is there a way to have a callback to listen to database changes? Would triggers help?

To my knowledge, such a thing doesn't exist and I don't think a trigger would help. You might want to check this similar question here on SO.

So, I'd expose a hook at the Java application level (it could be a simple servlet in the case of a webapp) to notify it after an update of the database and have it invalidate its cache.

Answer 3

Another way would be to use a self compiled MySQL server with the patches from this project

ProjectPage External Language Stored Procedures

Check this blog post for a more detailed introduction

Calling Java code in MySQL