WL#9553: Upgrading the transactional data dictionary tables.

The MySQL server must support changing the definition of the data dictionary
tables between two mysqld server versions. At server restart, the server checks
if upgrade is needed (and possible), based on persistent version information. If
upgrade is not needed, the server continues with an ordinary restart. Otherwise:

1. Create a new set of DD tables with the desired table definitions.

2. For each dictionary object table, copy the persisted (meta) data from
   the old DD tables (which need to be upgraded) to the new ones (which
   have the desired definition).

3. Swap the old and new DD tables atomically, and perform DD initialization
   over again, this time using the desired table definitions.

It should be noted that binary downgrade (start the old binaries on an upgraded
data directory) is in general not possible for all DD upgrades, since the old
binaries know nothing about the changes that have been done during upgrade to a
newer version. This implies that upgrade of DD tables between minor versions is
officially *not* supported; however, the worklog will still try to provide some
support for this use case, since it is likely to happen some time anyway.

It should also be noted that this worklog is targeting the upgrade of the DD
tables only. Upgrade of the entire server, and coordination of the upgrade of
the individual server modules, is not the responsibility of this worklog.

1. Changes supported in an upgrade.
===================================
The definitions of the DD tables can change from a server version to the next,
but it is recommended to keep the amount of changes to a minimum, due to the
potential impact on server availability.

Deciding which kind of upgrade is supported in various situations is not only a
technical question, but also a political one. It is relevant to distinguish
between minor and major releases when discussing what DD changes may be 
supported.

1.1 Upgrades supported in minor releases.
-----------------------------------------
Officially, we do not support DD upgrades in minor releases. The motivation is
twofold: To avoid substantial changes in MRUs that may have an impact on system
critical components, and to enable binary upgrade/downgrade. However, for a
limited set of changes, some support may be provided (e.g., we will provide
support for binary downgrade in these cases):

i)   Addition of new attributes to a predefined general purpose
     option-like field.
ii)  Addition of a column at the end of the table definition.
iii) Addition of elements at the end of an enumeration column type.
iv)  Extension of a VARCHAR field.
v)   Addition of an index on a column.

In addition to the changes above, some support may be provided for the 
following changes, depending on the circumstances:

vi)  Addition of a unique index or FK  might be problematic (the older version
     will not know that it needs to enforce this constraints, i.e. constraints 
     will be enforced, but on the DD layer, not by the SQL-layer code.
vii) New tables might be problematic, depending on how isolated they are.

It is still technically possible to do other changes in a minor release of
course, but the price we pay is that binary downgrade will not be supported.

It should be noted that:

* Changes like iii) and iv) above may be allowed in the sense that we will be 
  able to open the more recent table definitions (which contain e.g. an 
  extended VARCHAR field) in the previous server version. However, the previous 
  server version may or may note be able to handle the data stored there in a 
  reasonable way. If there e.g. is buffer sizing that depends on the field
  definition, server may not be able to handle data stored in the newer server
  version. Thus, each case must be considered carefully to determine whether
  changes like these may be permitted in a minor upgrade.

* After an upgrade which adds e.g. a column, if the server is downgraded to a 
  previous version, and then re-upgraded, the new column will already be in 
  place. However, in this case, the data stored in that column may not be 
  consistent with the rest of the data (since the previous version did not
  know how to maintain the added column). In this case, the data must be re-
  initialized upon the second upgrade. This also applies to scenario i) above, 
  and this is relevant even with a fixed DD table schema, i.e., it is relevant
  even outside the context of DD upgrades.

* The added support for downgrades within a GA cycle has implications that
  must be handled, e.g. if the SQL DDL semantics change. This will be discussed 
  further below.

1.2 Upgrades supported in major releases.
-----------------------------------------
For a major release, we are not required to support binary downgrade, which 
gives us more freedom in what changes we can make. However, we cannot list
specific changes that are allowed or prohibited, because, the issue here is
not only about changing the DD table definitions and migrating the meta data
from the old to the new version, the problem is that to allow upgrade, we must
start the server using the old data dictionary, and support some subset of the
server functionality until upgrade is completed. If the changes in the DD 
tables are major, they will also affect the internal structure of the 
dictionary objects, and the server functions that rely on these objects. Hence, 
there will be server code that will need to make its execution conditional, 
depending on the dictionary version from which we upgrade.


