path: root/mysql-test/suite/innodb_zip/t/wl6560.test

#
# WL#6560: InnoDB: separate tablespace for innodb-temp-tables.
#

--source include/have_innodb.inc
--source include/have_innodb_zip.inc
# Embedded server does not restart of server
--source include/not_embedded.inc
-- source include/big_test.inc

--disable_query_log
call mtr.add_suppression("Tablespace innodb_temporary ran out of space. Please add another file or use 'autoextend' for the last file in setting innodb_temp_data_file_path.");
call mtr.add_suppression("The table 't1' is full");
--enable_query_log

################################################################################
#
# Will test following scenarios:
# 1.  creation of shared temp-tablespace.
# 2.  ddl + dml operation involving temp-tablespace.
#     insert/delete/update/select
#     create/drop/alter/truncate/import-discard (though blocked).
# 3.  ddl + dml operation on compressed table.
#     (table doesn't reside in shared temp-tablespace).
# 4.  Test bulk-loading that result in auto-extension of temp-tablespace.
# 5.  re-creation of temp-tablespace on re-start.
#     also to ensure non-existence of existing temp-table.
# 6.  restart server in innodb-read-only mode. this will also
#     block creation of temp-tables.
# 7.  try starting server with shared and temp-tablespace filename same.
# 8.  try re-starting server with param so that temp-tablespace can't be
#     expanded and insert enough data to make it full.
# 9.  tests for different row format types and key block sizes for
#     compressed tables.
# 10. try restarting server with raw device specified for temp-tablespace.
# 11. try restarting server with temp-tablespace less than min. threshold
# 12. no file specified for temp-tablespace.
################################################################################

#-----------------------------------------------------------------------------
#
# create test-bed
#
let $per_table = `select @@innodb_file_per_table`;

set global innodb_file_per_table = off;
let $MYSQL_TMP_DIR = `select @@tmpdir`;
let $MYSQL_DATA_DIR = `select @@datadir`;
let SEARCH_FILE = $MYSQLTEST_VARDIR/log/my_restart.err;
let $args = --loose-console --core-file > $SEARCH_FILE 2>&1;
let crash = --loose-console > $SEARCH_FILE 2>&1 --innodb-force-recovery-crash;
let readonly = $args --innodb_read_only;
let nameconflicts = $args --innodb_data_file_path="ibdata1:12M:autoextend:max:134217728" --innodb_temp_data_file_path="ibdata1:12M:autoextend";
let rawdevice1 = $args --innodb_temp_data_file_path="/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw";
let rawdevice2 = $args --innodb_temp_data_file_path="/dev/hdd1:3Graw;/dev/hdd2:2Graw";
let sizeoftempfile1 = $args --innodb_temp_data_file_path="ibtmp1:2M:autoextend";
let sizeoftempfile2 = $args --innodb_data_file_path="ibdata1:2M:autoextend";
let notemptablespacefile = $args --innodb_temp_data_file_path="";

#-----------------------------------------------------------------------------
#
# 1. creation of shared temp-tablespace.
#
--echo # files in MYSQL_DATA_DIR
--list_files $MYSQL_DATA_DIR/ ibtmp*


#-----------------------------------------------------------------------------
#
# 2. ddl + dml operation involving temp-tablespace.
#    insert/delete/update/select
#    create/drop/alter/truncate/import-discard (though blocked).
#
select @@global.innodb_file_per_table;
create temporary table t1 (i int, f float, c char(100)) engine=innodb;
#
--source suite/innodb_zip/include/innodb_temp_table_dml.inc
#
# alter table
--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
alter table t1 discard tablespace;
--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
alter table t1 import tablespace;
#
# drop table
drop table t1;

#-----------------------------------------------------------------------------
#
# 3. ddl + dml operation on compressed table.
#    (table doesn't reside in shared temp-tablespace).
#
--echo #files in MYSQL_TMP_DIR
--list_files $MYSQL_TMP_DIR/ *.ibd
set global innodb_file_per_table = 1;
select @@global.innodb_file_per_table;
create temporary table t1
	(i int, f float, c char(100)) engine = innodb key_block_size = 4;
