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

[18 Apr 2006 13:20] Björn Lindqvist
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.
[18 Apr 2006 13:53] MySQL Verification Team
Thank you for the bug report feature request.
[13 Dec 2006 15:44] David Newcomb
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?
[21 Dec 2006 11:08] David Newcomb
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.
[30 Mar 2007 7:11] Steve Bearman
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';
[5 Apr 2008 17:01] Dan Hagon
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 ;
[4 Oct 2008 20:04] Konstantin Osipov
This is a duplicate of Bug#15287