bk commit into 5.0 tree (pem:1.1950) BUG#12712

[pem]

Below is the list of changes that have just been committed into a local
5.0 repository of pem. When pem 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.1950 05/09/13 17:16:12 pem@stripped +7 -0
  Fixed BUG#12712: SET AUTOCOMMIT should fail within SP/functions/triggers
    Second version after review. Allow 'set autocommit' in procedures, but not
    functions or triggers. Can return error in run-time (when a function calls
    a procedure).

  sql/sql_yacc.yy
    1.422 05/09/13 17:16:05 pem@stripped +9 -0
    Disallow setting AUTOCOMMIT in stored function and triggers.

  sql/sp_head.h
    1.69 05/09/13 17:16:05 pem@stripped +4 -1
    New flag: has 'set autocommit', and testing for this in is_not_allowed_in_function().

  sql/share/errmsg.txt
    1.43 05/09/13 17:16:05 pem@stripped +2 -0
    New error message for disallowing the setting of autocommit in stored functions and
triggers.

  sql/set_var.h
    1.71 05/09/13 17:16:05 pem@stripped +1 -0
    Made sys_autocommit external, to allow testing in sql_yacc.yy.

  sql/set_var.cc
    1.140 05/09/13 17:16:05 pem@stripped +4 -4
    Made sys_autocommit external, to allow testing in sql_yacc.yy.

  mysql-test/t/sp-error.test
    1.87 05/09/13 17:16:05 pem@stripped +58 -0
    New test case for BUG#12712.

  mysql-test/r/sp-error.result
    1.83 05/09/13 17:16:04 pem@stripped +47 -0
    New test case for BUG#12712.

# 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:	pem
# Host:	mysql.comhem.se
# Root:	/usr/home/pem/mysql-5.0

--- 1.421/sql/sql_yacc.yy	2005-09-13 12:50:11 +02:00
+++ 1.422/sql/sql_yacc.yy	2005-09-13 17:16:05 +02:00
@@ -8004,6 +8004,15 @@
             if (tmp == &sys_time_zone &&
                 lex->add_time_zone_tables_to_query_tables(YYTHD))
               YYABORT;
