WL#12236: CREATE TABLESPACE without DATAFILE clause

Introduce a CREATE TABLESPACE variant that does not require a DATAFILE clause, but instead creates a unique filename implicitly.

This will allow users to create tablespaces without having the FILE privilege.

The scope of this worklog is as such is to provide a way not to use the DATAFILE clause in the CREATE TABLESPACE. This relieves the user from knowing physical layout of the storage spaces maintained by SE for tables. The DATAFILE name however would be generated internally at SQL layer. Since datafile is not implicitly specified as part of CREATE TABLESPACE, the file names created on master and slave shall not be the same.Under INNODB, it doesn't bring any change to name of the filename created by innodb_file_per_tablespace option that implicitly create a tablespace as part of CREATE TABLE DDL statement.

Functional Requirements

FR#1: The specification of ADD DATAFILE clause in CREATE TABLESPACE shall be optional.

FR#2: The tablespace creation shall not require a FILE privilege ACL if no DATAFILE clause is specified. The tablespace creation shall be privileged requiring CREATE_TABLESPACE_ACL privilege.(because it involves initial storage provisioning under storage engines like NDB).

FR#3: There shall not be any difference from external user perspective in terms of usage of a tablespace created without explicit specification of a datafile from a tablespace created with implicit specification of a datafile.

FR#4: ALTER and DROP TABLESPACE shall provide same functionality as now on tablespaces created without a datafile specification.

Non-Functional Requirements

NF#1: There should not be any significant performance changes as a result of this worklog.

The worklog effects a change in the syntax of the CREATE TABLESPACE DDL statement. With this the worklog, the ADD DATAFILE specification is made optional. Thus the syntax of CREATE TABLESPACE with these worklog is: CREATE TABLESPACE tablespace_name InnoDB and NDB: [ADD DATAFILE 'file_name']

InnoDB only: [FILE_BLOCK_SIZE = value]

NDB only: USE LOGFILE GROUP logfile_group [EXTENT_SIZE [=] extent_size] [INITIAL_SIZE [=] initial_size] [AUTOEXTEND_SIZE [=] autoextend_size] [MAX_SIZE [=] max_size] [NODEGROUP [=] nodegroup_id] [WAIT] [COMMENT [=] 'string']

InnoDB and NDB: [ENGINE [=] engine_name]

To implement the above effected change in syntax shall require changes at
parser and the executor of the CREATE TABLESPACE at SQL layer. The parser changes require making changes to ensure that the ADD DATAFILE may not be specified in the CREATE TABLESPACE. Thus the datafile clause can be either empty or have an specification of the datafile and the corresponding rule to handle this is specified in the yacc file.

The other change is made in the executor to handle the case where the datafile is not specified. For this case, we need to generate the filename. InnoDB requires a mandatory .ibd file extension for it's datafile. An SE handler interface would required to obtain the file extension (if any) for the particular storage engine. This would make the code generic. This task of generating unique names is handled in the executor phase of SQL layer itself. This datafile may represent a location on the filesystem depending on the way the storage engines use it to provision the storage spaces for tablespaces. Filesystem names are case insensitive and case sensitive and have different allowable characters on different platforms. Thus the need is to have a simple mechanism to generate the names that is acceptable on all platforms and at the same time ensure the names we use are universally unique (considering the tablespace files can be copied and used across different platforms/systems etc). Hence for the purpose, we use the 128-bit Uinversal Unique Identifier. The mysqld source has UUID generation code. We crave this into a function mysql_generate_uuid which can be used by tablespace executor code as well other existing code. The UUID generated shall be in the format aaaaaaaa-bbbb-cccc- dddd-eeeeeeeeeeee which is a 128 bit consisting of groups of five hexadecimal numbers separated by dashes. This both ensures uniqueness as well not to worry about the portability of names across different platforms. To this UUID, we add the extension name supplied by the handler interface and set the dd::Tablespace datafile attribute.

1. Parser rule Change to make the DATAFILE clause of CREATE TABLESPACE optional:

@@ -1272,7 +1272,7 @@ void warn_about_deprecated_national(THD *thd)
         TEXT_STRING_sys_nonewline
         filter_wild_db_table_string
         opt_constraint
-        ts_datafile lg_undofile /*lg_redofile*/ opt_logfile_group_name
+        ts_datafile lg_undofile /*lg_redofile*/ opt_logfile_group_name 
opt_ts_datafile_name
         opt_describe_column
         opt_datadir_ssl

@@ -2756,20 +2756,20 @@ create:

             Lex->sql_command= SQLCOM_ALTER_TABLESPACE;
           }
-        | CREATE TABLESPACE_SYM ident ADD ts_datafile
+        | CREATE TABLESPACE_SYM ident opt_ts_datafile_name
           opt_logfile_group_name opt_tablespace_options
           {
             auto pc= NEW_PTN Alter_tablespace_parse_context{YYTHD};
             if (pc == NULL)
               MYSQL_YYABORT; /* purecov: inspected */ // OOM

-            if ($7 != NULL)
+            if ($6 != NULL)
             {
-              if (YYTHD->is_error() || contextualize_array(pc, $7))
+              if (YYTHD->is_error() || contextualize_array(pc, $6))
                 MYSQL_YYABORT;
             }

-            auto cmd= NEW_PTN Sql_cmd_create_tablespace{$3, $5, $6, pc};
+            auto cmd= NEW_PTN Sql_cmd_create_tablespace{$3, $4, $5, pc};
             if (!cmd)
               MYSQL_YYABORT; /* purecov: inspected */ //OOM
             Lex->m_sql_cmd= cmd;
@@ -5161,6 +5161,14 @@ trg_event:
   DROP LOGFILE GROUP_SYM name
 */

+opt_ts_datafile_name:
+    /* empty */ { $$= { nullptr, 0}; }
+    | ADD ts_datafile
+      {
+        $$ = $2;
+      }
+    ;
+

1. Introduce the mysql_uuid_generate. This UUID generation algorithm used by UUID() function would be used by CREATE TABLESPACE as well.

/**
  Generate Universal Unique Identifier (UUID).

  @param  str Pointer to string which will hold the UUID.

  @return str Pointer to string which contains the UUID.
*/

String *mysql_generate_uuid(String *str);

1. Following changes are made in the executor of CREATE TABLESPACE to handle the DATAFILE clause being empty.

  if (m_datafile_name.str == nullptr) {
    char tmp_buf[40];
    String str(tmp_buf, sizeof(tmp_buf), &my_charset_bin);
    String *uuid = mysql_generate_uuid(&str);
    if (hton->get_tablespace_filename_ext)
      uuid->append(hton->get_tablespace_filename_ext());

    m_datafile_name.str = thd->strmake(uuid->c_ptr_quick(), uuid->length());
    m_datafile_name.length = uuid->length();
  }

Also new SE handler inerface get_tablespace_filename_ext is introduced. InnoDB SE shall define and sets this handlerton function as:

  innobase_hton->get_tablespace_filename_ext =
      innobase_get_tablespace_filename_ext;
 ;;;


 ;;;
/**                                                                                                                                                                                    
  Return data filename extension for a InnoDB tablespace.                                                                                                                              
*/

static const char *innobase_get_tablespace_filename_ext() { return ".ibd"; }