WL#9173: New data-dictionary: Improve crash-safety of non-table DDL.

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

With the advent of new data-dictionary it becomes possible to package 
metadata changes and writing binary log into single transaction.      
                                                                      
We aim to make DDL statements crash-safe by using    
this feature. However they don't cover DDL which does not involve     
storage engine.                                                       
                                                                     
The goal of this WL is to improve crash-safety of non-table DDLs:

  * CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION 
           <sp_name> ([func_parameter[,...]]) RETURNS type
           [characteristic ...] <routine_body>

  * ALTER FUNCTION <sp_name> [ characteristic ...]

  * DROP FUNCTION [IF_EXISTS] <sp_name>

  * CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE
           <sp_name>([proc_parameters[,...]])
           [characteristic ... ] <routine_body>

  * ALTER PROCEDURE <sp_name> [ characteristic ...]

  * DROP PROCEDURE [IF_EXISTS] <sp_name>

  * CREATE [DEFINER = { user | CURRENT_USER }] EVENT
           [IF NOT EXISTS] <event_name>
	   ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE]
	   [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment']
	   DO <event_body>;

  * ALTER [DEFINER = { user | CURRENT_USER }] EVENT
	   <event_name> [ON SCHEDULE schedule]
	   [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name]
	   [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment']
	   [DO event_body]

  * DROP EVENT [IF EXISTS] <event_name>

  * CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
           [DEFINER = { user | CURRENT_USER }]
           [SQL SECURITY { DEFINER | INVOKER }]
           VIEW <view_name> [(column_list)]
           AS select_statement
           [WITH [CASCADED | LOCAL] CHECK OPTION]


  * ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
          [DEFINER = { user | CURRENT_USER }]
	  [SQL SECURITY { DEFINER | INVOKER }]
	  VIEW <view_name> [(column_list)]
	  AS select_statement
	  [WITH [CASCADED | LOCAL] CHECK OPTION]

  * DROP VIEW [IF EXISTS] view_name [, view_name] ...
         [RESTRICT | CASCADE]

  * CREATE [AGGREGATE] FUNCTION <function_name> RETURNS
	   {STRING|INTEGER|REAL|DECIMAL} SONAME <shared_library_name>

  * DROP FUNCTION <function_name>
                                                                      
                                                                      
By employing the new data-dictionary support for transactional changes.

The current approach for CREATE/DROP trigger bundles the operation
on data-dictionary tables and binlog event write in the same
transaction. Hence CREATE/DROP TRIGGER statements are already atomic
and no code changes are done for it as part of this WL.

ACL DDL's are already made atomic.

Other non-table DDLs
  * INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

  * UNINSTALL PLUGIN plugin_name

  * INSTALL COMPONENT component_name [, component_name ] ...

  * UNINSTALL COMPONENT component_name [, component_name ] ...

  * CREATE SERVER server_name FOREIGN DATA WRAPPER wrapper_name
           OPTIONS (option[, option] ...)

  * ALTER SERVER  server_name OPTIONS (option [, option] ...)

  * DROP SERVER [ IF EXISTS ] server_name are handled
F-1: DDL operation to CREATE stored function should be fully atomic.

       * Stored function should be either created and binlog event
         is written for it or fails and do not have any side effects
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, stored routines,
                       table definition cache should not
                       contain stale/incorrect data)

F-2: DDL operation to CREATE stored function should be crash safe.

	* In case of crash while creating a stored function there should
          be no discrepancy between data-dictionary tables
          and binary log content.

F-3: DDL operation to ALTER stored function should be fully atomic.

       * Stored function should be either altered and binlog event
         is written for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, stored routines cache
                       should not contain stale/incorrect data,
                       no binlog event is written)

F-4: DDL operation to ALTER stored function should be crash safe.

	* In case of crash while altering a stored function there should
          be no discrepancy between data-dictionary tables and binary
          log content.

F-5: DDL operation to DROP stored function should be fully atomic.

       * Stored function should be either dropped and binlog event
         is written for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, stored routines cache
                       should not contain stale/incorrect data)

F-6: DDL operation to DROP stored function should be crash safe.

	* In case of crash while dropping a stored function there should
          be no discrepancy between data-dictionary tables and binary
          log content.

