MySQL Bugs: #21975: Grant and revoke statements are non-transactional

Bug #21975	Grant and revoke statements are non-transactional
Submitted:	1 Sep 2006 18:59	Modified:	9 Oct 2007 17:58
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0/5.1BK	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
I can put GRANT and REVOKE statements inside transactions, but their nature is non-transactional. This violates atomicity (database changes within transactions are not all committed at the same point), and violates isolation (a different connection can see grant/revoke changes before commit). And it has some
particular bad effects for ROLLBACK:

After ROLLBACK, I see that changes to InnoDB tables have been rolled back, but changes due to GRANT/REVOKE -- although they were made after the InnoDB table changes -- are not rolled back. And I don't see the warning
"Warning: Some non-transactional changed tables couldn't be rolled back"
(ER_WARNING_NOT_COMPLETE_ROLLBACK) although a non-transactional table
(mysql.tables_priv) was changed.

The manual says that "If a transaction that is rolled back includes
modifications to non-transactional tables, the entire transaction is
logged with a ROLLBACK statement at the end to ensure that the modifications
to those tables are replicated." But that does not happen, GRANT and REVOKE
statements simply disappear from the binlog.

How to repeat:
Start mysqld with --log-bin so that logging is enabled.

mysql> drop database d1;
Query OK, 1 row affected (0.01 sec)

mysql> create database d1;
Query OK, 1 row affected (0.00 sec)

mysql> use d1;
Database changed
mysql> create table t (s1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.01 sec)

mysql> grant select on t to x@y;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for x@y;
+-------------------------------------+
| Grants for x@y                      |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'x'@'y'       |
| GRANT SELECT ON `d1`.`t` TO 'x'@'y' |
+-------------------------------------+
2 rows in set (0.00 sec)

mysql> show binlog events;
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                                             |
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------+
| binlog2.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.1.12-beta-debug-log, Binlog ver: 4 |
| binlog2.000001 | 102 | Query       |         1 |         179 | drop database d1                                 |
| binlog2.000001 | 179 | Query       |         1 |         258 | create database d1                               |
| binlog2.000001 | 258 | Query       |         1 |         356 | use `d1`; create table t (s1 int) engine=innodb  |
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------+
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> revoke select on t from x@y;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| s1   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

mysql> show grants for x@y;
+-------------------------------+
| Grants for x@y                |
+-------------------------------+
| GRANT USAGE ON *.* TO 'x'@'y' |
+-------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events;
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                                             |
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------+
| binlog2.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.1.12-beta-debug-log, Binlog ver: 4 |
| binlog2.000001 | 102 | Query       |         1 |         179 | drop database d1                                 |
| binlog2.000001 | 179 | Query       |         1 |         258 | create database d1                               |
| binlog2.000001 | 258 | Query       |         1 |         356 | use `d1`; create table t (s1 int) engine=innodb  |
| binlog2.000001 | 356 | Query       |         1 |         422 | use `d1`; BEGIN                                  |
| binlog2.000001 | 422 | Query       |         1 |          85 | use `d1`; insert into t values (2)               |
| binlog2.000001 | 507 | Query       |         1 |         173 | use `d1`; revoke select on t from x@y            |
| binlog2.000001 | 595 | Xid         |         1 |         622 | COMMIT /* xid=53 */                              |
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------+
8 rows in set (0.01 sec)

Thank you for the bug report.

A fix proposal is sent to Internals list. See <http://lists.mysql.com/internals/34466>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32772

ChangeSet@1.2516, 2007-08-20 16:58:03-03:00, davi@moksha.local +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

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33390

ChangeSet@1.2580, 2007-08-29 16:59:38-03:00, davi@moksha.local +6 -0
  Bug#21975 Grant and revoke statements are non-transactional
  Bug#21422 GRANT/REVOKE possible inside stored function, probably in a trigger
  Bug#17244 GRANT gives strange error message when used in a stored function
  
  GRANT/REVOKE statements are non-transactional (no explicit transaction
  boundaries) in nature and hence are forbidden inside stored functions and
  triggers, but they weren't being effectively forbidden. Furthermore, the
  absence of implict commits makes changes made by GRANT/REVOKE statements to
  not be rolled back.
  
  The implemented fix is to issue a implicit commit with every GRANT/REVOKE
  statement, effectively prohibiting these statements in stored functions
  and triggers. The implicit commit also fixes the replication bug, and looks
  like being in concert with the behavior of DDL and administrative statements.
  
  Since this is a incompatible change, the following sentence should be
  added to the Manual in the very end of the 3rd paragraph, subclause
  13.4.3 "Statements That Cause an Implicit Commit": "Beginning with
  MySQL 5.0.??, the GRANT and REVOKE statements cause an implicit commit."
  
  Patch contributed by Vladimir Shebordaev

On 2006-09-01, I commented:
"- SET PASSWORD is also non-transactional"

Is anything being done about SET PASSWORD?
If not, should it be reported as a separate issue?

Davi, Peter, please coordinate who reports it as a separate issue.

Pushed into 5.1.23-beta

I reported a new bug:
Bug #30904  	SET PASSWORD statement is non-transactional

Noted in 5.1.23 changelog.

Incompatible change: GRANT and REVOKE statements now cause an
implicit commit, and thus are prohibited within stored functions and
triggers.