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

[1 Sep 2006 18:59] Peter Gulutzan
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)
[1 Sep 2006 20:24] Miguel Solorzano
Thank you for the bug report.
[1 Apr 2007 13:22] Vladimir Shebordaev
A fix proposal is sent to Internals list. See <http://lists.mysql.com/internals/34466>
[20 Aug 2007 19:58] Bugs System
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
[29 Aug 2007 20:01] Bugs System
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
[5 Sep 2007 19:59] Peter Gulutzan
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?
[5 Sep 2007 20:54] Konstantin Osipov
Davi, Peter, please coordinate who reports it as a separate issue.
[7 Sep 2007 8:09] Bugs System
Pushed into 5.1.23-beta
[7 Sep 2007 15:51] Peter Gulutzan
I reported a new bug:
Bug #30904  	SET PASSWORD statement is non-transactional
[9 Oct 2007 17:58] Paul Dubois
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.