show create table t1;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
#
--source suite/innodb_zip/include/innodb_temp_table_dml.inc
#
# alter table
--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
alter table t1 discard tablespace;
#
# drop table
drop table t1;
set global innodb_file_per_table = off;

#-----------------------------------------------------------------------------
#
# 4. Test bulk-loading that result in auto-extension of temp-tablespace.
#
create temporary table t1
	(keyc int, c1 char(100), c2 char(100),
	 primary key(keyc)) engine = innodb;
delimiter |;
CREATE PROCEDURE populate_t1()
BEGIN
	DECLARE i INT DEFAULT 1;
	while (i <= 20000) DO
		insert into t1 values (i, 'a', 'b');
		SET i = i + 1;
	END WHILE;
END|
delimiter ;|
set autocommit=0;
select count(*) from t1;
call populate_t1();
select count(*) from t1;
select * from t1 limit 10;
set autocommit=1;
truncate table t1;
select count(*) from t1;
#
drop procedure populate_t1;
drop table t1;

#-----------------------------------------------------------------------------
#
# 5. re-creation of temp-tablespace on re-start.
#    also to ensure non-existence of existing temp-table.
#
create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;
insert into t1 values (1, 'c', 'b');
select * from t1;
#
--source include/restart_mysqld.inc
#
--echo # files in MYSQL_DATA_DIR
--list_files $MYSQL_DATA_DIR/ ibtmp*
use test;
--error ER_NO_SUCH_TABLE
select * from t1;

#-----------------------------------------------------------------------------
#
# 6. restart server in innodb-read-only mode. this will also
#    block creation of temp-tables.
#
#
--echo "testing temp-table creation in --innodb_read_only mode"
let $restart_parameters=--innodb-read-only;
--source include/restart_mysqld.inc
#
use test;
show tables;
--error ER_INNODB_READ_ONLY, 1005
create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;

#-----------------------------------------------------------------------------
#
# 7. try starting server with shared and temp-tablespace filename same.
#
--source include/shutdown_mysqld.inc
--echo "testing system and temp tablespace name conflict"
--error 1
--exec $MYSQLD_CMD $nameconflicts
let SEARCH_PATTERN = innodb_temporary and innodb_system file names seem to be the same;
--source ./include/search_pattern_in_file.inc
--remove_file $SEARCH_FILE
--echo "restarting server in normal mode"
--enable_reconnect
let $restart_parameters = restart;
--source include/start_mysqld.inc
#
show tables;
create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;
drop table t1;

#-----------------------------------------------------------------------------
#
# 8. try re-starting server with param so that temp-tablespace can't be expanded
#    and insert enough data to make it full.
#
--echo # test condition of full-temp-tablespace
let $restart_parameters=--innodb_temp_data_file_path=ibtmp1:12M;
--source include/restart_mysqld.inc
#
create temporary table t1
	(keyc int, c1 char(100), c2 char(100),
	 primary key(keyc)) engine = innodb;
delimiter |;
CREATE PROCEDURE populate_t1()
BEGIN
	DECLARE i INT DEFAULT 1;
	while (i <= 20000) DO
		insert into t1 values (i, 'a', 'b');
		SET i = i + 1;
	END WHILE;
END|
delimiter ;|
set autocommit=0;
select count(*) from t1;
--error ER_RECORD_FILE_FULL
call populate_t1();
#
drop procedure populate_t1;
drop table t1;

#-----------------------------------------------------------------------------
#
# 9. tests for different row format types and key block sizes for
#     compressed tables.
#
set innodb_strict_mode = off;
--disable_warnings
set global innodb_file_per_table = 0;
set global innodb_file_format = 'Antelope';
create temporary table t (
	i int)
	engine = innodb row_format = compressed;
--replace_regex /[0-9]+/NUMBER/
show warnings;
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = compressed key_block_size = 8;
--replace_regex /[0-9]+/NUMBER/
show warnings;
#
drop table t;
set global innodb_file_per_table = 1;
create temporary table t (
	i int)
	engine = innodb row_format = compressed key_block_size = 8;
