WL#7896: Use DD API to work with triggers
Affects: Server-8.0 — Status: Complete
WL#6383 provides new DD API to work with triggers. The aim of this WL task is to switch to that API from the current "API" to create/drop/load triggers. So, scope of this WL is * to use the new system table mysql.triggers to store metadata of table's triggers; * to use API introduced by WL#6383 to store/load table's trigger definitions; * to remove support for .TRG/.TRN files from server code base. Scope of this WL doesn't include: * upgrade of triggers metadata from .TRN/.TRG files to a new Data Dictionary. * downgrade procedures to move trigger definition from the table mysql.triggers to .TRN/.TRG files. This WL also doesn't solve a task for hiding direct access to a table mysql.triggers from users. As a consequence, any possible issues caused by changes of trigger's metadata manually and following execution of statements SHOW TRIGGERS/SHOW CREATE TRIGGER aren't covered by this worklog.
Prerequisite Req. 1: The table mysql.triggers must exist in the new Data Dictionary in order to allow storing of trigger's metadata. Func. Req. 1: The statement CREATE TRIGGER must store a trigger's metadata into the table mysql.triggers; Func. Req. 2: The statement DROP TRIGGER must remove a metadata about a trigger being dropped from the table mysql.triggers; Func. Req. 3: The table INFORMATION_SCHEMA.TRIGGERS must be filled by reading trigger's metadata from the Data Dictionary; Func. Req. 4: The statements SHOW TRIGGERS and SHOW CREATE TRIGGER must output a trigger's metadata based on a content of the table mysql.triggers; Func. Req. 5: For case when trigger's metadta has been modified manually using SQL statements INSERT/UPDATE/DELETE on the table mysql.triggers the behaviour of the statements SHOW TRIGGERS and SHOW CREATE TRIGGER is out of scope. Func. Req. 6: Behaviour of the server mustn't depend on a content of .TRG/.TRN files, their presence or absence. Func. Req. 7: MTR tests that depends on .TRG/.TRN files must be rewritten or removed.
This worklog does the following things: * Provides auxiliary APIs in dd namespace to create, drop, load triggers from underlying data dictionary table(s) using dictionary client interface * Makes use of the auxiliary APIs in the existing triggers subsystem of the server to implement the triggers functionality. This worklog doesn’t make any major changes to the existing design of the triggers subsystem in the server code. The user observable changes are: * Non-SUID triggers stopped to be supported since definer user/definer host have NOT NULL constraint in the table mysql.trigger; * A text of message for the error code ER_TRG_NO_DEFINER has been changed. Former it was looked as "No definer attribute for trigger '%-.192s'.'%-.192s'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger." Now it's changed to "No definer attribute for trigger '%-.192s'.'%-.192s'. It's disallowed to create trigger without definer." * Message for the code ER_TRG_NO_DEFINER now is emitted as an error instead of warning as it was before. This change was inspired by the fact that non-SUID triggers no more supported; * Trigger’s metadata now is stored in the new Data Dictionary table mysql.triggers; * Support for .TRG/.TRN files is removed from the server codebase. These file suffixes no more recognized as reserved and files with such suffixes no more handled in any special way; * MDL lock for trigger name is acquired while handling CREATE TRIGGER/DROP TRIGGER/DROP DATABASE/DROP TABLE.
To support triggers this worklog uses the new Data Dictionary API and the Data Dictionary table mysql.triggers provided by the WL#6383. The class dd::cache::Dictionary_client is used for work with the Data Dictionary. Its methods acquire() and update() is used for getting a dd::Table object that associated with a trigger being created/looked for/dropped. To find a table name by trigger name the method Dictionary_client::get_table_name_by_trigger_name() is used for. The following methods of the class dd::Table are used for finding/storing/dropping metadata information for a trigger: bool has_trigger() const; Trigger_const_iterator *triggers() const; Trigger *get_trigger(const char *name); Trigger *add_trigger(Trigger::enum_action_timing at, Trigger::enum_event_type et); Trigger *add_trigger_following(const Trigger *trigger, Trigger::enum_action_timing at, Trigger::enum_event_type et); Trigger *add_trigger_preceding(const Trigger *trigger, Trigger::enum_action_timing at, Trigger::enum_event_type et); void drop_trigger(Trigger *trigger); This worklog does the following things in the existing server code base: * Code for handling CREATE/DROP TRIGGER is moved to classes derived from Sql_cmd. Common code used while processing the statements CREATE TRIGGER/DROP TRIGGER moved to the class Sql_cmd_ddl_trigger_common that inherited from the class Sql_cmd. Two new classes Sql_cmd_create_trigger, Sql_cmd_drop_trigger introduced to handle the statements CREATE TRIGGER/DROP TRIGGER. These classes are inherited from the class Sql_cmd_ddl_trigger_common; * Text of the whole CREATE TRIGGER statement that used to create trigger no more stored in the Data Dictionary as it was before. Instead, only a trigger’s body stored in a separate column of the table mysql.trigger. The full content of the statement CREATE TRIGGER shown in resulting output of the statement SHOW CREATE TRIGGER and in output of the command mysqldump is reconstructed from the metadata stored in the system table mysql.triggers. Such modification allows us doesn't worry about modification in a statement for trigger creation when a trigger's table name changed; * Since the whole text of the statement CREATE TRIGGER no more stored in the data dictionary and reconstructed on the fly we don’t need storing in a LEX the pointers to begin/end of ON clause and begin/end of FOLLOWS/PRECEDES clauses that used to reconstruct the statement CREATE TRIGGER in case a table name changed or to reconstruct the statement CREATE TRIGGER before write to binlog. It means that size of LEX is decreased by the size of 4 pointers. * The columns Trigger name, definer and table name that are output from the statement SHOW CREATE TRIGGER and the command mysqldump are always included in backquotes; * Before this WL a statement CREATE TRIGGER that is used to create a trigger was stored in a file with suffix .TRG. A statement was stored literally as it was entered by a user. Since this WL doesn’t store the whole statement and reconstructs it on the fly some differences exists in result output of SHOW CREATE TRIGGERS/mysqldump run against the server without and with this WL. For example, before this WL a table name and a trigger name is output as it was entered by a user whereas this WL always outputs a table name and a trigger name in backquotes. * The order of storing triggers in the data dictionary has been changed. Before this WL triggers were stored in the order of creation. After this WL triggers are stored in the Data Dictionary based on internal rules that doesn’t coincide with order that triggers were created; * Tests that depend on files .TRG/.TRN were disabled or removed; * The new source files sql/dd/dd_trigger.h, sql/dd/dd_trigger.cc were added. These files contains adapter functions declared in the namespace dd to interact with new dd API. List of new functions used for working with dd trigger API are listed below: bool create_trigger(THD *thd, MEM_ROOT *mem_root, ::Trigger *new_trigger, enum_trigger_order_type ordering_clause, const MYSQL_LEX_STRING &referenced_trigger_name); bool load_triggers(THD *thd, MEM_ROOT *mem_root, const char *schema_name, const char *table_name, List<::Trigger> *triggers); bool table_has_triggers(THD *thd, const char *schema_name, const char *table_name); bool check_trigger_exists(THD *thd, const char *schema_name, const char *trigger_name, bool *trigger_exists); bool drop_trigger(THD *thd, const char *schema_name, const char *table_name, const char *trigger_name, bool *trigger_found); bool drop_all_triggers(THD *thd, const char *schema_name, const char *table_name, List<::Trigger>* triggers); bool get_table_name_for_trigger(THD *thd, const char *schema_name, const char *trigger_name, std::string *table_name, bool *trigger_found); * The source files sql/trigger_loader.h, sql/trigger_loader.cc have been removed from the code base; * To support metadata locking for trigger name the following modifications were done: - additional function acquire_mdl_for_trigger() was added to acquire MDL lock by schema name and trigger name - trigger MDL lock is requested as an exclusive metadata lock from the namespace MDL_key::TRIGGER with transaction duration - the function acquire_mdl_for_trigger() is called from the methods Sql_cmd_create_trigger::execute(), Sql_cmd_drop_trigger::execute() as part of handing the statements CREATE TRIGGER, DROP TRIGGER - the new standalone function lock_trigger_names() was introduced to acquire MDL lock for every trigger associated with a table. This function is called during handling the statement DROP TABLE/DROP DATABASE. The function iterates over a list of triggers associated with a table (in case of DROP TABLE) or several tables (in case of DROP DATABASE) and calls the function acquire_mdl_for_trigger() to get a lock; * Test cases for trigger MDL were put into the new file trigger_mdl.test. * Support for trigger mdl fixes the bug BUG#77095.
Copyright (c) 2000, 2022, Oracle Corporation and/or its affiliates. All rights reserved.