MySQL - Prevent Insertion of Record if ID and Timestamp Unique Combo Constraint Within Timeframe

MySQL - Prevent Insertion of Record if ID and Timestamp Unique Combo Constraint Within Timeframe

I have a scenario in which 3 standalone agents are reporting uptime statuses for various hosts. If the hosts go down and are offline, a downtime record should be created. Unfortunately, since the agents report exactly at the same time with the same information, I've seen duplicate entries that are 1-2 seconds apart.

I have a scenario in which 3 standalone agents are reporting uptime statuses for various hosts. If the hosts go down and are offline, a downtime record should be created. Unfortunately, since the agents report exactly at the same time with the same information, I've seen duplicate entries that are 1-2 seconds apart.

I have a unique constraint that was created on the table for both the datetime and the host ID. Thus, they cannot be the same. But if the requests from the agents come in at the same time or a second apart, a duplicate might be created despite code checks looking for an existing entry (in this case, an entry hasn't been created yet in all three instances if the agents report at the same time). The unique constraint won't prevent the duplicates either, since the datetime might be 1 second ahead or behind when the PHP / MySQL call finishes getting processed...

So, what is the best way to handle this situation? Is there a way in MySQL to specify that if a unique constraint (which includes a datetime field) is within a certain time frame of another record with the unique constraint, it shouldn't be allowed to insert?

Do I need to run a job that removes entries within a few seconds of each other, or is there a way to get MySQL to do this for me somehow?

Table structure looks like this

entry_id host_id datetime

Entries might be

1 121 01/17/2019 02:38:04 AM

1 121 01/17/2019 02:38:05 AM

1 121 01/17/2019 02:36:04 AM

I want to prevent the insertion of the bold entry since it's within 1 second from the last entry that was inserted. Code checks won't work because no entries may exist at the time it checks for one. I already have a code check looking for an existing entry, and since it doesn't find one and the code can be run at the same time for each request, the check fails and says a new entry should be created.

There are more datetime columns in my table, but they aren't needed to understand this situation. Any help is appreciated.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

MySQL Tutorial - Learn MySQL from Scratch

Complete MySQL Course: Beginner to Advanced. MySQL Tutorial - Learn MySQL from scratch and go from beginner to advanced in MySQL.

MySQL Tutorial: A Beginners Guide To Learn MySQL

MySQL Tutorial: A Beginners Guide To Learn MySQL - Let's take a look at a very in-depth MySQL tutorial that explores all the operations and commands that you need to explore your databases...

MySQL Tutorial for Beginners - MySQL Full Course

Learn MySQL for web development. This MySQL tutorial for beginners is a great way to learn SQL basics. What is SQL? SQL, short for Structured Query Language, is a language used by software developers, data scientists and anyone who works with data. In this course, we'll be using MySQL to learn SQL. MySQL is the most popular free and open-source database management system in the world. If you use a different DBMS (eg SQL Server), you can still benefit from this course as 90% of the skills you'll learn in this course can be transferred to other database management systems.