Skip to content
  • Sivert Sorumgard's avatar
    863aac45
    WL#9553: Upgrading the transactional data dictionary tables. · 863aac45
    Sivert Sorumgard authored
    This patch implements support for changing the DD table
    definitions.
    
    Overview.
    ---------
    The main changes are the following:
    
    - SE private data previously hard coded in InnoDB is now
      used only during first time server start. The meta data
      is stored in a DD table. On restart, the meta data is
      fetched from the DD table instead of InnoDB. Thus, we
      may have SE private data that can change.
    
    - During upgrade, we create the required target tables in
      a temporary schema, and migrate the meta data from the
      actual DD tables to the new target tables.
    
    - At the end of upgrade, we modify the persistently stored
      SE private data mentioned in 1) to that of the new target
      tables. We also adjust the schema ID of the target tables
      to simulate altering the schema of the tables. This way,
      we are able to switch from the old to the new DD version
      atomically. The temporary schemas are removed on next
      restart.
    
    In more detail, the patch implements the following:
    
    Performance schema.
    -------------------
    - Change in a performance schema test: Select only the PS_VERSION
      from 'mysql.dd_properties' to avoid reflecting irrelevant meta
      data in the result file, and thus to avoid unnecessary re-recordings
      of the result file.
    
    - Minor changes in the way Plugin_table_impl is used; replaced by
      Object_table_impl for more uniform code.
    
    - Approved by Marc Alff.
    
    Handler and handlerton API and InnoDB.
    --------------------------------------
    - The handler function 'get_se_private_data()' will now be
      called only during '--initialize'. During ordinary restart,
      it will be called once to get the SE private data for the
      'mysql.dd_properties' table, which can never change. This
      is the table that stores the SE private data for the other
      DD tables.
    
    - Modify 'get_se_private_data()' to be in line with what
      'dd_write_table()' does when storing SE private data:
    
      * Store se_private_data for columns. This is now done
        for DD tables in the same way as it is done for
        user tables.
      * Extend se_private_data for indexes to also store
        table_id and space_id. Again, this is done to get
        the same set of meta data for DD tables and user
        tables.
    
      Could dd_write_table() be used to ensure consistency in the
      meta data that is stored?
    
    - At the end of upgrade, we start over DD initialization to
      do the same as for a restart. Thus, we had to provide a new
      parameter to 'get_se_private_data()' to reset the counters
      for this to work with two phases of function calls in the
      case of upgrade.
    
    - We maintain a set in InnoDB of SE private ids of the DD tables.
      This set is used this instead of the hard coded id range used
      previously.
    
    - Replace hard coded ids of tables used in the processing
      of I_S queries by name based lookup.
    
    - Change the order of the DD and DDSE tables in the System_tables
      registry to make sure the table 'innodb_dynamic_metadata' is
      created on a low table id and index id. The motivation is that
      for now, this table must stay at fixed ids because it may be
      opened by InnoDB before the DD is available.
    
    - Approved by Jimmy.
    
    Extensions of 'mysql.dd_properties' and data structures.
    --------------------------------------------------------
    - Valid key/value pairs are explicitly defined:
    
        DD_VERSION                Actual DD version.
        IS_VERSION                Actual I_S version.
        PS_VERSION                Actual P_S version.
        SDI_VERSION               Actual SDI version.
        LCTN                      L_C_T_N setting used during
                                  --initialize.
        MYSQLD_VERSION_LO         Lowest server version which has
                                  been using the data directory.
        MYSQLD_VERSION_HI         Highest server version which has
                                  been using the data directory.
        MYSQLD_VERSION            Current server version.
        MINOR_DOWNGRADE_THRESHOLD The current DD can be used by
                                  previous MRUs, unless their
                                  target DD version is less than
                                  the downgrade threshold.
        SYSTEM_TABLES             List of system tabels with
                                  definitions.
        UPGRADE_TARGET_SCHEMA     Temporary schema used during
                                  upgrade.
        UPGRADE_ACTUAL_SCHEMA     Temporary schema used during
                                  upgrade.
    
    - Simplify Object_table, Object_table_definition,
      Plugin_table_definition, their subclasses and clients.
      Remove unnecessary functions, and rename according to
      usual naming rules. Merge Object_table* and Plugin_table*
      into one class.
    
    - Version number handling does not need to be part of these
      classes, this will be handled elsewhere, so it is removed.
    
    - Object table definitions now may hold definitions of both
      the target and actual tables.
    
    - Introduce explicit enumerations for options, indexes and
      foreign keys for the DD table definitions.
    
    - Explicitly define indexes needed by foreign keys.
    
    - Use the index enumerations when creating instances of
      object keys.
    
    - A new 'DD_bootstrap_ctx' class is introduced as an aid in the
      upgrade process, but also for normal DD bootstrapping. The
      handling of the bootstrap stages is moved into this class.
    
    Changes to the current DD initialization.
    -----------------------------------------
    - Extend the bootstrapper to create target or actual tables
      depending on context.
    
    - Change the way DD objects are flushed (during --initialize) and
      synced (during restart) to avoid problems with overlapping
      ID sequences for the scaffolding and the persisted object IDs.
      This is needed since the DD tables will no more be fixed on low
      IDs.
    
    - Add a new stage before creating tables where the inert table
      'dd_properties' is opened and the version numbers etc. is read.
      Here, the actual DD table definitions are read in case of
      upgrade or minor downgrade.
    
    New handling of upgrade.
    ------------------------
    - Create two temporary schemas with unused schema names, store
      in 'dd_properties'.
    
    - Upon restart, the temporary schemas are dropped.
    
    - First initialize the meta data for the actual DD tables, and
      use this to open the actual tables.
    
    - Then create the target tables, and migrate the meta data from
      the old to the new tables.
    
    - Adjust object ids to simulate altering schema of the new
      and old DD tables at the end of DD upgrade.
    
    - Update properties for all tables, make sure removed
      tables are not reflected in the persisted properties.
    
    Add 'options' columns.
    ----------------------
    - Add a general purpose column to all DD tables that store
      DD entities (i.e.:
    
            catalogs,
            character_sets,
            collations,
            column_statistics,
            events,
            resource_groups,
            routines,
            schemata,
            st_spatial_reference_systems,
           *tables,
           *tablespaces
    
    - Plus a selection of important non-entity tables:
    
           *columns,
           *indexes,
            foreign_keys,
            triggers,
           *parameters
    
    - Tables prefixed by '*' above already have a column named
      'options' which can be used for this purpose.
    
    Miscellaneous.
    --------------
    - Set created/last altered when creating schema.
    
    - Add command line option for disabling automatic DD upgrade.
    
    - Change test for is_dd_table_name() to check for table types in the
      System_tables registry.
    
    - Change dictionary object type names for better conformity.
    
    - Remove *_type classes for the DD object classes.
    
    - Refactor object table usage
    
    Test changes.
    -------------
    - Extend dd_schema_definition_debug_c{i,s} to also record the
      CREATE TABLE statements for the DD tables.
    
    - Record new test results.
    
    - Mask out the DD version number from the SDI which is extracted
      from tablespace files in some tests.
    863aac45
    WL#9553: Upgrading the transactional data dictionary tables.
    Sivert Sorumgard authored
    This patch implements support for changing the DD table
    definitions.
    
    Overview.
    ---------
    The main changes are the following:
    
    - SE private data previously hard coded in InnoDB is now
      used only during first time server start. The meta data
      is stored in a DD table. On restart, the meta data is
      fetched from the DD table instead of InnoDB. Thus, we
      may have SE private data that can change.
    
    - During upgrade, we create the required target tables in
      a temporary schema, and migrate the meta data from the
      actual DD tables to the new target tables.
    
    - At the end of upgrade, we modify the persistently stored
      SE private data mentioned in 1) to that of the new target
      tables. We also adjust the schema ID of the target tables
      to simulate altering the schema of the tables. This way,
      we are able to switch from the old to the new DD version
      atomically. The temporary schemas are removed on next
      restart.
    
    In more detail, the patch implements the following:
    
    Performance schema.
    -------------------
    - Change in a performance schema test: Select only the PS_VERSION
      from 'mysql.dd_properties' to avoid reflecting irrelevant meta
      data in the result file, and thus to avoid unnecessary re-recordings
      of the result file.
    
    - Minor changes in the way Plugin_table_impl is used; replaced by
      Object_table_impl for more uniform code.
    
    - Approved by Marc Alff.
    
    Handler and handlerton API and InnoDB.
    --------------------------------------
    - The handler function 'get_se_private_data()' will now be
      called only during '--initialize'. During ordinary restart,
      it will be called once to get the SE private data for the
      'mysql.dd_properties' table, which can never change. This
      is the table that stores the SE private data for the other
      DD tables.
    
    - Modify 'get_se_private_data()' to be in line with what
      'dd_write_table()' does when storing SE private data:
    
      * Store se_private_data for columns. This is now done
        for DD tables in the same way as it is done for
        user tables.
      * Extend se_private_data for indexes to also store
        table_id and space_id. Again, this is done to get
        the same set of meta data for DD tables and user
        tables.
    
      Could dd_write_table() be used to ensure consistency in the
      meta data that is stored?
    
    - At the end of upgrade, we start over DD initialization to
      do the same as for a restart. Thus, we had to provide a new
      parameter to 'get_se_private_data()' to reset the counters
      for this to work with two phases of function calls in the
      case of upgrade.
    
    - We maintain a set in InnoDB of SE private ids of the DD tables.
      This set is used this instead of the hard coded id range used
      previously.
    
    - Replace hard coded ids of tables used in the processing
      of I_S queries by name based lookup.
    
    - Change the order of the DD and DDSE tables in the System_tables
      registry to make sure the table 'innodb_dynamic_metadata' is
      created on a low table id and index id. The motivation is that
      for now, this table must stay at fixed ids because it may be
      opened by InnoDB before the DD is available.
    
    - Approved by Jimmy.
    
    Extensions of 'mysql.dd_properties' and data structures.
    --------------------------------------------------------
    - Valid key/value pairs are explicitly defined:
    
        DD_VERSION                Actual DD version.
        IS_VERSION                Actual I_S version.
        PS_VERSION                Actual P_S version.
        SDI_VERSION               Actual SDI version.
        LCTN                      L_C_T_N setting used during
                                  --initialize.
        MYSQLD_VERSION_LO         Lowest server version which has
                                  been using the data directory.
        MYSQLD_VERSION_HI         Highest server version which has
                                  been using the data directory.
        MYSQLD_VERSION            Current server version.
        MINOR_DOWNGRADE_THRESHOLD The current DD can be used by
                                  previous MRUs, unless their
                                  target DD version is less than
                                  the downgrade threshold.
        SYSTEM_TABLES             List of system tabels with
                                  definitions.
        UPGRADE_TARGET_SCHEMA     Temporary schema used during
                                  upgrade.
        UPGRADE_ACTUAL_SCHEMA     Temporary schema used during
                                  upgrade.
    
    - Simplify Object_table, Object_table_definition,
      Plugin_table_definition, their subclasses and clients.
      Remove unnecessary functions, and rename according to
      usual naming rules. Merge Object_table* and Plugin_table*
      into one class.
    
    - Version number handling does not need to be part of these
      classes, this will be handled elsewhere, so it is removed.
    
    - Object table definitions now may hold definitions of both
      the target and actual tables.
    
    - Introduce explicit enumerations for options, indexes and
      foreign keys for the DD table definitions.
    
    - Explicitly define indexes needed by foreign keys.
    
    - Use the index enumerations when creating instances of
      object keys.
    
    - A new 'DD_bootstrap_ctx' class is introduced as an aid in the
      upgrade process, but also for normal DD bootstrapping. The
      handling of the bootstrap stages is moved into this class.
    
    Changes to the current DD initialization.
    -----------------------------------------
    - Extend the bootstrapper to create target or actual tables
      depending on context.
    
    - Change the way DD objects are flushed (during --initialize) and
      synced (during restart) to avoid problems with overlapping
      ID sequences for the scaffolding and the persisted object IDs.
      This is needed since the DD tables will no more be fixed on low
      IDs.
    
    - Add a new stage before creating tables where the inert table
      'dd_properties' is opened and the version numbers etc. is read.
      Here, the actual DD table definitions are read in case of
      upgrade or minor downgrade.
    
    New handling of upgrade.
    ------------------------
    - Create two temporary schemas with unused schema names, store
      in 'dd_properties'.
    
    - Upon restart, the temporary schemas are dropped.
    
    - First initialize the meta data for the actual DD tables, and
      use this to open the actual tables.
    
    - Then create the target tables, and migrate the meta data from
      the old to the new tables.
    
    - Adjust object ids to simulate altering schema of the new
      and old DD tables at the end of DD upgrade.
    
    - Update properties for all tables, make sure removed
      tables are not reflected in the persisted properties.
    
    Add 'options' columns.
    ----------------------
    - Add a general purpose column to all DD tables that store
      DD entities (i.e.:
    
            catalogs,
            character_sets,
            collations,
            column_statistics,
            events,
            resource_groups,
            routines,
            schemata,
            st_spatial_reference_systems,
           *tables,
           *tablespaces
    
    - Plus a selection of important non-entity tables:
    
           *columns,
           *indexes,
            foreign_keys,
            triggers,
           *parameters
    
    - Tables prefixed by '*' above already have a column named
      'options' which can be used for this purpose.
    
    Miscellaneous.
    --------------
    - Set created/last altered when creating schema.
    
    - Add command line option for disabling automatic DD upgrade.
    
    - Change test for is_dd_table_name() to check for table types in the
      System_tables registry.
    
    - Change dictionary object type names for better conformity.
    
    - Remove *_type classes for the DD object classes.
    
    - Refactor object table usage
    
    Test changes.
    -------------
    - Extend dd_schema_definition_debug_c{i,s} to also record the
      CREATE TABLE statements for the DD tables.
    
    - Record new test results.
    
    - Mask out the DD version number from the SDI which is extracted
      from tablespace files in some tests.
Loading