WL#12236: CREATE TABLESPACE without DATAFILE clause

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

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:
<pre style="white-space:pre-wrap; font-family: 'Open Sans', Tahoma, Verdana, Arial, Helvetica, sans-serif; font-size:14px; font-weight: 400; line-height: 17px; border:0px solid lightgrey; padding: 10px; background:#e3e3e7; color:#000080; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace;">
@@ -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;
+      }
+    ;
+
</pre>

2. Introduce the mysql_uuid_generate. This UUID generation algorithm used by 
UUID() function would be used by CREATE TABLESPACE as well.
<pre style="white-space:pre-wrap; font-family: 'Open Sans', Tahoma, Verdana, Arial, Helvetica, sans-serif; font-size:14px; font-weight: 400; line-height: 17px; border:0px solid lightgrey; padding: 10px; background:#e3e3e7; color:#000080; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace;">
/**
  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);
</pre>
3. Following changes are made in the executor of CREATE TABLESPACE to handle the
   DATAFILE clause being empty.
<pre style="white-space:pre-wrap; font-family: 'Open Sans', Tahoma, Verdana, Arial, Helvetica, sans-serif; font-size:14px; font-weight: 400; line-height: 17px; border:0px solid lightgrey; padding: 10px; background:#e3e3e7; color:#000080; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace;">
  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();
  }

</pre>

Also new SE handler inerface get_tablespace_filename_ext is introduced.
InnoDB SE shall define and sets this handlerton function as:
<pre style="white-space:pre-wrap; font-family: 'Open Sans', Tahoma, Verdana, Arial, Helvetica, sans-serif; font-size:14px; font-weight: 400; line-height: 17px; border:0px solid lightgrey; padding: 10px; background:#e3e3e7; color:#000080; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace;">
  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"; }



</pre>