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.
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?
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.
Joined: Sep 02, 2008
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.