2. Functional requirements.
===========================
FR1. There shall be a new command line option to prevent automatic upgrade of
     the DD tables: --no_dd_upgrade. The default behavior is that a 
     starting server will upgrade the DD tables if necessary, unless this 
     option is submitted.

FR2. There shall be a dedicated DD version number. We will use the version
     number of the mysqld server where the set of DD table definitions were 
     first introduced, as the DD version number. This is consistent with the 
     versioning scheme for performance schema and information schema.

FR3. Upon a server restart, if the DD version number stored in the DD is the
     same as the server's own DD version number, the restart will be carried
     out as usual without any change of the DD table definitions.

FR4. Upon a server restart, if the DD version numbers (as explained in FR2 and 
     FR3) differ, and if the command line option '--no_dd_upgrade' is
     submitted, the server will exit with an error message ER_DD_UPGRADE_OFF.

FR5. Upon a server restart, if the DD version numbers (as explained in FR2 and 
     FR3) differ, and if the command line option '--no_dd_upgrade' is NOT 
     submitted, the server will check to see if it is capable of upgrading from
     the actual DD version to the target DD version. If it's capable of doing
     so, the actual DD version is considered 'supported' as far as upgrade is 
     concerned.

     If the actual version is not in the list of supported versions, the server
     will check to see if this is minor downgrade. If it is not minor downgrade,
     then the server will exit with an error message 
     ER_DD_UPGRADE_VERSION_NOT_SUPPORTED.

     If this is an attempt at minor downgrade, but the newer version is not
     possible to donwgrade (because it is marked as a non-downgradable version),
     then the server will abort restart with the error message 
     ER_DD_MINOR_DOWNGRADE_VERSION_NOT_SUPPORTED

FR6. 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

     shall have a column which may be used to handle in-GA changes. Thus, 
     MRUs may add information to this column as they see fit. However, binary 
     downgrade must still be supported, so information in other columns, which 
     is relied upon by previous MRUs, must still be maintained.

     In the list above, tables prefixed by '*' already have a column named 
     'options' which can be used for this purpose. For the other tables in the
     list, such a column shall be added by this worklog.

FR7. The DD upgrade shall be atomic, meaning that the server shall not be left 
     in a state where some of the DD tables are upgraded, and some are not. If 
     upgrade completes, the server shall keep running, and it shall be using 
     the new DD version.

FR8. The DD upgrade shall be atomic. If upgrade fails, the server shall exit.
     If the old server binaries, supporting the old DD version, are used to 
     restart the server, it shall be using the old DD version. If the new 
     server binaries are used to restart the server, it shall retry DD upgrade.

FR9. After a successful DD upgrade, it is in the general case not possible to
     restart the server with the old server binaries, i.e., binary downgrade
     is not supported. This means that DD upgrade is not officially supported
     between minor server versions.

FR10. Upgrade will be supported for the following DD tables:

        catalogs,
        character_sets,
        collations,
        column_statistics,
        column_type_elements,
        columns,
        dd_properties,
        events,
        foreign_key_column_usage,
        foreign_keys,
        index_column_usage,
        index_partitions,
        index_stats,
        indexes,
        parameter_type_elements,
        parameters,
        resource_groups,
        routines,
        schemata,
        st_spatial_reference_systems, 
        table_partition_values,
        table_partitions,
        table_stats,
        tables, 
        tablespace_files,
        tablespaces,
        triggers,
        view_routine_usage,
        view_table_usage

FR11. There will also be infrastructure to support creating new definitions of
      the InnoDB specific tables:

        innodb_ddl_log,
        innodb_dynamic_metadata,
        innodb_index_stats,
        innodb_table_stats

      The actual migration of meta data from old to new versions of
      the InnoDB specific tables can be handled by InnoDB itself, or it
      can also be handled at the SQL layer for now.


