WL#6205: InnoDB: Implement CREATE TABLESPACE for general use
Status: Complete
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 " 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).
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.