bk commit into 5.1 tree (andrey:1.2109) BUG#16407

[ahristov]

Below is the list of changes that have just been committed into a local
5.1 repository of andrey. When andrey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2109 06/02/20 23:52:22 andrey@lmy004. +6 -0
  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)

  mysql-test/t/events_bugs.test
    1.1 06/02/20 23:52:12 andrey@lmy004. +121 -0

  mysql-test/t/events_bugs.test
    1.0 06/02/20 23:52:12 andrey@lmy004. +0 -0
    BitKeeper file /work/mysql-5.1-bug16407/mysql-test/t/events_bugs.test

  mysql-test/r/events_bugs.result
    1.1 06/02/20 23:52:12 andrey@lmy004. +149 -0

  sql/sql_show.cc
    1.302 06/02/20 23:52:11 andrey@lmy004. +11 -3
    - show real content in I_S.EVENTS.SQL_MODE , disallow NULL

  sql/event_timed.cc
    1.26 06/02/20 23:52:11 andrey@lmy004. +12 -4
    - exchange thd->variables.sql_mode before and after execution
    - set the sql_mode of the anonymous SP -> event_timed::sphead

  sql/event.h
    1.18 06/02/20 23:52:11 andrey@lmy004. +1 -1
    add sql_mode as member variable

  sql/event.cc
    1.25 06/02/20 23:52:11 andrey@lmy004. +9 -1
    store the sql_mode when the body is stored
    - always on create event
    - during alter event if the user changes the body

  mysql-test/r/events_bugs.result
    1.0 06/02/20 23:52:11 andrey@lmy004. +0 -0
    BitKeeper file /work/mysql-5.1-bug16407/mysql-test/r/events_bugs.result

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	andrey
# Host:	lmy004.
# Root:	/work/mysql-5.1-bug16407

--- 1.301/sql/sql_show.cc	2006-02-09 09:34:36 +01:00
+++ 1.302/sql/sql_show.cc	2006-02-20 23:52:11 +01:00
@@ -3877,8 +3877,16 @@ fill_events_copy_to_schema_table(THD *th
   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"},
--- New file ---
+++ mysql-test/r/events_bugs.result	06/02/20 23:52:11
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;

--- New file ---
+++ mysql-test/t/events_bugs.test	06/02/20 23:52:12
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;


--- 1.24/sql/event.cc	2006-02-07 19:28:24 +01:00
+++ 1.25/sql/event.cc	2006-02-20 23:52:11 +01:00
@@ -268,10 +268,18 @@ evex_fill_row(THD *thd, TABLE *table, ev
 
   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 
 {
   TABLE *table;
   Open_tables_state backup;
-  uint ret;
+  int ret;
 
   DBUG_ENTER("db_drop_event");
   ret= EVEX_OPEN_TABLE_FAILED;

--- 1.17/sql/event.h	2006-01-31 17:22:28 +01:00
+++ 1.18/sql/event.h	2006-02-20 23:52:11 +01:00
@@ -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;

--- 1.25/sql/event_timed.cc	2006-02-07 19:37:22 +01:00
+++ 1.26/sql/event_timed.cc	2006-02-20 23:52:11 +01:00
@@ -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
   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 
   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 
   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 
   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.
   */