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