MySQL deletes row after expiration

Question

I am trying to delete expired records in a MySQL database, when creating or updating a field named lastBeat CURRENT_TIME is updated, and I use the following query to check / delete rows older than 20 seconds:

DELETE * FROM `rmachines` WHERE
 `lastBeat` < (NOW() - 20);

I also tried CURRENT_TIME instead of NOW ()

There are 2 main cycles:

• Updates a line in rmachines every second

• Executes a delete request

If 2 runs fast, as time moves on to the next minute (i.e. 59-60 seconds), it deletes the line as if it had expired (even if it is definitely not specified!), Otherwise it behaves normally.

If 2 is executed once per second, it is not so noticeable, "false expiration" is rare, but I run it 5 times per second to expose the "problem".

I found the solution tested and seems to work in the same scenarios:

job to delete rows older than 3 months in mysql database

But can anyone tell me why my method is not working?

Answer 1

You add NOW () to a number, and then subtract 20 from it. Instead, you must subtract 20 seconds at intervals:

NOW() - interval 20 second

The difference can be seen here:

SELECT NOW() - interval 20 second, NOW() - 20;

2010-06-11 00:06:49, 20100611000689.000000
                 ^^              ^^

Time 00:06:59will be compared after 00:06:49, but before 000689.