path: root/mysql-test/suite/innodb/t/temporary_table.test

#
# 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/have_innodb.inc
--source include/innodb_page_size.inc
# Embedded server does not restart of server
--source include/not_embedded.inc
--source include/no_valgrind_without_big.inc

--disable_query_log
call mtr.add_suppression("Can't create/write to file '' \\\(Errcode: 20 \"Not a directory\"\\\)");
call mtr.add_suppression("Can't create/write to file '/dev/null/.*/ib");
call mtr.add_suppression("InnoDB: Unable to create temporary file");
call mtr.add_suppression("last file in setting innodb_temp_data_file_path");
call mtr.add_suppression("The table 't1' is full");
call mtr.add_suppression("Plugin 'InnoDB' init function returned error");
call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed");
call mtr.add_suppression("InnoDB: Tablespace doesn't support raw devices");
call mtr.add_suppression("InnoDB: Plugin initialization aborted");
call mtr.add_suppression("innodb_temporary and innodb_system file names seem to be the same");
call mtr.add_suppression("Could not create the shared innodb_temporary");
call mtr.add_suppression("InnoDB: syntax error in file path");
call mtr.add_suppression("InnoDB: Unable to parse innodb_temp_data_file_path=");
--enable_query_log

let $MYSQL_TMP_DIR = `select @@tmpdir`;
let $MYSQL_DATA_DIR = `select @@datadir`;

--echo # files in MYSQL_DATA_DIR
--list_files $MYSQL_DATA_DIR/ ibtmp*

create temporary table t1 (i int, f float, c char(100)) engine=innodb;
insert into t1 values (100, 1.1, 'pune');
insert into t1 values (99, 1.2, 'mumbai');
insert into t1 values (98, 1.3, 'jaipur');
insert into t1 values (97, 1.4, 'delhi');
insert into t1 values (96, 1.5, 'ahmedabad');
select * from t1;
select * from t1 where i = 98;
select * from t1 where i < 100;

explain select * from t1 where f > 1.29999;
alter table t1 add index sec_index(f);
explain select * from t1 where f > 1.29999;
select * from t1 where f > 1.29999;

explain select * from t1 where i = 100;
alter table t1 add unique index pri_index(i);
explain select * from t1 where i = 100;
select * from t1 where i = 100;

delete from t1 where i < 97;
select * from t1;
insert into t1 values (96, 1.5, 'kolkata');
select * from t1;

update t1 set f = 1.44 where c = 'delhi';
select * from t1;

truncate table t1;
insert into t1 values (100, 1.1, 'pune');
insert into t1 values (99, 1.2, 'mumbai');
insert into t1 values (98, 1.3, 'jaipur');
insert into t1 values (97, 1.4, 'delhi');
insert into t1 values (96, 1.5, 'ahmedabad');
select * from t1;

--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
alter table t1 discard tablespace;
--error ER_CANNOT_DISCARD_TEMPORARY_TABLE
alter table t1 import tablespace;
drop temporary table t1;

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 * from t1;
#

--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;
begin;
--error ER_RECORD_FILE_FULL
call populate_t1();

drop procedure populate_t1;

--echo # test read-only mode
--let $restart_parameters = --innodb-read-only
--source include/restart_mysqld.inc

--echo # files in MYSQL_DATA_DIR
--list_files $MYSQL_DATA_DIR/ ibtmp*

--error ER_NO_SUCH_TABLE
select * from t1;
show tables;
--error ER_CANT_CREATE_TABLE
create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;

SET GLOBAL innodb_encrypt_tables=DEFAULT;

--echo # test various bad start-up parameters

let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err;
let SEARCH_ABORT = NOT FOUND;
let $check_no_innodb=SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb'
AND support IN ('YES', 'DEFAULT', 'ENABLED');

# We cannot use include/restart_mysqld.inc in this particular test,
# because SHOW STATUS would fail due to unwritable (nonexistent) tmpdir.
--source include/shutdown_mysqld.inc
--exec echo "restart: --tmpdir=/dev/null/$MYSQL_TMP_DIR --skip-innodb-fast-shutdown" > $_expect_file_name
--enable_reconnect
--disable_result_log
--disable_query_log
let $counter= 5000;
let $mysql_errno= 9999;
while ($mysql_errno)
{
  --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,2026
  select 1;

  dec $counter;
  if (!$counter)
  {
    --die Server failed to restart
  }
  --sleep 0.1
}
--enable_query_log
--enable_result_log
--disable_reconnect
--let SEARCH_PATTERN= InnoDB: Unable to create temporary file
--source include/search_pattern_in_file.inc

