Bug #30904 SET PASSWORD statement is non-transactional
Submitted: 7 Sep 2007 15:50 Modified: 8 Nov 2007 23:53
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:6.0.3-alpha-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Davi Arnaut CPU Architecture:Any

[7 Sep 2007 15:50] Peter Gulutzan
Description:
I can put SET PASSWORD statements inside transactions, but their nature
is non-transactional.

The explanation "why is this sort of thing a problem" is in
Bug#21975 Grant and revoke statements are non-transactional.

Bug#21975 is now fixed. But, as I noted in the Bug#21975
comment on 2006-09-01, SET PASSWORD is also non-transactional.

How to repeat:
This demonstrates that the password changes although
@@autocommit=0 and ROLLBACK wiped out on an earlier INSERT.

mysql> select hex(Password) from mysql.user where User='root' and Host='localhost';
+---------------+
| hex(Password) |
+---------------+
|               |
+---------------+
1 row in set (0.00 sec)

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

mysql> create table th (s1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

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

mysql> set password = password('x');
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from th;
Empty set (0.00 sec)

mysql> select hex(Password) from mysql.user where User='root' and Host='localhost';
+------------------------------------------------------------------------------------+
| hex(Password)                                                                      |
+------------------------------------------------------------------------------------+
| 2A42363930323744343446364535454443303746314145414431343737393637423136463238323237 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[7 Sep 2007 19:01] Hartmut Holzgraefe
This is expected behavior due to the fact that
the MySQL system tables are MyISAM tables and
so non-transactional
[7 Sep 2007 20:03] Peter Gulutzan
The explanation for this bug's existence is in Bug#21975.
I am sorry for not making it clear that the bug needs to be looked at.

I have assigned it to Konstantin, who of course may declare it
"not a bug" if he agrees with the earlier assessment.
[26 Oct 2007 2:47] Marc Alff
Related Bug#21975 (Grant and revoke statements are non-transactional)
was fixed in 5.1.23, this bug should be fixed in 5.1 as well.
[31 Oct 2007 2:59] 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/36722

ChangeSet@1.2598, 2007-10-30 20:51:04-02:00, davi@endora.local +5 -0
  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.
[7 Nov 2007 21:59] Bugs System
Pushed into 6.0.4-alpha
[7 Nov 2007 22:01] Bugs System
Pushed into 5.1.23-rc
[8 Nov 2007 23:53] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

SET PASSWORD statements now cause an implicit commit, and thus are
prohibited within stored functions and triggers.