diff options
Diffstat (limited to 'mysql-test/main/events_1.test')
| -rw-r--r-- | mysql-test/main/events_1.test | 493 |
1 files changed, 493 insertions, 0 deletions
diff --git a/mysql-test/main/events_1.test b/mysql-test/main/events_1.test new file mode 100644 index 00000000000..9768127f458 --- /dev/null +++ b/mysql-test/main/events_1.test @@ -0,0 +1,493 @@ +# 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 + +set sql_mode=""; + +call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); + +--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(curdate(), interval 5 minute) ends date_add(curdate(), 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 check the error is emitted. +--echo +ALTER TABLE mysql.event ADD dummy INT; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name FROM INFORMATION_SCHEMA.events; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW CREATE EVENT intact_check; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +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; +DROP EVENT intact_check; +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_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name FROM INFORMATION_SCHEMA.events; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW CREATE EVENT intact_check; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +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_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW CREATE EVENT intact_check; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +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; + +--echo +--echo # +--echo # Bug#12394306: the sever may crash if mysql.event is corrupted +--echo # + +--echo +CREATE EVENT ev1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ALTER EVENT ev1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; + +--echo +CREATE TABLE event_original LIKE mysql.event; +INSERT INTO event_original SELECT * FROM mysql.event; + +--echo +ALTER TABLE mysql.event MODIFY modified CHAR(1); + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name, created, last_altered FROM information_schema.events; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT ev2 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT ev1 ON SCHEDULE EVERY 9 HOUR DO SELECT 9; + +--echo +DROP TABLE mysql.event; +RENAME TABLE event_original TO mysql.event; + +--echo +DROP EVENT ev1; + +--echo +SHOW EVENTS; + + +--echo # +--echo # MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +--echo # +CREATE TABLE t1 (a INT); +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +--replace_column 8 # 9 # +SHOW EVENTS; +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +--replace_column 8 # 9 # +SHOW EVENTS; +DROP EVENT ev1; +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +--replace_column 8 # 9 # +SHOW EVENTS; +DROP EVENT ev1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo +--echo # +--echo # End of tests +--echo # + +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; |