bk commit into 5.0 tree (andrey:1.2245) BUG#21795

[ahristov]

Below is the list of changes that have just been committed into a local
5.0 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@stripped, 2006-08-25 15:51:29+02:00, andrey@stripped +8 -0
  Fix for bug#21795: SP: sp_head::is_not_allowed_in_function() contains
  erroneous check
  
  Problem: Actually there were two problems in the server code. The check
  for SQLCOM_FLUSH in SF/Triggers were not according to the existing
  architecture which uses sp_get_flags_for_command() from sp_head.cc .
  This function was also missing a check for SQLCOM_FLUSH which has a
  problem combined with prelocking. This changeset fixes both of these
  deficiencies as well as the erroneous check in
  sp_head::is_not_allowed_in_function() which was a copy&paste error.

  mysql-test/r/sp-error.result@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +39 -0
    update result

  mysql-test/r/trigger.result@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +72 -1
    update result

  mysql-test/t/sp-error.test@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +39 -0
    FLUSH can create a problem with prelocking, hence it's disabled.
    There is a better way to check this than a check in the parser.
    Now we use sp_get_flags_for_command() and the error returned is
    different.

  mysql-test/t/trigger.test@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +89 -1
    FLUSH can create a problem with prelocking, hence it's disabled.
    There is a better way to check this than a check in the parser.
    Now we use sp_get_flags_for_command() and the error returned is
    different.

  sql/sp_head.cc@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +6 -0
    FLUSH and RESET are not allowed inside a SF/Trigger.
    Because they don't imply a COMMIT sp_head::HAS_COMMIT_OR_ROLLBACK
    cannot be used. Two new flags were introduced for that reason.

  sql/sp_head.h@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +11 -7
    Don't check m_type as this check is erroneous. This is probably
    a copy and paste error when moving code from somewhere else. Another
    fact which supports this was prefixing the enum value with the name
    of class sp_head.
    
    Adding two new flags HAS_SQLCOM_RESET and HAS_SQLCOM_FLUSH. The values
    are 2048 and 4096 because in the 5.1 branch there are already new flags
    which are with values up-to 1024.

  sql/sql_parse.cc@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +1 -5
    FLUSH can cause a problem with prelocking in SF/Trigger and
    therefore is already disabled. RESET is also disabled because
    is handled by the same code as FLUSH. We won't allow RESET inside
    SF/Trigger at that stage without thorough analysis. The check for
    them is already done in the parser by calling
    is_not_allowed_in_function()

  sql/sql_yacc.yy@stripped, 2006-08-25 15:51:23+02:00, andrey@stripped +2 -11
    By listing SQLCOM_FLUSH as command which implies COMMIT
    in sp_get_flags_for_command() the check in sql_yacc.yy is
    obsolete.

# 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:	example.com
# Root:	/work/mysql-5.0-runtime

