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.

mysql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

Exploring MySQL Binlog Server - Ripple

How MySQL Ripple binlog server can help you improve the performance of your master server by introducing a proxy server between the master and slave servers.MySQL does not limit the number of slaves that you can connect to the master server in a replication topology.

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)の解決

railsでWebアプリケーションを作成しているのですが、rails sでサーバーを起動した際に、Can't connect to local MySQL server through socket  '/var/lib/mysql/mysql.sock' (2) のエラーが出てしまいます

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...