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.

why dont you use a listener deamon , that checks the Db for changes and calls your second application or does the job that the second application should do ? monitoring tools are not for such use actually. What you ask for is exactly a "listener" that works as a daemon — kommradHomer, Jul 13 '12 at 11:13
yes. a daemon that checks the database or the table. like a reporter daemon — kommradHomer, Jul 13 '12 at 12:17
Well the question doesn't exclude a daemon listening so it still stands. Is there a readymade solution in Hibernate to do such a thing? In this case: A Hibernate listener daemon of some sort? — Thizzer, Jul 13 '12 at 12:22
Do you need atomic processing or can you process changes in batches? — Paulo Scardine, Jul 14 '12 at 19:30
:FahimParkar: Yes I am serious, I would like to have an answer to my question, and SO seems to be kinda quiet without bounty lately. :PauloScardine: I can process in batches. — Thizzer, Jul 14 '12 at 21:29

obmarg · Answer 1 · 2012-07-14T19:31:46.247

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.

Note that when readig and clearing records a transaction should be used, to prevent from deleting unreaded rows. You could also simply update a last_insert_timestamp in your monitor table, so that 'listening' queries can simply check if they are up to date. Finally you can ask MySQl devs for a feature (PostgreSQL have a Listen/Notify command) — regilero, Jul 16 '12 at 15:22

dbf · Accepted Answer · 2012-07-14T19:24:01.050

5

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.

edited Jul 14 '12 at 19:24

answered Jul 14 '12 at 19:03

dbf

6,399
2
38
65

2

You can make likes in-line [like this](http://stackoverflow.com/a/11486564/458741) using the following: `[like this](http://stackoverflow.com/a/11486564/458741)` it massively helps increase the readability of your code. – Ben Jul 14 '12 at 19:25
It seems the java parser(s) doesn't keep running and check if the binlog changes is that correct? – Thizzer Jul 16 '12 at 09:40
Yes, you need to monitor file changes (for example, like this one: http://java.dzone.com/news/how-watch-file-system-changes) – dbf Jul 16 '12 at 09:49

jpkroehling · Answer 3 · 2012-07-17T08:17:41.310

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.

Is there an easy way to create such a message from a non-Java application? — Black, Jul 20 '12 at 10:58
Sure, you can create a webservice which accepts pretty much anything, and converts that into a proper JMS message. You'll only need a "client" which is able to talk HTTP. — jpkroehling, Jul 21 '12 at 17:25

Fathah Rehman P · Answer 4 · 2012-07-17T16:32:31.300

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

score 2 · Answer 5 · answered Jul 14 '12 at 19:37

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.

score 1 · Answer 6 · answered Aug 26 '20 at 21:23

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.

Monitor MySQL inserts from different application

6 Answers6

Linked