--- 1.566/sql/sql_parse.cc	2006-08-25 15:51:40 +02:00
+++ 1.567/sql/sql_parse.cc	2006-08-25 15:51:40 +02:00
@@ -6681,11 +6681,7 @@ bool reload_acl_and_cache(THD *thd, ulon
   select_errors=0;				/* Write if more errors */
   bool tmp_write_to_binlog= 1;
 
-  if (thd && thd->in_sub_stmt)
-  {
-    my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH");
-    return 1;
-  }
+  DBUG_ASSERT(!thd || !thd->in_sub_stmt);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   if (options & REFRESH_GRANT)

--- 1.479/sql/sql_yacc.yy	2006-08-25 15:51:40 +02:00
+++ 1.480/sql/sql_yacc.yy	2006-08-25 15:51:40 +02:00
@@ -6783,17 +6783,8 @@ flush:
 	FLUSH_SYM opt_no_write_to_binlog
 	{
 	  LEX *lex=Lex;
-	  if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-	  {
-            /*
-              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;
+	  lex->sql_command= SQLCOM_FLUSH;
+          lex->type= 0;
           lex->no_write_to_binlog= $2;
 	}
 	flush_options

--- 1.44/mysql-test/r/trigger.result	2006-08-25 15:51:41 +02:00
+++ 1.45/mysql-test/r/trigger.result	2006-08-25 15:51:41 +02:00
@@ -626,16 +626,87 @@ Trigger	Event	Table	Statement	Timing	Cre
 t1_bi	INSERT	t1	set new.a = '2004-01-00'	BEFORE	#		root@localhost
 drop table t1;
 create table t1 (id int);
+create trigger t1_ai after insert on t1 for each row reset query cache;
+ERROR 0A000: RESET is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row reset master;
+ERROR 0A000: RESET is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row reset slave;
+ERROR 0A000: RESET is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush hosts;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush tables with read lock;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush logs;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush status;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush slave;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush master;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush des_key_file;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create trigger t1_ai after insert on t1 for each row flush user_resources;
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
 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;
+drop procedure if exists p1;
 create trigger t1_ai after insert on t1 for each row call p1();
+create procedure p1() flush tables;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() reset query cache;
+insert into t1 values (0);
+ERROR 0A000: RESET is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() reset master;
+insert into t1 values (0);
+ERROR 0A000: RESET is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() reset slave;
+insert into t1 values (0);
+ERROR 0A000: RESET is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush hosts;
 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;
+create procedure p1() flush tables with read lock;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush tables;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush logs;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush status;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush slave;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush master;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush des_key_file;
+insert into t1 values (0);
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+drop procedure p1;
+create procedure p1() flush user_resources;
 insert into t1 values (0);
 ERROR 0A000: FLUSH is not allowed in stored function or trigger
 drop procedure p1;

--- 1.50/mysql-test/t/trigger.test	2006-08-25 15:51:41 +02:00
+++ 1.51/mysql-test/t/trigger.test	2006-08-25 15:51:41 +02:00
@@ -651,17 +651,105 @@ drop table t1;
 # 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 reset query cache;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row reset master;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row reset slave;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush hosts;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush tables with read lock;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush logs;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush status;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush slave;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush master;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush des_key_file;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create trigger t1_ai after insert on t1 for each row flush user_resources;
+--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;
+--disable_warnings
+drop procedure if exists p1;
+--enable_warnings
+
 create trigger t1_ai after insert on t1 for each row call p1();
+create procedure p1() flush tables;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() reset query cache;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() reset master;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() reset slave;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush hosts;
 --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;
+create procedure p1() flush tables with read lock;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush tables;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush logs;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush status;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush slave;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush master;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush des_key_file;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
+drop procedure p1;
+create procedure p1() flush user_resources;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+insert into t1 values (0);
+
 drop procedure p1;
 drop table t1;
 

--- 1.107/mysql-test/r/sp-error.result	2006-08-25 15:51:41 +02:00
+++ 1.108/mysql-test/r/sp-error.result	2006-08-25 15:51:41 +02:00
@@ -634,6 +634,45 @@ flush tables;
 return 5;
 end|
 ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin reset query cache;
+return 1; end|
+ERROR 0A000: RESET is not allowed in stored function or trigger
+create function bug8409() returns int begin reset master;
+return 1; end|
+ERROR 0A000: RESET is not allowed in stored function or trigger
+create function bug8409() returns int begin reset slave;
+return 1; end|
+ERROR 0A000: RESET is not allowed in stored function or trigger
+create function bug8409() returns int begin flush hosts;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush privileges;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush tables with read lock;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush tables;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush logs;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush status;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush slave;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush master;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush des_key_file;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
+create function bug8409() returns int begin flush user_resources;
+return 1; end|
+ERROR 0A000: FLUSH is not allowed in stored function or trigger
 create procedure bug9529_901234567890123456789012345678901234567890123456789012345()
 begin
 end|

--- 1.107/mysql-test/t/sp-error.test	2006-08-25 15:51:41 +02:00
+++ 1.108/mysql-test/t/sp-error.test	2006-08-25 15:51:41 +02:00
@@ -899,6 +899,45 @@ begin
   flush tables;
   return 5;
 end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin reset query cache;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin reset master;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin reset slave;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush hosts;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush privileges;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush tables with read lock;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush tables;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush logs;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush status;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush slave;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush master;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush des_key_file;
+return 1; end|
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+create function bug8409() returns int begin flush user_resources;
+return 1; end|
 
 
 #

--- 1.221/sql/sp_head.cc	2006-08-25 15:51:41 +02:00
+++ 1.222/sql/sp_head.cc	2006-08-25 15:51:41 +02:00
@@ -230,6 +230,12 @@ sp_get_flags_for_command(LEX *lex)
     else
       flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
     break;
+  case SQLCOM_FLUSH:
+    flags= sp_head::HAS_SQLCOM_FLUSH;
+    break;
+  case SQLCOM_RESET:
+    flags= sp_head::HAS_SQLCOM_RESET;
+    break;
   case SQLCOM_CREATE_INDEX:
   case SQLCOM_CREATE_DB:
   case SQLCOM_CREATE_VIEW:

--- 1.87/sql/sp_head.h	2006-08-25 15:51:41 +02:00
+++ 1.88/sql/sp_head.h	2006-08-25 15:51:41 +02:00
@@ -115,7 +115,9 @@ public:
     IS_INVOKED= 32,             // Is set if this sp_head is being used
     HAS_SET_AUTOCOMMIT_STMT= 64,// Is set if a procedure with 'set autocommit'
     /* Is set if a procedure with COMMIT (implicit or explicit) | ROLLBACK */
-    HAS_COMMIT_OR_ROLLBACK= 128
+    HAS_COMMIT_OR_ROLLBACK= 128,
+    HAS_SQLCOM_RESET= 2048,
+    HAS_SQLCOM_FLUSH= 4096
   };
 
   /* TYPE_ENUM_FUNCTION, TYPE_ENUM_PROCEDURE or TYPE_ENUM_TRIGGER */
@@ -335,14 +337,16 @@ public:
       my_error(ER_SP_NO_RETSET, MYF(0), where);
     else if (m_flags & HAS_SET_AUTOCOMMIT_STMT)
       my_error(ER_SP_CANT_SET_AUTOCOMMIT, MYF(0));
-    else if (m_type != TYPE_ENUM_PROCEDURE &&
-             (m_flags & sp_head::HAS_COMMIT_OR_ROLLBACK))
-    {
+    else if (m_flags & HAS_COMMIT_OR_ROLLBACK)
       my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-      return TRUE;
-    }
+    else if (m_flags & HAS_SQLCOM_RESET)
+      my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "RESET");
+    else if (m_flags & HAS_SQLCOM_FLUSH)
+      my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "FLUSH");
+
     return test(m_flags &
-		(CONTAINS_DYNAMIC_SQL|MULTI_RESULTS|HAS_SET_AUTOCOMMIT_STMT));
+		(CONTAINS_DYNAMIC_SQL|MULTI_RESULTS|HAS_SET_AUTOCOMMIT_STMT|
+                 HAS_COMMIT_OR_ROLLBACK|HAS_SQLCOM_RESET|HAS_SQLCOM_FLUSH));
   }
 
 #ifndef DBUG_OFF