MySQL delete row after 'expiry'

Question

Hi,

I am trying to delete expired entries in a MySQL database, on creation or update a field called lastBeat is updated with CURRENT_TIME and I use the following query to check/delete rows older than 20 seconds:

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

I have also tried CURRENT_TIME instead of NOW()

There are 2 main loops:

1: Updates a row in rmachines every second

2: Executes the delete query

If 2 is executed rapidly, as the time rolls over to the next minute (ie 59-60 seconds) it deletes the row as if it has expired (even thought it definitely has not!), otherwise it behaves fine.

If 2 executes once a second this is not so noticable, the "false expiry" happens rarely but I run it 5 times per second to expose the 'issue'.

I found a solution, tested and seems to work under the same scenarios:

http://stackoverflow.com/questions/813434/a-job-to-delete-rows-older-than-3-months-in-mysql-database

But can anyone tell me why my method does not work?

Thanks in advance for all insights into this matter.

Answer 1

You are casting NOW() to a number and then subtracting 20 from it. Instead you should subtract 20 seconds using an interval:

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

The time 00:06:59 will compare after 00:06:49 but before 000689.