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
|
# Can't test with embedded server that doesn't support grants
-- source include/not_embedded.inc
call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted");
let $collation_server=`select @@collation_server`;
#
# Test that when the server is restarted, it checks mysql.event table,
# and disables the scheduler if it's not up to date.
#
# Switch off the scheduler for now.
set global event_scheduler=off;
--disable_warnings
drop database if exists events_test;
--enable_warnings
create database events_test;
use events_test;
create table execution_log(name char(10));
create event abc1 on schedule every 1 second do
insert into execution_log value('abc1');
create event abc2 on schedule every 1 second do
insert into execution_log value('abc2');
create event abc3 on schedule every 1 second do
insert into execution_log value('abc3');
#
# There are various conditions when a server would regard mysql.event
# table as damaged:
# - the table has more column than specified in the compiled in value, but
# the version of the server which created the table is the same
# - the column count in the table is less than the compiled in value
# - some column has an incompatible type specification (for what is an
# incompatible type specification please refer to the comments in the source
#
# Unfortunately, in order to test a condition, we need to restart the
# server. Therefore, here we test only one simple case: changing the data
# type of the 'body' field to blob.
#
# First, let's do a backup to not depend on actual definition of mysql.event
create table event_like like mysql.event;
insert into event_like select * from mysql.event;
# Now let's alter the table and restart the server
alter table mysql.event
change column body body longtext character set utf8 collate utf8_bin;
--echo "Now we restart the server"
call mtr.add_suppression("Incorrect definition of table mysql.event:.*");
--source include/restart_mysqld.inc
use events_test;
# Event scheduler should be disabled: the system tables are damaged
select @@event_scheduler;
# Try various Event Scheduler operation and check the output.
--error ER_EVENTS_DB_ERROR
show events;
--error ER_EVENTS_DB_ERROR
select event_name from information_schema.events;
--error ER_EVENTS_DB_ERROR
show create event intact_check;
--error ER_EVENTS_DB_ERROR
drop event no_such_event;
--error ER_EVENTS_DB_ERROR
create event intact_check_1 on schedule every 5 hour do select 5;
--error ER_EVENTS_DB_ERROR
alter event intact_check_1 on schedule every 8 hour do select 8;
--error ER_EVENTS_DB_ERROR
alter event intact_check_1 rename to intact_check_2;
--error ER_EVENTS_DB_ERROR
drop event intact_check_1;
--error ER_EVENTS_DB_ERROR
drop event intact_check_2;
--error ER_EVENTS_DB_ERROR
drop event intact_check;
--error ER_STARTUP
set global event_scheduler=on;
--error ER_STARTUP
set global event_scheduler=off;
show variables like 'event_scheduler';
--echo Make sure that we still can create and drop databases,
--echo and no warnings are produced.
drop database if exists mysqltest_database_not_exists;
create database mysqltest_db1;
drop database mysqltest_db1;
--echo Restore the original mysql.event table
drop table mysql.event;
rename table event_like to mysql.event;
--echo check that we can now enable events without restart
set global event_scheduler=original;
select @@global.event_scheduler;
set global event_scheduler=on;
select @@global.event_scheduler;
--sorted_result
--replace_column 6 # 9 # 10 #
--replace_result $collation_server latin1_swedish_ci
show events;
--echo Now let's restart the server again
--source include/restart_mysqld.inc
# We need this file primarily to make sure that the scheduler is restarted
# and enabled after we have restored mysql.event table.
# This is the final step of the "cleanup".
#
# Make sure also that events are executed OK after restart, just in case.
use events_test;
# Make sure the scheduler was started successfully
select @@event_scheduler;
let $wait_condition=select count(distinct name)=3 from execution_log;
--source include/wait_condition.inc
drop table execution_log;
# Will drop all events
drop database events_test;
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
--echo #
--echo # Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION
--echo # NOT PRESERVE IS DELETED AT SERVER
--echo #
SELECT @@event_scheduler;
USE test;
--disable_warnings
DROP EVENT IF EXISTS e1;
--enable_warnings
CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1;
--replace_column 6 # 9 # 10 #
SHOW EVENTS;
--echo "Now we restart the server"
--source include/restart_mysqld.inc
USE test;
SELECT @@event_scheduler;
--replace_column 6 # 9 # 10 #
SHOW EVENTS;
DROP EVENT e1;
--echo # end test for bug#11748899
--echo #
--echo # Test for MDEV-21758 Events switched randomly to SLAVESIDE_DISABLED
--echo #
create event ev on schedule every 1 minute do set @a= 1;
select name, originator, status from mysql.event;
--let $server_id= `SELECT @@global.server_id`
--echo #
--echo # Restarting server with server_id=100
--echo #
--let $restart_parameters= --server-id=100
--source include/restart_mysqld.inc
select @@global.server_id;
select name, originator, status from mysql.event;
--eval set global server_id= $server_id
--echo #
--echo # Restarting server with the original server_id=$server_id
--echo #
--let $restart_parameters=
--source include/restart_mysqld.inc
select @@global.server_id;
select name, originator, status from mysql.event;
# Cleanup
drop event ev;
|
