bk commit into 5.0 tree (dlenev:1.1939) BUG#12280

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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.1939 05/08/10 10:31:32 dlenev@stripped +7 -0
  Fix for bug #12280 "Triggers: crash if flush tables".
  
  We should not allow FLUSH statement to be executed inside both triggers
  and stored functions.

  sql/sql_yacc.yy
    1.411 05/08/10 10:31:27 dlenev@stripped +8 -3
    We should not allow FLUSH statement inside both triggers and stored
    functions. Replaced error which is thrown in this case with more
    specific.
    Also now we issue more general ER_SP_BADSTATEMENT error when one tries
    to use USE command inside of stored routine.

  sql/sql_parse.cc
    1.458 05/08/10 10:31:26 dlenev@stripped +7 -0
    reload_acl_and_cache():
      FLUSH TABLES and FLUSH PRIVILEGES should not be allowed if we are inside
      of stored function or trigger.

  sql/share/errmsg.txt
    1.38 05/08/10 10:31:26 dlenev@stripped +2 -2
    Removed ER_SP_NO_USE error. Now we use more general ER_SP_BADSTATEMENT in this
    case. Instead added error message for barking about statements which should not
    be allowed inside of stored functions or triggers.
    It is safe to do this since it is highly unprobable that someone will upgrade
    first to the new 5.0 release and then downgrade back to the old one.

  mysql-test/t/trigger.test
    1.21 05/08/10 10:31:26 dlenev@stripped +20 -0
    Added test for bug #12280 "Triggers: crash if flush tables"

  mysql-test/t/sp-error.test
    1.80 05/08/10 10:31:26 dlenev@stripped +2 -2
    Updated test after replacing error, which is thrown when one uses FLUSH
    statement inside of stored function, with more specific.
    Also now we issue more general error when we barking about USE command
    in stored routines.

  mysql-test/r/trigger.result
    1.16 05/08/10 10:31:26 dlenev@stripped +16 -0
    Added test for bug #12280 "Triggers: crash if flush tables"

  mysql-test/r/sp-error.result
    1.77 05/08/10 10:31:26 dlenev@stripped +2 -2
    Updated test after replacing error, which is thrown when one uses FLUSH
    statement inside of stored function, with more specific.
    Also now we issue more general error when we barking about USE command
    in stored routines.

# 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:	dlenev
# Host:	brandersnatch.localdomain
# Root:	/home/dlenev/src/mysql-5.0-bg12280

--- 1.457/sql/sql_parse.cc	2005-08-03 14:13:59 +04:00
+++ 1.458/sql/sql_parse.cc	2005-08-10 10:31:26 +04:00
@@ -6395,6 +6395,13 @@
   bool result=0;
   select_errors=0;				/* Write if more errors */
   bool tmp_write_to_binlog= 1;
+
+  if (thd->in_sub_stmt)
+  {
+    my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH");
+    return 1;
+  }
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   if (options & REFRESH_GRANT)
   {

--- 1.410/sql/sql_yacc.yy	2005-07-19 20:52:55 +04:00
+++ 1.411/sql/sql_yacc.yy	2005-08-10 10:31:27 +04:00
@@ -1960,7 +1960,7 @@
 	    }
 	    if (lex->sql_command == SQLCOM_CHANGE_DB)
 	    { /* "USE db" doesn't work in a procedure */
-	      my_message(ER_SP_NO_USE, ER(ER_SP_NO_USE), MYF(0));
+	      my_error(ER_SP_BADSTATEMENT, MYF(0), "USE");
 	      YYABORT;
 	    }
 	    /*
@@ -6642,9 +6642,14 @@
 	FLUSH_SYM opt_no_write_to_binlog
 	{
 	  LEX *lex=Lex;
-	  if (lex->sphead && lex->sphead->m_type == TYPE_ENUM_FUNCTION)
+	  if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
 	  {
-	    my_error(ER_SP_BADSTATEMENT, MYF(0), "FLUSH");
+            /*
+              Note that both FLUSH TABLES and FLUSH PRIVILEGES will break
+              execution in prelocked mode. So it is better to disable
+              FLUSH in stored functions and triggers completely.
+            */
+            my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH");
 	    YYABORT;
 	  }
 	  lex->sql_command= SQLCOM_FLUSH; lex->type=0;