3. New messages for errors and notes.
=====================================
ER_DD_UNEXPECTED_TABLE_DEFINITION
  eng "Unexpected definition of dictionary table '%s'."

ER_DD_INITIALIZE
  eng "Data dictionary initializing version '%u'."

ER_DD_RESTART
  eng "Data dictionary restarting version '%u'."

ER_DD_UPGRADE
  eng "Data dictionary upgrading from version '%u' to '%u'."

ER_DD_UPGRADE_OFF
  eng "Data dictionary upgrade prohibited by the command line option 
       '--no_dd_upgrade'."

ER_DD_UPGRADE_VERSION_NOT_SUPPORTED
  eng "Upgrading the data dictionary from dictionary version '%u' is not 
       supported."

ER_DD_MINOR_DOWNGRADE
  eng "Data dictionary minor downgrade from version '%u' to '%u'."

ER_DD_MINOR_DOWNGRADE_VERSION_NOT_SUPPORTED
  eng "Minor downgrade of the Data dictionary from dictionary version '%u' is 
       not supported."


4. New command line options.
============================
--no_dd_upgrade: If the server starts, and finds a data directory with a data 
    dictionary version different from the server's target version, the server 
    will exit.

1. Definitions.
===============
* Actual : The persistently stored DD version or
  DD table, i.e., what is reflected in the persistently stored mete data.
* Target : The DD version or table which the server
  is using during normal operation. If the actual DD version is different
  from the target DD version, upgrade is required. For the limited set of
  operations needed during upgrade, the server is able to use both the
  actual and the target version at the same time.


2. Prerequisites.
=================

2.1 Supporting lightweight in-GA "upgrades".
--------------------------------------------
We will support in-GA upgrades by adding a general purpose column which can be
used for storing additional meta data, unless such a column exists already. We
will also add support for server restarts using newer DD table definitions that
are pure extensions.

2.1.1 Adding a general purpose column.
---------------------------------------
The tables listed in FR6 shall have a column to store arbitrary key/value based
meta data to support lightweight upgrades within a GA lifecycle, i.e., between
minor versions. Thus, they will have a column like:

CREATE TABLE mysql. (..., options MEDIUMTEXT, ...);

This column will be treated as a dd::Properties object internally, i.e., a list
of key=value pairs. If MRUs need to store additional meta data, the column can
be used for this purpose, instead of doing a change of the table definition.
Thus, an MRU can e.g. add one or more key=value pairs to the 'options' column. 
The MRU adding a key should take absence of keys to mean some default value in 
order to avoid adding keys for all rows in the table.

For this to work, we must ensure that all changes extend the previous data, 
i.e.:

- Even if information is added to 'options', information may not be
  removed from other columns, because previous MRUs must be able to restart
  using the same data directory.
- If a new MRU extends the set of valid key=value pairs, it must still support 
  the already existing ones. Thus, if a previous MRU starts, using the same 
  data directory, it can safely assume that the key=value pairs it needs are 
  indeed present.
- If a new MRU extends the set of valid key=value pairs, starts storing these
  new keys, and the previous MRU is then restarted on the same data directory,
  the previous MRU will obviously not maintain the new keys. Thus, if we at a
  later stage upgrades to the new MRU, we will risk that there are key=value
  pairs stored that are not consistent with the rest of the meta data anymore.
  This means that on a repeated upgrade to a new server version (this is 
  relevant even outside the context of a DD upgrade, since we may add new keys
  to these columns without changing the DD version), we need to make sure the 
  new keys are re-initialized.

For each case, it must be considered whether downgrade is safe or not. If the 
added key/value pairs can safely be ignored in previous versions, downgrade may 
be supported. If the key/value pairs cannot be ignored, downgrade must be 
prohibited.