F-7: DDL operation to CREATE stored procedure should be fully atomic.

       * Stored procedure should be either created and binlog event
         is written for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, stored routines cache
                       should not contain stale/incorrect data)

F-8: DDL operation to CREATE stored procedure should be crash safe.

	* In case of crash while creating a stored procedure there
          should be no discrepancy between data-dictionary tables
          and binary log content.

F-9: DDL operation to ALTER stored procedure should be fully atomic.

       * Stored procedure should be either altered and binlog event
         is written for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, stored routines cache
                       should not contain stale/incorrect data)

F-10: DDL operation to ALTER stored procedure should be crash safe.

	* In case of crash while altering a stored procedure there
	  should be no discrepancy between data-dictionary tables and
          binary log content.

F-11: DDL operation to DROP stored procedure should be fully atomic.

       * Stored procedure should be either dropped and binlog event
         is written for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, stored routines cache
                       should not contain stale/incorrect data)

F-12: DDL operation to DROP stored procedure should be crash safe.

	* In case of crash while dropping a stored procedure there
          should be no discrepancy between data-dictionary tables and
	  binary log content.

F-13: DDL operation to CREATE view should be fully atomic.

       * View should be either created and binlog event is written for
         it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, table definitions
                       cache should not contain stale/incorrect data)

F-14: DDL operation to CREATE view should be crash safe.

	* In case of crash while creating a view there should be no
	  discrepancy between data-dictionary tables and binary log
          content.

F-15: DDL operation to ALTER view should be fully atomic.

       * View should be either altered and binlog event is written for
         it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, table definitions
                       cache should not contain stale/incorrect data)

F-16: DDL operation to ALTER view should be crash safe.

	* In case of crash while altering a view there should be no
	  discrepancy between data-dictionary tables and binary log
          content.

F-17: DDL operation to DROP view should be fully atomic.

       * View should be either dropped and binlog event is written for
         it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written and
                       data-dictionary, table definitions
                       cache should not contain stale/incorrect data)
       
F-18: DDL operation to DROP view should be crash safe.

	* In case of crash while dropping a view there should be no
	  discrepancy between data-dictionary tables and binary log
          content.

F-19: DDL operation to CREATE event should be fully atomic.

       * Event should be either created and binlog event is written
         for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written,
                       data-dictionary cache and event queue should
                       not contain stale/incorrect data)

F-20: DDL operation to CREATE event should be crash safe.

        * In case of crash while creating a event there should be no
          discrepancy between data-dictionary tables and binary log
          content.

F-21: DDL operation to ALTER event should be fully atomic.

       * Event should be either altered and binlog event is written
         for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written,
                       data-dictionary cache and event queue
                       should not contain stale/inconsistent data)

F-22: DDL operation to ALTER event should be crash safe.

        * In case of crash while altering a event there should be no
	  discrepancy between data-dictionary tables and binary log
	  content.

F-23: DDL operation to DROP event should be fully atomic.

       * Event should be either dropped and binlog event is written
         for it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event is written,
                       data-dictionary cache and event queue
                       should not contain stale/inconsistent data)

F-24: DDL operation to DROP event should be crash safe.

       * In case of crash while dropping a event there should be no
	 discrepancy between data-dictionary tables and binary log
	 content.

F-25: DDL operation to CREATE UDF should be fully atomic.

       * UDF should be either created and binlog event is written for
         it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event and
                       no entry for the UDF in the UDF hash)

F-26: DDL operation to CREATE UDF should be crash safe.

       * In case of crash while creating a UDF there should be no
	 discrepancy between data-dictionary tables and binary log
	 content.

F-27: DDL operation to DROP UDF should be fully atomic.

       * UDF should be either dropped and binlog event is written for
         it or fails and do not have any side effects.
         (specifically no changes to the data-dictionary,
                       no binlog event and
                       no update to the UDF hash)

F-28: DDL operation to DROP UDF should be crash safe.

       * In case of crash while dropping a UDF there should be no
	 discrepancy between data-dictionary tables and binary log
	 content.
On a high level we can say to make non-table DDL operation atomic
we need to pack its updates to data-dictionary and writes to binary log
into single atomic transaction (i.e. it should either commit and have
its effect properly reflected in the Data-dictionary and binary log or
rollback and doesn't have any effect at all).

To implement this we need to ensure that,

1) There are no intermediate commits on SQL-layer during DDL.