--- 1.37/sql/share/errmsg.txt	2005-08-03 03:28:30 +04:00
+++ 1.38/sql/share/errmsg.txt	2005-08-10 10:31:26 +04:00
@@ -5137,8 +5137,8 @@
 	eng "Failed to ALTER %s %s"
 ER_SP_SUBSELECT_NYI 0A000 
 	eng "Subselect value not supported"
-ER_SP_NO_USE 42000 
-	eng "USE is not allowed in a stored procedure"
+ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 0A000
+        eng "%s is not allowed in stored function or trigger"
 ER_SP_VARCOND_AFTER_CURSHNDLR 42000 
 	eng "Variable or condition declaration after cursor or handler declaration"
 ER_SP_CURSOR_AFTER_HANDLER 42000 

--- 1.15/mysql-test/r/trigger.result	2005-07-30 10:24:47 +04:00
+++ 1.16/mysql-test/r/trigger.result	2005-08-10 10:31:26 +04:00
@@ -2,6 +2,7 @@
 drop view if exists v1;
 drop database if exists mysqltest;
 drop function if exists f1;
+drop procedure if exists p1;
 create table t1 (i int);
 create trigger trg before insert on t1 for each row set @a:=1;
 set @a:=0;
@@ -634,4 +635,19 @@
 show triggers;
 Trigger	Event	Table	Statement	Timing	Created	sql_mode
 t1_bi	INSERT	t1	 set new.a = '2004-01-00'	BEFORE	#	
+drop table t1;
+create table t1 (id int);
+create trigger t1_ai after insert on t1 for each row flush tables;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush privileges;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create procedure p1() flush tables;
+create trigger t1_ai after insert on t1 for each row call p1();
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush privileges;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
 drop table t1;

--- 1.20/mysql-test/t/trigger.test	2005-07-30 10:24:47 +04:00
+++ 1.21/mysql-test/t/trigger.test	2005-08-10 10:31:26 +04:00
@@ -7,6 +7,7 @@
 drop view if exists v1;
 drop database if exists mysqltest;
 drop function if exists f1;
+drop procedure if exists p1;
 --enable_warnings
 
 create table t1 (i int);
@@ -641,4 +642,23 @@
 show create table t1;
 --replace_column 6 #
 show triggers;
+drop table t1;
+
+# Test for bug #12280 "Triggers: crash if flush tables"
+# FLUSH TABLES and FLUSH PRIVILEGES should be disallowed inside
+# of functions and triggers.
+create table t1 (id int);
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush tables;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush privileges;
+create procedure p1() flush tables;
+create trigger t1_ai after insert on t1 for each row call p1();
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+drop procedure p1;
+create procedure p1() flush privileges;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+drop procedure p1;
 drop table t1;

--- 1.76/mysql-test/r/sp-error.result	2005-07-13 13:57:01 +04:00
+++ 1.77/mysql-test/r/sp-error.result	2005-08-10 10:31:26 +04:00
@@ -247,7 +247,7 @@
 ERROR 42000: Duplicate cursor: c
 create procedure u()
 use sptmp|
-ERROR 42000: USE is not allowed in a stored procedure
+ERROR 0A000: USE is not allowed in stored procedures
 create procedure p()
 begin
 declare c cursor for select * from t1;
@@ -616,7 +616,7 @@
 flush tables;
 return 5;
 end|
-ERROR 0A000: FLUSH is not allowed in stored procedures
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
 create procedure
bug9529_90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123()
 begin
 end|

--- 1.79/mysql-test/t/sp-error.test	2005-07-13 13:57:01 +04:00
+++ 1.80/mysql-test/t/sp-error.test	2005-08-10 10:31:26 +04:00
@@ -334,7 +334,7 @@
 end|
 
 # USE is not allowed
---error 1336
+--error ER_SP_BADSTATEMENT 
 create procedure u()
   use sptmp|
 
@@ -885,7 +885,7 @@
 --disable_warnings
 drop function if exists bug8409|
 --enable_warnings
---error ER_SP_BADSTATEMENT
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
 create function bug8409()
   returns int
 begin