| 1 |
# Can't test with embedded server that doesn't support grants |
| 2 |
-- source include/not_embedded.inc |
| 3 |
--disable_query_log |
| 4 |
call mtr.add_suppression('Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted'); |
| 5 |
call mtr.add_suppression('Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler'); |
| 6 |
call mtr.add_suppression('Event Scheduler.*shutdown.*'); |
| 7 |
--enable_query_log |
| 8 |
|
| 9 |
let $collation_server=`select @@collation_server`; |
| 10 |
# |
| 11 |
# Test that when the server is restarted, it checks mysql.event table, |
| 12 |
# and disables the scheduler if it's not up to date. |
| 13 |
# |
| 14 |
|
| 15 |
# Switch off the scheduler for now. |
| 16 |
set global event_scheduler=off; |
| 17 |
|
| 18 |
create database events_test; |
| 19 |
use events_test; |
| 20 |
create table execution_log(name char(10)); |
| 21 |
|
| 22 |
create event abc1 on schedule every 1 second do |
| 23 |
insert into execution_log value('abc1'); |
| 24 |
create event abc2 on schedule every 1 second do |
| 25 |
insert into execution_log value('abc2'); |
| 26 |
create event abc3 on schedule every 1 second do |
| 27 |
insert into execution_log value('abc3'); |
| 28 |
# |
| 29 |
# There are various conditions when a server would regard mysql.event |
| 30 |
# table as damaged: |
| 31 |
# - the table has more column than specified in the compiled in value, but |
| 32 |
# the version of the server which created the table is the same |
| 33 |
# - the column count in the table is less than the compiled in value |
| 34 |
# - some column has an incompatible type specification (for what is an |
| 35 |
# incompatible type specification please refer to the comments in the source |
| 36 |
# |
| 37 |
# Unfortunately, in order to test a condition, we need to restart the |
| 38 |
# server. Therefore, here we test only one simple case: changing the data |
| 39 |
# type of the 'body' field to blob. |
| 40 |
# |
| 41 |
# First, let's do a backup to not depend on actual definition of mysql.event |
| 42 |
create table event_like like mysql.event; |
| 43 |
insert into event_like select * from mysql.event; |
| 44 |
# Now let's alter the table and restart the server |
| 45 |
alter table mysql.event |
| 46 |
change column body body longtext character set utf8 collate utf8_bin; |
| 47 |
--echo # Now we restart the server |
| 48 |
|
| 49 |
call mtr.add_suppression("Incorrect definition of table mysql.event:.*"); |
| 50 |
--source include/restart_mysqld.inc |
| 51 |
|
| 52 |
use events_test; |
| 53 |
# Event scheduler should be disabled: the system tables are damaged |
| 54 |
select @@event_scheduler; |
| 55 |
# Try various Event Scheduler operation and check the output. |
| 56 |
--error ER_EVENTS_DB_ERROR |
| 57 |
show events; |
| 58 |
--error ER_EVENTS_DB_ERROR |
| 59 |
select event_name from information_schema.events; |
| 60 |
--error ER_EVENTS_DB_ERROR |
| 61 |
show create event intact_check; |
| 62 |
--error ER_EVENTS_DB_ERROR |
| 63 |
drop event no_such_event; |
| 64 |
--error ER_EVENTS_DB_ERROR |
| 65 |
create event intact_check_1 on schedule every 5 hour do select 5; |
| 66 |
--error ER_EVENTS_DB_ERROR |
| 67 |
alter event intact_check_1 on schedule every 8 hour do select 8; |
| 68 |
--error ER_EVENTS_DB_ERROR |
| 69 |
alter event intact_check_1 rename to intact_check_2; |
| 70 |
--error ER_EVENTS_DB_ERROR |
| 71 |
drop event intact_check_1; |
| 72 |
--error ER_EVENTS_DB_ERROR |
| 73 |
drop event intact_check_2; |
| 74 |
--error ER_EVENTS_DB_ERROR |
| 75 |
drop event intact_check; |
| 76 |
--error ER_STARTUP |
| 77 |
set global event_scheduler=on; |
| 78 |
--error ER_STARTUP |
| 79 |
set global event_scheduler=off; |
| 80 |
show variables like 'event_scheduler'; |
| 81 |
--echo # Make sure that we still can create and drop databases, |
| 82 |
--echo # and no warnings are produced. |
| 83 |
drop database if exists mysqltest_database_not_exists; |
| 84 |
create database mysqltest_db1; |
| 85 |
drop database mysqltest_db1; |
| 86 |
--echo # Restore the original mysql.event table |
| 87 |
drop table mysql.event; |
| 88 |
rename table event_like to mysql.event; |
| 89 |
|
| 90 |
--echo # check that we can now enable events without restart |
| 91 |
set global event_scheduler=original; |
| 92 |
select @@global.event_scheduler; |
| 93 |
set global event_scheduler=on; |
| 94 |
select @@global.event_scheduler; |
| 95 |
--sorted_result |
| 96 |
--replace_column 6 # 9 # 10 # |
| 97 |
--replace_result $collation_server latin1_swedish_ci |
| 98 |
show events; |
| 99 |
|
| 100 |
--echo # Now let's restart the server again |
| 101 |
|
| 102 |
--source include/restart_mysqld.inc |
| 103 |
|
| 104 |
# We need this file primarily to make sure that the scheduler is restarted |
| 105 |
# and enabled after we have restored mysql.event table. |
| 106 |
# This is the final step of the "cleanup". |
| 107 |
# |
| 108 |
# Make sure also that events are executed OK after restart, just in case. |
| 109 |
use events_test; |
| 110 |
# Make sure the scheduler was started successfully |
| 111 |
select @@event_scheduler; |
| 112 |
let $wait_condition=select count(distinct name)=3 from execution_log; |
| 113 |
--source include/wait_condition.inc |
| 114 |
drop table execution_log; |
| 115 |
# Will drop all events |
| 116 |
drop database events_test; |
| 117 |
|
| 118 |
let $wait_condition= |
| 119 |
select count(*) = 0 from information_schema.processlist |
| 120 |
where db='events_test' and command = 'Connect' and user=current_user(); |
| 121 |
--source include/wait_condition.inc |
| 122 |
|
| 123 |
--echo # |
| 124 |
--echo # Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION |
| 125 |
--echo # NOT PRESERVE IS DELETED AT SERVER |
| 126 |
--echo # |
| 127 |
SELECT @@event_scheduler; |
| 128 |
USE test; |
| 129 |
--disable_warnings |
| 130 |
DROP EVENT IF EXISTS e1; |
| 131 |
--enable_warnings |
| 132 |
CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; |
| 133 |
--replace_column 6 # 9 # 10 # |
| 134 |
SHOW EVENTS; |
| 135 |
|
| 136 |
--echo # Now we restart the server |
| 137 |
--source include/restart_mysqld.inc |
| 138 |
USE test; |
| 139 |
SELECT @@event_scheduler; |
| 140 |
--replace_column 6 # 9 # 10 # |
| 141 |
SHOW EVENTS; |
| 142 |
DROP EVENT e1; |
| 143 |
|
| 144 |
--echo # end test for bug#11748899 |
| 145 |
|
| 146 |
--echo # |
| 147 |
--echo # Test for MDEV-21758 Events switched randomly to SLAVESIDE_DISABLED |
| 148 |
--echo # |
| 149 |
|
| 150 |
create event ev on schedule every 1 minute do set @a= 1; |
| 151 |
select name, originator, status from mysql.event; |
| 152 |
|
| 153 |
--let $server_id= `SELECT @@global.server_id` |
| 154 |
|
| 155 |
--echo # |
| 156 |
--echo # Restarting server with server_id=100 |
| 157 |
--echo # |
| 158 |
--let $restart_parameters= --server-id=100 |
| 159 |
--source include/restart_mysqld.inc |
| 160 |
|
| 161 |
select @@global.server_id; |
| 162 |
select name, originator, status from mysql.event; |
| 163 |
--eval set global server_id= $server_id |
| 164 |
|
| 165 |
--echo # |
| 166 |
--echo # Restarting server with the original server_id=$server_id |
| 167 |
--echo # |
| 168 |
--let $restart_parameters= |
| 169 |
--source include/restart_mysqld.inc |
| 170 |
|
| 171 |
select @@global.server_id; |
| 172 |
select name, originator, status from mysql.event; |
| 173 |
|
| 174 |
# Cleanup |
| 175 |
drop event ev; |