MySQL Bugs: #19166: DROP USER IF EXISTS

Bug #19166	DROP USER IF EXISTS
Submitted:	18 Apr 2006 13:20	Modified:	4 Oct 2008 20:04
Reporter:	Björn Lindqvist	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
you have DROP TABLE IF EXISTS and DROP DATABASE IF EXISTS, but no DROP USER IF EXISTS. What's up with that?

How to repeat:
it is a feature request.

Thank you for the bug report feature request.

You even have drop database if exists.

I am really missing this. I am trying to write a setup script which can be re-run. There is no way to test (without the uses of externally intellegent programs) if a user exists. If you try to create a user that exists it fails. If you try to drop a user that does not exists it fails. What am I to do?

If you are running an sql batch file you can use -f to force it through.
But then you can't tell if there has been a real error.
Alternatively, you can separate out all the user creation stuff into different files, and then run them separately checking return codes etc. Loads of extra work which everyone will have to do.

A good workaround is to grant a harmless privilege to the user before dropping it.  This will create the user if it doesn't exist, so that it can be dropped safely, like so:

   GRANT USAGE ON *.* TO 'username'@'localhost';
   DROP USER 'username'@'localhost';

The following script works for 5.1.23-rc-cummunity.

/*The following resolves the error documented at http://bugs.mysql.com/bug.php?id=34794 */
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';

USE `test` ;

CREATE USER 'username' IDENTIFIED BY 'password' ;

DROP PROCEDURE IF EXISTS `test`.`drop_user_if_exists` ;
DELIMITER $$
CREATE PROCEDURE `test`.`drop_user_if_exists`()
BEGIN
  DECLARE foo BIGINT DEFAULT 0 ;
  SELECT COUNT(*)
  INTO foo
    FROM `mysql`.`user`
      WHERE `User` = 'username' ;
  
  IF foo > 0 THEN 
         DROP USER 'username' ;
  END IF;
END ;$$
DELIMITER ;

CALL `test`.`drop_user_if_exists`() ;

DROP PROCEDURE IF EXISTS `test`.`drop_users_if_exists` ;

/*The following resolves the error documented at http://bugs.mysql.com/bug.php?id=34794 */
SET SQL_MODE=@OLD_SQL_MODE ;

This is a duplicate of Bug#15287