summaryrefslogtreecommitdiff
path: root/mysql-test/t/events_1.test
blob: ccdeb70d291daba124725d22698561d5209ac9bd (plain)
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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
# changes 2008-02-20 hhunger splitted events.test into events_1 and events_2
# changes 2008-02-22 hhunger replaced all sleep by wait_condition
#
# Can't test with embedded server that doesn't support grants
-- source include/not_embedded.inc

--disable_warnings
drop database if exists events_test;
drop database if exists db_x;
drop database if exists mysqltest_db2;
drop database if exists mysqltest_no_such_database;
--enable_warnings
create database events_test;
use events_test;

#
# START:  BUG #17289 Events: missing privilege check for drop database
#
CREATE USER pauline@localhost;
CREATE DATABASE db_x;
GRANT EVENT ON db_x.* TO pauline@localhost;
USE db_x;
CREATE TABLE x_table(a int);
connect (priv_conn,localhost,pauline,,db_x);
CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x;
CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table;
connection default;
SHOW DATABASES LIKE 'db_x';
SET GLOBAL event_scheduler=1;
let $wait_condition= SELECT count(*)= 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'db_x';
--source include/wait_condition.inc
SHOW DATABASES LIKE 'db_x';
SHOW TABLES FROM db_x;
SET GLOBAL event_scheduler=off;
connection priv_conn;
DROP EVENT e_x1;
DROP EVENT e_x2;
disconnect priv_conn;
connection default;
DROP DATABASE db_x;
DROP USER pauline@localhost;
USE events_test;
#
# END:    BUG #17289 Events: missing privilege check for drop database
#
SET GLOBAL event_scheduler=off;
drop event if exists event1;
create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end;
alter event event1 rename to event2 enable;
alter event event2 disable;
alter event event2 enable;
alter event event2 on completion not preserve;
delimiter __;
alter event event2 on schedule every 1 year on completion preserve rename to event3 comment "new comment" do begin select 1; end__
delimiter ;__
alter event event3 rename to event2;

drop event event2;
create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end;
drop event event2;

#
# BUG #16537 (Events: mysql.event.starts is null)
#
CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1;
SELECT interval_field, interval_value, body FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
ALTER EVENT event_starts_test ON SCHEDULE AT '2020-02-02 20:00:02';
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
ALTER EVENT event_starts_test COMMENT "non-empty comment";
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
ALTER EVENT event_starts_test COMMENT "";
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
DROP EVENT event_starts_test;

CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '2020-02-02 20:00:02' ENDS '2022-02-02 20:00:02' DO SELECT 2;
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
ALTER EVENT event_starts_test COMMENT "non-empty comment";
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
ALTER EVENT event_starts_test COMMENT "";
SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
DROP EVENT event_starts_test;
#
#
create table test_nested(a int);
create event e_43 on schedule every 1 second do set @a = 5;
--error ER_EVENT_RECURSION_FORBIDDEN
alter event e_43 do alter event e_43 do set @a = 4;
delimiter |;
alter event e_43 do
begin
  alter event e_43 on schedule every 5 minute;
  insert into test_nested values(1);
end|
delimiter ;|
set global event_scheduler = on;
let $wait_condition= SELECT count(*)>0 from mysql.event where name='e_43' and interval_value= 5;
--source include/wait_condition.inc
select db, name, body, status, interval_field, interval_value from mysql.event;
drop event e_43;
drop table test_nested;

--echo "Let's check whether we can use non-qualified names"
create table non_qualif(a int);
create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219);
let $wait_condition=SELECT count(*)= 1 from non_qualif where a=800219;
--source include/wait_condition.inc
select * from non_qualif;
drop event non_qualif_ev;
drop table non_qualif;

--error ER_EVENT_DOES_NOT_EXIST
alter event non_existant rename to non_existant_too;

set global event_scheduler = off;
create event existant on schedule at now() + interval 1 year do select 12;
--error ER_EVENT_ALREADY_EXISTS
alter event non_existant rename to existant;
--error ER_EVENT_SAME_NAME
alter event existant rename to events_test.existant;
drop event existant;