2.1.2 Supporting downgrade of pure DD table extensions.
-------------------------------------------------------
While the approach outlined above will solve some required changes for in-GA
upgrades, we still miss support for e.g. adding indexes. It is not unlikely 
that this will be needed. In order to still allow downgrade to previous MRUs, 
we will store the SQL DDL statements, and the SE private data of the DD tables, 
into the 'mysql.dd_properties' table; thus, an older version is able to restart 
using the upgraded DD tables. If the new table is a simple extension of the old
one (e.g. just adding columns at the end), then the old version should be able
to execute as usual, simply ignoring the additional extensions defined in the
upgrade.

This has the implication that if a new server version has changes in the SQL 
DDL semantics that affects the meta data created for the DD tables, there must 
be code to handle that without changing the SQL DDL which is stored 
persistently. I.e., for the stored SQL DDL statements, execution of the same 
statements must result in the same meta data for both the new server version, 
and for older versions to which binary downgrade is supported. The new server 
version may handle changes in SQL DDL semantics in two ways:

- Change the SQL DDL statements before it is executed, without changing the
  SQL DDL statement that is stored persistently.
- Change the generated dictionary objects after they are created, before they 
  are stored persistently.

So between MRUs, we will try to keep "canonical" SQL DDL statements stored in 
'mysql.dd_properties', in order to support minor upgrade/downgrade (the same 
canonical statement should result in the same low level meta data), while at 
the same time being able to support changes in DDL semantics (changes in DDL 
semantics can be countered by modifying the SQL statement before executing it, 
but without changing the canonical statement stored in 'mysql.dd_properties').

As an example, let us say that server version 8.0.4 creates the DD tables with 
the default compression table option ('none'). E.g. (as a simple example), 

DDL_804= CREATE TABLE catalogs (id BIGINT AUTO INCREMENT, name VARCHAR(255), 
         PRIMARY KEY (id)) ENGINE=InnoDB;

So when the 8.0.4 server is initialized, statement DDL_804 is stored in 
'mysql.dd_properties'.

Further, let us assume that server version 8.0.5 (an MRU) extends the table 
above by an additional column (which is by itself minor downgradable), while at 
the same time, the default compression table option is changed from 'none' to 
'zlib'. We could change all the target DD table DDL statements in 8.0.5 to 
explicitly specify 'COMPRESSION=none'. However, this would mean that all the 
DDL statements are changed, which is not desirable since a change in the DDL 
statements indicates that there is a new verison of the table (while in this 
case, that is true only for the catalogs table, which has an added column).

So a better alternative is to handle the changed default by keeping the target 
statements unchanged, and just modifying them in 8.0.5 before they are 
executed. So, in 8.0.5, we would have a canonical target table definition like:

DDL_805= CREATE TABLE catalogs (id BIGINT AUTO INCREMENT, name VARCHAR(255), 
         new_col INT, PRIMARY KEY (id)) ENGINE=InnoDB;

Then, before execution we would add ‘COMPRESSION=none’ to the statement above.
However, in the DD_properties table, we would store the unmodified canonical 
statement DDL_805, so when it is executed in a 8.0.4 context during a minor 
downgrade, we would use the default compression in 8.0.4, which was 'none'. 
Thus, we may in fact say that the canonical statements stored in 
'mysql.dd_properties' are on the old server version's format.

An alternative to changing the DDL statement before execution is to modify the 
meta data as part of DDL execution. This would also need to be done on the new 
MRU to make sure the semantics is the same as on the old MRU. It is probably 
better to modify the SQL statement before execution, though, in order to avoid 
polluting DDL execution with special code for handling DD tables.

2.2 Storing hard coded meta data.
---------------------------------
When restarting the server, we need to have certain SE private data available 
in order to open the DD tables, e.g. the table's se_private_id. However, we 
need a more flexible solution for persisting this hard coded meta data. 
Currently, for each DD table, the following is assigned by InnoDB based on hard 
coded values in source code:

* dd::Table::se_private_id
* dd::Table::tablespace_id
* dd::Index::se_private_data[root page number and space id]
* dd::Index::tablespace_id

