WL#6205: InnoDB: Implement CREATE TABLESPACE for general use

Status: Complete   —   Priority: Medium

This task will add the ability for InnoDB to create a tablespace via the CREATE
TABLESPACE syntax. The CREATE TABLESPACE syntax already exists in MySQL for use
by NDB.  This task will make it work when 'engine=InnoDB' is used in the command
or when DEFAULT_STORAGE_ENGINE=InnoDB.  It will not make any changes to the
tablespace commands as used by NDB.

This is a subset of the full InnoDB support for tablespaces.

SQL Commands
------------
WL#5980 created the infrastructure to assign a Single-Table tablespace to any
location via the CREATE TABLE ... DATA DIRECTORY='/absolute/path/to/data/'
syntax.  This worklog expands on that ability to put data at any chosen location
by allowing the user to create a tablespace first with

  CREATE TABLESPACE `tablespace_name`
    ADD DATAFILE 'file_name'
    [FILE_BLOCK_SIZE = n];

and then add tables to this general tablespace using

  CREATE TABLE tablename TABLESPACE `tablespace_name`;

Note that `tablespace_name` must exist in the InnoDB storage engine.

After all tables in this tablespace have been dropped, an empty tablespace
created with CREATE TABLESPACE can be deleted with

  DROP TABLESPACE `tablespace_name`;

The only new SQL Syntax added to the MySQL server will be the keyword
FILE_BLOCK_SIZE on the CREATE TABLESPACE command.

Reserved tablespace names
-------------------------
InnoDB will reject the character / in CREATE TABLESPACE names and in the
TABLESPACE clauses of CREATE TABLE and ALTER TABLE (FR9).

Tablespace names starting with innodb_ cannot be created by the user (FR10).
In CREATE TABLE and ALTER TABLE, TABLESPACE=innodb_* is either not allowed
(the tablespace does not exist) or has a special meaning.

The following reserved tablespace names have a special meaning:

TABLESPACE=innodb_system: The InnoDB system tablespace (file names derived from
innodb_data_file_path, innodb_data_home_dir), with space_id=0. This was the only
available tablespace before the introduction of innodb_file_per_table=ON in
MySQL 4.1.

TABLESPACE=innodb_file_per_table: Explicitly requests a table to be moved to a
new single-table tablespace. This will allow single-table tablespaces to be
created even after SET GLOBAL innodb_file_per_table=OFF.

The following tablespace names are not allowed in CREATE/ALTER TABLE…TABLESPACE
but they may be used internally by InnoDB:

innodb_temporary: The tablespace for CREATE TEMPORARY TABLE.
Not allowed in TABLESPACE clauses. ALTER TABLE is not allowed to move
a table between a temporary tablespace and a persistent (redo-logged) tablespace.

innodb_undo*: InnoDB undo tablespaces.

innodb_log: The InnoDB redo log pseudo-tablespace.

innodb_file_per_table.%x where %x is the InnoDB-internal tablespace ID.

The internal representation (fil_space_t::name) of these
reserved tablespace names will either start with the string "innodb_" or
contain the forward slash character /. This is why the forward slash / will
currently be rejected in user-specified TABLESPACE names (FR9).

General Tablespaces
-------------------
The tablespace created this way be referred to as a 'general' tablespace.  In
theory, a general tablespace can hold tables of any ROW_FORMAT as long as the
FILE_BLOCK_SIZE is the same as the physical page size of the table. In practice,
compressed tables will have a different physical page size than non-compressed
tables. So it will not be possible to mix compressed tables with non-compressed
tables until InnoDB can support tables with multiple page sizes at once.

This means that a tablespace created with a file_block_size other than the
default page size can only hold compressed tables with
KEY_BLOCK_SIZE=FILE_BLOCK_SIZE/1024.

A tablespace that is created with no FILE_BLOCK_SIZE mentioned or with a
FILE_BLOCK_SIZE that is the same as the global setting innodb_page_size can
contain tables of each of the other 3 row formats. So if [FILE_BLOCK_SIZE = n]
is used, where n is not innodb_page_size, only a compressed table with that same
compressed page size can be added to it.

Any number of tables can be added to a general tablespace just like the system
tablespace. 

Single-table-tablespaces (only one table per tablespace) are created using

 CREATE TABLE t(…) TABLESPACE=innodb_file_per_table;

