4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/innodb/t/temporary_table.test

699 lines
23 KiB
Plaintext
Raw Permalink Blame History

#
# InnoDB temporary table test case, including
# WL#6560: InnoDB: separate tablespace for innodb-temp-tables.
# WL#7899: InnoDB: Map compressed temporary tables to uncompressed
#
--source include/no_valgrind_without_big.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");
call mtr.add_suppression("Skip re-populating collations and character sets tables in InnoDB read-only mode.");
call mtr.add_suppression("Skip updating information_schema metadata in InnoDB read-only mode.");
call mtr.add_suppression("Skipped updating resource group metadata in InnoDB read only mode.");
call mtr.add_suppression("innodb-rollback-segments");
--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 row_format=dynamic table
# (table would still 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, which would be ignored when innodb_strict_mode is OFF,
# but regarded as an error when innodb_strict_mode is ON.
# 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.
# 13. Test the structure and info of I_S.innodb_temp_table_info
################################################################################
#-----------------------------------------------------------------------------
#
# create test-bed
#
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).
#
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 row_format=dynamic table
# (table would still reside in shared temp-tablespace).
#
create temporary table t1
(i int, f float, c char(100)) engine = innodb row_format=dynamic;
show create table t1;
#
--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;
#-----------------------------------------------------------------------------
#
# 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 = restart: --innodb-read-only;
--source include/restart_mysqld.inc
#
use test;
show tables;
--error ER_INNODB_READ_ONLY
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.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.
# Note that each rollback segment will take one full page
--echo # test condition of full-temp-tablespace
let $restart_parameters = restart: --innodb-rollback-segments=32 --innodb_temp_data_file_path=ibtmp1:64M;
--source include/restart_mysqld.inc
#
create temporary table t1
(keyc int, c1 char(250), c2 char(250), c3 char(250),
primary key(keyc, c1, c2, c3)) engine = innodb charset latin1;
delimiter |;
CREATE PROCEDURE populate_t1()
BEGIN
DECLARE i INT DEFAULT 1;
while (i <= 20000) DO
insert into t1 values (i, repeat('a', 250), repeat('b', 250), repeat('c', 250));
SET i = i + 1;
END WHILE;
END|
delimiter ;|
set autocommit=0;
select count(*) from t1;
# we now use session temporary tablespaces and they can
# expanded.
call populate_t1();
#
drop procedure populate_t1;
drop table t1;
#-----------------------------------------------------------------------------
#
# 9. tests for different row format types and key block sizes for
# compressed tables, which would be ignored when innodb_strict_mode is OFF,
# but regarded as an error when innodb_strict_mode is ON.
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
create temporary table t (
i int)
engine = innodb row_format = compressed;
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
create temporary table t (
i int)
engine = innodb row_format = compressed key_block_size = 8;
#
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 = 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;
#
create temporary table t (
i int)
engine = innodb row_format = compressed key_block_size = 8;
show warnings;
set innodb_strict_mode = default;
#
drop table t;
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
create temporary table t (
i int)
engine = innodb row_format = compressed;
--echo #files in MYSQL_TMP_DIR, expecting only default temporary tablespace file
--list_files $MYSQL_TMP_DIR/ *.ibd
--list_files $MYSQL_DATA_DIR/ ibtmp*
#
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 = 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;
#
create temporary table t (
i int)
engine = innodb row_format = dynamic key_block_size = 4;
--replace_regex /[0-9]+/NUMBER/
show warnings;
drop table t;
#
create temporary table t (
i int)
engine = innodb row_format = compact;
--replace_regex /[0-9]+/NUMBER/
show warnings;
drop table t;
#
create temporary table t (
i int)
engine = innodb key_block_size = 4;
--replace_regex /[0-9]+/NUMBER/
show warnings;
drop table t;
#
#-----------------------------------------------------------------------------
#
# Before doing following test, create a temp table,
# get its space no
#
CREATE TEMPORARY TABLE t1(
i INT
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
CREATE TABLE space_id(
id INT UNSIGNED
) ENGINE = InnoDB;
INSERT INTO space_id SELECT space FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPACT;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 8;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
# Test alter table for temporary tables with row format = compressed or
# key_block_size
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
ALTER TABLE t1 KEY_BLOCK_SIZE = 4 ROW_FORMAT = COMPRESSED;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
# Set innodb_strict_mode=ON and test compressed temporary tables again.
set innodb_strict_mode = ON;
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
--replace_regex /[0-9][0-9]+/NUMBER/
SHOW WARNINGS;
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4, ROW_FORMAT = COMPACT;
--replace_regex /[0-9][0-9]+/NUMBER/
SHOW WARNINGS;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
--replace_regex /[0-9][0-9]+/NUMBER/
SHOW WARNINGS;
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
--replace_regex /[0-9][0-9]+/NUMBER/
SHOW WARNINGS;
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;
--replace_regex /[0-9][0-9]+/NUMBER/
SHOW WARNINGS;
#
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 7;
--replace_regex /[0-9][0-9]+/NUMBER/
SHOW WARNINGS;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY,
c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
--replace_regex /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
i INT NOT NULL PRIMARY KEY
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
#
# Some checking for turning innodb_strict_mode ON and OFF.
set innodb_strict_mode = OFF;
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
set innodb_strict_mode = ON;
ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
SHOW CREATE TABLE t1;
set innodb_strict_mode = OFF;
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
ALTER TABLE t1 KEY_BLOCK_SIZE = 8;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
set innodb_strict_mode = ON;
--error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
ALTER TABLE t1 ADD COLUMN j INT;
--replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
SHOW WARNINGS;
SHOW CREATE TABLE t1;
set innodb_strict_mode = OFF;
ALTER TABLE t1 KEY_BLOCK_SIZE = 0;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
set innodb_strict_mode = ON;
ALTER TABLE t1 ADD COLUMN j INT;
SHOW CREATE TABLE t1;
DROP TABLE t1, space_id;
set innodb_strict_mode = OFF;
#-----------------------------------------------------------------------------
#
# 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.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.inc
--remove_file $SEARCH_FILE
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;
#-----------------------------------------------------------------------------
#
# 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,42
--exec $MYSQLD_CMD $sizeoftempfile1
let SEARCH_PATTERN = Tablespace size must be at least;
--source ./include/search_pattern.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.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.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;
#-----------------------------------------------------------------------------
#
# 13. Test the structure and info of I_S.innodb_temp_table_info
#
SHOW CREATE TABLE information_schema.innodb_temp_table_info;
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_temp_table_info;
CREATE TEMPORARY TABLE t1 (
i int,
f float
) ENGINE = InnoDB;
CREATE TEMPORARY TABLE t2 (
i int,
f float
) ENGINE = InnoDB;
SELECT count(*) AS `Expect 2` FROM information_schema.innodb_temp_table_info;
DROP TABLE t1, t2;
Reference in New Issue View Git Blame Copy Permalink