This restricts the options we have regarding changing the DD tables. Thus, we
will use the values that are hard coded in source code only during initial
start. The hard coded meta data will then be stored in the 
'mysql.dd_properties' table, and during server restart, we will get the hard
coded meta data from 'mysql.dd_properties' instead of getting it from the SE,
(except for the meta data for the 'mysql.dd_properties' table itself; this meta
data must be retrieved from the SE even during restart, otherwise we will not 
be able to open it to get the meta data for the other DD tables).

The motivation for this is to allow SE private data to change during an 
upgrade. This means we can allow modifications that result in e.g. new SE
private ids, since we can update the 'mysql.dd_properties' table with the new
meta data.

2.3 DD version number format.
-----------------------------
We will not officially support DD upgrades between MRUs of a GA, unless 
critical issues require this to be done. If this is needed, and if we at the 
same time have more recent GA releases, it will be helpful to have a two-level 
DD version number, since otherwise, the version sequence will be quite awkward.

Thus, we will adopt the same versioning schema as employed by performance 
schema: The numbering to use is the MySQL version number of the first MySQL 
version that published a given database schema. The format is Mmmdd with 
M=Major, m=minor, d=dot, so that MySQL 8.0.4 is encoded as 80004. In case of -
dash version numbers, encode MySQL 8.12.34-56 as 8123456.


3.Upgrading the DD tables.
==========================
Throughout the upgrade procedure below, the server will be single threaded, so
we don't need to take e.g. concurrent DDL execution into account.

3.1 Upgrade procedure.
----------------------
1. Compare the actual DD version number (stored in the 'mysql.dd_properties'
   table) and the target DD version number (define in the binaries) to
   determine if upgrade is needed.

2. Check if the actual DD version number is supported (i.e., if the server is
   able to upgrade from that DD version). The list of supported DD versions is
   represented in source code within the DD module. If the actual DD version is 
   not supported, err out, unless this is a minor downgrade. If this is a minor
   downgrade, continue with an ordinary restart based on using the actual DD 
   table definitions.

3. Create the meta data for the tables of the actual DD version. This is done
   by retrieving the stored SQL DDL statements from the 'mysql.dd_properties'
   table and executing them. Further, add the generated meta data to the DD 
   cache, i.e., keep the meta data only in memory. The meta data is needed to 
   open the actual DD tables.

4. Look into the DD table 'mysql.dd_properties' to see if the two schema names
    and  are defined. If so, remove the two schemas and
   their contents if they exists (see below for an explanation). Also, remove 
   the two schema names from 'mysql.dd_properties'.

5. Generate two unique schema names  and . Update the 
   table 'mysql.dd_properties' with the two names and commit. These two 
   schemas are used as a sandbox while doing the upgrade.

6. Create a new empty schema , i.e., using the schema name we just
   created in the previous step. The new schema will only be used temporarily.
   Also create another temporary empty schema , to be used while
   swapping the DD tables (see below).

7. Create the target DD tables in schema  in the DD tablespace 
   (i.e., the same tablespace as the existing DD tables). Thus, swapping the
   new and old DD tables boils down updating meta data in the DD tables. The 
   meta data of the target DD tables is now represented in the actual DD 
   tables. The tables are created using the target SQL statements, which are 
   auto committed.

8. Copy the meta data from the actual DD tables to the target DD tables. This
   will include possible modification of values, e.g. if new columns are added,
   and values need to be filled in based on existing meta data. In this step, 
   we can also do analysis to catch invalid or deprecated meta data.

9. Finalize the target DD tables. At this point, they can still be considered
   ordinary user tables, and updates are done by means of DML statements.

   * Update the schema id of the actual DD table entries (i.e., the entries
     present in the target DD tables) to the  id.
   * Update the schema id of the target DD table entries to the id of the
     'mysql' schema.

   The target DD tables are now mostly ready for replacing the actual DD
   tables. Note that this step is not committed before the next step is
   completed, to ensure an atomic swap of tables. The remaining step is to
   make sure the target tables are used instead of the actual DD tables when 
   the server is restarted.

10. Update the persistent meta data in 'mysql.dd_properties' (i.e., replace the 
    SQL DDL statements and the SE dependent data from the actual DD tables by 
    the same meta data from the target DD tables). Also update the DD version 
    number to the target DD version. After commit, a restart will use the new 
    target DD tables.