+            else
+              if (spc && tmp == &sys_autocommit)
+              {
+                /*
+                  We don't allow setting AUTOCOMMIT from a stored function
+		  or trigger.
+                */
+                lex->sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT;
+              }
 	  }
 	  else
 	  {

--- 1.42/sql/share/errmsg.txt	2005-09-03 01:25:41 +02:00
+++ 1.43/sql/share/errmsg.txt	2005-09-13 17:16:05 +02:00
@@ -5403,3 +5403,5 @@
         eng "The definition of table '%-.64s' prevents operation %s on table '%-.64s'."
 ER_PS_NO_RECURSION
         eng "The prepared statement contains a stored routine call that refers to that
same statement. It's not allowed to execute a prepared statement in such a recursive
manner"
+ER_SP_CANT_SET_AUTOCOMMIT
+	eng "Not allowed to set autocommit from a stored function or trigger"

--- 1.139/sql/set_var.cc	2005-09-07 12:36:58 +02:00
+++ 1.140/sql/set_var.cc	2005-09-13 17:16:05 +02:00
@@ -447,10 +447,10 @@
 
 /* Variables that are bits in THD */
 
-static sys_var_thd_bit	sys_autocommit("autocommit", 0,
-				       set_option_autocommit,
-				       OPTION_NOT_AUTOCOMMIT,
-				       1);
+sys_var_thd_bit sys_autocommit("autocommit", 0,
+                               set_option_autocommit,
+                               OPTION_NOT_AUTOCOMMIT,
+                               1);
 static sys_var_thd_bit	sys_big_tables("big_tables", 0,
 				       set_option_bit,
 				       OPTION_BIG_TABLES);

--- 1.70/sql/set_var.h	2005-09-07 12:36:58 +02:00
+++ 1.71/sql/set_var.h	2005-09-13 17:16:05 +02:00
@@ -905,6 +905,7 @@
 extern sys_var_str sys_init_connect;
 extern sys_var_str sys_init_slave;
 extern sys_var_thd_time_zone sys_time_zone;
+extern sys_var_thd_bit sys_autocommit;
 CHARSET_INFO *get_old_charset_by_name(const char *old_name);
 gptr find_named(I_List<NAMED_LIST> *list, const char *name, uint length,
 		NAMED_LIST **found);

--- 1.82/mysql-test/r/sp-error.result	2005-09-07 21:03:50 +02:00
+++ 1.83/mysql-test/r/sp-error.result	2005-09-13 17:16:04 +02:00
@@ -786,3 +786,50 @@
 ERROR 0A000: HANDLER is not allowed in stored procedures
 SELECT bug12995()|
 ERROR 42000: FUNCTION test.bug12995 does not exist
+drop procedure if exists bug12712;
+drop function if exists bug12712;
+create procedure bug12712()
+set session autocommit = 0;
+select @@autocommit;
+@@autocommit
+1
+set @au = @@autocommit;
+call bug12712();
+select @@autocommit;
+@@autocommit
+0
+set session autocommit = @au;
+create function bug12712()
+returns int
+begin
+call bug12712();
+return 0;
+end|
+set @x = bug12712()|
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger
+drop procedure bug12712|
+drop function bug12712|
+create function bug12712()
+returns int
+begin
+set session autocommit = 0;
+return 0;
+end|
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger
+create function bug12712()
+returns int
+begin
+set @@autocommit = 0;
+return 0;
+end|
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger
+create function bug12712()
+returns int
+begin
+set local autocommit = 0;
+return 0;
+end|
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger
+create trigger bug12712
+before insert on t1 for each row set session autocommit = 0;
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger

--- 1.86/mysql-test/t/sp-error.test	2005-09-07 21:03:50 +02:00
+++ 1.87/mysql-test/t/sp-error.test	2005-09-13 17:16:05 +02:00
@@ -1130,6 +1130,64 @@
 SELECT bug12995()|
 delimiter ;|
 
+
+#
+# BUG#12712: SET AUTOCOMMIT should fail within SP/functions/triggers
+#
+--disable_warnings
+drop procedure if exists bug12712;
+drop function if exists bug12712;
+--enable_warnings
+# Can...
+create procedure bug12712()
+  set session autocommit = 0;
+
+select @@autocommit;
+set @au = @@autocommit;
+call bug12712();
+select @@autocommit;
+set session autocommit = @au;
+
+delimiter |;
+create function bug12712()
+  returns int
+begin
+  call bug12712();
+  return 0;
+end|
+
+# Can't...
+--error ER_SP_CANT_SET_AUTOCOMMIT
+set @x = bug12712()|
+drop procedure bug12712|
+drop function bug12712|
+--error ER_SP_CANT_SET_AUTOCOMMIT
+create function bug12712()
+    returns int
+begin
+  set session autocommit = 0;
+  return 0;
+end|
+--error ER_SP_CANT_SET_AUTOCOMMIT
+create function bug12712()
+    returns int
+begin
+  set @@autocommit = 0;
+  return 0;
+end|
+--error ER_SP_CANT_SET_AUTOCOMMIT
+create function bug12712()
+    returns int
+begin
+  set local autocommit = 0;
+  return 0;
+end|
+delimiter ;|
+--error ER_SP_CANT_SET_AUTOCOMMIT
+create trigger bug12712
+  before insert on t1 for each row set session autocommit = 0;
+
+
 #
 # BUG#NNNN: New bug synopsis
 #

--- 1.68/sql/sp_head.h	2005-09-07 17:39:41 +02:00
+++ 1.69/sql/sp_head.h	2005-09-13 17:16:05 +02:00
@@ -114,7 +114,8 @@
     IN_HANDLER= 4,              // Is set if the parser is in a handler body
     MULTI_RESULTS= 8,           // Is set if a procedure with SELECT(s)
     CONTAINS_DYNAMIC_SQL= 16,   // Is set if a procedure with PREPARE/EXECUTE
-    IS_INVOKED= 32              // Is set if this sp_head is being used.
+    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'
   };
 
   int m_type;			// TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE
@@ -282,6 +283,8 @@
       my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "Dynamic SQL");
     else if (m_flags & MULTI_RESULTS)
       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));
     return test(m_flags & (CONTAINS_DYNAMIC_SQL|MULTI_RESULTS));
   }
 private: