bk commit into 5.0 tree (davi:1.2516) BUG#21975

[Davi Arnaut]

Below is the list of changes that have just been committed into a local
5.0 repository of davi. When davi 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, 2007-08-20 16:58:03-03:00, davi@stripped +6 -0
  Bug#21975 Grant and revoke statements are non-transactional
  Bug#17244 GRANT gives strange error message when used in a stored function
  
  All statements which commit (implicit or explicitly) or rollbacks a transaction are
prohibited inside of stored functions or triggers.
  
  Patch contributed by Vladimir Shebordaev

  mysql-test/r/grant-binlog.result@stripped, 2007-08-20 16:58:00-03:00, davi@stripped +52
-0
    Add test result for Bug#21975 transaction

  mysql-test/r/grant-binlog.result@stripped, 2007-08-20 16:58:00-03:00, davi@stripped +0 -0

  mysql-test/r/sp-error.result@stripped, 2007-08-20 16:57:59-03:00, davi@stripped +12 -0
    Add test result for Bug#21975

  mysql-test/t/grant-binlog.test@stripped, 2007-08-20 16:58:00-03:00, davi@stripped +39 -0
    Add test case for Bug#21975 transaction

  mysql-test/t/grant-binlog.test@stripped, 2007-08-20 16:58:00-03:00, davi@stripped +0 -0

  mysql-test/t/sp-error.test@stripped, 2007-08-20 16:57:59-03:00, davi@stripped +15 -0
    Add test case for Bug#21975

  sql/sp_head.cc@stripped, 2007-08-20 16:57:59-03:00, davi@stripped +3 -0
    Set that a procedure with GRANT/REVOKE command has a (implicit or explicit) commit.

  sql/sql_parse.cc@stripped, 2007-08-20 16:58:00-03:00, davi@stripped +4 -0
    End active transaction, effectively prohibiting commit in stored functions or
triggers.

diff -Nrup a/mysql-test/r/grant-binlog.result b/mysql-test/r/grant-binlog.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/grant-binlog.result	2007-08-20 16:58:00 -03:00
@@ -0,0 +1,52 @@
+drop database if exists d1;
+Warnings:
+Note	1008	Can't drop database 'd1'; database doesn't exist
+create database d1;
+use d1;
+create table t (s1 int) engine=innodb;
+set @@autocommit=0;
+start transaction;
+insert into t values (1);
+grant select on t to x@y;
+rollback;
+show grants for x@y;
+Grants for x@y
+GRANT USAGE ON *.* TO 'x'@'y'
+GRANT SELECT ON `d1`.`t` TO 'x'@'y'
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	4	Format_desc	1	98	Server ver: VERSION, Binlog ver: 4
+master-bin.000001	98	Query	1	185	drop database if exists d1
+master-bin.000001	185	Query	1	264	create database d1
+master-bin.000001	264	Query	1	362	use `d1`; create table t (s1 int) engine=innodb
+master-bin.000001	362	Query	1	428	use `d1`; BEGIN
+master-bin.000001	428	Query	1	85	use `d1`; insert into t values (1)
+master-bin.000001	513	Xid	1	540	COMMIT /* xid=11 */
+master-bin.000001	540	Query	1	625	use `d1`; grant select on t to x@y
+start transaction;
+insert into t values (2);
+revoke select on t from x@y;
+commit;
+select * from t;
+s1
+1
+2
+show grants for x@y;
+Grants for x@y
+GRANT USAGE ON *.* TO 'x'@'y'
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	4	Format_desc	1	98	Server ver: VERSION, Binlog ver: 4
+master-bin.000001	98	Query	1	185	drop database if exists d1
+master-bin.000001	185	Query	1	264	create database d1
+master-bin.000001	264	Query	1	362	use `d1`; create table t (s1 int) engine=innodb
+master-bin.000001	362	Query	1	428	use `d1`; BEGIN
+master-bin.000001	428	Query	1	85	use `d1`; insert into t values (1)
+master-bin.000001	513	Xid	1	540	COMMIT /* xid=11 */
+master-bin.000001	540	Query	1	625	use `d1`; grant select on t to x@y
+master-bin.000001	625	Query	1	691	use `d1`; BEGIN
+master-bin.000001	691	Query	1	85	use `d1`; insert into t values (2)
+master-bin.000001	776	Xid	1	803	COMMIT /* xid=17 */
+master-bin.000001	803	Query	1	891	use `d1`; revoke select on t from x@y
+drop user x@y;
+drop database d1;
diff -Nrup a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
--- a/mysql-test/r/sp-error.result	2007-06-22 06:55:46 -03:00
+++ b/mysql-test/r/sp-error.result	2007-08-20 16:57:59 -03:00
@@ -969,6 +969,18 @@ CREATE TRIGGER tr1 BEFORE INSERT ON t1 F
 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END
