WL#7160: Move plugin and servers tables from MyISAM to transactional storage

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

Up until MySQL 5.6 plugins and servers tables are stored in non-transactional
MyISAM. We would like to move these tables to transactional storage. 

These tables are mostly static but updated by special DDL.
FR.1 Provide possibilities to store the tables "plugin", "servers" 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:

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

  - at the moment, we are talking about 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:
  - plugin
  - servers

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

The plugin and servers tables are accessed as read-write.

There are dedicated DDL statements to operate on these tables:

These statements are DDL which means they force implicit commit
before/after statement execution. Thus, these statements can be
handled without the need for nested RW-transaction support.

Although INSTALL/UNINSTALL PLUGIN statements are DDL by implication, the
flag CF_AUTO_COMMIT_TRANS wasn't set for the commands SQLCOM_INSTALL_PLUGIN,
SQLCOM_UNINSTALL_PLUGIN in the server before version 5.7.6. This WL fixes
this issue.

Since plugins are loaded on the server startup the plugin table
used to read plugin information has to be opened in a nested transaction

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
above. Similar changes will be done in sql-scripts used to make server upgrade.

Effects on MTR test suite

In the current testsuite there is i_main.audit_plugin_debug test that
expects MyISAM as a storage engine for the plugin system table.
This test checks whether handling of the INSTALL/UNISTALL PLUGIN statements
is correct with regard to acquiring/releasing of LOCK_plugin mutex.

The test sets debug variable myisam_pretend_crashed_table_on_usage to simulate
error on working with MyISAM table. Since this debug variable is handled inside
MyISAM storage engine source code. After the plugin table has been migrated to
InnoDB the test started to fail.

In order to keep the test checks for correctness working with
LOCK_plugin mutex the debug variable handler_crashed_table_on_usage will be
introduced that serves the same goal as the
myisam_pretend_crashed_table_on_usage variable. However it will be exposed to
the upper level and therefore will be independent of storage engine
being used by the plugin table.

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


the 'servers' table will be migrated 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.

Support for server downgrade
It is possible to downgrade the server from version 5.7.6 that stores plugin and
servers 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 the plugin and servers tables
have to be altered to set engine type to MyISAM, that is for every such
table the statement


has to be executed.

Associated refactoring
Since this WL indirectly relates to handling of statements
INSTALL/UNINSTALL PLUGIN implementation of this statements will be moved to 
separate classes derived from Sql_cmd.

Changes in implementation of low-level system table API
The following block of code will be removed from the function 

if (t->table->s->table_category != TABLE_CATEGORY_SYSTEM)
 // Crash in the debug build … 
 DBUG_ASSERT(!"Table category is not system”);

 // ... or report an error in the release build.
 my_error(ER_UNKNOWN_ERROR, MYF(0));

This change will be done since some system tables have table_category
different from TABLE_CATEGORY_SYSTEM. For example, such tables are
general_log, slow_log, slave_relay_log_info.