| Bug #21975 | Grant and revoke statements are non-transactional | ||
|---|---|---|---|
| Submitted: | 1 Sep 2006 20:59 | Modified: | 9 Oct 2007 19:58 |
| Reporter: | Peter Gulutzan | ||
| Status: | Closed | ||
| Category: | Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.0/5.1BK | OS: | Linux (SUSE 10.0 / 64-bit) |
| Assigned to: | Davi Arnaut | Target Version: | |
[1 Sep 2006 22:24]
Miguel Solorzano
Thank you for the bug report.
[1 Apr 2007 15:22]
Vladimir Shebordaev
A fix proposal is sent to Internals list. See <http://lists.mysql.com/internals/34466>
[20 Aug 2007 21: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 22: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 21: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 22:54]
Konstantin Osipov
Davi, Peter, please coordinate who reports it as a separate issue.
[7 Sep 2007 10:09]
Bugs System
Pushed into 5.1.23-beta
[7 Sep 2007 17:51]
Peter Gulutzan
I reported a new bug: Bug #30904 SET PASSWORD statement is non-transactional
[9 Oct 2007 19: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.
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)