forked from ricksilva/mysql_cc
-
Notifications
You must be signed in to change notification settings - Fork 0
/
chapter_13.sql
148 lines (110 loc) · 2.78 KB
/
chapter_13.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
--
-- MySQL Crash Course
--
-- Chapter 13 – Creating Events
--
-- You can copy and paste any of these commands into your favorite MySQL tool
-- (like MySQL Workbench) and run them in your own MySQL environment.
--
-- Is your event scheduler on?
show variables like 'event_scheduler';
-- Create event e_cleanup_payable_audit
use bank;
drop event if exists e_cleanup_payable_audit;
delimiter //
create event e_cleanup_payable_audit
on schedule every 1 month
starts '2024-01-01 10:00'
do
begin
delete from payable_audit
where audit_datetime < date_sub(now(), interval 1 year);
end //
delimiter ;
-- Create p_account_update() to set up for the event e_account_update
-- The procedure does nothing. It just displays a message so we have a procedure to call.
use bank;
drop procedure if exists p_account_update;
delimiter //
create procedure p_account_update()
begin
select 'Running p_account_update()';
end//
delimiter ;
-- Show the events scheduled
show events;
-- Create event e_account_update
use bank;
drop event if exists e_account_update;
delimiter //
create event e_account_update
on schedule at '2024-03-10 00:01'
do
begin
call p_account_update();
end //
delimiter ;
-- Create table current_time_zone to set up for event e_change_to_dst
use bank;
create table current_time_zone
(
time_zone varchar(10)
);
insert into current_time_zone
(
time_zone
)
values
(
'EST'
);
-- Create event e_change_to_dst
use bank;
drop event if exists e_change_to_dst;
delimiter //
create event e_change_to_dst
on schedule
at '2024-03-10 1:59'
do
begin
-- Make any changes to our application needed for DST
update current_time_zone
set time_zone = 'EDT';
end //
delimiter ;
-- Any event errors?
select *
from performance_schema.error_log
where data like '%Event Scheduler%';
-- Any event errors for the event "e_account_update"?
select *
from performance_schema.error_log
where data like '%e_account_update%';
-- Try it Yourself exercises
-- Set up for exercise 13-1:
create database eventful;
use eventful;
create table event_message
(
message varchar(100)
);
-- Exercise 13-1: Create e_write_timestamp event
use eventful;
drop event if exists e_write_timestamp;
delimiter //
create event e_write_timestamp
on schedule every 1 minute
starts current_timestamp
ends current_timestamp + interval 5 minute
do
begin
insert into event_message (message)
values (current_timestamp);
end //
delimiter ;
-- Exercise 13-2: Check for errors
select *
from performance_schema.error_log
where data like '%e_write_timestamp%';
-- Exercise 13-3: Check for new records being inserted every minute
select * from event_message;