|
 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to
'mysqltest_1'; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return
1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from
'mysqltest_1'; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1';
return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on
*.* from 'mysqltest_1'; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from
'mysqltest_1'; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |
diff -Nrup a/mysql-test/t/grant-binlog.test b/mysql-test/t/grant-binlog.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/grant-binlog.test	2007-08-20 16:58:00 -03:00
@@ -0,0 +1,39 @@
+-- source include/have_innodb.inc
+-- source include/not_embedded.inc
+
+let $VERSION=`select version()`;
+
+# Bug #21975: grant/revoke statements in transaction
+# used to disappear from binlog upon rallback.
+# Now GRANT/REVOKE do implicitly commit
+# transaction
+
+--disable-warnings
+drop database if exists d1;
+create database d1;
+use d1;
+create table t (s1 int) engine=innodb;
+set @@autocommit=0;
+start transaction;
+insert into t values (1);
+grant select on t to x@y;
+#
+# There is no active transaction here
+#
+rollback;
+show grants for x@y;
+--replace_result $VERSION VERSION
+show binlog events;
+start transaction;
+insert into t values (2);
+revoke select on t from x@y;
+#
+# There is no active transaction here
+#
+commit;
+select * from t;
+show grants for x@y;
+--replace_result $VERSION VERSION
+show binlog events;
+drop user x@y;
+drop database d1;
diff -Nrup a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
--- a/mysql-test/t/sp-error.test	2007-06-22 06:55:46 -03:00
+++ b/mysql-test/t/sp-error.test	2007-08-20 16:57:59 -03:00
@@ -1383,6 +1383,21 @@ CREATE TRIGGER tr1 BEFORE INSERT ON t1 F
 CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END
|
 
 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to
'mysqltest_1'; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return
1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from
'mysqltest_1'; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1';
return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on
*.* from 'mysqltest_1'; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from
'mysqltest_1'; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
 CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |
diff -Nrup a/sql/sp_head.cc b/sql/sp_head.cc
--- a/sql/sp_head.cc	2007-07-31 09:23:23 -03:00
+++ b/sql/sp_head.cc	2007-08-20 16:57:59 -03:00
@@ -242,11 +242,14 @@ sp_get_flags_for_command(LEX *lex)
   case SQLCOM_CREATE_TRIGGER:
   case SQLCOM_CREATE_USER:
   case SQLCOM_ALTER_TABLE:
+  case SQLCOM_GRANT:
+  case SQLCOM_REVOKE:
   case SQLCOM_BEGIN:
   case SQLCOM_RENAME_TABLE:
   case SQLCOM_RENAME_USER:
   case SQLCOM_DROP_INDEX:
   case SQLCOM_DROP_DB:
+  case SQLCOM_REVOKE_ALL:
   case SQLCOM_DROP_USER:
   case SQLCOM_DROP_VIEW:
   case SQLCOM_DROP_TRIGGER:
diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc
--- a/sql/sql_parse.cc	2007-08-02 06:51:00 -03:00
+++ b/sql/sql_parse.cc	2007-08-20 16:58:00 -03:00
@@ -4061,6 +4061,8 @@ end_with_restore_list:
     if (check_access(thd, UPDATE_ACL, "mysql", 0, 1, 1, 0) &&
         check_global_access(thd,CREATE_USER_ACL))
       break;
+    if (end_active_trans(thd))
+      goto error;
     /* Conditionally writes to binlog */
     if (!(res = mysql_revoke_all(thd, lex->users_list)))
       send_ok(thd);
@@ -4077,6 +4079,8 @@ end_with_restore_list:
                      select_lex->db ? is_schema_db(select_lex->db) : 0))
       goto error;
 
+    if (end_active_trans(thd))
+      goto error;
     if (thd->security_ctx->user)              // If not replication
     {
       LEX_USER *user, *tmp_user;