Bug #15287 IF EXIST / IF NOT EXIST
Submitted: 28 Nov 2005 14:19 Modified: 28 Nov 2005 17:28
Reporter: Federico Razzoli (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[28 Nov 2005 14:19] Federico Razzoli
Description:
I think that IF EXIST and IF NOT EXIST clauses should be added to the following SQL statements:

CREATE USER
CREATE VIEW
CREATE PROCEDURE
CREATE FUNCTION
CREATE TRIGGER

DROP USER
DROP VIEW
DROP TRIGGER

DROP PROCEDURE and DROP FUNCTION already have a IF EXIST clause.
CREATE VIEW has a OR CREATE CLAUSE, but it doesn't replace IF NOT EXIST.

How to repeat:
.

Suggested fix:
.
[28 Nov 2005 17:28] Valeriy Kravchuk
Thank you for a feature request. It is reasonable to implement a complete set of DDL statements with all these IF EXIST and IF NOT EXIST, as soon as we added that non-standard clauses for some of them. 

But I can't tell you exectly when it will be done...
[21 Apr 2006 3:01] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=19241 was marked as
duplicate of this one.
[4 Oct 2008 20:00] Konstantin Osipov
Bug#19166 DROP USER IF EXISTS was marked a duplicate of this bug.
[6 Oct 2008 10:34] Valeriy Kravchuk
Bug #23543 was marked as a duplicate of this one.
[21 Oct 2008 14:49] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=40211 was marked as duplicate of this one.
[21 Nov 2008 13:18] Simon Mudd
Is there any progress on this or any way from SQL to work around this?

The ticket has been open for 3 years which is a long time.

I want to clean up some grants on a server and would do 

mysql <<-EOF
DROP USER XXXX@YYYYY;
GRANT .....;
GRANT ....;
EOF

This of course will fail as the first command fails.

Using mysql -f allows me to solve this problem but is untidy as it may give error messages for non-existent users.

However I can't do 

mysql <<-EOF
-- only do this if the user exists
IF EXISTS ( SELECT 1 FROM mysql.user where user='XXXXX' AND host='YYYY' )
DROP USER XXXX@YYYYY;

GRANT .....;
GRANT ....;
EOF

as MySQL "stored procedure" programming is not possible directly from the command line[1], something which is common in other DBMS environments. This extra functionality would enable one to "program around" current limitations of for example the DROP USER command and other similar issues and provide a lot more flexibility to the MySQL user. 

[1] without having been explicitly configured as a stored procedure first. Also DROP PROCEDURE doesn't replicate so even if small tasks like this were put into a stored procedure this would break seriously in a replicated environment.
[21 Jan 2009 12:46] Simon Mudd
See also BUG#13684 which has recently been updated and seems pretty similar to this problem.

Is there likely to be any work done on this issue? The 2 problems of:

1. IF EXIST missing, and
2. the command not being replicated is a major nuisance for us.

In my specfic case I'm thinking about the DROP USER IF EXISTS case.

Correcting that would be good and the work on BUG#13684 seems to indicate that this type of issue is getting addressed.
[1 Sep 2009 3:11] Roel Van de Paar
Bug #19166 contains workaround info
[1 Oct 2009 9:17] The Assimilator
This simple feature request was submitted nearly 4 years ago and still hasn't been implemented.

Is it going to be implemented any time before the 22nd century dawns?
[12 Jan 2010 23:01] Notareal Smith
When this request be completed? It is now over 5 years old and there has been no response from the MySQL developers.
[26 Mar 2010 23:03] Cowtown Coder
Indeed. Would be nice to be able to vote -- this is a no-brainer functionality that other DBs have. And yes, it should throw an exception if existing index differs from requested one.
[26 Mar 2010 23:06] Cowtown Coder
By the way, description seems to be missing specific thing I am looking for, "create index if not exists" (like what h2 and others support).
Since another bug ( [19354] ) was closed because this bug supposedly covers it, it probably should be added.
[27 Mar 2010 9:31] Simon Mudd
Cowtown Coder: all the more reason to get MySQL to support "anonymous code blocks" so that you can code around this easily yourself directly from the command line. Then all this hacky "IF EXISTS" special syntax could be removed.

The syntax would be generic enough to allow us to do a lot more things besides solve the "IF [NOT] EXISTS" problem and also to simplify the MySQL syntax.
[21 May 2010 15:32] Valeriy Kravchuk
Bug #53881 was marked as a duplicate of this one.
[8 Jul 2011 11:01] Bastian Knerr
Hello MySQL,
it's still out there. I'd require both DROP and CREATE USER IF [NOT] EXISTS for setting up a configuration script that can be rerun.
Please get your things together and provide this feature!
Cheers
Bastian
[29 Feb 2012 12:15] Olag Ulga
We also need this feature!
[28 Nov 2012 19:32] Sam Caldwell
I agree that this feature is sorely needed.  It would simplify the process of deploying applications.
[9 Jul 2014 15:21] William Kimball
Well, NINE YEARS later and still no progress on this?  My use-case is the same as reported by others:  I need to reliably deploy applications in repeated fashion (testing environment) on a variable set of hosts that are used by other teams; so, it's impossible for my deployment code to know in advance whether users already exist, or not at the time my DDL is executed.  The workaround listed (GRANT USAGE ...) is reported as no longer working.
[1 Jul 2015 20:13] David Squier
Would love to see this also. The folks at Postgresql appear to have figured it out already:

http://www.postgresql.org/docs/current/static/sql-dropuser.html
[1 Jul 2015 21:33] Federico Razzoli
Please, don't implement this with a weird syntax just to add incompatibilities with MariaDB.