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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
|
create database if not exists events_test;
use events_test;
#
# START - 16415: Events: event names are case sensitive
#
CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1;
--error ER_EVENT_ALREADY_EXISTS
CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2;
DROP EVENT Lower_case;
SET NAMES cp1251;
CREATE EVENT __1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100;
--error ER_EVENT_ALREADY_EXISTS
CREATE EVENT __1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200;
DROP EVENT __1251;
SET NAMES utf8;
CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300;
--error ER_EVENT_ALREADY_EXISTS
CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400;
DROP EVENT ДОЛЕН_регистър_утф8;
SET NAMES latin1;
#
# END - 16415: Events: event names are case sensitive
#
#
# START - BUG#16408: Events: crash for an event in a procedure
#
set @a=3;
CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5;
call p_16();
--echo "Here we used to crash!"
--error ER_EVENT_ALREADY_EXISTS
call p_16();
--error ER_EVENT_ALREADY_EXISTS
call p_16();
DROP EVENT e_16;
CALL p_16();
--error ER_EVENT_ALREADY_EXISTS
CALL p_16();
DROP PROCEDURE p_16;
DROP EVENT e_16;
#
# END - BUG#16408: Events: crash for an event in a procedure
#
#
# Start - 16396: Events: Distant-future dates become past dates
#
--error ER_WRONG_VALUE
create event e_55 on schedule at 99990101000000 do drop table t;
--error ER_WRONG_VALUE
create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t;
--error ER_EVENT_ENDS_BEFORE_STARTS
create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t;
#
# End - 16396: Events: Distant-future dates become past dates
#
#
# Start - 16407: Events: Changes in sql_mode won't be taken into account
#
set global event_scheduler=2;
--echo "Wait a bit to settle down"
--sleep 1
delete from mysql.event;
set global event_scheduler= 1;
set @old_sql_mode:=@@sql_mode;
set sql_mode=ansi;
select get_lock('test_bug16407', 60);
delimiter |;
create event e_16407 on schedule every 60 second do
begin
select get_lock('test_bug16407', 60);
drop table "hashed_num";
end|
delimiter ;|
--sleep 1
--echo "Now if everything is fine the event has compiled and is locked
select /*1*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
select release_lock('test_bug16407');
set global event_scheduler= 2;
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
--echo "Let's check whether we change the sql_mode on ALTER EVENT"
set sql_mode='traditional';
alter event e_16407 do select 1;
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
drop event e_16407;
--echo "Another sql_mode test"
set sql_mode="traditional";
create table events_smode_test(ev_name char(10), a date) engine=myisam;
--echo "This should never insert something"
delimiter |;
create event ee_16407_2 on schedule every 60 second do
begin
select get_lock('ee_16407_2', 60) /*ee_16407_2*/;
insert into events_test.events_smode_test values('ee_16407_2','1980-19-02');
end|
--error ER_TRUNCATED_WRONG_VALUE
insert into events_smode_test values ('test','1980-19-02')|
--echo "This is ok"
create event ee_16407_3 on schedule every 60 second do
begin
select get_lock('ee_16407_2', 60) /*ee_16407_3*/;
insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19');
insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29');
end|
set sql_mode=""|
--echo "This will insert rows but they will be truncated"
create event ee_16407_4 on schedule every 60 second do
begin
select get_lock('ee_16407_2', 60) /*ee_16407_4*/;
insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956');
end|
delimiter ;|
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
set sql_mode="ansi";
select get_lock('ee_16407_2', 60);
set global event_scheduler= 1;
--sleep 1
select /*2*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
select release_lock('ee_16407_2');
--sleep 2
select /*3*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
set global event_scheduler= 2;
select * from events_smode_test order by ev_name, a;
--echo "OK, last check before we drop them"
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
drop event ee_16407_2;
drop event ee_16407_3;
drop event ee_16407_4;
--echo "And now one last test regarding sql_mode and call of SP from an event"
delete from events_smode_test;
set sql_mode='traditional';
delimiter |;
create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end|
create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end|
create event ee_16407_5 on schedule every 60 second do
begin
select get_lock('ee_16407_5', 60) /*ee_16407_5*/;
call events_test.ee_16407_5_pendant();
end|
create event ee_16407_6 on schedule every 60 second do
begin
select get_lock('ee_16407_5', 60) /*ee_16407_6*/;
call events_test.ee_16407_6_pendant();
end|
delimiter ;|
set sql_mode='ansi';
select get_lock('ee_16407_5', 60);
set global event_scheduler= 1;
--sleep 1
--echo "Should have 2 locked processes"
select /*4*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
select release_lock('ee_16407_5');
--sleep 2
--echo "Should have 0 processes locked"
select /*5*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
select * from events_smode_test order by ev_name, a;
--echo "And here we check one more time before we drop the events"
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
drop event ee_16407_5;
drop event ee_16407_6;
drop procedure ee_16407_5_pendant;
drop procedure ee_16407_6_pendant;
set global event_scheduler= 2;
drop table events_smode_test;
set sql_mode=@old_sql_mode;
#
# End - 16407: Events: Changes in sql_mode won't be taken into account
#
drop database events_test;
|
