我怎么能有MySQL日志预定的事件？ [英] How can i have MySQL log scheduled Events?
问题描述
我已经创建了一个事件，但是不知道它运行时如何记录，它需要多长时间，如果它有任何错误。如何做到这一点？
创建事件每次5分钟
DO BEGIN
...做某事...
END
请使用以下内容进行事件性能报告。
请注意，它可以处理您想要抛出的单独事件（例如，您单独编写的N个事件）。 / p>
您的活动中的步骤取决于您。我在下面的事件中有一个参考，这里是一个未显示的表，以及我在这个事务中做什么，这是一个这个业务的习惯。显示所有这些会使这个答案太久。如果你是一个活动程序员，你会发现 使用它。
另外， WHILE 循环在我的事件可能不是最好的，直到你编写一些简单的事件没有他们。如果您不安全地离开WHILE，您的活动将永远运行。
模式存根
下表用于附近的事件其代码的顶部是针对它执行插入操作，其唯一目的是获得一个独特的 id ，用于插入到日志表中。用datetime列等展开它。它显示一个微不足道的 usedBy 列来至少捕获一些东西，就像事件名称一样。主要是，它需要从中分配一个auto_increment（ id ）。
drop table if exists incarnations;
创建表化身
（ - NoteA
- 用于将化身ID提供给希望执行性能报告的事件的控制表
- 短的一段时间，插入一行这里只是为了获取一个auto_increment id
id int auto_increment主键，
usedBy varchar（50）not null
- 可以使用其他列，如使用或datetime
- 但主要是反馈一个auto_increment
- usedBy列就像一个虚拟列，只是为了输入一个last_insert_id（）
- 但是插入必须插入一些东西，所以我们使用usedBy
）;
通用日志记录表如下：
drop table if exists EvtsLog;
创建表EvtsLog
（id int auto_increment主键，
incarnationId int not null， - 请参见NoteA（以上）
evtName varchar（20）not null， - 允许通过多个事件使用这个表
step int not null， - 方便报告事件级别的性能
debugMsg varchar（1000）not null，
dtWhenLogged datetime not null
- 调整这个与你可以承担的任何索引
- 运行这个表上的维护，以摆脱不必要的行周期性
- 因为它影响性能，所以，将行排除到归档表或
）;
示例活动
- 现在，事件以其间隔时间表在服务器上启动并处理批处理。
- 但是我们需要修改该事件代码，因为事先所做的是将一行写入日志表
- 表示正在查看。但是如果存在`Every_2_Minutes_QuestionUpdateImport`，它实际上并没有做任何
drop事件;
DELIMITER $$
CREATE EVENT每2_Minutes_QuestionUpdateImport
在每个2分钟的开始时间表上2015-09-01 00:00:00'
完成保存
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）;
- 在这里你可以将`now（）`保存到一个变量中，让我们称之为STARTEVT
set evtAlias：='ev2minQUI'; - 一个更短的唯一别名，最多20个字符
- 下面我们必须从用于所有事件
的控件表中获取我们的化身ID - 要使用它。它通过使用放置在EvtsLog表
中的`steps`列和datetime
来简化性能报告，它们是插入元素（usedBy）值（evtAlias）; - 参见NoteA
将last_insert_id（）选择为incarnationId; - 控制表给我们的一个auto_increment
insert EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
选择incarnationId，evtAlias，1，'Event Fired，begin looking' ，现在（）; - 1：我们开始
WHILE bContinue DO - 这是一个中级技能事件示例。小心或者你永远被困在事件
从EvtsQuestionsToImportBatchHandler中选择minid（批处理）到@idToPerform
- 在Stackoverflow
中没有显示的表 - 其中batchStatus = 1; - @idToPerform，一个变量，如果没有这样的行，将为null
insert EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
select incarnationId，evtAlias，5，'调试地点1'，now（）;
IF（@idToPerform IS NOT NULL）THEN
- 下一个更新行是非常关键的，将批次标记为正在进行，并且从未重新获取
- 在这个WHILE循环的顶部（否则你可能永远在这里）
更新EvtsQuestionsToImportBatchHandler设置batchStatus = 2，dtProcessBegan = now（）其中batchId = @ idToPerform;
set counter：= counter + 1; - 在while循环之外使用
插入EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
选择incarnationId，evtAlias，10一个消息可能来自concat和变量，现在（）;
-
- 这里是你真正做的事情
- 这里是你实际做的事情
- 这里是你真正做的事情
插入EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
选择incarnationId，evtAlias，20，一个消息可能来自concat和variables，now（）;
- 这里是你实际做的事情
- 这里是你实际做的事情
- 这里是你真正做的事情
插入EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
选择incarnationId，evtAlias，30，一个消息可能来自concat和variables，now（）;
- 将此批次标记为已完成：
更新EvtsQuestionsToImportBatchHandler设置batchStatus = 3，dtProcessFinished = now（）其中batchId = @ idToPerform;
ELSE
set bContinue = false; - 我们完成了事件循环
END IF;
- 如果bContinue为true，我们将寻求批处理的batchStatus = 1，如果有一个
- 在这里可以执行一个新的now（）和一个对STARTEVT
的timediff（）来确定你是否还要用set bContinue = false;
END WHILE; - 这是一个中级技能事件的例子。要小心或者你永远被困在事件
- 这个msg在日志表中看到关键是要知道你没有锁定在一个无尽的WHILE循环
设置logMsg：= concat （批处理数=，计数器）; - concat示例（可以在上面使用）
insert EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
select incarnationId，evtAlias，90，logMsg，now（）; - 90：我们几乎完成
插入EvtsLog（incarnationId，evtName，step，debugMsg，dtWhenLogged）
选择incarnationId，evtAlias，99，'Event Done'，now（）; - 99：我们完成
END $$
DELIMITER; - 将分隔符设置为我们以前使用的分号，分号
自然，你是根据包含 incarnationId ， evtName ， step 和datetime。以下查询将多行事件信息折叠到每个事件运行的1行，时间差异。
select incarnationId，dtBegin ，dtEnd，TIMESTAMPDIFF（SECOND，dtBegin，dtEnd）as
（select incarnationId，min（dtBegin）as dtBegin，max（dtEnd））作为secDiff
作为dtEnd
从
incarnationId，
case
when step = 1 then dtWhenLogged
end as dtBegin，
case
when step = 99 then dtWhenLogged
end as dtEnd
from evtsLog
其中evtName ='evtName01'
）d1
group by incarnationId
）d2;
+ --------------- + --------------------- + ------- -------------- + --------- +
| incarnationId | dtBegin | dtEnd | secDiff |
+ --------------- + --------------------- + ------- -------------- + --------- +
| 101 |于01-01 14:02:00 2016-05-01 14:02:01 | 1 |
| 102 | 2016-05-01 14:02:01 | 2016-05-01 14:02:07 | 6 |
+ --------------- + --------------------- + ------- -------------- + --------- +
为了以微秒为单位进行更精确的报告，需要MySQL 5.6.4或更高版本。请参阅此答案。
事件很难写，因为没有UX与它们相关联。通过使用日志记录表，您不仅可以实现性能报告，还可以在开发过程中获取有关调试消息的洞察。
不要忘记修剪日志表保持其规模受到控制。也许将其归档到某个地方（也许通过另一个事件！）。
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
DO BEGIN
...do something...
END
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.
Also, the 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.
Schema stub
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 id).
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.
);
A Sample Event
-- 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 get Performance Reporting against the log table that includes incarnationId, evtName, step, and the datetime. The below query collapses the multi-row event information to 1 row per event run, with a time difference.
select incarnationId,dtBegin,dtEnd,TIMESTAMPDIFF(SECOND,dtBegin,dtEnd) as secDiff
from
( select incarnationId,min(dtBegin) as dtBegin,max(dtEnd) as dtEnd
from
( select incarnationId,
case
when step=1 then dtWhenLogged
end as dtBegin,
case
when step=99 then dtWhenLogged
end as dtEnd
from evtsLog
where evtName='evtName01'
) d1
group by incarnationId
) d2;
+---------------+---------------------+---------------------+---------+
| incarnationId | dtBegin | dtEnd | secDiff |
+---------------+---------------------+---------------------+---------+
| 101 | 2016-05-01 14:02:00 | 2016-05-01 14:02:01 | 1 |
| 102 | 2016-05-01 14:02:01 | 2016-05-01 14:02:07 | 6 |
+---------------+---------------------+---------------------+---------+
For more precise reporting in microseconds, MySQL 5.6.4 or above is required. See this answer.
Events are tricky to write as there 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!).
这篇关于我怎么能有MySQL日志预定的事件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