--let $restart_parameters= --innodb_data_file_path=ibdata1:12M:autoextend --innodb_temp_data_file_path=ibdata1:12M:autoextend
--source include/restart_mysqld.inc
--let SEARCH_PATTERN = innodb_temporary and innodb_system file names seem to be the same
--source include/search_pattern_in_file.inc
eval $check_no_innodb;

--let $restart_parameters= --innodb_temp_data_file_path=foobar:3Gnewraw
--source include/restart_mysqld.inc
--let SEARCH_PATTERN = support raw device
--source include/search_pattern_in_file.inc
eval $check_no_innodb;

--let $restart_parameters= --innodb_temp_data_file_path=barbar:3Graw
--source include/restart_mysqld.inc
--source include/search_pattern_in_file.inc
eval $check_no_innodb;

--let $restart_parameters= --innodb_temp_data_file_path=
--source include/restart_mysqld.inc
--let SEARCH_PATTERN = InnoDB: syntax error in file path
--source include/search_pattern_in_file.inc
eval $check_no_innodb;

--let $restart_parameters=
--source include/restart_mysqld.inc

--error ER_UNSUPPORTED_COMPRESSED_TABLE
create temporary table t (
	i int)
	engine = innodb row_format = compressed;
#
--error ER_UNSUPPORTED_COMPRESSED_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;
show warnings;
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = dynamic;
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_UNSUPPORTED_COMPRESSED_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;
show warnings;
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = dynamic;
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;
show warnings;
drop table t;
#
create temporary table t (
	i int)
	engine = innodb row_format = compact;
show warnings;
drop table t;
#
create temporary table t (
	i int)
	engine = innodb key_block_size = 4;
show warnings;
drop table t;
#

CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPACT;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#

CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
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;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
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;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
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;

ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
ALTER TABLE t1 KEY_BLOCK_SIZE = 4 ROW_FORMAT = COMPRESSED;
SHOW WARNINGS;
SHOW CREATE TABLE t1;

ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;

# Set innodb_strict_mode=ON and test compressed temporary tables again.
set innodb_strict_mode = ON;

--error ER_UNSUPPORTED_COMPRESSED_TABLE
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;
#

--error ER_UNSUPPORTED_COMPRESSED_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;
SHOW WARNINGS;
#

CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#

--error ER_UNSUPPORTED_COMPRESSED_TABLE
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;
#

--error ER_UNSUPPORTED_COMPRESSED_TABLE
CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
SHOW WARNINGS;
#

--error ER_UNSUPPORTED_COMPRESSED_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;
SHOW WARNINGS;
#

--error ER_UNSUPPORTED_COMPRESSED_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;
SHOW WARNINGS;
#

CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY,
  c CHAR(10) NOT NULL
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#

CREATE TEMPORARY TABLE t1 (
  i INT NOT NULL PRIMARY KEY
) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;

--error ER_UNSUPPORTED_COMPRESSED_TABLE
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
SHOW WARNINGS;

--error ER_UNSUPPORTED_COMPRESSED_TABLE
ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
SHOW WARNINGS;

--error ER_UNSUPPORTED_COMPRESSED_TABLE
ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
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;
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;
SHOW WARNINGS;

ALTER TABLE t1 KEY_BLOCK_SIZE = 8;
SHOW WARNINGS;

set innodb_strict_mode = ON;

--error ER_UNSUPPORTED_COMPRESSED_TABLE
ALTER TABLE t1 ADD COLUMN j INT;
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;