--replace_regex /[0-9]+/NUMBER/
show warnings;
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = dynamic;
--replace_regex /[0-9]+/NUMBER/
show warnings;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
drop table t;
#
set innodb_strict_mode = on;
create temporary table t (
	i int)
	engine = innodb row_format = dynamic;
--replace_regex /[0-9]+/NUMBER/
drop table t;
#
set global innodb_file_format = 'Barracuda';
set innodb_strict_mode = off;
create temporary table t (
	i int)
	engine = innodb row_format = compressed key_block_size = 8;
--replace_regex /[0-9]+/NUMBER/
# explicitly disabling it else it will generate warning of ignoring
# key_block_size when suite is run with innodb-page-size=4k
#show warnings;
set innodb_strict_mode = default;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
#
drop table t;
create temporary table t (
	i int)
	engine = innodb row_format = compressed;
--replace_regex /[0-9]+/NUMBER/
show warnings;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = dynamic;
--replace_regex /[0-9]+/NUMBER/
show warnings;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
drop table t;
#
set innodb_strict_mode = on;
create temporary table t (
	i int)
	engine = innodb row_format = dynamic;
--replace_regex /[0-9]+/NUMBER/
show warnings;
drop table t;
set innodb_strict_mode = off;
#
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
create temporary table t (
	i int)
	engine = innodb row_format = dynamic key_block_size = 4;
--replace_regex /[0-9]+/NUMBER/
show warnings;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = compact;
--replace_regex /[0-9]+/NUMBER/
show warnings;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
drop table t;
#
create temporary table t (
	i int)
	engine = innodb key_block_size = 4;
--replace_regex /[0-9]+/NUMBER/
show warnings;
--echo #files in MYSQL_TMP_DIR
--replace_regex /#sql[0-9a-f_]*/#sql<temporary>/
--list_files $MYSQL_TMP_DIR/ *.ibd
drop table t;
#

#-----------------------------------------------------------------------------
#
# 10. try restarting server with raw device specified for temp-tablespace.
#
--source include/shutdown_mysqld.inc
--echo "testing temp tablespace non-support for raw device"
--error 1
--exec $MYSQLD_CMD $rawdevice1
let SEARCH_PATTERN = support raw device;
--source include/search_pattern_in_file.inc
--remove_file $SEARCH_FILE
--echo "testing temp tablespace non-support for raw device"
--error 1
--exec $MYSQLD_CMD $rawdevice2
let SEARCH_PATTERN = support raw device;
--source include/search_pattern_in_file.inc
--remove_file $SEARCH_FILE

--source include/start_mysqld.inc

show tables;
create temporary table t1 (
	keyc int, c1 char(100), c2 char(100)
	) engine = innodb;
drop table t1;

#-----------------------------------------------------------------------------
#
# 11. try restarting server with temp-tablespace less than min. threshold
#
--source include/shutdown_mysqld.inc
--echo "try starting server with temp-tablespace size < min. threshold"
--error 1
--exec $MYSQLD_CMD $sizeoftempfile1
let SEARCH_PATTERN = Tablespace size must be at least;
--source ./include/search_pattern_in_file.inc
--remove_file $SEARCH_FILE
--echo "try starting server with sys-tablespace size < min. threshold"
--error 1
--exec $MYSQLD_CMD $sizeoftempfile2
let SEARCH_PATTERN = Tablespace size must be at least;
--source ./include/search_pattern_in_file.inc
--remove_file $SEARCH_FILE

--source include/start_mysqld.inc

show tables;
create temporary table t1 (
	keyc int, c1 char(100), c2 char(100)
	) engine = innodb;
drop table t1;

#-----------------------------------------------------------------------------
#
# 12. no file specified for temp-tablespace.
#
--source include/shutdown_mysqld.inc

--echo "try starting server with no file specified for temp-tablespace"
--error 1
--exec $MYSQLD_CMD $notemptablespacefile
let SEARCH_PATTERN = init function returned error;
--source ./include/search_pattern_in_file.inc
--remove_file $SEARCH_FILE

--source include/start_mysqld.inc

show tables;
create temporary table t1 (
	keyc int, c1 char(100), c2 char(100)
	) engine = innodb;
drop table t1;