今天介紹的是 Event Status。

這個取得資料的方式，除了之前提到的擴充 Event 可以得到 Event 結果以外，就是系統的 information_schema.EVENTS 可以得到相關資訊。

DB, Event Name, Last Run Time

這些都能從 information_schema.EVENTS 拿 (event_schema, event_name, last_executed)。

Event 種類

Event 種類有 ENABLE, DISABLE, SLAVESIDE_DISABLED (Replication 時從 Master 傳來的 Event，不會在 Slave 執行)，根據實際情形作標示。

CASE status 
  WHEN 'ENABLED' THEN '<div class="Healthy">Yes</div>' 
  WHEN 'SLAVESIDE_DISABLED' THEN '<div class="Healthy">No</div>' 
  ELSE '<div class="Warning">No</div>' 
END, 

Succeed

根據 master.event 與 information_schema.EVENTS 來 count，沒有 errno 的就是成功的。

CASE (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is null and start >= subdate(now(), @NumDays)) 
  WHEN 0 THEN CONCAT('<div class="Warning">', (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is null and start >= subdate(now(), @NumDays)), '</div>') 
  ELSE (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is null and start >= subdate(now(), @NumDays)) 
END,

Failed

根據 master.event 與 information_schema.EVENTS 來 count，有 errno 的就是失敗的。

CASE (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is not null and start >= subdate(now(), @NumDays)) 
  WHEN 0 THEN '<div class="Healthy">0</div>' 
  ELSE CONCAT('<div class="Critical">', (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is not null and start >= subdate(now(), @NumDays)),'</div>') 
END,

Next Run Time

根據 master.event 可以用算的，但是算的動作不能直接傳入 column 裡頭的值 (select date_add(colA, interval interval_value interval_field)...)，所以寫成醜醜的 CASE。

這個部份是筆者覺得最沒有自動化的地方。

case interval_field
  when "YEAR"	    then date_add(last_executed, interval interval_value YEAR)
  when "QUARTER"	then date_add(last_executed, interval interval_value QUARTER)
  when "MONTH"	    then date_add(last_executed, interval interval_value MONTH)
  when "DAY"	    then date_add(last_executed, interval interval_value DAY)
  when "HOUR"	    then date_add(last_executed, interval interval_value HOUR)
  when "MINUTE"	    then date_add(last_executed, interval interval_value MINUTE)
  when "WEEK"	    then date_add(last_executed, interval interval_value WEEK)
  when "SECOND"	    then date_add(last_executed, interval interval_value SECOND)
end,

(這個 interval 不是全部，筆者只有寫常用的)

Last Result

根據 master.event 與 information_schema.EVENTS 來看最後一次結果的內容。

(select CASE 
          WHEN errno is null THEN CONCAT('<span class="Healthy">', message_text, '</span>') 
          ELSE CONCAT('<span class="Critical">', message_text, '</span>') 
        END 
 from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME order by start limit 1),