WL#8972: Rename a general tablespace

Affects: Server-8.0   —   Status: Complete

As general (named) tablespace now are feature of both NDB and Innodb it makes sense to extend the capabilities of the ALTER TABLESPACE statement to include the sub command ... RENAME TO .... A general tablespace is a user-visible entity which users refer to by name, so it is reasonable to allow users to change this name.

The new syntax is: ALTER TABLESPACE s1 RENAME TO s2;

Now that general tablespaces are becoming "first class citizens", and their metadata are stored in the DD, it is desirable to refactor the implementation of the parser and execution layers to make it fit the preferred style for these layers. This refactoring can be seen as foundation for other general tablespace features that are in the pipeline.

Functional Requirements

(Please read n. in the following as FRn)

  1. Rename without CREATE_TABLESPACE_ACL-privilege (CREATE TABLESPACE privlege) must be rejected
  2. Rename must be supported for all engines having general tablespaces (NDB and InnoDB)
  3. Rename to/from implicit tablespace must be rejected (InnoDB only) (ER_WRONG_TABLESPACE_NAME)
  4. Rename to/from system tablespace (name has prefix 'innodb_') must be rejected (InnoDB only) (ER_TABLESPACE_EXISTS/ER_WRONG_TABLESPACE_NAME)
  5. Rename to/from DD tablespace (named mysql) must be rejected (ER_TABLESPACE_EXISTS/ER_WRONG_TABLESPACE_NAME)
  6. Rename to/from invalid (e.g. too long) (as reported by SE) must be rejected (ER_WRONG_TABLESPACE_NAME)
  7. Rename must be reflected in INFORMATION_SCHEMA.FILES
  8. Rename must be reflected when doing SHOW CREATE TABLE
  9. Must be possibe to perform DML (SELECT, UPTDATE, DELETE) on table in renamed tablespace
  10. Must be possible to perform DDL (DROP, CREATE, ALTER) in renamed tablespace
  11. Must be possible to do CREATE TABLE ... LIKE with the source table in a renamed tablespace
  12. Rename statement must be atomic
  13. Rename must block access (through MDL) to all tables in tablespace being renamed
  14. Rename must be prevented if FLUSH TABLES WITH READ LOCK is active (ER_LOCK_WAIT_TIMEOUT)
  15. Rename must be prevented if LOCK TABLES has been issued (ER_LOCK_WAIT_TIMEOUT)
  16. Rename statement must be run in auto commit mode (be implicitly autocomitted, even when autocommit mode has been turned off)
  17. Rename statement must be binlogged
  18. ENGINE tablespace option must be deprecated for DROP and ALTER TABLESPACE statements (ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT)

Non-Functional Requirements

Not required

  • Grammar for all tablespace commands will be modified/refactored, and the new syntax added
  • Syntax is accepted for all SEs - those not supporting tablespaces and/or RENAME must return ER_CHECK_NOT_IMPLEMENTED
  • The ENGINE tablespace option is not needed, and will be deprecated, for statements which operate on an existing tablespace (DROP and ALTER), since the engine name is now stored in the DD. In 8.0 ENGINE is still permitted, but will trigger a deprecation warning and an error if its value does not match what is recorded in the DD.
  • The following undocumented, unimplemented tablespace-related syntax will be removed:
    • ALTER TABLESPACE ... CHANGE DATAFILE ...
    • ALTER TABLESPACE ... (READ_ONLY|READ_WRITE|NOT ACCESSIBLE)
    • ALTER LOGFILE GROUP ... ADD REDOFILE ...
  • New execution base class for tablespace opertaions (class Sql_cmd_tablespace : public Sql_cmd). New tablespace execution classes:
    • class Sql_cmd_create_tablespace final : public Sql_cmd_tablespace
    • class Sql_cmd_drop_tablespace final : public Sql_cmd_tablespace
    • class Sql_cmd_alter_tablespace_add_datafile final : public Sql_cmd_tablespace
    • class Sql_cmd_alter_tablespace_drop_datafile final : public Sql_cmd_tablespac
    • class Sql_cmd_alter_tablespace_rename final : public Sql_cmd_tablespace
  • Separate execution class for log file group operations (class Sql_cmd_logfile_group final : public Sql_cmd)
  • Existing class st_alter_tablespace will be used only to interface with SEs. New struct Tablespace_options will be created for communication between parser and execution layer.
  • RENAME will reuse existing handlerton::alter_tablespace function pointer to inform SE about name change
  • enum ts_alter_tablespace_type is extended with new value ALTER_TABLESPACE_RENAME = 3