or

 SET GLOBAL innodb_file_per_table=ON;
 CREATE TABLE t(…);

The tablespace name will be generated by InnoDB. Generated tablespace names
cannot conflict with CREATE TABLESPACE names, because they will be reserved
tablespace names.

It is assumed that you can DISCARD and IMPORT a single-table tablespace
using the WL#5522 syntax

 ALTER TABLE…DISCARD TABLESPACE;
 ALTER TABLE…IMPORT TABLESPACE;

If multiple tables were allowed into these existing single table tablespaces,
then the DISCARD command would DISCARD more than what is intended.

All existing IBD tablespaces created by previous InnoDB versions were created
for only one table and will remain limited to only one table. But tables
created by CREATE TABLESPACE will be available to add more than one table.

The following list shows the differences between Single-Table and General
tablespaces;

General Tablespaces
-------------------
1) Explicitly created with CREATE TABLESPACE.
2) Has no dependency upon INNODB_FILE_PER_TABLE.
3) Can hold multiple tables of any ROW_FORMAT as long as the FILE_BLOCK_SIZE is
correct for the table.
4) Must use DROP TABLESPACE to delete it. A general tablespace will not be
automatically deleted when the last table is dropped.
5) Not associated with any database or schema.  A DROP DATABASE may drop some
tables within a general tablespace, but it will not drop the tablespace.
6) ALTER TABLE ... DISCARD & IMPORT will not work on tables that belong to a
general tablespace.
7) The server must use Tablespace-Level Meta-Data-Locking.
8) Tablespace name is explicitly assigned from a MySQL identifier.
9) Tablespace file name is explicitly assigned by ADD DATAFILE.

Single-Table tablespaces
------------------------
1) Created implicitly with CREATE TABLE.
2) Requires INNODB_FILE_PER_TABLE=ON.
3) Can hold only one table with one specific ROW_FORMAT.
4) Deleted when DROP TABLE is issued.
5) Deleted when DROP DATABASE is issued.
6) Supports ALTER TABLE ... DISCARD & IMPORT.
7) The server need only use Table-Level Meta-Data-Locking.
8) Tablespace name is implicitly created from a filename-safe identifier.
9) Tablespace file name is derived from the table name and optional
DATA DIRECTORY attribute.

FR1: CREATE TABLESPACE works to create a .ibd tablespace that can contain
multiple tables.

FR2: Any number of tables can be added to a general tablespace with;
     CREATE TABLE ... TABLESPACE[=]name;

FR3:  DROP TABLESPACE completely drops an empty tablespace. This statement will
error if the tablespace is not empty. You must do DROP TABLE first.

FR4: Rules for tablespace management are strictly enforced independent of the
setting innodb-strict-mode.
  * COMPACT, REDUNDANT, & DYNAMIC formats may be mixed in the same tablespace if
the FILE_BLOCK_SIZE of the tablespace equals to innodb_page_size.
  * A general tablespace created with a FILE_BLOCK_SIZE other than
innodb_page_size cannot contain a table with
the COMPACT, REDUNDANT or DYNAMIC formats nor a ROW_FORMAT=COMPRESSED table
except when KEY_BLOCK_SIZE=FILE_BLOCK_SIZE/1024.

FR5: A generated or predefined tablespace cannot be made into a general
tablespace. Among other things, this means that one cannot reference an
undo log tablespace or a specific file-per-table tablespace in a
TABLESPACE[=]tsname clause. (See Reserved tablespace names.)

FR6: A general tablespace is not a file-per-table tablespace and so it is not
deleted when the table is deleted and cannot support ALTER TABLE DISCARD/IMPORT.

FR7: Tablespace creation does not depend on innodb-file-format,
innodb-max-file-format or innodb_file-per-table settings.

FR8: All parts of a table added to a general tablespace are found in that
tablespace including all indexes and BLOB pages.  This includes auxiliary tables
of FTS indexes.

FR9: There is no conflict between general tablespace names and file-per-table
tablespace names. The character '/' which is always used for file-per-table
tablespace names will not be allowed in a general tablespace name.

FR10: A general tablespace name cannot start with the characters `innodb_` which
are reserved for InnoDB internal tablespace names.

FR11: Temporary general tablespaces cannot be created by this worklog, but the
shared temporary tablespace gets the name `innodb_temporary`.  Temporary tables
cannot be added to a non-temporary tablespace and non-temporary tables cannot be
added to a temporary tablespace.

