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.

1 files changed, 19 insertions, 0 deletions

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