I've created an Event, but can't figure out how to log when it runs, how long it takes and if it has any errors. How do I do this?
CREATE EVENT ON SCHEDULE EVERY 5 MINUTE
I use the following for Event Performance Reporting.
Note that it can handle as many separate events (for instance N events that you code separately) that you want throw at it.
What you do as Steps inside your Event is up to you. I have a reference in the Event below to a non-shown table here as well as what I do in the Event that is custom to this one business. Showing all that would make this answer too long. If you are an Event programmer, you will find your use for it.
WHILE loop in my event might not be best until you code some simple events without them. If you don't safely bail out of the WHILE, your event will run forever. So keep that in mind if anything here.
The following table is used by an Event near the top of its code to perform an insert against it, for the sole purpose of getting a unique
id back for use in inserts to the log table. Expand it with a datetime column or the like. It shows a trivial
usedBy column to capture something at least, like the Event name. Mainly, it wants an assigned auto_increment back from it (the
drop table if exists incarnations; create table incarnations ( -- NoteA -- a control table used to feed incarnation id's to events that want performance reporting. -- The long an short of it, insert a row here merely to acquire an auto_increment id id int auto_increment primary key, usedBy varchar(50) not null -- could use other columns perhaps, like how used or a datetime -- but mainly it feeds back an auto_increment -- the usedBy column is like a dummy column just to be fed a last_insert_id() -- but the insert has to insert something, so we use usedBy );
A generic logging table is below:
drop table if exists EvtsLog; create table EvtsLog ( id int auto_increment primary key, incarnationId int not null, -- See NoteA (above) evtName varchar(20) not null, -- allows for use of this table by multiple events step int not null, -- facilitates reporting on event level performance debugMsg varchar(1000) not null, dtWhenLogged datetime not null -- tweak this with whatever indexes your can bear to have -- run maintenance on this table to rid it of unwanted rows periodically -- as it impacts performance. So, dog the rows out to an archive table or whatever. );
-- Now the event kicks in on the server on its interval schedule and processes the batch. -- But we need to modify that Event code because prior all it did was write a row to the log table -- saying it was looking around. But it didn't actually do anything drop event if exists `Every_2_Minutes_QuestionUpdateImport`; DELIMITER $$ CREATE EVENT `Every_2_Minutes_QuestionUpdateImport` ON SCHEDULE EVERY 2 MINUTE STARTS '2015-09-01 00:00:00' ON COMPLETION PRESERVE DO BEGIN DECLARE bContinue bool default true; DECLARE counter int default 0; DECLARE logMsg varchar(1000); DECLARE incarnationId int default 0; DECLARE evtAlias varchar(20); -- right here you could save `now()` into a variable, let's call it STARTEVT set evtAlias:='ev2minQUI'; -- a shorter unique alias name, max 20 chars -- Below we must acquire our incarnation id from the control table used for all events -- that want to use it. It facilitates performance reporting with the use of the `steps` column and the datetime -- that are put in the EvtsLog table insert incarnations(usedBy) values (evtAlias); -- see NoteA select last_insert_id() into incarnationId; -- an auto_increment handed to us by the control table insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,1,'Event Fired, begin looking',now(); -- 1: we are beginning WHILE bContinue DO -- this is an intermediate-level skills event example. Be careful or you are stuck in the event forever select min(batchId) into @idToPerform from EvtsQuestionsToImportBatchHandler -- table not shown in this post on Stackoverflow where batchStatus=1; -- @idToPerform, a variable, will be null if there is no such row insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,5,'Debug Place 1',now(); IF (@idToPerform IS NOT NULL) THEN -- This next update line is very crucial, to mark the batch as underway and never picked up again -- at the top of this WHILE loop (otherwise you may be stuck in here forever) update EvtsQuestionsToImportBatchHandler set batchStatus=2,dtProcessBegan=now() where batchId=@idToPerform; set counter:=counter+1; -- used outside of the while loop in the end insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,10,"a message maybe from concat and variables",now(); -- -- Here is where you actually do something -- Here is where you actually do something -- Here is where you actually do something insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,20,"a message maybe from concat and variables",now(); -- Here is where you actually do something -- Here is where you actually do something -- Here is where you actually do something insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,30,"a message maybe from concat and variables",now(); -- mark this batch as done: update EvtsQuestionsToImportBatchHandler set batchStatus=3,dtProcessFinished=now() where batchId=@idToPerform; ELSE set bContinue=false; -- we are done with the event loop END IF; -- if bContinue is true, we will seek the next batch to process that has batchStatus=1, if there is one -- right here a new now() could be performed, and a timediff() against the STARTEVT -- to determine if you want to bail out also with a " set bContinue=false; " END WHILE; -- this is an intermediate-level skills event example. Be careful or you are stuck in the event forever -- this msg is crucial to see in the log table to know you are not locking in an endless WHILE loop set logMsg:=concat("Number of batches processed=",counter); -- concat example (could have been used above) insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,90,logMsg,now(); -- 90: we are almost done insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged) select incarnationId,evtAlias,99,'Event Done',now(); -- 99: we are done END$$ DELIMITER ; -- sets the delimiter back to what we are used to, the semi-colon
Naturally you are now able to perform Performance Reporting against the log table that includes
step, and the datetime.
Events are tricky to write as their is no UX associated with them. By using a logging table, you can not only achieve performance reporting, but also acquire insight with debugging messages during their development.
Don't forget to prune down the log table to keep its size under control. Perhaps archiving it off somewhere (maybe via another Event!).