WL#6381: Handler API changes for new dictionary

Affects: Server-8.0   —   Status: Complete

This is an umbrella task which covers sub-tasks for scenarios that
would require changes to handler API due to new data dictionary
implementation.

Following are some of such scenarios:


Using non-locking reads for DD tables used by I_S views:
================================================================
A SELECT query would use both I_S table and user table. Data for
I_S tables are retrieved from DD tables. All tables involved in a
query would use active isolation level set by user
during execution of the query. Suppose that the isolation level
is higher than 'repeatable read', then the read on DD table would
block. The requirement is that we should always use non-locking reads
while reading DD tables when processing SELECT on I_S table.

This requires special handling. InnoDB has to be informed that MySQL
is interested in reading particular DD table in non-locking mode.

This task has two parts in it,

a) Making MySQL server identify table handles for DD tables that belong
   to I_S system view. 

b) Conveying InnoDB to use non-locking reads for table handle's 
   found in part 1). 


Enable storage engines to manipulate se_private_data in DD objects:
===================================================================
se_private_data contain key value pairs that are private to storage
engine (SE). SE would need a way to get access to DD object so that
it can manipulate se_private_options.

One way to achieve this is to provide access to the DD object to
SE through TABLE object.

Required changes to DDL-related parts of API
============================================
We need to extend parts of SE API related to DDL (and related code
on SQL-layer) in order to support auxiliary columns (InnoDB-specific),
tables (needed for FTS) and to ensure DDL atomicity.

Extending handler interface to support serialized DD metadata:
==============================================================
We need to store serialized representation of DD info in tablespace
files in order to support more convenient transportable tablespaces
and metadata redundancy.

InnoDB should place DD tables in separate dd  tablespace:
========================================================
DD tables should use a separate tablespace that contains
all the global data dictionary tables.  This tablespace needs to be available
when during bootstrap so that the runtime server can read the data dictionary.


Option validation
=================

When accessing, creating or altering a table:

The storage engine may validate all options (such as indexes.options), and
refuse the operation if there are unknown, unsupported or mutually incompatible
attributes.

This is a change from the *.frm file. In *.frm files, MySQL would simply ignore
unknown or unsupported options. However, there were some format flags in the
InnoDB data dictionary that would protect us in case of a downgrade.

Accessing/modifying se_private_data from SE
=========================================== 

CREATE TABLE, ALTER TABLE (handler::create, 
handler::prepare_inplace_alter_table):

The storage engine may pass some engine-private data back to the data
dictionary. For InnoDB, this would include:

• the root page number of each B-tree index
• metadata for FULLTEXT INDEX

Flagging an index, table or tablespace as corrupted or read-only
================================================================

InnoDB may detect corruption in an index, table or in a system
page of a tablespace. Sometimes, the corruption is noticed when there
is no currently executing SQL statement for the table. For example,
change buffer merge or purge or file system read-ahead could notice
corruption.

The corruption will be marked immediately in the InnoDB DD cache,
and persisted via the InnoDB redo log.

Rollback/Purge
==============

InnoDB needs to be able to look up a table definition based on
dd.tables.se_private_id.

This is needed for explicit XA ROLLBACK and for rolling back recovered,
incomplete transactions after crash recovery. This may sometimes happen before
the system accepts general user connections.

The InnoDB purge thread may also need to look up a table definition
by table_id. This may happen when connections are already being accepted and
served.

Log checkpoints
===============

The storage engine may want to persist quickly changing metadata on a log
checkpoint. This could include update_time, the maximum auto-increment column
value, or metadata for fast count(*).

Foreign keys
============

Once information about foreign keys is moved into global DD there should be
SE API changes to support this move. Old methods retrieving FK information
from SE should be removed. New DD API methods providing the same information
to SE should be described.