MySQL select records missing occasionally when inserted by an EVENT

Question

I am running event to fetch data from main table and inserting it into Summary table

I am running MySQL event every 2 minute and calling stored procedure inside my event for some grouping, parsing and calculations from main table to summary table.

But while selecting 2 minutes records, some time last record got missed while creating summary occasionally

Frequency of missing record is in 1 day 90-100 records (1 day record count in main table is 30K).

MySQL select query inside stored procedure:

SELECT ID, COUNT(*) AS TOT_COUNT
FROM CUSTOMER 
WHERE (TIMESTAMP > (DATE_FORMAT((NOW() - INTERVAL 3 MINUTE),"%Y-%m-%d 
%H:%i:00"))
  AND TIMESTAMP <= (DATE_FORMAT((NOW() - INTERVAL 1 MINUTE),"%Y-%m-%d %H:%i:00")))
GROUP BY ID, NAME;

Note: selecting previous 2 minutes record, skipping current minute records

I have tried updating select statement where condition as below (missing record frequency reduced to 50%)

SELECT ID, NAME, COUNT(*) AS TOT_COUNT
FROM CUSTOMER 
WHERE (SUBSTRING_INDEX(TIMESTAMP, ':', 2) !=  SUBSTRING_INDEX((NOW()), ':', 2)
  AND SUBSTRING_INDEX(TIMESTAMP,':',2) >= SUBSTRING_INDEX((NOW() - INTERVAL 2 MINUTE),':',2))
GROUP BY ID,NAME;

Also try to catch exception by using below statement in stored procedure.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
    @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
    SELECT @full_error;
    INSERT INTO ERRORS_TABLE (Level,Code,Message,INSERT_TMST) VALUES ("ERROR ", @errno, @full_error, now());
END;

No event or stored procedure fail log or MySQL error log found. also checked for null values.

If I call the stored procedure manually for that missed minute record set manually then it returns the correct count.

Can you please help me how can I debug this issue, any other perfect select query to fetch 2 minutes record or i am missing something ?

Thanks in advance

Answer 1

You simply cannot rely on events running precisely on time. You just can't. You have learned what happens if you do.

You're trying to create a so-called materialized view of your data in your summary table. What can you do about this? A few different things.

1. You can use an ordinary, non-materialized, VIEW of your data. If you have the correct indexes on your table the VIEW will most likely perform well. And it's a robust solution.

2. You can rewrite the stored code in your event so it handles everything since the last event ran. To do that you may need a tiny one-row table with the TIMESTAMP used in the previous run.

3. Switch to a RDBMS that supports materialized views natively. That's probably Oracle, so it will cost a fortune.