2) Write to binary log happens as part of the DDL transaction.

3) Caches of data-dictionary, routine, events and UDF are in
   consistent with the DDL status.

Also while adding atomicity/crash-safeness to DDL from implementation
point of view, it also required to:

4) Change in behavior of some DDL statements (e.g. DROP VIEWS, CREATE
   and DROP of stored routines) to make user-visible behavior atomic.

Let us discuss changes for each of non-table DDL statements in details.	

A. DDL on Stored routines (Stored function / Stored procedure):
---------------------------------------------------------------
  A.1: CREATE ROUTINE(FUNCTION/PROCEDURE):
  -----------------------------------------
    Current approach to create routines looks like,

      a) Create dd::Routine object describing routine to be created.

      b) Store dd::Routine object in the Data-dictionary tables and
         COMMIT the transaction.

      c) If routine type is Stored function then find views using
         the stored function, update view status and view column
         metadata and COMMIT for each view using the stored function.

      d) Invalidate stored routine cache.

      e) Write stored routine create event to the binary log.

    To make routine create operation atomic/crash safe approach to
    create routine is replaced with,

      a) Create dd::Routine object describing routine to be created.

      b) Store dd::Routine object in the data-dictionary tables.

      c) If routine type is Stored function then find views using
         this stored function, update view status and view column
         metadata in the data-dictionary tables.

      d) Write stored routine create event to the binary log.

      e) If automatic_sp_privileges is set then grant privileges on
         the routine (No binlog event is written for this).

      d) COMMIT the transaction.

      f) Invalidate stored routine cache.	

   On error transaction is rolled back and error message is reported.

  	  
  A.2: ALTER ROUTINE(FUNCTION/PROCEDURE):
  -----------------------------------------
    Current approach to alter routines looks like,
  
      a) Acquire dd::Routine object for modification.

      b) Update dd::Routine object and store in the data-dictionary
         tables and COMMIT the transaction.

      c) Write stored routine alter event to the binary log.

      d) Invalidate stored routine cache.	

    To make routine alter operation atomic/crash safe approach to
    alter routine is replaced with,

      a) Acquire dd::Routine object for modification.

      b) Update dd::Routine object and store in the data-dictionary
         tables.

      c) Write stored routine alter event to the binary log.

      d) COMMIT the transaction.

      e) Invalidate stored routine cache.	

   (On error transaction is rolled back and error message is reported.)


  A.3: DROP ROUTINE(FUNCTION/PROCEDURE):
  -----------------------------------------
    Current approach to drop routines looks like,

      a) Acquire dd::Routine object of stored routine to be dropped.

      b) Drop dd::Routine object from the Data-dictionary tables and
         COMMIT the transaction.

      c) If routine type is Stored function then find views using
         the stored function, update view status and view column
         metadata and COMMIT for each view using the stored function.

      d) Write stored routine drop event to the binary log.

      e) Invalidate stored routine cache.	

    To make routine drop operation atomic/crash safe approach to
    drop routine is replaced with,

      a) Acquire dd::Routine object of stored routine to be dropped.

      b) Drop dd::Routine object from the Data-dictionary tables.

      c) If routine type is Stored function then find views using
         the stored function, update view status and view column
         metadata in the data-dictionary tables.

      d) Write stored routine drop event to the binary log.

      e) COMMIT the transaction.

      f) Invalidate stored routine cache.

   (On error transaction is rolled back and error message is reported.)