FR12: This worklog makes no effort to prevent the same tablespace name from
being used by other storage engines that can create tablespaces.  That will
need to be accomplished in the Global Data Dictionary.
WL#6205 will expand on the work done in WL#5980, which established the ability
to locate a tablespace at alternate locations, store tablespace and datafile
location in new system tables, display those system tables in
information_schema, and integrate the management of these alternate located
tablespaces into startup and recovery.

Basic Syntax Used
-----------------
We now give control to the user to create a tablespace first, and add any number
of tables to it. The syntax needed here is mostly available already in MySQL;

CREATE TABLESPACE `tablespace_name` 
  ADD DATAFILE 'file_name.ibd'
  [FILE_BLOCK_SIZE = n]  <-- new in WL#6205

CREATE TABLE `tablename` TABLESPACE [=] `tablespace name`

ALTER TABLE `tablename` TABLESPACE [=] `tablespace_name`

DROP TABLESPACE `tablespace_name`

The tablespace name is a case-sensitive identifier
(http://dev.mysql.com/doc/refman/5.7/en/identifiers.html) in MySQL which can be
unquoted or quoted with ` or ".  The 'file_name.ibd' is a string value which
must be quoted with either single (') or double (") quote marks.

Supported and Unsupported Syntax
--------------------------------
InnoDB will support, ignore or return an error for the following related syntax;

CREATE TABLESPACE `tablespace_name`       // See FR9, FR10 on reserved names
    ADD DATAFILE 'file_name'              // Supported
    [USE LOGFILE GROUP logfile_group]     // Error
    [EXTENT_SIZE [=] extent_size]         // Error
    [INITIAL_SIZE [=] initial_size]       // Error
    [AUTOEXTEND_SIZE [=] autoextend_size] // Ignored
    [MAX_SIZE [=] max_size]               // Ignored
    [NODEGROUP [=] nodegroup_id]          // Ignored
    [WAIT]                                // Ignored
    [COMMENT [=] 'comment text']          // Ignored
    [ENGINE [=] engine_name]              // Supported

CREATE TEMPORARY TABLESPACE               // Error

ALTER TABLESPACE `tablespace_name`        // Error
    {ADD|DROP} DATAFILE 'file_name'       // Error
    [INITIAL_SIZE [=] size]               // Error
    [MAX_SIZE [=] size]                   // Error
    [WAIT]                                // Error
    [COMMENT [=] comment_text]            // Error
    [ENGINE [=] engine_name]              // Error

DROP TABLESPACE tablespace_name           // Supported (see FR9, FR10)
    [ENGINE [=] engine_name]              // Supported

CREATE TABLE …
    TABLESPACE = `tablespace_name`        // Supported (see FR9, FR10)

CREATE TEMPORARY TABLE …
    TABLESPACE = `tablespace_name`        // Error

ALTER TABLE non_temporary_table_name
    TABLESPACE = `tablespace_name`;       // Supported
ALTER TABLE temp_table TABLESPACE=ts_name;// Error

Multiple Tables
---------------
A general tablespace can store any number of tables much like the system
tablespace.  This means that a ALTER TABLE DISCARD/IMPORT cannot be used on a
table assigned to a general tablespace.  

A single Datafile
-----------------
This task will only support a single datafile.  Support for ALTER
TABLESPACE ... ADD DATAFILE will be added later.

Temporary Tables and Tablespaces
--------------------------------
This worklog does not support the creation of temporary tablespaces. 
All temporary tables will continue to reside in the shared temporary tablespace
innodb_temporary that is controlled by the setting innodb_temp_data_file_path.

Partitioned Tables
------------------
This worklog will not support assigning each partition to its own tablespace
because the partition engine is being moved into InnoDB in a parallel project to
this work.  See WL#6035: Native InnoDB partitioning.

Compressed Tablespaces and FILE_BLOCK_SIZE
------------------------------------------
For this worklog, InnoDB will use the default page size or the value of
INNODB-PAGE-SIZE, if it is set. Inside InnoDB, this is known as UNIV_PAGE_SIZE.

If a compressed table is assigned to this tablespace which uses a different
compressed page size, then the file block of the tablespace must be set before
the tablespace can be created. Therefore it will be necessary to add the syntax;

CREATE TABLESPACE ... [FILE_BLOCK_SIZE = n] ...;

FILE_BLOCK_SIZE is a new phrase that must be added to the SQL parser. If it is
used, the the only tables that can be added to this tablespace are tables in
which the physical page size (KEY_BLOCK_SIZE) is the same size as
FILE_BLOCK_SIZE/1024.

Whereas innodb_page_size is currently limited to 4k, 8k and 16k pages, the
FILE_BLOCK_SIZE phrase on CREATE TABLESPACE can be set to any value that
KEY_BLOCK_SIZE can be set to. (For example; if innodb_page_size=8k, then
FILE_BLOCK_SIZE can be 8k, 4k, 2k or 1k.

Row Format
----------
ROW_FORMAT can be specified for an InnoDB table, but it is not necessary for
CREATE TABLESPACE since in theory, any row format can be supported by a
tablespace as long as the FILE_BLOCK_SIZE is the same as the physical page size
of the table.

innodb_strict_mode
------------------
This setting is not used with general tablespaces. If some input is incompatible
or wrong, such as a bad FILE_BLOCK_SIZE, the CREATE TABLESPACE command will fail
regardless of the value of INNODB_STRICT_MODE.  The CREATE TABLESPACE operation
will not make any assumptions about input parameters based on the current value
of INNODB_STRICT_MODE like the CREATE TABLE operation does.

innodb_file_per_table
---------------------
This setting normally is needed in order to create tablespaces other than the
system tablespace.  But as its name implies, it affects where a table is created
and affects CREATE TABLE and ALTER TABLE commands.  Adding a TABLESPACE=`...`
clause explicitly tells InnoDB where to place the created or altered table.  So
it will not be necessary to set INNODB_FILE_PER_TABLE=ON in order to use CREATE
TABLE ... TABLESPACE=name. Likewise, CREATE TABLESPACE in InnoDB does not
depend upon INNODB_FILE_PER_TABLE=ON.

ALTER TABLE t TABLESPACE=…; will always cause a full table rebuild,
even if the TABLESPACE attribute is not changed from the previous value.

The phrase TABLESPACE=`innodb_file_per_table` on CREATE TABLE or ALTER TABLE is
an explicit way to create a file-per-table tablespace independent of the
innodb_file_per_table setting. Normally, the DATA DIRECTORY phrase on
CREATE TABLE is incompatible with TABLESPACE=`almost_anything`, but it can
be used with TABLESPACE=`innodb_file_per_table`. MySQL Server always ignores
DATA DIRECTORY on ALTER TABLE.  It reports a log message numbered 1618 that
reads "<DATA DIRECTORY> option ignored".  So you cannot mix TABLESPACE= and DATA
DIRECTORY on ALTER TABLE commands.

innodb_file_format
------------------
This setting helps in replication environments with mixed engine capabilities.
It was introduced into the innodb_plugin in 5.1 to be compatible with the
built-in engine.  Any newer replication environment that supports CREATE
TABLESPACE will also support the Barracuda file formats.  So this setting will
not be consulted in order to create a tablespace that accepts tables with
row_format=Dynamic or row_format=Compressed. In addition, a CREATE TABLE
statement that adds a table to a general tablespace with be able to choose any
row_format regardless of the value of INNODB_FILE_FORMAT.

ADD DATAFILE
------------
This phrase will only be supported on CREATE TABLESPACE. 

The ADD DATAFILE phrase is not optional on CREATE TABLESPACE for NDB and thus it
is not optional in the MySQL parser.  Even though it would be possible to make
this optional for InnoDB, by using the tablespace name as the filename, this
task will continue to require the ADD DATAFILE phrase for every CREATE
TABLESPACE statement.

In addition, InnoDB will continue to enforce that every datafile use the
extension ".ibd".  So the filepath following the ADD DATAFILE phrase must end in
".ibd" and contain a basename of at least one byte or the CREATE TABLESAPCE will
be rejected.  The filepath provided does not need to contain a directory before
the basename.  If not, it will be created in the current directory for mysqld as
found in the global fil_path_to_mysql_datadir.

The ADD DATAFILE filepath can be a relative path or an absolute full filepath.
It must end in ".ibd".

Tablespace Discovery
--------------------
Before WL#7142, all tablespaces were discovered before recovery by traversing
the first level subdirectories below the default datadir, looking for any file
that ended in '.ibd'.  InnoDB would create a file_system cache entry for each on
and look it up in SYS_DATAFILES.  WL#7142 allowed any tablespace involved in the
REDO log to be discovered as part of recovery.  But there still remained a
directory search for tablespace files in subdirectories of the datadir in case
they were not previously found in the REDO log.

This task will add two new discovery searches for tablespaces.  The first search
traverses SYS_TABLESPACES and the related entries in SYS_DATAFILES.  This will
open all general tablespaces previously created even if they are empty.

The second search traverses SYS_TABLES.  If any space_id is > 0, the space_id
will be looked up in SYS_DATAFILES to make sure that the tablespace has ben opened.

ISL files
---------
These "Innodb Symbolic Link" files exist in the location of the FRM file when a
file-per-table tablespace is not found in that location. The FRM file is the
default location that MySQL considers a table should be, independent of which
Storage Engine is hosting the table.  With the added support for the DATA
DIRECTORY phrase in MySQL 5.6, InnoDB started using ISL files to
provide a secondary durable reference to where the file really is.  This link
file also allows one to move a single-table-tablespace file while MySQL is not
running and to tell InnoDB about the new location by just editing this file.

The plan is to get rid of these ISL files when they are no longer needed. 
WL#7142 got rid of most of the reason that these ISL file were needed,
and that was to be able to find the tablespace files before recovery when
SYS_DATAFILES is not yet available.  Once we have an independently recoverable
data dictionary that can tell us where the file is reliably, we can delete the
use of ISL files.

We will avoid writing and reading ISL files for general tablespaces created with
CREATE TABLESPACE because they will be discovered at startup either from
MLOG_FILE_NAME entries in the REDO log or a reference in SYS_DATAFILES.  Thus
ISL files will only be found for implicitly created file-per-table tablespaces
when DATA DIRECTORY is used.

Datafile Extension = .ibd
-------------------------
Shared tablespaces made with CREATE TABLESPACE have some differences from
single-table tablespaces historically using the .ibd extension.  But there are
also many similarities. Recovery and the IO subsystem, for example, will not
need to know the difference. They both have the same page structure and headers.
So shared tablespaces will also use the .ibd extension in order to keep the
codebase simpler and reduce the number of places where database 'metadata' is
stored.

Table Flags
-----------
When a feature is added to the storage of table data that makes an older version
of InnoDB unable to read that table, we have usually added a new table flag. 
This was done for DATA DIRECTORY support so that when a file-per-table table is
stored remotely, the table flags found in SYS_TABLES got a new flag that could
be read with DICT_TF_HAS_DATA_DIR(flags).  This will also be done for tables
stored in a general shared tablespace.  It will be retrieved with
DICT_TF_GET_SHARED_SPACE(flags).  In this way, an older engine will not be able
to open any table stored in a general tablespace.

Assigning a Table to a Tablespace
---------------------------------
The 'TABLESPACE=tablespace_name' phrase will be used in the CREATE TABLE and
ALTER TABLE commands. A new table can be added to a general tablespace with
CREATE TABLE and can be moved into a general tablespace with ALTER TABLE.  The
following assignments of table locations will be possible;

--  CREATE TABLE  --
Single-file Tablespace    use innodb_file_per_table=ON
Single-file Tablespace    use TABLESPACE=innodb_file_per_table
System Tablespace         use innodb_file_per_table=OFF
System Tablespace         use TABLESPACE=innodb_system
General Tablespace        use TABLESPACE=name

---  ALTER TABLE  --
MyISAM             -> General Tablespace    use ENGINE=InnoDB TABLESPACE=name
MyISAM             -> file-per-table        use ENGINE=InnoDB
                                                innodb_file_per_table=ON
MyISAM             -> file-per-table        or ENGINE=InnoDB
                                               TABLESPACE=innodb_file_per_table
MyISAM             -> System Tablespace     use ENGINE=InnoDB
                                                innodb_file_per_table=OFF
MyISAM             -> System Tablespace     or ENGINE=InnoDB
                                               TABLESPACE=innodb_system
Any InnoDB tablespace -> MyISAM             use ENGINE=MyISAM

→ General Tablespace    use TABLESPACE=name
→ System Tablespace     use TABLESPACE=innodb_system
→ file-per-table        use TABLESPACE=innodb_file_per_table

CREATE TABLE with no TABLESPACE clause:
If innodb_file_per_table=OFF, uses the System tablespace (innodb_system)
If innodb_file_per_table=ON, implies TABLESPACE=innodb_file_per_table

ALTER TABLE with no TABLESPACE clause:
Keeps the existing tablespace. Exception:
If innodb_file_per_table=ON and no TABLESPACE clause was specified or
implied by earlier CREATE TABLE or ALTER TABLE,
then TABLESPACE=innodb_file_per_table will be implied.
(This moves tables from the system tablespace into file-per-table tablespace,
unless TABLESPACE=innodb_system was specified earlier.)

Notice that CREATE TABLE…TABLESPACE=innodb_system provides a new way to put a
table into the system tablespace. When you do this, the table contains the
SHARED_SPACE flag and the TABLESPACE= parameter is remembered.  So the table is
now 'stuck' in the system tablespace until you do an ALTER TABLE with another
TABLESPACE=name parameter.

A typical table in the system tablespace will be there because
innodb_file_per_table was OFF when it was created.  And if
innodb_file_per_table=ON when an ALTER TABLE occurs, the table will be
implicitly moved to its own single table tablespace. This implicit movement of
tables to file-per-table does not occur if the table was put into the system
tablespace explicitly using the TABLESPACE parameter.

Note that before this worklog, InnoDB did not allow a table using a
file-per-table tablespace to be implicitly moved back into the system tablespace
when innodb_file_per_table=OFF. Once it became a file-per-table tablespace, it
could not change that even if innodb_file_per_table=OFF.  This same rule applies
now to tables assigned to general tablespaces.  They will remain assigned to
some named multi-table tablespace, even if that tablespace is the system tablespace.

The TABLESPACE=file_per_table phrase will move the table into its own
file-per-table tablespace. The tablespace name and location will be chosen
implicitly.  This provides an explicit method to move a table from either a
general tablespace or the system tablespace into a file-per-table tablespace.

Another interesting feature that can occur when you use TABLESPACE=innodb_system
is that just like general tablespaces, you can put a ROW_FORMAT=dynamic table
into the system tablespace, mixed with redundant and compact tables.  Normally
you cannot do this because row_format=dynamic requires innodb_file_per_table=ON.
But as mentioned above, the TABLESPACE=name option is independent of
innodb_file_per_table.  So when then TABLESPACE option is used on CREATE TABLE
or ALTER TABLE, the other limitation is not checked.

If both TABLESPACE and DATA DIRECTORY are used in the same CREATE TABLE
command, then the command will be rejected with an error, regardless
of innodb-strict-mode. The exception to this is when
TABLESPACE=`innodb_file_per_table`.  In this case, the DATA DIRECTORY is used
normally as if it were not there and innodb_file_per_table setting is ON.

The DATA DIRECTORY phrase is not allowed by MySQL on an ALTER TABLE statement. 
It is always ignored and the ALTER TABLE proceeds as if it were not there.  So
the only way to get a file_per_table tablespace to exist at a remote location is
to use DATA DIRECTORY with CREATE TABLE.  This means that if you do;

ALTER TABLE ... TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='path';

the DATA DIRECTORY will be ignored as MySQL has always done. The table will be
created in the normal location, a dbname directory under the datadir.

SYS_TABLESPACES & SYS_DATAFILES
-------------------------------
The tablespace name mentioned above for the InnoDB system tablespace,
"innodb_system" has not been used or exposed before.  In order to expose it, the
InnoDB system tables "./ibdata1" will need to be visible in
information_schema.innodb_sys_tablespaces and innodb_sys_datafiles.  So it
need to be added to SYS_TABLESPACES and SYS_DATAFILES at startup.

The other 'system' tablespace, "./ibtemp1", will not be added to SYS_TABLESPACES
and SYS_DATAFILES at startup because this worklog does not support CREATE
TEMPORARY TABLE ... TABLESPACE=. So there is no reason to make this temporary
system tablespace visible in information_schema.innodb_sys_tablespaces yet.

Note: In the Global Data Dictionary, the plan is to register the metadata for
TABLESPACE=innodb_temporary in dd.tablespaces and dd.tablespace_files. The file
itself can be deleted or created as needed, reusing the same space_id.
Currently, the temporary tablespace is re-created on startup, each time with a
new space_id.

We will institute a new convention for explicit tablespace naming, since this
worklog introduces explicit tablespace naming, that a prefix of "innodb_" is
reserved for InnoDB generated tablespace names.  We will use "innodb_system" and
"innodb_temporary", and in the future, we will name the UNDO and REDO log files
the same way, "innodb_undo1" & "innodb_log1".  There is no need to add these
UNDO and REDO files into SYS_TABLESPACES and SYS_DATAFILES with WL6205, but it
might be done for Global DD.

If a user tries to create a tablespace that starts with "innodb_", the CREATE
TABLESPACE command will return an error indicating that the name is invalid.

Information Schema
------------------
A new virtual column will be added to innodb_sys_tables and
innodb_sys_tablespaces that interprets the new flag for SHARED_SPACE.  This
virtual column is called "space_type" in both innodb_sys_tables and
innodb_sys_tablespaces.

In innodb_sys_tablespaces, it indicates whether the tablespace is of type
"System", "General" or "Single".  The word "Single" is used for a file-per-table
tablespace.

Since the SHARED_SPACE flag is also needed in the table flags to specify how the
table behaves, the same column is added to innodb_sys_tables.  Here, it shows
the type of tablespace the table is assigned to, using the same three values. 

Any table created without an explicit 'TABLESPACE=' clause while
innodb_file_per_table=OFF will be created in the system tablespace, just as
always.  These tables implicitly assigned to innodb_system will have a
space_type of 'System'.

But if the table has been explicitly assigned to the system tablespace by a
TABLESPACE=innodb_system clause, the "space_type" column in innodb_sys_tables
for that row will be "General" instead of "System" because even though it is a
"System" tablespace, the table was assigned there explicitly as if it were a
general tablespace. 

Tablespace Name Comparisons
---------------------------
MySQL has allowed table names to be file-system compatible because the table
name is automatically used as file names.  InnoDB has used these file-system
sensitive names as the tablespace names.  Internally, both MySQL and InnoDB
use case-sensitive comparison of table names. On case-insensitive file systems
and always on Windows, the table names can be made sort-of case-insensitive by
converting them into lower case (lower_case_table_names).

General tablespace names do not need to be made file-name-safe by the server
because they are not used in naming a file.  Each general tablespace has
a file-name explicitly assigned because ADD DATAFILE is a required parameter to
CREATE TABLESPACE.  Therefore, general tablespace names can be anything allowed
as a MySQL identifier (http://dev.mysql.com/doc/refman/5.7/en/identifiers.html)
with the following exceptions;

1) In order to prevent any conflict between explicit general tablespace names
and implicit file-per-table tablespace names InnoDB will prevent the use of the
`/` character in general tablespace names.  This character is always used in
the internal names (fil_space_t::name) of file-per-table tablespaces. (FR9)

2) InnoDB will reserve the case-sensitive prefix `innodb_` for tablespace names
internally assigned by InnoDB. (FR10)

Internal comparisons of tablespace names will be CASE-SENSITIVE.

Interfaces Affected
-------------------
SQL syntax/semantics - Discussed above.

File Formats - There will be a new flag added to the table flags and the
tablespace flags called SHARED_SPACE. This means that older engines will not be
able to open general tablespaces which have this flag.  Nor will older engines
be able to open tables that are assigned to general tablespaces.

Errors and Warnings - Several Warnings need to be reworded to take away the
assumption that a tablespace can only be assigned to only one table

A new SQL level error is added;
    ER_TABLESPACE_IS_NOT_EMPTY The actual number will be determined when that
patch is pushed.

These two new Handler level errors are also added;
    HA_ERR_TABLESPACE_MISSING
    HA_ERR_TABLESPACE_IS_NOT_EMPTY
Since general tablespaces will contain multiple tables of different row formats,
some parts of the tablespace flags may be handled differently.  The following
describes the FSP_FLAGS and how they are interpreted.

Tablespace Flags (FSP_FLAGS)
============================
Each IBD tablespace contains one set of FSP flags which identify the row
format and other characteristics of the tablespace.  The following is a
discussion of what these bits mean and how they are applied to both general and
file-per-table tablespaces.

Bit 1:  Compact format bit; This first bit is zero for Antelope-format
tablespaces (REDUNDANT and COMPACT) and one for file-per-table tablespaces that
hold a table with DYNAMIC or COMPRESSED row formats. In the similar table flags
this flag is used to distinguish between the two Antelope formats.  Since it is
zero in the space flags for both Antelope formats there is no way to tell
whether the tablespace is designed for REDUNDANT or COMPACT Row Formats. 

General tablespaces set this flag to 0 when the tablespace
will support REDUNDANT, COMPACT & DYNAMIC row formats.  If a General tablespace
is created with FILE_BLOCK_SIZE other than innodb_page_size, then
this bit will be set along with the other bits needed for a COMPRESSED
tablespace. (See below)

Bits 2-5: Compressed Page Size.  If this number is anything but zero, the
tablespace can only hold COMPRESSED tables.  This is true for both
file-per-table and general tablespaces. If this field is zero, then the
tablespace does not have compressed pages and the physical page size is the same
as the logical page size found in bytes 7-10 (described below).

General tablespaces can be created with any FILE_BLOCK_SIZE that can support a
compressed page.  That is, 1k, 2k, 4k, 8k & 16k.  These match up to the possible
KEY_BLOCK_SIZE values of 1, 2, 4, 8 & 16 that can be issued for a CREATE TABLE.
So if you want to put a COMPRESSED table into a general tablespace, the
FILE_BLOCK_SIZE must match the KEY_BLOCK_SIZE * 1024.  However, if the
FILE_BLOCK_SIZE matches the current innodb_page_size, this field will be zero
and the tablespace will only be able to support uncompressed tables.  This makes
it impossible to create a general tablespace that holds a COMPRESSED table where
the compressed page size is the same as the uncompressed page size.  That
scenario is not very useful anyway.  It is only used in file-per-table
tablespaces for testing purposes.

Bit 6: Atomic BLOBs - This flag is on when a DYNAMIC or COMPRESSED row format
table is
used in a file-per-table tablespace, or when a general tablespace is designated
for COMPRESSED tables (when the Compressed Page Size field is non-zero)

Bits 7-10: Page Size - This is the uncompressed page size, sometimes called the
logical page size.  When the Compressed Page Size above is non-zero, this is
what it will uncompress to.  This is set to whatever innodb_page_size is when
the tablespace is created, whether this is a file-per-table or a general
tablespace.  The special value of zero in this field indicates that the page
size is the InnoDB default page size of 16K. This allows InnoDB to use legacy
tablespaces that were created when 16k was the only possible page size.

Suppose innodb_page_size = 8k and CREATE TABLESPACE uses FILE_BLOCK_SIZE=8k. 
Since those two are the same, this tablespace will be dedicated to uncompressed
tables.  The Compressed Page Size field in bits 2-5 will be zero and bits 7-10
will indicate a Page Size of 8k.  This tablespace can not be used in an instance
that was bootstrapped with innodb_page_size=16k.  That rule is the same for
file-per-table and general tablespaces.  This page size must match
innodb_page_size in order for the tablespace to be useful.

Bit 11: Remotely located - This flag is used for file-per-table
tablespaces.  It indicates that the tablespace is located remotely due to the
use of the DATA DIRECTORY phrase in the CREATE TABLE statement.
This field is set to 0 for shared tablespaces.

Bit 12: Shared - This worklog introduces this new flag which identifies a
general tablespace that can contain multiple tables (and other objects in the
future). This flag will be set for any tablespace created by CREATE TABLESPACE.
The system tablespace commonly called ibdata1 does not use this flag since
InnoDB has always allowed it to be shared without it.  Adding it to existing
system tablespaces will make them unusable by any previous versions.

Bit 13: Temporary - A future worklog will allow the creation of
temporary general tablespaces.  The only way to create a file-per-table
temporary tablespace is to make it with a compressed temporary table.  The
reason that this flag is added now is so that the shared predefined temporary
tablespace named `innodb_temporary`, commonly using filename ‘ibtmp1’, can be
marked as a temporary tablespace and any CREATE TABLE or ALTER TABLE that uses
TABLESPACE=`innodb_temporary’ will fail with an error that says that you cannot
put a non-temporary table into a temporary tablespace.

Tablespace Flag Comparisons
===========================
There will be a new routine that uses the rules described above to match
tablespace flags (FSP_FLAGS) with the flags of the table that is being inserted
into it.  These rules change a little depending on whether it is a general or
file-per-table tablespace, indicated by the new bit 12.

For example, a DYNAMIC table would only go into a file-per-table tablespace that
had bit 1 and 6 set, but those bits are not needed in the FSP_FLAGS of a general
tablespace (where bit 12 is set).