Uploaded image for project: 'MariaDB Server'
  1. MDEV-5359

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

    Details

    • Type: Task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Fix Version/s: 10.1.4
    • Component/s: OTHER
    • Labels:

      Description

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

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                stephane@skysql.com VAROQUI Stephane
              • Votes:
                4 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 7 hours
                  7h