Replace cron jobs with special mysql functions?

I have an application which is starting to add up quite a lot in terms of having to add cron jobs to monitor certain things.

An example is that when a certain data is inserted into the database, a cron job looks at this data, then looks at a setting in another table to know if it should send a notice to an email address configured.

Now, we need to add another similar function where if a critical piece of data comes into the db and if there is a 1 in another table column, that again, an email should be sent. The problem is that this test needs to be done every single time data is inserted which means I would have to run this cron task every second of the day, 24/7.

That seems unbelievably stupid to me so I wanted to ask here because I think those telling me it's the only way to do this are perhaps not aware of some things.
projectsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
You could use a trigger which would run on each data insert. The trigger could then write into a table IF there is an email to be sent. It's of course best not to send the email from within the trigger coz you want to keep things fast when doing inserts. You might also want to be able to retry emails should the mail server be down at the time of the insert. Which again, suggests not doing too much in MySQL itself.

However, in my mind, a better way to accomplish this without hurting performance would be to keep your cron job. Having a separate process that checks and processes the email notifications is best. It's better than trying to do everything in the database during an insert. If necessary, you could do the check in the trigger and update a flag of an email needs to be sent. A mail server could be down at the time of the insert, and doing the emailing from a separate process triggered by the from timer allows you to do more complex and robust email processing with retries and templates and all that.

The very best approach, since you perhaps want to get rid of the cron job, is to use a message queue. This is a more complex setup, best suited for high volume scenarios. It's a waste to run cron jobs when there probably aren't any inserts. With the message queue option, your application would write a message to the queue as  soon as it had done and insert. You would have an application on the other end of the queue, the consumer, which would be sitting around waiting for messages. As soon as a message is written to the queue, the consumer will immediately see it and run the logic to check whether emails need to be sent or not. You can have multiple queues. Look here https://www.syncfusion.com/resources/techportal/ebooks/rabbitmq
0
projectsAuthor Commented:
If I am understanding correctly, the last part means something like this...

We might add some code in our initial data entry point which creates a queue of some sort which another application could monitor.

That second application which does nothing but wait to see if anything comes into the queue. When something does, it is the code that reads the database to find out if something should be sent by comparing the table/column values etc.

Something along those lines?

The URL you gave is closed to subscribed users only or something.
0
MlandaTCommented:
You have understood it exactly as intended. If you don't mind, you can register on that site and download a copy of the book. It talks about rabbitmq, a popular message queue system  (open source, free, but highly recommended). It gives C# code samples, walking you through some examples that you can try to see how this mechanism works . I recently read the book and it was a nice short read. Of course you can also find rabbitmq documentation online, just Google and they have great examples on their website.https://www.rabbitmq.com/getstarted.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.