MySQL Bugs: #30904: SET PASSWORD statement is non-transactional

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

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)

This is expected behavior due to the fact that
the MySQL system tables are MyISAM tables and
so non-transactional

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.

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.

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.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

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.