WL#8980: Move UDF table from MyISAM to Transactional Storage
Affects: Server-8.0 — Status: Complete
Up until MySQL 5.7 UDF(User-Defined Function) system tables are stored in MyISAM. With the new data dictionary stored in transactional tables, the metadata relating to UDF also need to be stored in a table whose storage engine supports transactions. The underlying storage engine that is used is InnoDB.
FR#1 Modify install/bootstrap to create the mysql.func table in InnoDB (instead of MyISAM). FR#2 DDL statement CREATE FUNCTION and DROP FUCTION may return errors like lock wait timeout and deadlock errors and other errors that may be specific to transactional storage engine InnoDB. FR#3 Modify sql-scripts that are used to deploy new database and to upgrade existent database in order to set the InnoDB as as default storage for the system table. FR#4 The DDL statements (CREATE FUNCTION & DROP FUNCTION) will cause implicit commit before and after statement execution. FR#5 Do not disable the system table mysql.func to be stored in MyISAM by user request.
Transactional and non-transactional SE ====================================== This WorkLog(WL) is about moving MyISAM system table into InnoDB. However we in general refer MyISAM by the term "non-transactional" and InnoDB by the term "transactional". The reason for using the generic words "non-trasactional" and "transactional" are the following: - All system tables may not be in MyISAM. There may be system tables in SE like CSV. - Current we envision Data Dictionary tables to be in InnoDB only. However in future, we might want to store the DD tables in other transactional storage engines. Thus we in general use the generic terms "Non-transactional" and "transactional". They all invariably with respect to this worklog refer the storage engines MyISAM and InnoDB. Essential Changes ================= In the scope of the WL, the system non-transactional table mysql.func is migrated to the transactional table mysql.func. These table store meta- information relating to the User-Defined Function (UDF). We may subsequently refer this metatable by UDF table in this worklog. UDF statements =============== The following statements update the meta information relating to UDF: - CREATE FUNCTION - DROP FUNCTION These statements are DDL statements and thus they force an implicit commit before and after statement execution (irrespective of the settings of autocommit). This change was not important when the UDF table was stored in non-transactional storage engine. Now that the metadata of UDF is stored in the transactional storage engine, this needs to be noted in the documentation. Changes to InnoDB ================= The function innobase_is_supported_system_table is modified to return true for the UDF table mysql.func if it is called with this argument. The function pointer handler::is_supported_system_table to initialized with innobase_is_supported_system_table that returns true for the passed in argument value "mysql.func" Changes to SQL Scripts ======================= The sql-scripts that create the schema definitions of the UDF table during bootstrap is modified to specify the transactional storage engine explicitly. Similar changes are done in the sql-scripts to make server upgrade. Changes to the UDF subsystem ============================= There are two major operations that the UDF subsystem does: 1. Read from the meta table mysql.func: This is done to get the attributes of the meta information (like name of the user defined function, corresponding dynamic library name , the UDF type etc). This reads are transactional in nature because the underlying storage engine is InnoDB. This requires specification of the transactional semantics. We strive to keep the operational semantics as was before when the system table is in the non-transactional storage engine MyISAM. In MyISAM we do have exclusive access to the table by way of table-level locks. However InnoDB does row-level locking. Hence to keep the operational semantics as earlier, we obtain a stronger MDL lock of type Shared Read Only (SRO). This lock ensure when a read of the meta table is done, no concurrent write can happen.Thus this simulates the table-level locking of the MyISAM. The read transaction semantics is Auto Commit, Read Only, Read Committed And Non-Locking (AC-RO-RC-NL) type. This is done by using attachable transactions. The APIs open_trans_system_tables_for_read is used for opening the table and close_trans_table is used for closing the table. This APIs encapsulate the transactional semantics described above. 2. Write to the meta table mysql.func: We update the meta-table mysql.func to reflect the newly added UDF or an UDF being dropped. This happens via the DDL statements CREATE FUNCTION and DROP FUNCTION. Now that storage engine is transactional, we need to specify the commit semantics of this DDLs. The DDLs does an implicit commit before and after the statement. In addition like in read, we obtain the MDL lock of type Shared Not Read Write (SNRW) for write operations. This ensures the table-level locking of the system table as it is done by MyISAM, thereby, allowing us to maintain the operational semantics as before. Thus, we have exclusive access of mysql.func table when it's being modified or read as is the case before. With InnoDB, we do explicit commit (at code-level) to persist the information. THR_LOCK_udf scope is reduced and protects only the global in-memory hash data structure. InnoDB shall do I/O operations and thus holding THR_LOCK_udf to protect code section that involve SE calls is unnecessary and lead to complications like deadlock and hangs. Also exclusive access semantics of the table via the MDL lock ensure that LOCK_THR_udf should be used to protect only the global in-memory data structures. The commit for a UDF meta operation involves two things: 1. Storage engine commit to persist the date in the table. 2. In-memory modification of the hash data structure (addition or removal). The above operation should happen in a atomic to maintain consistency. To this end, new function udf_end_transaction is introduced in sql_udf.cc file. MyISAM and InnoDB interoperability ================================== The WL won't disable the storing of mysql.func in non-transaction storage engine if a user requests it. That is, if a user executes the statement, ALTER TABLE mysql.func ENGINE=MyISAM., the mysql.func table will be transferred to the MyISAM. Neither this WL will enforce table in the non-transactional storage engine to remain in the same non-transactional storage engine. This means we allow the user to do: ALTER TABLE mysql.func ENGINE=InnoDB. This may be required of to support the downgrade and upgrade functionalities of the server. Hence this functionality of allowing internally the modification of the system table should not be documented. However in general , we disallow the external user to directly manipulate the system table and this shall be the scope of the WL#6391 (Hide DD system tables from users.)
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.