MySQL Bugs: #12307: SP hangs server when it includes FLUSH PRIVILEGES;

Bug #12307	SP hangs server when it includes FLUSH PRIVILEGES;
Submitted:	2 Aug 2005 0:07	Modified:	10 Aug 2005 16:53
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.10/BK source	OS:	Windows (Win32/Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Stored procedures crash the server when they include a FLUSH PRIVILEGES statement. In the test case below, removing the FLUSH PRIVILEGES; line allows the procedure to complete successfully.

How to repeat:
CREATE USER 'fred'@'localhost' IDENTIFIED BY 'fred';

DROP DATABASE admin;

CREATE DATABASE admin;

USE admin;

CREATE TABLE update_password_users (username VARCHAR(81) PRIMARY KEY);

INSERT INTO admin.update_password_users VALUES ('mark@localhost');

DELIMITER //

DROP PROCEDURE update_user_password//

CREATE PROCEDURE update_user_password ( IN uname VARCHAR(16), IN hname VARCHAR(60), 
                                          IN newpass VARCHAR(16) )
SQL SECURITY INVOKER
BEGIN
  DECLARE res VARCHAR(40) DEFAULT "";
  DECLARE priv_user VARCHAR(81);
  DECLARE cur_user VARCHAR(81);
  DECLARE done INT DEFAULT 0;
  DECLARE c1 CURSOR FOR SELECT username FROM admin.update_password_users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  SET cur_user = (SELECT CURRENT_USER());
  OPEN c1;
  
  users: LOOP
    FETCH c1 INTO priv_user;
    
    IF done = 1 THEN
      LEAVE users;
    END IF;
    
    IF cur_user = priv_user THEN
      UPDATE mysql.user SET password = PASSWORD(newpass) 
      WHERE user = uname AND host = hname;
      SET res = "Password updated.";
      SET done = 1;
    END IF;
  END LOOP users;  
  CLOSE c1;
  
  IF res = "" THEN 
    SET res = "You are not authorised to do this.";
  END IF;
  
  SELECT cur_user as user, res as result;
END;
//

call update_user_password('fred','localhost','ass')//

INSERT INTO admin.update_password_users VALUES ('root@localhost')//

call update_user_password('fred','localhost','ass')//

Suggested fix:
Shrug. Handle the FLUSH PRIVILEGES properly, or disallow it on the CREATE PROCEDURE and document it as such.

Sorry, posted the one I tested *without* FLUSH PRIVILEGES, that version is: 

CREATE PROCEDURE update_user_password ( IN uname VARCHAR(16), IN hname VARCHAR(60), 
                                          IN newpass VARCHAR(16) )
SQL SECURITY INVOKER
BEGIN
  DECLARE res VARCHAR(40) DEFAULT "";
  DECLARE priv_user VARCHAR(81);
  DECLARE cur_user VARCHAR(81);
  DECLARE done INT DEFAULT 0;
  DECLARE c1 CURSOR FOR SELECT username FROM admin.update_password_users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  SET cur_user = (SELECT CURRENT_USER());
  OPEN c1;
  
  users: LOOP
    FETCH c1 INTO priv_user;
    
    IF done = 1 THEN
      LEAVE users;
    END IF;
    
    IF cur_user = priv_user THEN
      UPDATE mysql.user SET password = PASSWORD(newpass) 
      WHERE user = uname AND host = hname;
      FLUSH PRIVILEGES;
      SET res = "Password updated.";
      SET done = 1;
    END IF;
  END LOOP users;  
  CLOSE c1;
  
  IF res = "" THEN 
    SET res = "You are not authorised to do this.";
  END IF;
  
  SELECT cur_user as user, res as result;
END;
//

Thank you for the bug report.
The current behavior is the mysql client hangs and isn't possible to
connect anymore with the server, however the server not crash.
Verified on Linux/Windows.

Indeed, sorry - I submitted this at 1am and should have just gone to bed and reported it in the morning ;) I've changed the synopsis to reflect this.

Hi, Mark!

I can repeat this problem with MySQL 5.0.10. But it is no longer repeatable with latest snapshot of 5.0 tree. Investigation showed that it was fixed by the same patch as bug #11126.

So I marking this bug as fixed in 5.0.11.

Note that similar problems are possible if one calls FLUSH PRIVILEGES inside
of stored function or trigger. They will be fixed (by disallowing this statement
inside of stored functions and triggers) with the same patch as bug #12280.

Thank you for your interest in MySQL!

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.12 changelog.