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 guess this answer is pretty late but as @dbf pointed out, reading binlog might be the way to go. I suggest looking into Debezium's MySQL Connector.

---

The Debezium MySQL connector reads the binlog and produces change events for row-level INSERT, UPDATE, and DELETE operations and records the change events in a Kafka topic.

Your "another" application can then read those Kafka topics.

Answer 2

assume we want to monitor changes in the table 'table1'

CREATE TABLE `table1` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `value` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10;

above is the query to create 'table1' it contain a 'id' column which is auto increment

create another table to store changes. Query is given below

CREATE TABLE `changes` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `changes` VARCHAR(200) NULL DEFAULT '0',
    `change_time` TIMESTAMP NULL DEFAULT NULL,
    `tablename` VARCHAR(50) NULL DEFAULT NULL,
    `changed_id` VARCHAR(10) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=21;

now create a trigger in first table ie.. 'table1' Query is given below

delimiter |
create trigger trg_table1 AFTER INSERT ON table1
FOR EACH ROW BEGIN
DECLARE lastid INT DEFAULT 0;
SELECT max(id) INTO lastid from table1;
insert into changes values(null,'insert',now(),'table1',lastid);
end;
|
delimiter ;

Now if you try to insert anything in 'table1' its details will be automatically inserted in to changes table. In changes table changes indicates the type of change ie.. insert,update etc change_time indicates time at which change occur tablename indicates table in which change occur changed_id indicates id of the newly inserted row in the 'table1'

Now create a java program that continuously reading from 'changes' table. A new entry in 'changes' table means something happened to database. From each record in the 'changes' table you can understand in which table the insert operation is occured. And based on this you can perform appropriate action. After performing appropriate operation delete that row from 'changes' table.

You can create trigger (like i did above) for each table in your database... From 'changes' table's 'tablename' column you can understand insert occurred in which table..

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.

Answer 4

You could use a queuing solution like Q4M, but it might be overkill in your situation. But you could:

In the MySQL database, add a timestamp column to the table that is being inserted into. In the 'import' application either use a java.util.timer, or an external scheduler like cron. Use one of those to trigger a task that reads the insert table where the timestamp column is null. Take the appropriate action for those rows, and then set the timestamp column with a value. If there are no rows with a null time stamp, you have no new inserts. Simple, but it works.

You may want to add an index to the timestamp column for performance reasons.

Answer 5

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 6

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.