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).


mysql-test/r/sp-error.result:
  New test case for BUG#12712.
mysql-test/t/sp-error.test:
  New test case for BUG#12712.
sql/set_var.cc:
  Made sys_autocommit external, to allow testing in sql_yacc.yy.
sql/set_var.h:
  Made sys_autocommit external, to allow testing in sql_yacc.yy.
sql/share/errmsg.txt:
  New error message for disallowing the setting of autocommit in stored functions and triggers.
sql/sp_head.h:
  New flag: has 'set autocommit', and testing for this in is_not_allowed_in_function().
sql/sql_yacc.yy:
  Disallow setting AUTOCOMMIT in stored function and triggers.

5 files changed, 20 insertions, 5 deletions

diff --git a/sql/set_var.cc b/sql/set_var.cc
index 774062dedf2..ff997158941 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -447,10 +447,10 @@ sys_var_thd_date_time_format sys_datetime_format("datetime_format",
 
 /* 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);
diff --git a/sql/set_var.h b/sql/set_var.h
index 40ff4c8583f..854409c159e 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -905,6 +905,7 @@ extern sys_var_const_str sys_charset_system;
 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);
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 5f1f7035af9..57e70c6acb6 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5403,3 +5403,5 @@ ER_VIEW_PREVENT_UPDATE
         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"
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 271119ff2fb..7c9cf364b17 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -114,7 +114,8 @@ public:
     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 @@ public:
       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:
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 104f9ca8445..a8810f5de94 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -8004,6 +8004,15 @@ internal_variable_name:
             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
 	  {