Contents

Parser

The grammar for the tablespace and log file group (NDB only) statements will be extensively refactored (much of this work done by and in cooperation with the Optimizer Team). Key points include:

  • Reduce direct manipulation of Lex members
  • Utilize grammar rule productions where possible, including for processing statement option list (e.g. ENGINE, NODEGROUP, ...)
  • Group rules so that all required information is available when execution class objects are constructed
  • Use the Tablespace_options (which replaces st_alter_tablespace in Parser/Executor) only for options, and keep information which is either necessary for all statements or specific to a particular statement as members in the execution class

Executor

The old mysql_alter_tablespace() function is replaced by the following execution classes:

  • class Sql_cmd_tablespace is a base class with protected data members used by all tablespace statements.
  • class Sql_cmd_create_tablespace final : public Sql_cmd_tablespace and class Sql_cmd_drop_tablespace final : public Sql_cmd_tablespace provide exactly the same functionality as is currently found in mysql_alter_tablespace().
  • class Sql_cmd_alter_tablespace_add_datafile final : public Sql_cmd_tablespace and class Sql_cmd_alter_tablespace_drop_datafile final : public Sql_cmd_tablespace implement the only existing ALTER TABLESPACE statements which are only supported with NDB.
  • Sql_cmd_alter_tablespace_rename final : public Sql_cmd_tablespace implements the new ALTER TABLESPACE ... RENAME TO statement which gives this wl its name.
  • class Sql_cmd_logfile_group final : public Sql_cmd is a thin wrapper around the handlerton call which implements the log file group statements in the SE. There is really not much which can be done in the server at this point since the log file groups are not represented in the DD.

RENAME Implementation

The rename of a tablespace is made up of the following sub-tasks:

  1. Check for CREATE_TABLESPACE_ACL
  2. Obtain X MDL on both the new and old names, and acquire the dd::Tablespace object for the tablespace being renamed. Note that exclusive MDL is overkill at this point since we only need MDL to be able to read the engine name and locate the correct handlerton. Intention exclusive would have been sufficient, but it would have had to be upgraded to exclusive later when object is actually being modiefied, so for now X is taken right away)
  3. Obtain handlerton pointer to the tablespace' engine (as recorded in the DD)
  4. Run necessary checks on both the old and new name to makes sure the rename is allowed
  5. Obtain a list of tables stored completely, or partially (in the case of partitioned tables), in the tablespace. The method for doing this is described below.
  6. Obtain X MDL for all tables in this list
  7. Evict the table shares for all these tables to ensure that all users of these tables must get a fresh share with updated information from the DD.
  8. Acquire the dd::Tablespace object for modification and update the name in the modifiable object.
  9. Invoke handlerton::alter_tablespace() to notify SE about the name change
  10. Update the object in the DD
  11. Binlog the statement

Finding Tables in Tablespace

There is no DD table which maps a tablespace directly to the tables it contains. To get this information one needs to join multiple DD tables which all can possibly contain a tablespace id, (this is very different from looking up the tablespaces for a table, which can be done traversing the object graph rooted in the table and collecting the tablespace ids).

The following DD tables have a tablespace_id column:

  • INDEX_PARTITIONS
  • TABLE_PARTITIONS
  • INDEXES
  • TABLES

Of these TABLES, INDEXES and TABLE_PARTITIONS have a table_id column which will map a tablespace id directly to a set of table ids. For INDEX_PARTITION one must instead join with either TABLE_PARTITIONS on partition_id or with INDEXES on index_id. One must also join with TABLES on table_id to get the table name, and with SCHEMATA on schema_id to get the schema name since the fully qualified name is needed for MDL.

Unfortunately, the dd::Raw_table (handler) interface which is used to query the DD tables does not allow us to perform joins in SQL. As a workaround we use repeated dd::Raw_table queries to do the join manually by collecting ids in vectors and iterating over them.



Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.