0

I work at a startup that is working with different databases among which are MySQL (v4/5) and MSSQL (2014 and older). Different clients we cater to run different dbs. Currently we have a Java program that connects to the db and then pushes data to the API - we tap into the db. We were looking to upgrade the system to have real time updates to the API and then got into a bit of standstill with designing real time updates with a MySQL or MSSQL databases.

One of the developers is pushing for having triggers in either db that run an external command (our java program perhaps) that will initiate a push to the API. Somewhat similar to some NoSQL dbs like Rethink. Another dev is against triggers and is proposing more frequent polling of the db, which will not provide real time, but will get pretty close to real time.

I searched for a while for SQL real time solution, but was not able to find any solid reads with use cases and examples that support either approach. So my questions are:

  • Is frequent polling of the db engine like that of MySQL or MSSQL an ok thing to do? I am talking about 10 or 30 second polling. The db normally gets traffic from 3-4 clients at most and has roughly 20-40k records in table we are interested in. So we'd be adding another client that's polling aggressively.

  • Has anyone ran into actual negative use cases with triggers calling external commands? We won't be using them to guarantee integrity and consistency is also not that big of a concern in our use case (it is, but there is some wiggle room).

I am not a DBA or an experienced dev yet. Quite fresh from CS university with some experience. And all of us are like that so I am concerned that this lack of knowledge might backfire later with clients.

  • "... and then pushes data to the API" what does this mean? You push what where by what mechanism? Are you broadcasting to connected clients? – Mark Storey-Smith Nov 24 '16 at 19:17
  • @MarkStorey-Smith The java app runs on desktop, reads the db, does minor processing and sends it to the Rest API on the server (another service we provide). – Serge Poele Nov 24 '16 at 19:27
  • That doesn't make sense. What happens once data is "sent"? How is it stored at "the Rest API"? – Mark Storey-Smith Nov 25 '16 at 19:20
  • @MarkStorey-Smith Could you clarify which part exactly is not clear please? – Serge Poele Nov 27 '16 at 22:27
3

My opinion is that polling in your case (10 seconds is OK to me) is better than the trigger solution. The trigger is closely coupled with your transaction, and can cause lots of un-expected issues that are hard to trouble-shoot. Personally, I have never used trigger to call external application (what if the application fails, long to start, stuck? )

A better (but more difficult solution) can be using service broker to engage with external application.

  • +1 for polling. Subjective comment: It's a much cleaner solution to implement, you get a single point of calling that is initiated on a consistent schedule. My environment is fairly trigger heavy in some legacy areas, and while we're using some old versions of both software and sql I can confirm while triggers may not be a root cause of issues they make debugging issues much more complex than they need to be. – Libertheme Nov 25 '16 at 1:01
  • Thank you for your feedback. I noticed the same theme in multiple answers that triggers should not be used for external commands. Considering that we would not have direct access to all java clients after deployment, having triggers might make debugging much harder. – Serge Poele Nov 25 '16 at 4:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.