WL#7159: Move time zone tables and help tables from MyISAM to transactional storage

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

Up until MySQL 5.6 time zone tables and help tables are stored in MyISAM. For
robustness it is desirable to store these as transactional tables. These tables
are static and updated by normal DML.
FR.1 Provide possibilities to store the tables "help_topic", "help_category", 
"help_relation", "help_keyword", "time_zone", "time_zone_leap_second", 
"time_zone_name", "time_zone_transition","time_zone_transition_type" in InnoDB.
FR.2 Modify sql-scripts that are used to deploy new database and to upgrade
existent database in order to set the InnoDB as a default storage for the
system tables listed in FR.1.
FR.3 Don't disable the system tables listed in FR.1 to be stored in MyISAM by 
user request.
Transactional and non-transactional SE

This WL is about moving MyISAM system tables into InnoDB.

This WL however tends to use "non-transactional" instead of MyISAM and
"transactional" instead of InnoDB. The reasons for that are the following:

  - there is preliminary refactoring needed, which will be make the
    foundation of other WLs of this kind;

  - not all system tables are MyISAM. There are (or might be) CSV tables
    for instance. 

  - at the moment, we are talking about the Data Dictionary in InnoDB only,
    but in the future, we might want to the DD tables in NDB. Thus, it's
    better to talk in general terms from the start and highlight the current
    dependencies with / requirements from InnoDB.

Essential changes

In the scope of this WL the following system tables will be migrated to InnoDB
storage engine:
  - help_category
  - help_keyword
  - help_relation
  - help_topic
  - time_zone
  - time_zone_leap_second
  - time_zone_name
  - time_zone_transition,
  - time_zone_transition_type

WL#8003 and WL#7828 provide a way to do reads from the InnoDB system tables
within an attachable transaction.

The time zone and help tables are accessed in the read-only mode when
processing any user SQL statements (apart from the SQL statements on the
system 'mysql' schema).

The time zone tables can be accessed while processing SQL statements, which
involve other tables. For example:

   SELECT CONVERT_TZ(start_date, 'GMT', 'MET') FROM t1, t2 WHERE ...;

The help tables can be accessed only by the HELP statement, which does not
open user tables.

That means that:

  - the time zone tables must be opened in a nested THD context (as there
    are already opened user tables, and the server doesn't allow to open
    another set of tables once some tables have been opened);

  - the help tables can be opened in the user THD-context (as there are no
    user tables to open).

Although this distinction is important, both help and timezone tables will
be dealt with in the same way (using a nested THD context and attachable

Changes to InnoDB

In order to allow the listed system tables to be created with InnoDB
storage engine the function pointer handler::is_supported_system_table will
be initialized by the function innobase_is_supported_system_table() that
returns true in case when it is called for one of the system tables listed

Changes to SQL-scripts

The sql-scripts that are used to deploy new data base will be modified in
order to set the InnoDB as a default storage for the system tables listed

Effects on MTR test suite

In the current MTR test base there are a lot of tests that use the command
line options for disabling InnoDB engine. As long as there are system
tables in InnoDB, it is no longer possible to start the server without
InnoDB support. In order to move ahead with this WL those options have to
be removed. WL#7976 is dedicated for that.

Preliminary refactoring

The server provides open_system_tables_for_read() and close_system_tables()
functions, which are intended to respectively open and close MyISAM system

These functions will be renamed to open_nontrans_system_tables_for_read()
and close_nontrans_system_tables(). Then, a corresponding couple for
transactional tables will be added: open_trans_system_tables_for_read() and

It's expected that when all the system tables are migrated to InnoDB,
open_nontrans_system_tables_for_read() and close_nontrans_system_tables()
will be removed.

MyISAM and InnoDB interoperability

This WL won’t disable storing of the mentioned system tables in MyISAM
storage engine if a user requests it. That is, if a user executes
the statement

  ALTER TABLE time_zone_name ENGINE = MyISAM

the 'time_zone_name' table will be transferred to MyISAM.

Neither this WL will enforce MyISAM tables to remain in MyISAM.
That is, the user can do


to voluntarily move the 'proc' table to InnoDB
(mysql.proc is a MyISAM table at the time of writing this WL).

The server uses different code to deal with non-transactional (MyISAM)
and transactional (InnoDB) system tables. However, technically the code
dealing with transactional (InnoDB) tables is able to handle MyISAM

We will not put further restrictions on this code so that if user moved
system tables back to MyISAM, the server will continue to work.
This "feature" however should not be advertised in the user manual.

On the other hand, the switch from MyISAM to InnoDB will not be tolerated
and will result in a crash in the debug build and undefined behavior
(deadlock or crash) in the release build.

The users must not mess up with the system tables. That will be addressed
in the future by WL#6391 (Hide DD system tables from users).

Support for server downgrade.

It is possible to downgrade the server from version 5.7.5 that stores timezone-
and help- related tables in InnoDB to the previous version where such tables are
stored in MyISAM but such downgrade requires additional preparatory works to be 
done. Namely, before run downgrade procedure all timezone- and help- related 
tables have to be altered to set engine type to MyISAM, that is for every such 
table the statement
has to be executed.

Changes in initialization script used to fill help- related tables.
The proper operation of help-related statements requires that the help tables
in the mysql database be initialized with help topic information. This task is
done by processing the contents of the fill_help_tables.sql script.
The content of this file is created as part of source distribution process.
It should be noted that since help-related tables are stored now in
transactional storage engine, execution of INSERT statements to fill in
help-related tables has to be followed by explicit COMMIT. This fact either
has to be documented in the MySQL Reference Manual or explicit COMMIT has to be
added as the last statement in the script fill_help_tables.sql.

Changes in sql scripts generated by the utility mysql_tzinfo_to_sql.
After timezone- related tables will be migrated to transactional storage engine
the scripts for loading timezone description data into these tables has to be
changed. Namely, output of statements START TRANSACTION and COMMIT has to be
added to make the changes in system tables permanent. Scripts for loading
timezone- related tables are generated by the utility mysql_tz_info_to_sql,
so changes in output from this utility will be done.