fix for bug#16407 (Events: Changes in sql_mode won't be taken into account)

WL#1032
sql_mode setting was disregarded during create/alter event and wasn't set during
event execution.
(post-review small fixes)

1 files changed, 121 insertions, 0 deletions

diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test
new file mode 100644
index 00000000000..2d4374dcb41
--- /dev/null
+++ b/mysql-test/t/events_bugs.test
@@ -0,0 +1,121 @@
+create database if not exists events_test;
+use events_test;
+#
+# Start - 16407: Events: Changes in sql_mode won't be taken into account
+#
+set global event_scheduler=0;
+--echo "Wait a bit to settle down"
+--sleep 1
+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);
+delimiter |;
+create event e_16407 on schedule every 60 second do
+begin
+  select get_lock('test_bug16407', 60);
+  drop table "hashed_num";
+end|
+delimiter ;|
+--sleep 1
+--echo "Now if everything is fine the event has compiled and is locked
+--replace_column 1 # 6 #
+show processlist;
+select release_lock('test_bug16407');
+set global event_scheduler= 0;
+select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
+--echo "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;
+drop event e_16407;
+
+--echo "Another sql_mode test"
+set sql_mode="traditional";
+create table events_smode_test(ev_name char(10), a date) engine=myisam;
+--echo "This should never insert something"
+delimiter |;
+create event ee_16407_2 on schedule every 60 second do
+begin
+  select get_lock('ee_16407_2', 60);
+  insert into events_test.events_smode_test values('ee_16407_2','1980-19-02');
+end|
+--error 1292
+insert into events_smode_test values ('test','1980-19-02')|
+--echo "This is ok"
+create event ee_16407_3 on schedule every 60 second do
+begin
+  select get_lock('ee_16407_2', 60);
+  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=""|
+--echo "This will insert rows but they will be truncated"
+create event ee_16407_4 on schedule every 60 second do
+begin
+  select get_lock('ee_16407_2', 60);
+  insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956');
+end|
+delimiter ;|
+select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
+set sql_mode="ansi";
+select get_lock('ee_16407_2', 60);
+set global event_scheduler= 1;
+--sleep 1
+--replace_column 1 # 6 #
+show processlist;
+select release_lock('ee_16407_2');
+--sleep 3
+--replace_column 1 # 6 #
+show processlist;
+set global event_scheduler= 0;
+select * from events_smode_test order by ev_name, a;
+--echo "OK, last check before we drop them"
+select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
+drop event ee_16407_2;
+drop event ee_16407_3;
+drop event ee_16407_4;
+
+
+--echo "And now one last test regarding sql_mode and call of SP from an event"
+delete from events_smode_test;
+set sql_mode='traditional';
+delimiter |;
+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
+  select get_lock('ee_16407_5', 60);
+  call events_test.ee_16407_5_pendant();
+end|
+create event ee_16407_6 on schedule every 60 second do
+begin
+  select get_lock('ee_16407_5', 60);
+  call events_test.ee_16407_6_pendant();
+end|
+delimiter ;|
+set sql_mode='ansi';
+select get_lock('ee_16407_5', 60);
+set global event_scheduler= 1;
+--sleep 1
+--replace_column 1 # 6 #
+show processlist;
+select release_lock('ee_16407_5');
+--sleep 3
+--replace_column 1 # 6 #
+show processlist;
+select * from events_smode_test order by ev_name, a;
+--echo "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;
+drop event ee_16407_5;
+drop event ee_16407_6;
+drop procedure ee_16407_5_pendant;
+drop procedure ee_16407_6_pendant;
+set global event_scheduler= 0;
+drop table events_smode_test;
+set sql_mode=@old_sql_mode;
+#
+# End  - 16407: Events: Changes in sql_mode won't be taken into account  
+#
+drop database events_test;