11. Re-initialize the DD cache, and do a new DD initialization (without 
    restarting the server) i.e., start over from step 1 above.

The normal restart procedure must be extended to do the same as step 4 above
(i.e., clean up the sandbox after a successful upgrade).

3.2 Notes.
----------
N1) DD table meta data is located in a "lookaside buffer" in the storage 
    adapter. This is initialized when the DD is initialized, and is based on 
    the information stored in 'mysql.dd_properties'.

N2) Beyond storing the meta data for the new target DD tables, the actual DD 
    tables are not modified. Thus, if there is a failure before step 10, we can 
    restart the new binaries with the old, actual DD tables (i.e., upgrade will 
    be retried); or we can restart with the old binaries (which will find the 
    sandbox schemas in 'mysql.dd_properties' and remove them and their 
    contents).

N3) We need to upgrade into the same tablespace due to InnoDB constraints 
    regarding the DD tablespace id, file name etc., but also due to the 
    difficulty of ensuring atomicity when swapping tablespaces (because we need 
    to access the file system).


4. Version dependent source code.
=================================
A major issue in this context is to structure the upgrade related source code 
in a way that does not pollute the rest of the source code, and which allows
code needed to support a given DD version to be removed easily. We need to
maintain version dependent instances of at least the following:

* Code for storing/reading DD objects and their children.
* Code for storing/reading the DD_properties (if keys are added or removed).
* The object table definitions (including field enumerations).
* Key creation for retrieving objects from the DD tables.
* System table registry.
* Index numbers (currently hard coded in various object key related classes).

It is absolutely vital to write the version dependent code in a well organized
way to avoid chaos. It is also vital to organize the code in a way that lets us
easily identify and remove code that is dependent on a version which is not
supported anymore.
            
                                    

                
1. Compatibility issues.
========================
We are basing the approach on SQL DDL statements, and will therefore face
problems when DDL semantics change, e.g. an SQL level type is mapped to a
different internal type. There are three ways we can handle this.

a) Changes affecting the upgradability of old supported versions can be
   handled by checking for the actual version from which we upgrade, and 
   modify the dictionary objects that are generated after the CREATE TABLE 
   statement has been executed. Here, 'version' may mean both specific DD 
   and MySQL server versions. This approach can also be used to adjust meta
   data created for the target tables. This may be needed to preserve 
   downgrade support to previous MRUs.

b) We can patch up the CREATE TABLE statements internally in the server 
   before they are executed. This means that te statements must be stored in
   a structured way, both for the target definitions in the binary, and for 
   the actual definitions in the 'mysql.dd_properties' table, not as 
   complete statements. Thus, we can change e.g. the SQL level type of 
   specific fields before the statement is executed.

c) For changes that affect the execution of the target DDL statements, we
   can change the target table definitions and define a new DD version 
   number. This will be a problem in minor releases, though, if we execute 
   downgrade by creating the DD table definitions based on the contents of 
   the 'mysql.dd_properties' table, but execute the statements in the old 
   server environment. Thus, this alternative should probably be restricted
   to major releases.

During minor upgrades we will store the table definitions as SQL DDL which is 
fully compatible with the older MRUs, and will adjust their execution on newer 
MRUs, either by modifying the SQL before execution, or by modifying the 
resulting meta data, as explained in a) and b) above. So the SQL DDL defining 
the table is stored with the previous MRUs in mind, and if there is a change in 
the SQL DDL semantics, then the difference between the behavior of the old and 
MRU will be handled by patching up the SQL DDL, or adjusting the SQL DDL 
execution in the newer version.

2. Supporting in-GA DD upgrade.
===============================
a) Add a general purpose column to support new needs without schema changes:
   * Add a new MEDIUMTEXT column to the DD tables, intended to store
     a general purpose property string.
   * Add new member and access functions as needed when starting to
     use this.
   * Provide support for ensuring consistency of the new key values after a
     downgrade and repeated upgrade, even if skipping several MRUs.