B. DDL on Events:
-----------------------------
  B.1: CREATE EVENT:
  -----------------------
    Current approach to create event looks like,

      a) Create dd::Event object describing event to be created.

      b) Store dd::Event object in the Data-dictionary tables and
         COMMIT the transaction.

      c) Create event element object for the event queue.

      d) Drop event from Data-dictionary tables on failure to create
         event element or populate it and COMMIT the transaction.

      e) Add event element to the events queue.

      f) Write create event statement to the binary log.

    To make event create operation atomic/crash safe approach to
    create event is replaced with,

      a) Create dd::Event object describing event to be created.

      b) Store dd::Event object in the Data-dictionary tables.

      c) Create event element object for the event queue and populate 
         the event element.

      d) Add event element to the events queue.

      e) Write create event statement to the binary log.

      f) COMMIT the transaction.

   (On error transaction is rolled back and error message is reported.)

   Step c) and d) are included in the same transaction to make user
   visible behavior atomic for drop routine operation.
   On error after d), event element is removed from the event queue
   and memory is de-allocated.


  B.2: ALTER EVENT:
  -------------------
    Current approach to alter event looks like,

      a) Acquire dd::Event object for modification.

      b) Update dd::Event object, store in the data-dictionary
         tables and COMMIT the transaction.
	
      c) Update event element in the event queue.	

      d) Write alter event statement to the binary log.

    To make event alter operation atomic/crash safe approach to
    alter event is replaced with,

      a) Acquire dd::Event object for modification.

      b) Update dd::Event object and store in the data-dictionary
         tables.

      c) Write alter event statement to the binary log.

      d) COMMIT the transaction.

      e) Update event element in the event queue.
	 (Method to update event element does not fail in the current
          implementation)

   (On error transaction is rolled back and error message is reported.)


  B.3: DROP EVENT:
  -------------------
    Current approach to drop event looks like,

      a) Acquire dd::Event object of event to be dropped.

      b) Drop dd::Event object from the Data-dictionary tables and
         COMMIT the transaction.

      c) Drop event element from the event queue.	

      d) Write drop event statement to the binary log.

    To make event drop operation atomic/crash safe approach to
    drop event is replaced with,

      a) Acquire dd::Event object of event to be dropped.

      b) Drop dd::Event object from the Data-dictionary tables.

      c) Write drop event statement to the binary log.

      d) COMMIT the transaction.

      e) Drop event element from the event queue.
	 (Method to drop event element does not fail in the current
          implementation)

   (On error transaction is rolled back and error message is reported.)


C. DDL on View:
-----------------------------
  C.1: CREATE VIEW:
  -------------------
    Current approach to create view looks like,

      a) Create dd::View object describing view to be created.

      b) Store dd::View object in the Data-dictionary tables and
         COMMIT the transaction.

      c) Find view(in invalid state) using the view being created,
         update view status and view column metadata and COMMIT
         transaction for each view.         

      d) Write create view event to the binary log.

    To make create event operation atomic/crash safe approach to
    create view is replaced with,

      a) Create dd::View object describing view to be created.

      b) Store dd::View object in the Data-dictionary tables.

      c) Find view(in invalid state) using the view being created,
         update view status and view column metadata in the
         data-dictionary tables.

      d) Write create view event to the binary log.

      e) Commit the transaction.

   (On error transaction is rolled back and error message is reported.)


  C.2: ALTER VIEW:
  -------------------
    Current approach to alter view looks like,

      a) Acquire dd::View object for modification.

      b) Update dd::view object and store in the data-dictionary
         tables and COMMIT the transaction.

      c) Find view(in invalid state) using the view being altered,
         update view status and view column metadata and COMMIT
         transaction for each view.         

      d) Write alter view event to the binary log.

    To make alter view operation atomic/crash safe approach to
    alter view is replaced with,

      a) Acquire dd::View object for modification.

      b) Update dd::view object, store in the data-dictionary
         tables.

      c) Find view using the view being altered, update view
         status and view column metadata.

      d) Write alter view event to the binary log.

      e) COMMIT the transaction.

   (On error transaction is rolled back and error message is reported.)


  C.3: DROP VIEW:
  -----------------
    Current approach to drop view looks like,

      a) For all the view names listed in the drop view statement,

          a.1) Acquire view object to be dropped.

          a.2) If view is not found in the data-dictionary then report
               a warning if IF EXISTS clause is used in the statement
               else save view name to the non_existing_view lists.

          a.3) If name is of TABLE then save name of a table.

          a.4) Otherwise drop view object from the data-dictionary
               and COMMIT the transaction.

          a.5) Find all the views using the view being dropped, update
               view status in the data-dictionary and COMMIT transaction
               for each view.

      b) Report an error for using non existing views in the statement
         if non_existing_view list is not empty.

      c) Report an error if any table is used in the drop view
         statement.

      d) If any view is dropped then write binary log for drop
         view statement even in error cases mentioned in step
         b and c.

    To make drop view operation atomic/crash safe approach to
    drop view is replaced with,

      a) For all the view names listed in the drop view statement,
        
          a.1) Acquire view object to be dropped.

          a.2) If view is not found in the data-dictionary then report
               a warning if IF EXISTS clause is used in the statement
               else save view name to the non_existing_view lists.

          a.3) If name is of TABLE then report an error and return.

      b) Report an error for using non-existing views in the statement
         if non_existing_view is not empty and return.

      c) After handling error cases start dropping views.

           For all the view names in the drop view statement,
            c.1) Drop view from the data-dictionary tables.

            c.2) Find all the views using the view being dropped,
                 update view status in the data-dictionary tables.

      d) Write drop view event to the binlog.

      e) COMMIT the transaction.

   (On error transaction is rolled back and error message is reported.)


