WL#9173: New data-dictionary: Improve crash-safety of non-table DDL.
Affects: Server-8.0
—
Status: Complete
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
([func_parameter[,...]]) RETURNS type
[characteristic ...]
* ALTER FUNCTION [ characteristic ...]
* DROP FUNCTION [IF_EXISTS]
* CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE
([proc_parameters[,...]])
[characteristic ... ]
* ALTER PROCEDURE [ characteristic ...]
* DROP PROCEDURE [IF_EXISTS]
* CREATE [DEFINER = { user | CURRENT_USER }] EVENT
[IF NOT EXISTS]
ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment']
DO ;
* ALTER [DEFINER = { user | CURRENT_USER }] EVENT
[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]
* CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
* ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
* DROP VIEW [IF EXISTS] view_name [, view_name] ...
[RESTRICT | CASCADE]
* CREATE [AGGREGATE] FUNCTION RETURNS
{STRING|INTEGER|REAL|DECIMAL} SONAME
* DROP FUNCTION
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.
...
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.