CREATE TEMPORARY TABLE t1(f1 INT, KEY(f1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES(NULL);
UPDATE t1 SET f1 = 0;
START TRANSACTION;
UPDATE t1 SET f1 = 4;
UPDATE t1 SET f1 = 0;
ROLLBACK;
SELECT * FROM t1;
DROP TABLE t1;

delimiter //;
create procedure t1_proc()
begin
DECLARE var INT UNSIGNED;
CREATE TEMPORARY TABLE t1(f1 INT UNSIGNED, f2 INT UNSIGNED, KEY( f1, f2 ) )engine=innodb;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
INSERT INTO t1 SET f1 = 1, f2 = 1;
UPDATE t1 SET f2 = 2;
SET var = ( SELECT 1 FROM t1 );
DROP TABLE t1;
END//
delimiter ;//

call t1_proc;
drop procedure t1_proc;

--echo #
--echo # MDEV-15874 CREATE TABLE creates extra transaction
--echo #
call mtr.add_suppression("Warning 150 Create  table `mysqld.1`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.*");
SET FOREIGN_KEY_CHECKS = 0;
--error ER_CANT_CREATE_TABLE
CREATE TEMPORARY TABLE t1(f1 INT NOT NULL,
			  FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB;

CREATE TABLE t (c INT) ENGINE=InnoDB;
INSERT INTO t VALUES(0);
CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB;
START TRANSACTION READ ONLY;
INSERT INTO t2 SELECT * FROM t;
COMMIT;
DROP TABLE t, t2;

CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB;
START TRANSACTION READ ONLY;
--error ER_WRONG_VALUE_COUNT_ON_ROW
INSERT INTO t VALUES(0);
SAVEPOINT s;
INSERT INTO t VALUES(0,0);
COMMIT;
DROP TABLE t;

CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB;
START TRANSACTION READ ONLY;
--error ER_WRONG_VALUE_COUNT_ON_ROW
INSERT INTO t VALUES(0);
SAVEPOINT s;
INSERT INTO t VALUES(0,0);
ROLLBACK;
DROP TABLE t;

CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
START TRANSACTION READ ONLY;
UPDATE t1 SET a= 2;
COMMIT;
DROP TABLE t1;

CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB;
SET SESSION TRANSACTION_READ_ONLY=TRUE;
LOCK TABLE test.t READ;
SELECT * FROM t;
INSERT INTO t VALUES(0xADC3);
SET SESSION TRANSACTION_READ_ONLY=FALSE;
DROP TABLE t;

CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
START TRANSACTION READ ONLY;
UPDATE t1 SET a= 2;
COMMIT;
DROP TABLE t1;

CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 1);
START TRANSACTION READ ONLY;
UPDATE t1 SET b= 2;
COMMIT;
DROP TABLE t1;

CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int, c varchar(255)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 1, repeat('a', 200));
START TRANSACTION READ ONLY;
UPDATE t1 SET b= 2, c=repeat('a', 250);
COMMIT;
DROP TABLE t1;

CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
START TRANSACTION READ ONLY;
UPDATE t1 SET a= 2;
ROLLBACK;
DROP TABLE t1;

CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
START TRANSACTION READ ONLY;
DELETE FROM t1 WHERE a= 2;
COMMIT;
DROP TABLE t1;

CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB;
INSERT INTO tmp () VALUES (),();
SET TRANSACTION_READ_ONLY= 1;
INSERT INTO tmp SELECT * FROM tmp;
SET TRANSACTION_READ_ONLY= 0;
DROP TABLE tmp;

SET sql_mode='';
SET GLOBAL TRANSACTION_READ_ONLY=TRUE;
CREATE TEMPORARY TABLE t (c INT);
SET SESSION TRANSACTION_READ_ONLY=DEFAULT;
INSERT INTO t VALUES(1);
INSERT INTO t SELECT * FROM t;
SET SESSION TRANSACTION_READ_ONLY=FALSE;
SET GLOBAL TRANSACTION_READ_ONLY=OFF;
DROP TABLE t;

CREATE TEMPORARY TABLE t(a INT);
SET SESSION TRANSACTION_READ_ONLY=ON;
LOCK TABLE t READ;
SELECT COUNT(*)FROM t;
INSERT INTO t VALUES (0);
SET SESSION TRANSACTION_READ_ONLY=OFF;
DROP TABLE t;

CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB;
INSERT INTO t VALUES (1);
START TRANSACTION READ ONLY;
UPDATE t SET a = NULL;
ROLLBACK;
DROP TABLE t;

--echo #
--echo # MDEV-29886 Assertion !index->table->is_temporary() failed
--echo # in trx_undo_prev_version_build upon CHECK
--echo #
CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB SELECT 1;
UPDATE t SET a=2;
CHECK TABLE t;
CHECK TABLE t EXTENDED;
DROP TEMPORARY TABLE t;

--echo #
--echo # MDEV-29978 Corruption errors upon CHECK on temporary InnoDB table
--echo #

CREATE TEMPORARY TABLE t (f INT UNIQUE) ENGINE=InnoDB;
INSERT INTO t (f) VALUES (1),(2);
CHECK TABLE t;
CHECK TABLE t EXTENDED;
DROP TEMPORARY TABLE t;

--echo # End of 10.6 tests