create table t_event3 (a int, b float);
drop event if exists event3;
create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand());
let $wait_condition=SELECT count(*)=0 from t_event3;
--source include/wait_condition.inc
select count(*) from t_event3;
drop event event3;
drop table t_event3;


set names utf8;
#
# SHOW CREATE EVENT test begin
#
CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root6;
create event root7 on schedule every 2 year do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root7;
create event root8 on schedule every '2:5' year_month do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root8;
create event root8_1 on schedule every '2:15' year_month do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root8_1;
create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root9;
create event root10 on schedule every '20:5' day_hour do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root10;
create event root11 on schedule every '20:25' day_hour do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root11;
create event root12 on schedule every '20:25' hour_minute do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root12;
create event root13 on schedule every '25:25' hour_minute do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root13;
create event root13_1 on schedule every '11:65' hour_minute do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root13_1;
create event root14 on schedule every '35:35' minute_second do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root14;
create event root15 on schedule every '35:66' minute_second do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root15;
create event root16 on schedule every '35:56' day_minute do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root16;
create event root17 on schedule every '35:12:45' day_minute do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root17;
create event root17_1 on schedule every '35:25:65' day_minute do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root17_1;
create event root18 on schedule every '35:12:45' hour_second do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root18;
create event root19 on schedule every '15:59:85' hour_second do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root19;
create event root20 on schedule every '50:20:12:45' day_second do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT root20;
set names cp1251;
create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT ðóóò21;
insert into mysql.event (
  db,
  name,
  body,
  definer,
  interval_value,
  interval_field,
  originator,
  character_set_client,
  collation_connection,
  db_collation,
  body_utf8)
values (
  database(),
  "root22",
  "select 1",
  user(),
  100,
  "SECOND_MICROSECOND",
  1,
  'utf8',
  'utf8_general_ci',
  'utf8_general_ci',
  'select 1');
--error ER_NOT_SUPPORTED_YET
show create event root22;
--error ER_NOT_SUPPORTED_YET
SHOW EVENTS;
drop event root22;
--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG
create event root23 on schedule every -100 year do select 1;
--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG
create event root23 on schedule every 222222222222222222222 year do select 1;
drop event root6;
drop event root7;
drop event root8;
drop event root8_1;
drop event root9;
drop event root10;
drop event root11;
drop event root12;
drop event root13;
drop event root13_1;
drop event root14;
drop event root15;
drop event root16;
drop event root17;
drop event root17_1;
drop event root18;
drop event root19;
drop event root20;
drop event ðóóò21;

set names latin1;
#
# SHOW CREATE EVENT test end
#

