disabled_storage_engines.test

#
# WL#8594 - Provide an option to reject creation of user tables for specified
#           storage engines.
#

# Test 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.

--source include/have_example_plugin.inc
--source include/not_embedded.inc

CALL mtr.add_suppression("default_storage_engine is set to a disabled storage engine .*");
CALL mtr.add_suppression("default_tmp_storage_engine is set to a disabled storage engine .*");


--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t1(c1 int) ENGINE=HEAP;

CREATE TABLE t1 (c1 int) ENGINE=MYISAM;
INSERT INTO t1 VALUES(1);

CREATE TABLESPACE tb1 ADD DATAFILE 't1.ibd' ENGINE=INNODB;
CREATE TABLE tp1 (c1 int) PARTITION BY KEY (c1) PARTITIONS 1;

--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server
--source include/wait_until_disconnected.inc

# Restart server along with myisam storage engine  disabled and check alter
# table on existing myisam table t1 created above is allowed.
--exec echo "restart: --disabled_storage_engines=innodb,myisam,heap,example" >$MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--enable_reconnect
--source include/wait_until_connected_again.inc

SHOW VARIABLES LIKE 'disabled_storage_engines';
SELECT * FROM t1;
ALTER TABLE t1 ENGINE=MyISAM, ADD COLUMN c2 INT;
ALTER TABLE t1 ADD COLUMN c3 INT;
DROP TABLESPACE tb1;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t2 LIKE t1;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t2 AS SELECT * FROM t1;

--ERROR ER_DISABLED_STORAGE_ENGINE
ALTER TABLE t1 ENGINE=InnoDB;

--ERROR ER_DISABLED_STORAGE_ENGINE
ALTER TABLE t1 ENGINE=HEAP;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t2(c1 int) ENGINE=INNODB SELECT c1 FROM t1;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t2(c1 int) ENGINE=HEAP SELECT c1 FROM t1;

TRUNCATE TABLE t1;
DROP TABLE t1;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLESPACE t1 ADD DATAFILE 't1.ibd' ENGINE=INNODB;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLESPACE t1 ADD DATAFILE 't1.ibd' ENGINE=HEAP;

--ERROR ER_DISABLED_STORAGE_ENGINE
ALTER TABLESPACE ts ADD DATAFILE 'ts.ibd' ENGINE=INNODB;

--ERROR ER_DISABLED_STORAGE_ENGINE
ALTER TABLESPACE ts ADD DATAFILE 'ts.ibd' ENGINE=HEAP;

--ERROR ER_DISABLED_STORAGE_ENGINE
ALTER TABLESPACE ts ADD DATAFILE 'ts.ibd';

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TEMPORARY TABLE t1 (c1 int) ENGINE=INNODB;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TEMPORARY TABLE t1 (c1 int) ENGINE=HEAP;

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  CREATE TABLE t1 (c1 int) ENGINE=MYISAM; 
END |

delimiter ;|
--ERROR ER_DISABLED_STORAGE_ENGINE
CALL p1();
DROP PROCEDURE p1;

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t1 (c1 int) PARTITION BY KEY (c1) PARTITIONS 1;

INSERT INTO tp1 VALUES(1);
DROP TABLE tp1;
# Check creation of table is disallowed under a dynamic storage plugin.
--replace_regex /\.dll/.so/
eval INSTALL PLUGIN example SONAME '$EXAMPLE_PLUGIN';

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t1(a int) ENGINE=EXAMPLE;
UNINSTALL PLUGIN example;

# Reload the plugin and ensure table creation is disallowed.
--replace_regex /\.dll/.so/
eval INSTALL PLUGIN example SONAME '$EXAMPLE_PLUGIN';

--ERROR ER_DISABLED_STORAGE_ENGINE
CREATE TABLE t1(a int) ENGINE=EXAMPLE;
UNINSTALL PLUGIN example;

SET default_storage_engine=MyISAM;
SET default_tmp_storage_engine=MyISAM;
SET default_storage_engine=default;
SET default_tmp_storage_engine=default;

--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server
--source include/wait_until_disconnected.inc

--exec echo "restart: --skip-grant-tables --disabled_storage_engines=innodb,myisam,heap,example" >$MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--enable_reconnect
--source include/wait_until_connected_again.inc
CREATE TABLE t1(a int) ENGINE=MYISAM;
DROP TABLE t1;

--source include/restart_mysqld.inc