CREATE OR REPLACE, CREATE IF NOT EXISTS, DROP IF EXISTS

This task is to add support for OR REPLACE and IF EXISTS / IF NOT EXISTS to all CREATE and DROP variants for all objects (where it makes sense). For (but not limited to) functions, procedures, triggers, events, views, users, roles, and databases.

see also the original task description in the history

We have these object affected:

Object      CREATE OR REPLACE  CREATE IF NOT EXISTS  DROP IF EXISTS

--------    -----------------  --------------------  --------------

DATABASE      -                 +                     +

EVENT         -                 +                     +

FUNCTION      -                 -                     +

FUNCTION(UDF) -                 -                     -

INDEX         -                 +                     +

PROCEDURE     -                 -                     +

ROLE          -                 -                     -

SERVER        -                 -                     +

TABLE         +                 +                     +

TRIGGER       -                 -                     +

USER          -                 -                     -

VIEW          +                 -                     +

There are also engine-specific (NDB) statements
"CREATE LOGFILE GROUP" and "CREATE TABLESPACE".
But these statements will be done in a separate task if(when) needed.

General guidance "CREATE OR REPLACE" should work

Atomicity

"CREATE OR REPLACE OBJECT name ..." is a short for

DROP OBJECT IF EXISTS name;

CREATE OBJECT name ...;

where OBJECT is DATABASE, EVENT, FUNCTION, INDEX, PROCEDURE,
ROLE, SERVER, TABLE, TRIGGER, USER, VIEW.

However, unlike a DROP followed by a CREATE,
"CREATE OR REPLACE" should work atomically ("all or nothing"),
i.e. either both DROP and CREATE should complete, or nothing should happen.

It should never happen that DROP succeeds, but then CREATE fails
because of insufficient privileges or some other logical errors
(CREATE can only fail if some fatal errors happen).

Therefore, all privileges required to perform both DROP and CREATE
should be checked before the action. If there is a DROP privilege,
but there is not enough CREATE privilege, the command should do nothing
and return with an error, reporting insufficient privileges.

CREATE OR REPLACE and metadata locking

Both "DROP" and "CREATE" parts of "CREATE OR REPLACE"
should be done under a single metadata lock, to make sure
the concurrent clients do not interfere in between.

CREATE OR REPLACE and replication

For all objects, "CREATE OR REPLACE" should be put into binary log as a single statement.
It should not be replaced to "DROP" followed by "CREATE"

All statements "CREATE IF NOT EXISTS" and "DROP IF EXISTS" should
be binlogged, even if nothing happened in the master side.

Object specific comments for CREATE OR REPLACE

CREATE OR REPLACE DATABASE

Requires the DROP and the CREATE privileges for the database.

"CREATE OR REPLACE db1" should never change the current database,
including the case if "db1" was the current database before the operation.
I.e. the operation should neither "unuse" the current database, nor use a new one.

CREATE OR REPLACE EVENT

Requires the EVENT privilege.
If the old event exists and has the "DEFINER=another_user" part,
requires the SUPER privilege.
If the new event definition has the "DEFINER=another_user" part,
requires the SUPER privilege.

CREATE OR REPLACE FUNCTION

Requires the ROUTINE privilege.
If the old function exists and has the "DEFINER=another_user" part,
requires the SUPER privilege.
If the new function definition has the "DEFINER=another_user" part,
requires the SUPER privilege.

CREATE OR REPLACE PROCEDURE

See "CREATE OR REPLACE FUNCTION".

CREATE OR REPLACE TRIGGER

Requires the TRIGGER privilege.
If the old trigger exists and has the "DEFINER=another_user" part,
requires the SUPER privilege.
If the new trigger definition has the "DEFINER=another_user" part,
requires the SUPER privilege.

CREATE OR REPLACE INDEX

Requires the INDEX privilege.

CREATE OR REPLACE SERVER

Requires the SUPER privilege.

CREATE OR REPLACE USER

DROP requires the global CREATE USER privilege,
or the DELETE privilege for the mysql database.
CREATE requires global CREATE USER privilege,
or the INSERT privilege for the mysql database.

CREATE OR REPLACE ROLE

See "CREATE OR REPLACE USER".