b) Support schema changes that are pure extensions:
   * Store a structured representation of the SQL DDL statement for
     each DD table in 'mysql.dd_properties'.
   * Upon in-GA downgrade (restarting an older MRU using the DD from a newer
     MRU with a newer DD version), continue ordinary restart using 
     the actual DDL statements store in 'mysql.dd_properties'.
   * We should check version numbers to verify that this is indeed an
     in-GA downgrade to prohibit attempts at downgrading to another major 
     version. We should also store, in 'mysql.dd_properties', the lowest
     version number to which in-GA downgrade is possible.
   * If the schema changes are only extensions, then an older server
     binary should be able to start with a more recent DD version.
   * Compatibility issues handled by approach 1.a or 1.b should make sure
     the semantics of the SQL DDL execution stay the same (i.e., the newer MRU
     must handle differences in SQL DDL semantics by modifying the SQL DDL 
     after the statements has been stored, or by modifying the generated meta 
     data.
   * We must provide support for ensuring consistency of the new data which is
     stored after a downgrade and repeated upgrade, even if skipping several 
     MRUs.

c) Above, a 'structured representation' means that we must support storing
   enumerations of e.g. fields, indexes, options, etc:
   * Store explicit enumeration of fields.
   * Store explicit enumeration of indexes.
   * Store explicit enumeration of table options.
   * Store explicit enumeration of foreign keys.

3. Command line options.
========================
* Add new command line option '--no_dd_upgrade'.
* Default should be 'false' (i.e., DD upgrades will happen automatically by
  default).
* Setting it to 'true' shall have the effect that a restart will be aborted
  if upgrade is required (i.e., if the actual DD version is different from 
  the target DD version).

4. Store additional version information in 'mysql.dd_properties'.
=================================================================
* Add server version number: High, low, current.
  These are not DD version numbers, but the version of the server binary.
* Add SDI version number.
* Add LCTN (the value used during --initialize).
* Add MINOR_DOWNGRADE_THRESHOLD to represent how far back (in terms of the DD 
  version number) we can downgrade.
* Add list of target system tables with definitions.
* Update MYSQLD_VERSION if the stored value is different from the starting 
  server version.
* Update MYSQLD_VERSION_LO if the stored value is higher than the starting 
  server version.
* Update MYSQLD_VERSION_HI if the stored value is lower than the starting 
  server version.
* Update SDI_VERSION if the stored value is lower than the starting server SDI 
  version.
* Comparing version numbers is done by the standard comparison operator.

5. Store and retrieve hard coded meta data and SQL DDL statements.
==================================================================
* Change create_dd_system_table() to fetch SE private data from the
  SE only during --initialize.
* Store SE private data in 'mysql.dd_properties' during --initialize.
* Fetch SE private data from 'mysql.dd_properties' during restart and upgrade.
* Store SE private data also for columns.
* Store structured representation of the SQL DDL statements. Should separate 
  type from column name. Should store enumerations of fields and indexes.
* Must store this after the DD tables are created to avoid unexpected glitches
  in the root page numbers.
* Store the system table index, to be used for initializing the System_tables 
  singleton.
* We must keep in the source code all Object_table classes that are referenced 
  in any supported version. The System_tables registry should be populated with 
  all known Object_tables from all supported versions.

6. Handling SE private data during --initialize.
================================================
* InnoDB must add additional SE private data for columns and indexes, 
  e.g. the 'table_id' and 'space_id'.
* We must keep track of the set of SE private ids used by the DD tables,
  since this is needed by InnoDB. This must replace the current range-based
  comparison that are used in InnoDB, since we cannot rely on ids staying 
  within a certain range since the DD tables can be re-created.
* Change handlerton method to support upgrade (i.e., update SE private data
  for 'dd_properties' during the restart phase after upgrade).

7. Change the Object_table_definition.
======================================
* Clean up version number usage, should probably get rid of the '==0' special 
  handling for inert tables.
