WL#8594: Provide an option to reject creation of user tables for a list of storage engines

Affects: Server-Prototype Only   —   Status: Complete

We have users who want to prevent MyISAM tables sneaking into production systems.  This can happen because MyISAM is the default in a developer's environment (using older version).

This worklog shall provide a command-line option --disabled-storage-engines in
which user shall supply a list of storage engines to be disabled. All user
create related DDLs shall be disabled for the list of storage engines specified.

User Story
----------

Michael is a DBA.  He has a goal of making sure all systems are up and running, 
and backups can be taken with very little system locking.

Last quarter, a developer at Michael's company introduced a MyISAM table as part 
of a new feature deployment, and the table has grown to such a point where it is 
blocking a large part of the backup.

Since this feature is now in production (and important), Michael can not just 
blind convert it to InnoDB without some investigation and testing.  However, in 
parallel to his investigation, Michael would like to ensure that no new MyISAM 
tables are introduced.

Operational semantics for storage engines specified in disabled-storage-engines
-------------------------------------------------------------------------------

* All existing tables is allowed to be read/written from 
* The DDL CREATE TABLE is prevented, resulting in an error message:
"The storage engine 'storage engine name' is disabled (Table creation is
disallowed)."
* Other DDL such as CREATE INDEX, DROP INDEX, TRUNCATE, OPTIMIZE, ALTER, RENAME,
  DROP TABLE, DROP TABLESPACE must continue to be supported.

User Documentation

FRQ1: Provide a global command-line server startup option
--disabled-storage-engines which accepts comma delimited storage engine names.
DDL's which end-up creating new table or tablespace on these engines will be
disallowed. Specifically, 'CREATE [TEMPORARY] TABLE', 'ALTER TABLE ... ENGINE'
and 'CREATE/ALTER TABLESPACE' using one of above engine.
 
FRQ2: The SQL statements  'CREATE [TEMPORARY] TABLE', 'ALTER TABLE ... ENGINE'
and 'CREATE/ALTER TABLESPACE' shall fail with the error "Storage engine 'storage
engine name' is disabled (Table creation is disallowed.)" for the storage
engines specified by disabled-storage-engine option.

FRQ3: This option does not affect any other DDL statements except for, 'CREATE
[TEMPORARY] TABLE', 'ALTER TABLE ... ENGINE' and 'CREATE/ALTER TABLESPACE'

FRQ4: This option shall be noop and will have no effect when the server is
either started with --bootstrap or --initialize or --initialize-insecure or
--skip-grant-tables. The bootstrap and related options are used by sys admin to
initialize system tables to prepare the server to operate normally and
--skip-grant-tables is meant to provide full control with no authorization 
required.
1. mysqld server command-line option --disabled-storage-engines is introduced.
   By default this option will have the null or empty string "". The server
   shall be started with specifying a comma separated list of storage engines    
   under which user table/tablespace creation is to be disallowed. This option   
   is  global and applies to all user sessions.

2. DDL statements like 'CREATE [TEMPORARY] TABLE', 'ALTER TABLE ... ENGINE' and 
  'CREATE/ALTER TABLESPACE' are disallowed for the storage engines specified in
   disabled-storage-engines. This calls are trapped at SQL layer and first 
   checked to see if the handlerton refers to any of the storage engines    
   specified by --disabled-storage-engines. If so, an error message "Storage 
   engine 'storage engine name' is disabled (Table creation is disallowed.)" is 
   logged and the operation is disallowed.

3. If server is started with option --initialize or --initialize-insecure or
   --bootstrap or --skip-grant tables and the option specifies a list of storage
   engines in disabled-storage-engines, then option doesn't have any effect and
   creation related DDLs are allowed.
New variables and methods
==========================
1. Introduce the bool option variable opt_disabled_storage_engines in   
   sql/mysqld.cc and the corressponding sys_var variable in sql/sys_vars.cc
   
  
static Sys_var_charptr Sys_disabled_storage_engines(
       "disabled_storage_engines",
       "Limit CREATE TABLE for the storage engines listed"
       READ_ONLY GLOBAL_VAR(opt_disabled_storage_engines),
       CMD_LINE(REQUIRED_ARG), DEFAULT(""));

char *opt_disabled_storage_engines= NULL;


2. Introduce new error code ER_DISABLED_STORAGE_ENGINE in file 
   sql/share/errmsg-utf8.txt as
   ER_DISABLED_STORAGE_ENGINE
      eng "Storage engine %s is disabled (Table creation is disallowed)."

3. Introduce methods ha_set_no_create_table and ha_check_if_no_create_table
   in sql/handler.h and this methods sets HTON_NO_CREATE_TABLE flags and checks
   HTON_NO_CREATE_TABLE flag is set.
   
inline bool ha_check_if_no_create_table(handlerton *ha_handle)
{
  DBUG_ENTER("ha_check_if_no_create_table");

  if (ha_check_storage_engine_flag(ha_handle, HTON_NO_CREATE_TABLE))
  {
    const char* se_name= ha_resolve_storage_engine_name(ha_handle);
    my_error(ER_DISABLED_STORAGE_ENGINE, MYF(0), se_name);
    DBUG_RETURN(true);
  }

  DBUG_RETURN(false);
}

inline void ha_set_no_create_table(handlerton *ha_handle)
{
  ha_handle->flags|= HTON_NO_CREATE_TABLE;
}



Flow control
============

1. During server startup, parse the disabled_storage_engines command-line option
   and for each storage engine listed, find the storage engine handle   
  (handlerton) and set HTON_NO_CREATE_TABLE for the handlerton. If the  
   handlerton doesn't exist for any of the storage engines listed, log an
   an warning and continue with server startup.

  if (!opt_bootstrap && !opt_noacl)
  {
    std::string disabled_storage_engines(opt_disabled_storage_engines);

    boost::char_separator<char> sep(",");
    boost::tokenizer< boost::char_separator<char> >
           tokens(disabled_storage_engines, sep);

    BOOST_FOREACH (const std::string& se_name, tokens)
    {
      LEX_STRING se_name_lstr= {const_cast<char*>(se_name.c_str()),
                                se_name.length() };
      plugin_ref plugin= ha_resolve_by_name(NULL, &se_name_lstr, FALSE);
      if (plugin)
      {
        handlerton *hton= plugin_data<handlerton*>(plugin);
        ha_set_no_create_table(hton);
        plugin_unlock(NULL, plugin);
      }
      else
        sql_print_warning("Unknown Storage Engine %s.", se_name.c_str());
    }
  }




2. The create_table, mysql_alter_table, mysql_alter_tablespace methods checks 
   if create table operation is allowed under the storage engine under which    
   these operations are invoked by invoking is_se_create_table_prevented.
   If not , they log the error  'Storage Engine    xxxx is Disabled 
   (Table creation disallowed).'