WL#7897: Use DD API to work with stored routines
Affects: Server-8.0 — Status: Complete
WL#6384 provides new DD tables ("Routines" and "Parameters") and new DD APIs to work with the stored routines(Stored function and procedures). The scope of this WL is, * Use new DD tables "Routines" and "Parameters" for the stored routines. * Use new DD APIs to create/drop/load stored routines. Related Work Logs: WL#6384: Define and Implement API for Stored Routines WL#6391: Hide DD system tables from users WL#6392: Upgrade and downgrade tool for new data dictionary
F-1: Bootstrap procedure must not create mysql.proc table. Currently table mysql.proc (in MyISAM engine) is created to hold the stored routine metadata. As part of this WL, modify mysql_system_tables.sql tool to avoid creation of mysql.proc table (in MyISAM engine). F-2: Bootstrap procedure must create new DD tables "Routines" and "Parameters". Create New DD table before creating any stored routines. With F-1 and F-2, stored routine DD tables will be hidden from the user. (Please refer "WL#6391- Hide DD system tables from user") F-3: Create routine operation must store metadata of stored routine in "Routines" and "Parameters" table using new DD API. This work is mostly about replacing calls to insert stored routine metadata in mysql.proc with New DD API's to store metadata in "Routines" and "Parameters" table. F-4: Drop routine operation must remove metadata of stored routine from "Routines" and "Parameters" tables using new DD API. This work is mostly about replacing calls to remove stored routine metadata from mysql.proc with New DD API's to remove metadata from "Routines" and "Parameters" table. F-5: Modification to stored routine characteristics must be supported using new DD API. This work is mostly about using new DD API's to update stored routine characteristics instead of updating mysql.proc table. F-6: sp_head object for stored routine must be prepared by loading metadata of stored routine from DD the tables using new DD APIs. This work is mostly about reading metadata from "Routines" and "Parameters" table to prepare sp_head instead of mysql.proc table. F-7: DD APIs to list stored routines of Schema should be used to lock and drop all stored routines of schema. This work is mostly about using DD API to get list of stored routines instead of reading mysql.proc rows directly. F-8: Information_schema table "Routines" and "Parameters" must read data from new DD tables. This work is mostly about reading stored routine metadata from new DD table using new DD API's instead of mysql.proc. F-9: Update tests to use Information_schema's routine or parameter table where needed. Since mysql.proc does not exists after this WL changes, tests which use mysql.proc should be updated to use INFORMATION_SCHEMA's "Routines" and "Parameters" tables or remove test case. NF-1: Using new DD table and DD API's should not affect stored routine feature. NF-2: Using new DD table and API's should not affect stored routine caches. NF-3: The transformation of the Data Dictionary related infrastructure during upgrade and downgrade of MySQL version is not in scope of the current WL. Such transformations and the corresponding QA get handled in WL#6392 Upgrade and downgrade tool for new data dictionary
WL uses the New DD APIs and DD tables "Routines" and "Parameters" provided by the WL#6384 to store/drop/alter/load stored routines metadata. Currently the stored routine metadata is stored in the mysql.proc(MyISAM table). Stored routine code is implemented to to use mysql.proc table. A) As part of this WL, followings APIs are introduced to operate on new DD tables using DD APIs. These methods are called by the current SP module. a) template
enum_sp_return_code dd::create_routine(...) Stores metadata information from sp_head object to the DD tables. DD API's used here are, // To create dd::Function object dd::Routine* dd::Schema::create_function(); // To create dd::Procedure object dd::Routine* dd::Schema::create_procedure(); // To store object dd::cache::Dictionary_client::store(); Helper methods: // to fill stored routine information in dd::Routine object bool fill_dd_routine_info(...) // to fill parameters information of stored routine in // dd::Parameter object(s) bool fill_routine_parameters_info(...) // to fill parameter information from Create_field object. // Method is called by fill_routine_parameters_info(). bool fill_parameter_info_from_field(...) // to fill return type of the stored function. bool fill_dd_function_return_type(...) b) template enum_sp_return_code dd::drop_routine(...) Removes metadata information of stored routine from the DD tables. DD API's used here are, // To get dd::Routine object of stored routine dd::cache::Dictionary_client::acquire(); // To drop metadata. dd::cache::Dictionary_client::drop(); c) template enum_sp_return_code dd::alter_routine(...) Alter metadata information of stored routine in the DD tables. DD API's used here are, // To get object of stored routine dd::cache::Dictionary_client::acquire(); // To update metadata. dd::cache::Dictionary_client::update(); d) template enum_sp_return_code dd::find_routine(...) Find stored routine in the Data Dictionary table. DD API's used here, // To get object of "name" dd::cache::Dictionary_client::acquire(); B) These API and classes are declared and implemented in the new files "sql/dd/dd_routine.h" and "sql/dd/dd_routine.cc". C) To iterate through the stored routines of the schema following DD API is used, template bool Dictionary_client::fetch_schema_components(...) const Currently these APIs are used by the sp.cc:lock_db_routines(), sp.cc::drop_db_routnes() and sql_show.cc:fill_schema_proc() to iterate through the routines of the schema. D) Following methods are introduced in the sp.cc. a) static enum_sp_return_code lock_routine_name(...) Acquire MDL lock MDL_SHARED or MDL_SHARED_HIGH_PRIO on routine name. b) static bool show_create_routine_from_dd_routine(...) Prepares "SHOW CREATE FUNCTION/PROCEDURE" output using the dd::Routine object. E) Following methods are introduced in the "new" files dd_sp.h & dd.sp.cc a) void prepare_sp_chistics_from_dd_routine(...) Prepares st_sp_chistics object from the dd::Routine object. b) void prepare_return_type_string_from_dd_routine(...) Prepares stored routine return type value in string format from dd::Routine object. c) void prepare_params_string_from_dd_routine(...) Prepares stored routine parameters string from the dd::Routine object. d) bool is_dd_routine_type_functon(...) Method to check whether routine is of stored function or not. F) New error messages introduced as part of this WL are, ER_TOO_LONG_ROUTINE_COMMENT "Comment for routine '%-.64s' is too long (max = %lu)" ER_SP_LOAD_FAILED "Failed to load routine '%-.64s'." G) The WL introduces the following user-visible changes: a) The mysql.proc table is now removed. This means that the table can no longer be e.g. queried, updated or locked explicitly by the user. b) A shared metadata lock is now taken when executing stored routine. This lock is held until the transaction terminates. This means that it will no longer be possible to alter or drop a stored routine while it is executing. c) As a consequence of removing mysql.proc, ACL on mysql.proc no longer applies. For now, full access is only given to the definer, any users with global SELECT privilege and users with CREATE ROUTINE, ALTER ROUTINE or EXECUTE ROUTINE privileges. A more long-term solution for allowing backup of stored routines will be implemented in WL#8131/WL#9049. d) Before this WL, creating a stored routine having a definition containing illegal characters only resulted in a warning. With this WL, an error will be raised instead (ER_INVALID_CHARACTER_STRING is still used). This makes stored routines behave similar to e.g. views. H) While upgrading MySQL to 5.8 from 5.7 version, metadata information from the mysql.proc is moved to the new data dictionary tables. WL#6392 provides a way to get even the stored routine metadata from 5.7 to 5.8.
Copyright (c) 2000, 2022, Oracle Corporation and/or its affiliates. All rights reserved.