WL#7784: Store temporary table metadata in memory

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

Temporary table metadata is now stored in FRM files.
There are two main reasons for that:

  1. To know the list of tables which should be deleted
     on the server restart and pass that knowldege to SE.

  2. Originally valid fully initialized TABLE_SHARE object
     could be constructed from FRM-file only.

These reasons become obsolete in 5.7 with the New DD:

  - InnoDB (the main SE) stores temporary tables in a dedicated
    tablespace, which is discarded on startup. So, there is
    no need to pass a list of individual table names.

    Other SEs can implement the same logic themself.

  - There are no FRM-files.

Storing temporary table metadata in persistent DD is wrong
by design and it also creates more problems than it solves.

This WL is to avoid storing temporary table metadata in
persistent DD.

NOTE: MySQL temporary table implementation differs from
The SQL Standard in a sense that MySQL temporary tables
are not shown in the INFORMATION_SCHEMA. That's why this WL
is possible.

NF1: No user visible changes.
Types of temporary tables

There are several types of temporary tables in server:

1) Implicit temporary tables created by optimizer for query execution.
   These tables do not have .FRM and represented by in-memory TABLE/
   TABLE_SHARE structure already. They don't need to be represented
   in on-disk data-dictionary or have in-memory dd::Table objects.
   In the scope of this WL they are relevant only because on start-up
   we need to remove orphan tables of this kind, which remain after
   previous server run has aborted due to crash (as it is done now).
   It is fairly easy to do so as these tables created in tempdir with
   #sql prefix or in temporary tablespace.

2) Explicit temporary tables. These are tables created by user with
   CREATE TEMPORARY TABLES statement. Currently these tables have .FRMs.
   The goal of this WL is to change implementation and don't represent
   these tables in on-disk DD. Instead they should be represented by
   in-memory dd::Table object which will be associated/owned by temporary
   table's TABLE_SHARE and won't be present in in-memory DD.
   Similarly to 1) on start-up we need to remove orphan tables of this
   kind which were left from previous server runs. Similarly to 1) these
   tables have #sql prefix and reside in tempdir or temporary tablespace.

3) Implicit temporary tables created by ALTER TABLE implementation.
   There are two subclasses for them:

   a) Implicit temporary tables representing new versions of user-created
      temporary tables. This case is similar to case 2) and should be
      handled in the same fashion.

   b) Implicit temporary tables representing new versions of user-created
      non-temporary tables. Such tables now have .FRM file and reside in
      datadir or general/system tablespaces. With new-DD information about
      these tables will end up in on-disk and in in-memory DD when new table
      version replaces old table version. In theory there is no need to store
      information about such tables in DD before this moment. But to limit
      the scope of this task we won't change the fact that information about
      such temporary tables (i.e. about new version of table) is stored
      on-disk DD even before they replace old version of table. For the same
      reason we won't keep in-memory dd::Table for such tables bound to
      TABLE_SHARE. Orphan tables of this kind should not be automatically
      removed on server start-up, as in some scenarios they might be the only
      chance to recover data if server crashes in the middle of DDL. Once
      crash-safe DDL is implemented this problem willgo away.

Cleaning up orphaned temporary tables on server start-up

Handlerton interface should be extended with an operation,
which instructs SE to discard all temporary tables of 1), 2)
and 3.a) types it has. That operation should be called at server

Supported SEs should be updated:

  - InnoDB -- should discard temporary table tablespace;

  - MyISAM, CSV, Archive -- should do the same logic
    as now is done for FRM-files, i.e. look for files
    with #sql prefix in tmpdir directory and remove them.
    This logic should be generalized so that different SEs
    can reuse the same code.
    Note that for MyISAM tables with DATA/INDEX DIRECTORY
    option such files will be symlinks to other directories.
    In this case we need to remove both symlink and file it
    points to. For security reasons we should not do this if
    symlink points to a file within the data directory (it is
    impossible to create tables with such symlinks without
    manual intervention anyway).

  - Blackhole, Heap, Example -- no changes

ID and namespace issue

- Since temporary tables are not referenced from other DD objects
  it is OK to have -1 IDs for all tmp tables.
- Temporary tables shadow normal tables. This sorted out on the
  layer above DD. So tmp tables doesn't have to be present in
  general in-memory/on-disk DD in any form.