• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL value insert/update monitoring using JDBC

 
Arun Suresh
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys

My app has to monitor certain columns of multiple tables in MySQL DB for anomalous value entry. If any wrong value is found to be entered, say score > 100, send a mail to Admin. Keeping scalability in mind, opted out of triggers. As of now thinking about running SELECT periodically in these columns and comparing with acceptable values and if failed populate a queue with warning message. From the queue the email app will pick up the messages and send email.
But was wondering if we can implement any event listeners in JDBC which can listen for anomalous entries in specific columns and then trigger the queue population. Other better options are also welcome. Please advise.

Thanks in Advance
 
K. Tsang
Bartender
Posts: 3457
14
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Arun

Is your JDBC part of a web app or something? Or just pure for "monitoring" sake?

If it's part of an app (web or not), why not do the checking when users enter data (eg commit and rollback)? If it's more of a batch format, one question you need to answer is suppose you find some records faulty, what you going to do with them? Delete, move to error table etc? Will the original user or admin know why this record is faulty?
 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have a trigger automatically populate a "queue" table, then read that table to generate the emails.

The problem with your solution is that the invalid data has already been inserted.
The trigger can write the "queue" table and rollback the insert all in one operation.

WP
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First of all, I'd try to prevent users from entering wrong values using check constraints as much as possible. Preventing the wrong data from entering the DB is much, much better than dealing with it retroactively. Even if you want to allow the anomalous values to enter the database for some reason, they could perhaps be put into separate tables and reviewed periodically by a dedicated process?

Secondly, why do you think that triggers will perform worse than repeated selects being run over the tables? I believe the opposite would probably be true. The statements to monitor for DB changes will have to somehow find just the new and updated rows (really not an easy task in concurrent environment), and then distinguish and skip rows that were already seen and reported. This kind of queries tends to be expensive and robust concurrent implementation can lead into locking issues (even on Oracle, in MySQL - which uses different concurrency mechanism - that would probably be harder still).

JDBC itself does not have tools to listen to database changes. I believe there are solutions which allow Java code to be notified of changes in the MySQL database, but firstly I believe these are trigger-based, and secondly this would imply a callback into your application, which would then probably have to re-read the data that was inserted and modified, which looks like a hell lot of an overhead to me.

I would go with triggers in some form. The queue table seems good for me, I'd just populate it from the triggers. For example, in one of our application (which runs on Oracle) we monitor changes to some 100+ tables. We have triggers over these tables which add records identifying the table, row and nature of the change (insert, update, delete) to a kind of queue table. Clients application then poll this table repeatedly. Once processed, the records are removed from the queue. We had to spend some time designing this to ensure no consistency and locking issues and the solution is highly specific to Oracle, but I believe something similar could work in MySQL.
 
Arun Suresh
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
Thanks for the replies. This is an independent monitoring app and cannot touch the original app that inserts/updates the data. And these values are mainly monitored not to rollback but for manual inspection by admin for any fraud. so they can be allowed to be inserted and Admin just needs to know. Later UI also need to be integrated to this.

From your advice I will look into the possibility of Triggers populating the MQ. So triggers will populate the MQ , from MQ we will check the transactions and send email and simultaneously populate another warnings table for usage of UI later.

Anyway, if any one knows any free/paid, hibernate/jdbc apps that already implements SQL value monitoring logic, it would help me from re inventing the wheel.

Thanks,
Arun




 
Bill Clar
Ranch Hand
Posts: 163
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If this data is for inspection then why not run your query once, at the end of the work day? This allows you to give daily updates to the admin every morning.

I have several Crystal Reports that tell the supervisors about questionable data entered on the previous day. I can't disallow it's data entry, but the supervisors still need to know about it.



 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic