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)


sql/event.cc:
  store the sql_mode when the body is stored
  - always on create event
  - during alter event if the user changes the body
sql/event.h:
  add sql_mode as member variable
sql/event_timed.cc:
  - exchange thd->variables.sql_mode before and after execution
  - set the sql_mode of the anonymous SP -> event_timed::sphead
sql/sql_show.cc:
  - show real content in I_S.EVENTS.SQL_MODE , disallow NULL

6 files changed, 303 insertions, 9 deletions

diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result
new file mode 100644
index 00000000000..aa5165c56ac
--- /dev/null
+++ b/mysql-test/r/events_bugs.result
@@ -0,0 +1,149 @@
+create database if not exists events_test;
+use events_test;
+set global event_scheduler=0;
+"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
+show processlist;
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	events_test	Query	#	NULL	show processlist
+#	event_scheduler		NULL	Connect	#	Sleeping	NULL
+#	root		events_test	Connect	#	User lock	select get_lock('test_bug16407', 60)
+select release_lock('test_bug16407');
+release_lock('test_bug16407')
+1
+set global event_scheduler= 0;
+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
+select get_lock('ee_16407_2', 60);
+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
+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=""|
+"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|
+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;
+show processlist;
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	events_test	Query	#	NULL	show processlist
+#	event_scheduler		NULL	Connect	#	Sleeping	NULL
+#	root		events_test	Connect	#	User lock	select get_lock('ee_16407_2', 60)
+#	root		events_test	Connect	#	User lock	select get_lock('ee_16407_2', 60)
+#	root		events_test	Connect	#	User lock	select get_lock('ee_16407_2', 60)
+select release_lock('ee_16407_2');
+release_lock('ee_16407_2')
+1
+show processlist;
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	events_test	Query	#	NULL	show processlist
+#	event_scheduler		NULL	Connect	#	Sleeping	NULL
+set global event_scheduler= 0;
+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
+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|
+set sql_mode='ansi';
+select get_lock('ee_16407_5', 60);
+get_lock('ee_16407_5', 60)
+1
+set global event_scheduler= 1;
+show processlist;
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	events_test	Query	#	NULL	show processlist
+#	event_scheduler		NULL	Connect	#	Sleeping	NULL
+#	root		events_test	Connect	#	User lock	select get_lock('ee_16407_5', 60)
+#	root		events_test	Connect	#	User lock	select get_lock('ee_16407_5', 60)
+select release_lock('ee_16407_5');
+release_lock('ee_16407_5')
+1
+show processlist;
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	events_test	Query	#	NULL	show processlist
+#	event_scheduler		NULL	Connect	#	Sleeping	NULL
+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;
+set global event_scheduler= 0;
+drop table events_smode_test;
+set sql_mode=@old_sql_mode;
+drop database events_test;
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;
diff --git a/sql/event.cc b/sql/event.cc
index a7c6d48b988..564b988d0f8 100644
--- a/sql/event.cc
+++ b/sql/event.cc
@@ -268,10 +268,18 @@ evex_fill_row(THD *thd, TABLE *table, event_timed *et, my_bool is_update)
 
   table->field[EVEX_FIELD_STATUS]->store((longlong)et->status);
 
+  /*
+    Change the SQL_MODE only if body was present in an ALTER EVENT and of course
+    always during CREATE EVENT.
+  */ 
   if (et->body.str)
+  {
+    table->field[EVEX_FIELD_SQL_MODE]->store((longlong)thd->variables.sql_mode);
+
     if (table->field[field_num= EVEX_FIELD_BODY]->
                      store(et->body.str, et->body.length, system_charset_info))
       goto trunc_err;
+  }
 
   if (et->starts.year)
   {
@@ -886,7 +894,7 @@ int db_drop_event(THD *thd, event_timed *et, bool drop_if_exists,
 {
   TABLE *table;
   Open_tables_state backup;
-  uint ret;
+  int ret;
 
   DBUG_ENTER("db_drop_event");
   ret= EVEX_OPEN_TABLE_FAILED;
diff --git a/sql/event.h b/sql/event.h
index 1fe5c8e5713..1092e4b85d8 100644
--- a/sql/event.h
+++ b/sql/event.h
@@ -109,7 +109,7 @@ public:
   enum enum_event_on_completion on_completion;
   enum enum_event_status status;
   sp_head *sphead;
-
+  ulong sql_mode;
   const uchar *body_begin;
   
   bool dropped;
diff --git a/sql/event_timed.cc b/sql/event_timed.cc
index e585f6252ca..80ad7851bf1 100644
--- a/sql/event_timed.cc
+++ b/sql/event_timed.cc
@@ -44,7 +44,9 @@ event_timed::init()
 
   definer_user.str= definer_host.str= 0;
   definer_user.length= definer_host.length= 0;
-    
+
+  sql_mode= 0;
+
   DBUG_VOID_RETURN;
 }
 
@@ -569,6 +571,9 @@ event_timed::load_from_row(MEM_ROOT *mem_root, TABLE *table)
   else
     et->comment.length= 0;
     
+
+  et->sql_mode= (ulong) table->field[EVEX_FIELD_SQL_MODE]->val_int();
+
   DBUG_RETURN(0);
 error:
   DBUG_RETURN(EVEX_GET_FIELD_FAILED);
@@ -1054,6 +1059,7 @@ event_timed::compile(THD *thd, MEM_ROOT *mem_root)
   char *old_query;
   uint old_query_len;
   st_sp_chistics *p;
+  ulong old_sql_mode= thd->variables.sql_mode;
   CHARSET_INFO *old_character_set_client, *old_collation_connection,
                *old_character_set_results;
 
@@ -1069,6 +1075,8 @@ event_timed::compile(THD *thd, MEM_ROOT *mem_root)
   thd->update_charset();
   
   DBUG_ENTER("event_timed::compile");
+  DBUG_PRINT("info",("old_sql_mode=%d new_sql_mode=%d",old_sql_mode, sql_mode));
+  thd->variables.sql_mode= this->sql_mode;
   // change the memory root for the execution time
   if (mem_root)
   {
@@ -1108,9 +1116,8 @@ event_timed::compile(THD *thd, MEM_ROOT *mem_root)
   sphead= lex.et->sphead;
   sphead->m_db= dbname;
   //copy also chistics since they will vanish otherwise we get 0x0 pointer
-  // Todo : Handle sql_mode !!
   sphead->set_definer(definer.str, definer.length);
-  sphead->set_info(0, 0, &lex.sp_chistics, 0/*sql_mode*/);
+  sphead->set_info(0, 0, &lex.sp_chistics, sql_mode);
   sphead->optimize();
   ret= 0;
 done:
@@ -1124,11 +1131,12 @@ done:
   thd->query_length= old_query_len;
   thd->db= old_db;
 
+  thd->variables.sql_mode= old_sql_mode;
   thd->variables.character_set_client= old_character_set_client;
   thd->variables.character_set_results= old_character_set_results;
   thd->variables.collation_connection= old_collation_connection;
   thd->update_charset();
-
+  
   /*
     Change the memory root for the execution time.
   */
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 7a1d843dd45..3ad01ef0ae9 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3877,8 +3877,16 @@ fill_events_copy_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table)
   sch_table->field[3]->store(et.definer.str, et.definer.length, scs);
   sch_table->field[4]->store(et.body.str, et.body.length, scs);
 
-  // [9] is SQL_MODE and is NULL for now, will be fixed later
-  sch_table->field[9]->set_null();
+  // [9] is SQL_MODE 
+  {
+    const char *sql_mode_str="";
+    ulong sql_mode_len=0;
+    sql_mode_str=
+           sys_var_thd_sql_mode::symbolic_mode_representation(thd, et.sql_mode,
+                                                              &sql_mode_len);  
+    sch_table->field[9]->store((const char*)sql_mode_str, sql_mode_len, scs);
+  }
+  
   if (et.expression)
   {
     //type
@@ -4720,7 +4728,7 @@ ST_FIELD_INFO events_fields_info[]=
   {"EXECUTE_AT", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Execute at"},
   {"INTERVAL_VALUE", 11, MYSQL_TYPE_LONG, 0, 1, "Interval value"},
   {"INTERVAL_FIELD", 18, MYSQL_TYPE_STRING, 0, 1, "Interval field"},
-  {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 1, 0},
+  {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 0, 0},
   {"STARTS", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Starts"},
   {"ENDS", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Ends"},
   {"STATUS", 8, MYSQL_TYPE_STRING, 0, 0, "Status"},