WL#9495: Update schema tables of dynamic plugins into data dictionary.
Affects: Server-8.0 — Status: Complete — Priority: Medium
The main aim of this WL is to, * This WL focuses to present INFORMATION_SCHEMA (I_S) tables metadata to be visible through I_S.TABLES. Following are the three areas where we can find I_S tables in MySQL server. 1. Server I_S table. 2. Builtin plugin's I_S table. 3. Dynamic plugin's I_S table. We need to write metadata of above I_S tables to data dictionary (DD) tables, so that the system view I_S.TABLES picks it. * Update DD tables when a plugin is loaded or unloaded. This can happen when server is started by command line options or by INSTALL/UNINSTALL commands. * Server should update DD tables with I_S table metadata based when I_S table structure is changed, using a version number. * Move creation of system views from mysql_system_tables.sql script to server initial start similar to the way the DD tables are created. * Upgrade system views by introducing I_S version number, similar to the way we have DD version. * Hide I_S.*_DYNAMIC and I_S.SHOW_* internal system view from user.
Acronym: DD - Data dictionary. I_S - Information schema. SITM - Server I_S Table Meta-data. PITM - Plugin I_S Table Meta-data. (Builtin/Dynamic) Note: * The term 'I_S table meta-data' would mean two things, 1) Metadata of I_S table that is implemented as a temporary table. 2) Metadata of I_S table that is implemented as a system view over DD tables. * The SITM is combination of both 1) and 2) right now. The long term goal is to minimize the number of SITM based on 1). * The term 'system view' would mean both I_S system views exposed to users like I_S.TABLES, and server internal I_S system views like I_S.TABLE_DYNAMIC. * The plugin name and the I_S table name added by a plugin would be same. FR1: Store SITM into DD during server initial start. FR2: Store builtin/dynamic PITM into DD table during server restart. FR3: On every server restart, remove builtin/dynamic PITM that were loaded during last server restart, unless the plugin is loaded. Remove PITM even if plugin is started with status OFF. FR4: Store dynamic PITM into DD upon INSTALL command. FR5: Remove dynamic PITM from DD upon UNINSTALL command. FR6: Move creation of system views from mysql_system_tables.sql script and create them during server initial start. FR7: The creation of system view I_S.TABLE_CONSTRAINTS which uses a SELECT with UNION clause fails during server initial startup, because the heap engine is not available during server initial start. In order to fix above, we need to force Optimizer to use the InnoDB engine during server initial start to instantiate temporary table. This is required to process UNION clause when creating I_S system view. Note: Needs review from InnoDB team. FR8: Hide I_S.SHOW_* and I_S.*_DYNAMIC system views from being listed by I_S.TABLES/VIEWS. FR9: Restrict use of I_S.SHOW_* and I_S.*_DYNAMIC I_S table names in a SQL statement by user, similar to the way we restrict use of DD table names. FR related to upgrade: FR10: Update SITM into DD, if the SITM version is changed. FR11: Update builtin/dynamic PITM into DD, if the PITM version is changed. FR12: Change the name of the DD table 'mysql.version' to 'mysql.dd_properties', and change the column name from 'version' to 'properties' and its type from 'unsigned int' to 'mediumtext'. Non-functional requirement: NFR1: Keep all the I_S metadata related code in sql/dd/info_schema namespace. NFR2: Introduce dd::System_view interface to represent a system view, similar to dd::Object_table. NFR3: Introduces dd::System_view_definition interface to represent system view definition. With methods 'add_field()', 'add_from()' and 'add_where()' to simplifying creation of system view similar to the way we create DD tables using dd::Object_table_definition interface. A variant of this class to handle a) Simple SELECT and b) SELECT with UNION clause could be introduced.
Design overview: - When a SQL statement uses I_S.TABLES in 5.7, the server requests the plugins for PITM and then the PITM is stored in a temporary table that represents I_S.TABLES. I_S.TABLES is a view over data dictionary. Query on I_S.TABLES table do not use a temporary table. The current problem is, if a dynamic plugin is loaded, I_S.TABLES do not show PITM that are added by the dynamic plugins. The approach to solve the problem is to store the PITM into DD tables when the plugin is loaded. And remove the same when a plugin is unloaded. The SITM is also stored in DD tables. More design details are presented below. - PITM or SITM is stored in DD as dd::View object of type dd::SYSTEM_VIEW. This is because of 1) 5.7 presents all the I_S table as 'SYSTEM VIEW', so it suites to use dd::View of type dd::View::SYSTEM_VIEW. 2) the vision is to move towards creating more and more I_S system views going forward, and eliminate use of temporary tables in I_S implementation. Details of design is a follows, A) PITM and INFORMATION_SCHEMA: ``````````````````````````````` 1) When are plugins loaded and unloaded ? 1.1) Builtin plugins: Load : a) Always loaded with the server restart. They are available even during server initial start. b) Load plugin using --<plugin_name>=OFF. PITM from the plugins will not be visible to users from I_S.TABLES. Unload: Not possible. 1.2) Dynamic plugins: Load : a) Using INSTALL command. These plugins are automatically loaded upon next server restart. They can be removed only by UNINSTALL command. b) using following server options, --plugin-load --plugin-load-add --early-plugin-load Note: The dynamic plugins are loaded and enabled when the server is restarted, unless explicitly deactivated, or if the server is started using --skip-grant-tables Unload: x) Using UNINSTALL command, 1) if plugin is loaded by 1.2.a. 2) if plugin is loaded by 1.2.b without FORCE_PLUS_PERMANENT y) During shutdown. 2) Updating Builtin/Dynamic PITM into DD tables: In order to enable users to see PITM from I_S.TABLES, the DD tables are updated with PITM in following events, a) On every server restart, traverse through each builtin/dynamic plugins. For each PITM table, if PITM is not already present in DD table then update DD. Otherwise, skip updating DD table with PITM. b) Remove dynamic PITM from DD table on UNINSTALL command. c) Remove PITM of plugins that are disabled OR started with status OFF, if in metadata for PITM is present in DD tables. pros: - Server restart would not update DD, if same plugins loaded as with previous server restart. - Server shutdown would not update DD and hence server shutdown is not affected at all. - No extra steps are required to handle server crash and restart scenario. cons: - Server restart would involve several DD lookup (read) operations. This would increase the server restart time a little. - Would need to traverse all the dynamic PITM present in DD table at startup, and remove them if the respective plugin is not loaded. 3) Performance notes: The increase in startup time is not expected to be significant, because the total number of plugins loaded is likely to be small. B) SERVER and INFORMATION_SCHEMA: ````````````````````````````````` There are two types of SITM in server, 1) I_S tables that are implemented as system views on DD. 2) I_S tables that are implemented as temporary tables. Current state after WL#6599 is implemented, the server stores 2) during server initial start. The SITM of 1) need not be stored as DD tables are updated when the system view is created. There will be no change in the way SITM are stored. But upgrade of SITM would be handled by this WL, as discussed in next section. C) UPGRADE: ``````````` This section discusses about upgrade of SITM and PITM information stored in DD tables. 1) Upgrade of SITM: The following procedure applies to both B.1) and B.2), - Introduce a dd::info_schema::i_s_version global variable indicating the version of SITM. - Store the 'i_s_version=<dd::info_schema::i_s_version>' in mysql.dd_properties.properties after storing SITM in the DD. - On server restart, do upgrade if needed by checking if the hard coded global dd::info_schema::i_s_version differ from property 'i_s_version' in mysql.dd_properties.properties. If yes, then we recreate all SITM in the DD tables. Then update new version number into mysql.dd_properties.properties. - If DD version is changed, we recreate all SITM in the DD. 2) Upgrade of PITM: The following steps are carried on every server restart, a) Whenever PITM is stored in DD tables, the plugin version is stored in dd::View::options as 'plugin_version=<number>' b) Upon server restart, discard old PITM from DD tables if the plugin_version of st_plugin_int* does not match to version stored in dd::View that belongs to PITM. And recreate PITM, which would stored new plugin_version in dd::View. See LLD section A) for more details about pseudo code of how PITM would be updated during server restart. D) CREATION OF SYSTEM VIEWS: ```````````````````````````` Currently, the system views are created by CREATE VIEW command in scripts/mysql_system_tables.sql. This allows the system view definitions to be modified by MySQL user. And modifying this definitions would cause unexpected server behavior. In order to avoid the same, this WL creates system views during server initial startup by executing CREATE VIEW within the server. The CREATE VIEW definitions are not accessible to users for modification as the definitions are hard-coded in the source code. The creation of system view would be handled as a separate stage of initialization during server initial start and would not be done as part of DD initialization. Hard-coding the I_S system view definitions in source code also allows us to eliminate need of I_S.STATISTICS_BASE system view and it is no more created. FR6 and FR7 traces to this section. E) HIDING INTERNAL SYSTEM VIEWS: ```````````````````````````````` Currently, the system views that are created for internal server usage like I_S.STATISTICS_DYNAMIC, I_S.TABLE_DYNAMIC, I_S.SHOW_STATISTICS and I_S.SHOW_STATISTICS_DYNAMIC are visible to MySQL users. This is not necessary. This WL hides the above system views by marking the dd::View::hidden() flag as true, indicating that they are internal system view definitions. The hidden tables/views are not visible to users, both from SQL command and from I_S tables. FR8 and FR9 traces to this section.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.