#
# mysql.event intact checking
# Check that the server does not crash if
# one has destroyed or tampered with the event table. 
# Please see see for events_restart_phase* tests to 
# see the server behavior at start up with bad mysql.event
# table.
#
#
--echo Create a test event. Only event metadata is relevant, 
--echo the actual schedule and body are not.
#
CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
--replace_column 8 # 9 #
SHOW EVENTS;
#
--echo Try to alter mysql.event: the server should fail to load
--echo event information after mysql.event was tampered with.
--echo 
--echo First, let's add a column to the end and make sure everything
--echo works as before
--echo 
ALTER TABLE mysql.event ADD dummy INT;
--replace_column 8 # 9 #
SHOW EVENTS;
SELECT event_name FROM INFORMATION_SCHEMA.events;
--replace_regex /STARTS '[^']+'/STARTS '#'/
SHOW CREATE EVENT intact_check;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT no_such_event;
CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;
ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;
ALTER EVENT intact_check_1 RENAME TO intact_check_2;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT intact_check_1;
DROP EVENT intact_check_2;
DROP EVENT intact_check;
DROP DATABASE IF EXISTS mysqltest_no_such_database;
CREATE DATABASE mysqltest_db2;
DROP DATABASE mysqltest_db2;
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=OFF;
# Clean up
ALTER TABLE mysql.event DROP dummy;
CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
--echo 
--echo Now let's add a column to the first position: the server
--echo expects to see event schema name there
--echo 
ALTER TABLE mysql.event ADD dummy INT FIRST;
--error ER_CANNOT_LOAD_FROM_TABLE
SHOW EVENTS;
--error ER_CANNOT_LOAD_FROM_TABLE
SELECT event_name FROM INFORMATION_SCHEMA.events;
--error ER_EVENT_DOES_NOT_EXIST
SHOW CREATE EVENT intact_check;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT no_such_event;
--error ER_EVENT_STORE_FAILED
CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;
--error ER_EVENT_DOES_NOT_EXIST
ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;
--error ER_EVENT_DOES_NOT_EXIST
ALTER EVENT intact_check_1 RENAME TO intact_check_2;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT intact_check_1;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT intact_check_2;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT intact_check;
# Should work OK
DROP DATABASE IF EXISTS mysqltest_no_such_database;
CREATE DATABASE mysqltest_db2;
DROP DATABASE mysqltest_db2;
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=OFF;
--echo Clean up
ALTER TABLE mysql.event DROP dummy;
DELETE FROM mysql.event;
CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
--echo Back up the table, further changes are not reversible
CREATE TABLE event_like LIKE mysql.event;
INSERT INTO event_like SELECT * FROM mysql.event;
--echo 
--echo Drop some columns and try more checks. 
--echo
--echo 
ALTER TABLE mysql.event DROP comment, DROP starts;
--error ER_CANNOT_LOAD_FROM_TABLE
SHOW EVENTS;
--error ER_CANNOT_LOAD_FROM_TABLE
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
--error ER_CANNOT_LOAD_FROM_TABLE
SHOW CREATE EVENT intact_check;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT no_such_event;
--error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED
CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;
--error ER_EVENT_DOES_NOT_EXIST
ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;
--error ER_EVENT_DOES_NOT_EXIST
ALTER EVENT intact_check_1 RENAME TO intact_check_2;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT intact_check_1;
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT intact_check_2;
# Should succeed
DROP EVENT intact_check;
DROP DATABASE IF EXISTS mysqltest_no_such_database;
CREATE DATABASE mysqltest_db2;
DROP DATABASE mysqltest_db2;
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=OFF;
--echo 
--echo Now drop the table, and test again
--echo
--echo 
DROP TABLE mysql.event;
--error ER_NO_SUCH_TABLE
SHOW EVENTS;
--error ER_NO_SUCH_TABLE
SELECT event_name FROM INFORMATION_SCHEMA.events;
--error ER_NO_SUCH_TABLE
SHOW CREATE EVENT intact_check;
--error ER_NO_SUCH_TABLE
DROP EVENT no_such_event;
--error ER_NO_SUCH_TABLE
CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;
--error ER_NO_SUCH_TABLE
ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;
--error ER_NO_SUCH_TABLE
ALTER EVENT intact_check_1 RENAME  TO intact_check_2;
--error ER_NO_SUCH_TABLE
DROP EVENT intact_check_1;
--error ER_NO_SUCH_TABLE
DROP EVENT intact_check_2;
--error ER_NO_SUCH_TABLE
DROP EVENT intact_check;
DROP DATABASE IF EXISTS mysqltest_no_such_database;
CREATE DATABASE mysqltest_db2;
DROP DATABASE mysqltest_db2;
--echo OK, there is an unnecessary warning about the non-existent table 
--echo but it's not easy to fix and no one complained about it.
--echo A similar warning is printed if mysql.proc is missing.
SHOW WARNINGS;
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=OFF;
--echo Restore the original table.
CREATE TABLE mysql.event like event_like;
DROP TABLE event_like;
--replace_column 8 # 9 #
SHOW EVENTS;
# 
# End of tests
#

let $wait_condition=
  select count(*) = 0 from information_schema.processlist
  where db='events_test' and command = 'Connect' and user=current_user();
--source include/wait_condition.inc

drop database events_test;