Monitor MySQL inserts from different application

Question

I currently have a webservice which inserts information in a mysql database using Hibernate. Some of this information needs to be processed by another 'import' application. I would like to not have to trigger this application from the webservice. So the webservice doesn't have a dependency on the webservice and visa versa.

Is there a way to "listen" to changes (specifically: insert) in the database from the 'import' application and then start executing an action. I have looked at triggers but these seem to only work for changes in the application's Hibernate Session and not for 'external' changes.

Edit*

In short, the answer I would like to have; Is it possible to monitor changes to a mysql database/table (coming from any source) from a java application which does not alter the database/table itself

Bounty Update*

I will award the bounty to the person who can explain to me how to monitor changes made to a MySQL table/database using a Java application. The Java application monitoring the changes is not the application applying any changes. The source of the alterations can be anything.

Answer 1

I think you could acheive something like this fairly easily, assuming you didn't mind a creating some extra tables & triggers on your database, and that the monitoring java application would have to poll the database rather than specifically receive triggers.

Assuming the table you're wanting to monitor is something like this:

CREATE TABLE ToMonitor ( id INTEGER PRIMARY KEY, value TEXT );

Then you create a table to track the changes, and a trigger that populates that table:

CREATE TABLE InsertedRecords( value TEXT );
CREATE TRIGGER trig AFTER INSERT ON account
FOR EACH ROW INSERT INTO InsertedRecords( value ) VALUES ( NEW.value );

This will cause the InsertedRecords table to be populated with every insert that happens in ToMonitor.

Then you just need to set up your monitoring app to periodically SELECT * from InsertedRecords, take the appropriate action and then clear out the records from InsertedRecords

EDIT: A slight alternative, if you didn't mind a bit of C/C++ coding, would be to follow the instructions here to create a custom SQL function that triggered your monitoring application into action, and then just call that SQL function from within the trigger you'd created.

Answer 2

You can read mysql binary log. Here you can find some information. There is a java parser and another one - but it is marked as unfinished) also you can look for similar parsers using another languages (for example, perl) and rewrite them in Java.
Also have a look at mysql-proxy.

Answer 3

I know it's not what you asked (thus, this is not a proper answer), but if you consider dropping the idea of "letting the DB notify the apps", you get the perfect case for using JMS for communication between apps.

Your app originating the change could publish a message to a JMS topic, which is subscribed by the second application. Once the first changes the database, it puts a message on the topic. The second then sees this new event and act accordingly. You could even publish the delta in the message, so that the second app don't need to reach the database.

I'm a bit against dealing with this by "hacking" the database to do more than just store data, as it will innevitably get into trouble in the future (as everything eventually will), and debugging it will be hard. Imagine adding a third app the the ecosystem, and you have now to replicate whatever you did for the second app, but now for the third app. If you didn't document your steps, you might get lost.

If you just use a JMS server between those two apps, you can certainly add a third app in the future, that just listens to this topic (and publish a new message, in case it has write access to the db), and the other apps don't even have to know that there's one more app out there. Nor the database.