MySQL Bugs: #15287: IF EXIST / IF NOT EXIST

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

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:
.

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...

Bug: http://bugs.mysql.com/bug.php?id=19241 was marked as
duplicate of this one.

Bug#19166 DROP USER IF EXISTS was marked a duplicate of this bug.

Bug #23543 was marked as a duplicate of this one.

Bug: http://bugs.mysql.com/bug.php?id=40211 was marked as duplicate of this one.

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.

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.

Bug #19166 contains workaround info

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?

When this request be completed? It is now over 5 years old and there has been no response from the MySQL developers.

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.

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.

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.

Bug #53881 was marked as a duplicate of this one.

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

We also need this feature!

I agree that this feature is sorely needed.  It would simplify the process of deploying applications.

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.

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

Please, don't implement this with a weird syntax just to add incompatibilities with MariaDB.

Any chance of this being added soon? It's been 14+ years. Maybe sometime this year?

For the record:

DROP VIEW IF EXISTS - implemented in MySQL 5.0
DROP TRIGGER IF EXISTS - implemented in MySQL 5.0
DROP PROCEDURE IF EXISTS - implemented in MySQL 5.0
DROP FUNCTION IF EXISTS - implemented in MySQL 5.0

CREATE USER IF NOT EXISTS - added in MySQL 5.7
DROP USER IF EXISTS - added in MySQL 5.7

CREATE PROCEDURE IF NOT EXISTS - to be done
CREATE FUNCTION IF NOT EXISTS - to be done
CREATE TRIGGER IF NOT EXISTS - to be done

CREATE PROCEDURE IF NOT EXISTS, CREATE FUNCTION IF NOT EXISTS, and
CREATE TRIGGER IF NOT EXISTS are implemented in MySQL 8.0.29.

The others have already been taken care of.

Closed.