events_bugs.result 10.5 KB
Newer Older
1 2
create database if not exists events_test;
use events_test;
3 4 5 6 7
CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1;
CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2;
ERROR HY000: Event 'Lower_case' already exists
DROP EVENT Lower_case;
SET NAMES cp1251;
unknown's avatar
unknown committed
8 9
CREATE EVENT __1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100;
CREATE EVENT __1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200;
10
ERROR HY000: Event 'ДоЛеН_регистър_1251' already exists
unknown's avatar
unknown committed
11
DROP EVENT __1251;
12 13 14 15 16 17
SET NAMES utf8;
CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300;
CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400;
ERROR HY000: Event 'ДОЛЕН_регистър_утф8' already exists
DROP EVENT ДОЛЕН_регистър_утф8;
SET NAMES latin1;
18 19
set @a=3;
CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5;
20
ERROR HY000: Recursivity of EVENT DDL statements is forbidden when body is present
21 22 23 24 25 26
create event e_55 on schedule at 99990101000000 do drop table t;
ERROR HY000: Incorrect AT value: '99990101000000'
create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t;
ERROR HY000: Incorrect STARTS value: '99990101000000'
create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t;
ERROR HY000: ENDS is either invalid or before STARTS
27
set global event_scheduler=2;
28 29 30 31 32 33 34 35 36 37 38 39 40 41
"Wait a bit to settle down"
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);
get_lock('test_bug16407', 60)
1
create event e_16407 on schedule every 60 second do
begin
select get_lock('test_bug16407', 60);
drop table "hashed_num";
end|
"Now if everything is fine the event has compiled and is locked
42 43
select /*1*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
44
event_scheduler	localhost	NULL	Connect	Waiting for next activation	NULL
45
root	localhost	events_test	Connect	User lock	select get_lock('test_bug16407', 60)
46 47 48
select release_lock('test_bug16407');
release_lock('test_bug16407')
1
49
set global event_scheduler= 2;
50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	e_16407	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
"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;
event_schema	event_name	sql_mode
events_test	e_16407	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
drop event e_16407;
"Another sql_mode test"
set sql_mode="traditional";
create table events_smode_test(ev_name char(10), a date) engine=myisam;
"This should never insert something"
create event ee_16407_2 on schedule every 60 second do
begin
66
select get_lock('ee_16407_2', 60) /*ee_16407_2*/;
67 68 69 70 71 72 73
insert into events_test.events_smode_test values('ee_16407_2','1980-19-02');
end|
insert into events_smode_test values ('test','1980-19-02')|
ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1
"This is ok"
create event ee_16407_3 on schedule every 60 second do
begin
74
select get_lock('ee_16407_2', 60) /*ee_16407_3*/;
75 76 77 78 79 80 81
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=""|
"This will insert rows but they will be truncated"
create event ee_16407_4 on schedule every 60 second do
begin
82
select get_lock('ee_16407_2', 60) /*ee_16407_4*/;
83 84 85 86 87 88 89 90 91 92 93 94
insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956');
end|
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	ee_16407_2	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_3	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_4	
set sql_mode="ansi";
select get_lock('ee_16407_2', 60);
get_lock('ee_16407_2', 60)
1
set global event_scheduler= 1;
95 96
select /*2*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
97
event_scheduler	localhost	NULL	Connect	Waiting for next activation	NULL
98 99 100
root	localhost	events_test	Connect	User lock	select get_lock('ee_16407_2', 60) /*ee_16407_2*/
root	localhost	events_test	Connect	User lock	select get_lock('ee_16407_2', 60) /*ee_16407_3*/
root	localhost	events_test	Connect	User lock	select get_lock('ee_16407_2', 60) /*ee_16407_4*/
101 102 103
select release_lock('ee_16407_2');
release_lock('ee_16407_2')
1
104 105
select /*3*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
106
event_scheduler	localhost	NULL	Connect	Waiting for next activation	NULL
107
set global event_scheduler= 2;
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
select * from events_smode_test order by ev_name, a;
ev_name	a
ee_16407_3	1980-02-19
ee_16407_3	1980-02-29
ee_16407_4	0000-00-00
"OK, last check before we drop them"
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	ee_16407_2	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_3	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_4	
drop event ee_16407_2;
drop event ee_16407_3;
drop event ee_16407_4;
"And now one last test regarding sql_mode and call of SP from an event"
delete from events_smode_test;
set sql_mode='traditional';
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
129
select get_lock('ee_16407_5', 60) /*ee_16407_5*/;
130 131 132 133
call events_test.ee_16407_5_pendant();
end|
create event ee_16407_6 on schedule every 60 second do
begin
134
select get_lock('ee_16407_5', 60) /*ee_16407_6*/;
135 136 137 138 139 140 141
call events_test.ee_16407_6_pendant();
end|
set sql_mode='ansi';
select get_lock('ee_16407_5', 60);
get_lock('ee_16407_5', 60)
1
set global event_scheduler= 1;
142 143 144
"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;
user	host	db	command	state	info
145
event_scheduler	localhost	NULL	Connect	Waiting for next activation	NULL
146 147
root	localhost	events_test	Connect	User lock	select get_lock('ee_16407_5', 60) /*ee_16407_5*/
root	localhost	events_test	Connect	User lock	select get_lock('ee_16407_5', 60) /*ee_16407_6*/
148 149 150
select release_lock('ee_16407_5');
release_lock('ee_16407_5')
1
151 152 153
"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;
user	host	db	command	state	info
154
event_scheduler	localhost	NULL	Connect	Waiting for next activation	NULL
155 156 157 158 159 160 161 162 163 164 165 166
select * from events_smode_test order by ev_name, a;
ev_name	a
ee_16407_6	2004-02-29
"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;
event_schema	event_name	sql_mode
events_test	ee_16407_5	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_6	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
drop event ee_16407_5;
drop event ee_16407_6;
drop procedure ee_16407_5_pendant;
drop procedure ee_16407_6_pendant;
167
set global event_scheduler= 2;
168 169
drop table events_smode_test;
set sql_mode=@old_sql_mode;
170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192
set global event_scheduler=2;
delete from mysql.user where User like 'mysqltest_%';
delete from mysql.db where User like 'mysqltest_%';
flush privileges;
drop database if exists mysqltest_db1;
create user mysqltest_user1@localhost;
create database mysqltest_db1;
grant event on events_test.* to mysqltest_user1@localhost;
create event mysqltest_user1 on schedule every 10 second do select 42;
alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1;
ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db1'
"Let's test now rename when there is no select DB"
select database();
database()
NULL
alter event events_test.mysqltest_user1 rename to mysqltest_user1;
ERROR 3D000: No database selected
select event_schema, event_name, definer, event_type, status from information_schema.events;
event_schema	event_name	definer	event_type	status
events_test	mysqltest_user1	mysqltest_user1@localhost	RECURRING	ENABLED
drop event events_test.mysqltest_user1;
drop user mysqltest_user1@localhost;
drop database mysqltest_db1;
193 194 195 196 197 198 199 200
create event e_53 on schedule at (select s1 from ttx) do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) do drop table t' at line 1
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) second do drop table t' at line 1
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) do drop table t' at line 1
create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) do drop table t' at line 1
201
drop database events_test;