Bug#30904 SET PASSWORD statement is non-transactional

The SET PASSWORD statement is non-transactional (no explicit transaction
boundaries) in nature and hence is forbidden inside stored functions and
triggers, but it weren't being effectively forbidden.

The implemented fix is to issue a implicit commit with every SET PASSWORD
statement, effectively prohibiting these statements in stored functions
and triggers. 


mysql-test/r/sp-error.result:
  Add test case result for Bug#30904
mysql-test/t/sp-error.test:
  Add test case for Bug#30904
sql/sql_lex.h:
  Add variable to set that a statement with SET PASSWORD causes a implicit
  commit.
sql/sql_parse.cc:
  End active transaction in SET PASSWORD.
sql/sql_yacc.yy:
  Set the correct flag on SET PASSWORD if inside a SP, thus effectively
  prohibiting SET PASSWORD statements in stored functions and triggers.

5 files changed, 42 insertions, 1 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 1b14d75cd9c..300fa42f3ad 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1523,3 +1523,13 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 SELECT ..inexistent();
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.inexistent()' at line 1
 USE test;
+create function f1() returns int
+begin
+set @test = 1, password = password('foo');
+return 1;
+end|
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger
+create trigger t1
+before insert on t2 for each row set password = password('foo');
+delimiter ;|
+ERROR HY000: Not allowed to set autocommit from a stored function or trigger
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index a956a246770..9f20d02480c 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -2223,6 +2223,25 @@ SELECT ..inexistent();
 USE test;
 
 #
+# Bug#30904 SET PASSWORD statement is non-transactional
+#
+
+delimiter |;
+
+--error ER_SP_CANT_SET_AUTOCOMMIT
+create function f1() returns int
+begin
+  set @test = 1, password = password('foo');
+  return 1;
+end|
+
+--error ER_SP_CANT_SET_AUTOCOMMIT
+create trigger t1
+  before insert on t2 for each row set password = password('foo');
+
+delimiter ;|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a26c4fd1ca0..507d64daf89 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1616,7 +1616,7 @@ typedef struct st_lex : public Query_tables_list
   uint8 create_view_algorithm;
   uint8 create_view_check;
   bool drop_if_exists, drop_temporary, local_file, one_shot_set;
-
+  bool autocommit;
   bool verbose, no_write_to_binlog;
 
   bool tx_chain, tx_release;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index ac042960388..85457fea41b 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3053,6 +3053,10 @@ end_with_restore_list:
   case SQLCOM_SET_OPTION:
   {
     List<set_var_base> *lex_var_list= &lex->var_list;
+
+    if (lex->autocommit && end_active_trans(thd))
+      goto error;
+
     if ((check_table_access(thd, SELECT_ACL, all_tables, 0) ||
 	 open_and_lock_tables(thd, all_tables)))
       goto error;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index b893014aacb..69cd7060778 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -10516,6 +10516,7 @@ set:
             lex->option_type=OPT_SESSION;
             lex->var_list.empty();
             lex->one_shot_set= 0;
+            lex->autocommit= 0;
           }
           option_value_list
           {}
@@ -10558,6 +10559,7 @@ option_type_value:
               lex->option_type=OPT_SESSION;
               lex->var_list.empty();
               lex->one_shot_set= 0;
+              lex->autocommit= 0;
               lex->sphead->m_tmp_query= lip->get_tok_start();
             }
           }
@@ -10799,10 +10801,16 @@ option_value:
             user->host=null_lex_str;
             user->user.str=thd->security_ctx->priv_user;
             thd->lex->var_list.push_back(new set_var_password(user, $3));
+            thd->lex->autocommit= TRUE;
+            if (lex->sphead)
+              lex->sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT;
           }
         | PASSWORD FOR_SYM user equal text_or_password
           {
             Lex->var_list.push_back(new set_var_password($3,$5));
+            Lex->autocommit= TRUE;
+            if (Lex->sphead)
+              Lex->sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT;
           }
         ;