1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
prat
  • 81
  • 12
  • 1
    I would not rely on my query to run exactly every two minutes. Who guarantees that it doesn't run a second too late making me miss the first second when I look back only two minutes? If I knew that "every two minutes" guaranteed me that it won't take longer than a pause of, say three minutes, I might be tempted to select those three past minutes and dismiss all duplicates on insert. But no, I wouldn't. I would store the last ID I handled, and on the next run I would select all rows with a higher ID. That would make me independent from the actual frequency of my batch run. – Thorsten Kettner Oct 01 '21 at 08:18
  • And are you really comparing a timestamp with a string there? This doesn't look good. – Thorsten Kettner Oct 01 '21 at 08:20
  • Thanks for notifying where clause, let me try out with DATE_FORMAT & DATE_SUB in where clause, what would you suggest ? i am continuously receiving records in main table , so that's the reason i am skipping current minute records. – prat Oct 01 '21 at 08:29
  • At 08:30:59, you get 08:27:00.00001 to 08:29:00.00000. Then if your query runs again after two minutes and 1 second: At 08:33:00, you get 08:30:00.00001 to 08:32:00.00000. Thus you miss everything from 08:29:00.00001 to 08:30:00.00000 if I am not mistaken. As mentioned: my advice is to remember the ID (provided it is guaranteed to be ascending) and work on that instead. – Thorsten Kettner Oct 01 '21 at 08:37
  • My event runs every odd minute 07:31 ,I select :07:29:00 to 07:30:59 records ,at next 07:33 i select 07:31:00 to to 07:32:59 records – prat Oct 01 '21 at 08:42
  • And you are absolutely sure that it can't possibly run a millisecond too early, i.e. 07:30:59.9999? That would break your algorithm as shown. – Thorsten Kettner Oct 01 '21 at 09:44
  • no i am not sure about when this mysql Event will run, how we can overcome this millisecond issue then ? – prat Oct 01 '21 at 10:02

1 Answers1

3

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • but it is not the case of millisecond it may some time miss record in between complete minute also? but record pattern is always last record is missed and i am fetching records transactions of fully completed minute even mysql event Ran 1 ms later or before it should not missed select for given criteria – prat Oct 01 '21 at 10:13