D. DDL on UDF
-----------------------------
  D.1: CREATE UDF:
  -------------------
    Current approach to create UDF looks like,
      
      a) Open system table mysql.func from InnoDB storage
         engine.

      b) Write new UDF row to the mysql.func table.

      c) Write create UDF event to the binary log.

      d) Insert UDF element to the hash used for UDFs.

      e) COMMIT the transaction.

   (On error transaction is rolled back and error message is reported.)

    UDF create operation and binlog event are bundled in the same
    transaction. But in case of transaction commit failure step d)
    is not rolled back. 

    To make operation atomic/crash safe, approach to create UDF is
    replaced with,

      a) Open system table mysql.func from InnoDB storage
         engine.

      b) Write new UDF row to the mysql.func table.

      c) Write create UDF event to the binary log.

      d) Insert UDF element to the hash used for UDFs.

      e) COMMIT the transaction.
         If transaction COMMIT fails then UDF is removed from the
         hash.

   (On error transaction is rolled back and error message is reported.)

         
  D.2: DROP UDF:
  -------------------
    Current approach to DROP UDF looks like,
      
      a) Open system table mysql.func from InnoDB storage
         engine.

      b) Delete UDF row from the mysql.func table.

      c) Write DROP UDF event to the binary log.

      d) Delete UDF element from the hash used for UDFs.

      e) COMMIT the transaction.

   (On error transaction is rolled back and error message is reported.)

    UDF drop operation and binlog event are bundled in the same
    transaction. But in case of transaction commit failure step d)
    is not rolled back. 

    To make operation atomic/crash safe, approach to create UDF is
    replaced with,

      a) Open system table mysql.func from InnoDB storage
         engine.

      b) Delete UDF row from the mysql.func table.

      c) Write DROP UDF event to the binary log.

      d) COMMIT the transaction.

      e) Delete UDF element from the hash used for UDFs.

   (On error transaction is rolled back and error message is reported.)


Behavior changes introduced by this WL:
========================================
  1) DROP VIEW removes one or more views. In 5.7, if any of the
     view does not exists or if table is specified in the statement
     then error is reported but the other views in the list are
     dropped. So partial execution of DROP VIEW statement is
     supported in 5.7.

     To make DROP VIEW statement atomic, non-existing views or
     tables used in the view list are checked and error is
     reported. If there are no errors then all the views in the
     list are dropped and binlog is written for the drop view
     statement. With this change, partial execution of the DROP
     VIEW statement is not possible.

     As a consequence of this change, in a cross-version
     replication setup, a DROP VIEW statement that partially
     executes on a 5.7 master will fail on an 8.0 slave due
     to atomic DROP VIEW statement support.

Notes for the documentation:
========================================

  1) Change following paragraph in the
     "https://dev.mysql.com/doc/refman/8.0/en/drop-view.html"

     ...
     DROP VIEW removes one or more views. You must have the DROP
     privilege for each view. If any of the views named in the
     argument list do not exist, MySQL returns an error indicating
     by name which non-existing views it was unable to drop, but it
     also drops all of the views in the list that do exist.
     ...

     To:

     ...
     DROP VIEW removes one or more views. You must have the DROP
     privilege for each view. If any of the views named in the
     argument list do not exist, MySQL returns an error indicating
     by name which non-existing views it was unable to drop.
     ...