* Let the Object_table contain a target definition and an actual definition.
* Make enumerations of fields and indexes explicit.
* Define fragments of SQL DDL statement. Should separate type from column name.
* Have a mechanism to make a definition persistent, or to instantiate one 
  based on persistent data.

8. DD version number format.
============================
* Use the server version number which first published a given DD schema
  as the DD version number.
* Separate out the version information in a single .h file under sql/dd.
* Update version info at end of initialize and restart. Update server
  version numbers.

9. InnoDB recovery needed before allowing DD table upgrade.
===========================================================
* Discussed with Jimmmy, seems ok. At the time we plan to do the upgrade, 
  recovery is completed, and the purge threads are not started.

10. Supporting upgrade of InnoDB internal tables.
=================================================
* Can associate changes of these tables with the DD version and treat them in a 
  similar way to upgrade of the other DD tables for now.
* For server restart (with or without upgrade), the actual table definitions 
  are fetched from 'mysql.dd_properties'.
* For server restart with upgrade, we get the target table definitions from 
  'dict_init()'. The target tables are created and upgraded using the same
  scheme as for the other DD tables.
* We may use an additional handlerton call to let InnoDB take care of the 
  migration from the old to the new tables: 'dict_upgrade_predefined_tables()'.
  Otherwise, we may also handle this at the SQL layer for now.

11. Verification and consistency checking.
==========================================
* We may add verification to the DD bootstrapping when the DD 
  tables are being created during --initialize, restart or upgrade. This 
  verification can be based on comparing actual SQL DDL statements.
* We must prohibit accidental and unintentional changes of the DD table 
  definitions in minor versions.
* We must also catch changes in the DDL semantics affecting the execution of 
  the SQL DDL statements for the DD tables.
* We should probably modify the dd_schema_definition tests to mask platform 
  differences, making it easier to maintain. It should also store the actual
  SQL DDL statements to catch changes of the statements.
* Tests suggested in the QA notes section should take care of these needs.

12. SDI handling and cleanup.
=============================
* We must probably re-generate all SDI if there is a change of the SDI version 
  at the same time as there is a DD upgrade.
* There could in theory be a change of SDI version without a DD upgrade, e.g. 
  if there are major bugs in the SDI format.

13. Supporting multiple versions.
=================================
There are at least three different aspects of this issue that we must take into
account.

a) DD object APIs:
   * The public API of the DD object types should reflect the target DD
     version. If a field is removed (or is not being used by the server code, 
     yet still stored in the DD tables), it should not be available through
     the public API.
   * The internal API (the *_impl classes) should reflect the union of
     the supported DD versions. If a field is removed, its corresponding
     data member should remain present until the last DD version where
     it was still persisted is not supported anymore.

b) When detecting an upgrade, we must:
   * Create an Object_table_definition instance 'm_actual_version' within
     each Object_table.
   * Parametrize code that needs to use the field or index enumerations, or
     the system table names, e.g.:
     - register_tables().
     - Key creation and updating.
     - Storing/reading record.
   * Provide support for default value assignment for unknown fields while
     migrating data from the actual tables to the target tables.

c) In the source code, we must refer to version labels in a way that lets us 
   easily detect code referring to a version that is not supported anymore so 
   we can clean up the version handling. E.g. instead of:

   if (actual_version == 80004)
   { ... }

   we can add a constant:

   const int DD_VERISION_80004= 80004;

   and use a function to do this check:

   if (actual_DD_version_is(DD_VERSION_80004))
   {...}

   where

   bool actual_DD_version_is(uint version)
   {
     DBUG_ASSERT(is_supported_version(version));
     return actual_version == version;
   }

   We may also provide abstractions helping out here, allowing us to associate
   capabilities with versions, so we can check for e.g.:

   if (DD_supports(DD_capability_label::CATALOGS))

   instead of relying on checking specific version numbers.

14. Miscellaneous changes.
==========================
* Allow initialization/restart/upgrade if '--read_only' is set. Reject only
  if '--super_read_only' is set.
* Rewrite charset/collation re-population, check 'super_read_only' instead
  of '--read_only'.
            
                    
    
        


    
    

        Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.