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.
Modify install/bootstrap to create the mysql.func table in InnoDB (instead of 

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.

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

The DDL statements (CREATE FUNCTION & DROP FUNCTION) will cause implicit 
commit before and after statement execution.

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
- 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 
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:
These statements are DDL statements and thus they force an implicit commit
before and after statement execution (irrespective of the settings of 
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,
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:
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.)