mirror of
https://github.com/MariaDB/server.git
synced 2025-11-03 20:36:16 +01:00
80439e6918
Any TMP_TABLE_SHARE must always have at least one TABLE instance. So whenever a temporary TABLE that is marked for reopen is closed, reopen it at once if its TMP_TABLE_SHARE list of tables becomes empty.
280 lines
8.6 KiB
Text
280 lines
8.6 KiB
Text
--echo #
|
|
--echo # MDEV-12459: The information_schema tables for getting temporary tables
|
|
--echo # info is missing, at least for innodb, there is no
|
|
--echo # INNODB_TEMP_TABLE_INFO
|
|
--echo #
|
|
|
|
# Save the initial number of concurrent sessions
|
|
--source include/count_sessions.inc
|
|
--source include/have_innodb.inc
|
|
|
|
--echo # -------------------------------
|
|
--echo # Test shadowing of a base table
|
|
--echo # -------------------------------
|
|
|
|
create database some_db;
|
|
use some_db;
|
|
|
|
--echo # Creating temporary table with the same name shadows the base table
|
|
--echo # in `show create` and by design, should not raise any warning
|
|
create table t(t int);
|
|
create temporary table t(t int);
|
|
show create table t;
|
|
select table_schema, table_name, temporary from information_schema.tables where table_name='t';
|
|
drop table t;
|
|
drop table t;
|
|
use test;
|
|
|
|
--echo # ------------------------
|
|
--echo # IS.tables tests
|
|
--echo # ------------------------
|
|
|
|
--echo # Create first temporary table
|
|
create temporary table test.t_temp(t int);
|
|
insert into t_temp values (1),(2), (3);
|
|
|
|
--echo # Show results
|
|
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
|
|
|
|
--echo # Create the base table with the same name (both should be visible)
|
|
# Create the base table with the same name as temporary table.
|
|
create table test.t_temp(t int);
|
|
insert into t_temp values (-1),(-2);
|
|
|
|
--echo # Show results
|
|
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary';
|
|
|
|
create database my_db;
|
|
--echo # Create the temporary table with the same name in new DB
|
|
create temporary table my_db.t_temp (t int);
|
|
insert into my_db.t_temp values (-2),(-1);
|
|
--echo # Show results
|
|
--horizontal_results
|
|
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary'
|
|
order by table_schema desc, table_name desc, table_type desc;
|
|
|
|
connect (con1,localhost,root,,my_db,,);
|
|
|
|
--echo # Create the temporary table with the same name in new connection
|
|
create temporary table t_temp(t int);
|
|
insert into t_temp values (4),(5),(6), (7);
|
|
--echo # Show results
|
|
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary'
|
|
order by table_schema desc, table_name desc, table_type desc;
|
|
|
|
connection default;
|
|
|
|
--echo # Show results in default connection
|
|
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary'
|
|
order by table_schema desc, table_name desc, table_type desc;
|
|
|
|
--echo # Check shadowing and (no)warning with explicit referencing database
|
|
create table some_db.my_t (t int);
|
|
show warnings;
|
|
create temporary table some_db.my_t (t int);
|
|
show warnings;
|
|
|
|
--echo # Show results
|
|
select table_schema, table_name, temporary from information_schema.tables where table_type='temporary'
|
|
order by table_schema desc, table_name desc, table_type desc;
|
|
|
|
# Check with sequences
|
|
use test;
|
|
create table t1 (a int);
|
|
create sequence s1;
|
|
create temporary table t1 (b int);
|
|
create temporary sequence s1;
|
|
create temporary sequence s2;
|
|
select table_schema, table_name, table_type, temporary from information_schema.tables where table_schema = 'test'
|
|
order by table_schema desc, table_name desc, table_type desc;
|
|
|
|
drop table t1;
|
|
drop table t1;
|
|
drop table s1;
|
|
drop table s1;
|
|
drop table s2;
|
|
|
|
# First we are removing temporary table and after base table
|
|
drop table some_db.my_t;
|
|
drop table some_db.my_t;
|
|
|
|
disconnect con1;
|
|
|
|
# Drop both temporary and "real" table from test.
|
|
drop table test.t_temp;
|
|
drop table test.t_temp;
|
|
|
|
drop database my_db;
|
|
drop database some_db;
|
|
|
|
# Wait till all disconnects are completed
|
|
--source include/wait_until_count_sessions.inc
|
|
|
|
--echo #
|
|
--echo # MDEV-28332: Alter on temporary table causes ER_TABLE_EXISTS_ERROR note
|
|
--echo #
|
|
create table t (a int);
|
|
create temporary table t (b int);
|
|
alter table t add c int;
|
|
|
|
# Cleanup
|
|
drop temporary table t;
|
|
drop table t;
|
|
|
|
--echo #
|
|
--echo # MDEV-28334: SHOW TABLE STATUS shows all temporary tables
|
|
--echo # ignoring database and conditions
|
|
--echo #
|
|
|
|
create temporary table test.tmp_in_test (a int);
|
|
create table test.base_in_test (t int);
|
|
# The base table with the same name as temporary table
|
|
create table test.tmp_in_test (t int);
|
|
# The temporary InnoDB table - CREATE TIME should be NULL, MDEV-28333
|
|
create temporary table test.tmp_innodb_in_test (a int) engine=InnoDB;
|
|
|
|
create database mysqltest;
|
|
use mysqltest;
|
|
|
|
# This should show tables from currently used DB
|
|
# no temporary tables created and empty result set should be returned
|
|
show table status;
|
|
|
|
# The same as before
|
|
show table status in mysqltest;
|
|
|
|
# The should show all ables from `test` DB
|
|
--replace_column 12 # 13 # 14 #
|
|
--horizontal_results
|
|
show table status in test;
|
|
|
|
# The same as before
|
|
--replace_column 12 # 13 # 14 #
|
|
--horizontal_results
|
|
show table status from test;
|
|
|
|
|
|
--echo # check that InnoDB temporary table
|
|
--echo # has a NULL value for `Create time` column (MDEV-28333)
|
|
select create_time from information_schema.tables where table_name='tmp_innodb_in_test';
|
|
|
|
# This shouldn't give any results
|
|
show table status like 'nonexisting';
|
|
|
|
# Cleanup
|
|
drop database mysqltest;
|
|
drop table test.base_in_test;
|
|
# We need first to drop temporary table that shadows the base table
|
|
drop table test.tmp_in_test;
|
|
drop table test.tmp_in_test;
|
|
|
|
--echo #
|
|
--echo # MDEV-28453: SHOW commands are inconsistent for temporary tables
|
|
--echo #
|
|
create database mysqltest;
|
|
use mysqltest;
|
|
create table t (a int, key(a)) engine=Aria;
|
|
create temporary table t (b int, key(b)) engine=MyISAM;
|
|
create table base_table(t int);
|
|
create temporary table tmp_table (b int, key(b));
|
|
create sequence s1;
|
|
create temporary sequence s1;
|
|
create temporary sequence s2;
|
|
|
|
# This should show all tables
|
|
show tables;
|
|
# This should show all tables with additional table_type
|
|
show full tables;
|
|
# This is already showing all tables (not related to bug)
|
|
--replace_column 12 # 13 # 14 #
|
|
show table status;
|
|
# This is showing temporary table as expected since it is shadowing base table
|
|
show columns in t;
|
|
# This is showing temporary table as expected since it is shadowing base table
|
|
show index in t;
|
|
|
|
# Cleanup
|
|
drop database mysqltest;
|
|
use test;
|
|
|
|
# many instances of the table temp table:
|
|
show full tables;
|
|
select * from tmp_innodb_in_test, tmp_innodb_in_test x;
|
|
show full tables;
|
|
drop temporary tables tmp_innodb_in_test;
|
|
|
|
# non-existent db
|
|
create temporary table foo.t1 (a int); # yup, that works
|
|
select table_schema, table_name from information_schema.tables where table_type='temporary';
|
|
drop temporary table foo.t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-28351 Assertion `this->file->children_attached' failed in ha_myisammrg::info
|
|
--echo #
|
|
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
|
|
insert into t1 values (1);
|
|
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MERGE UNION=(t1);
|
|
CREATE TABLE t3 (a INT) ENGINE=MERGE UNION=(t1);
|
|
--sorted_result
|
|
--replace_column 10 X 11 X 12 X 13 X 15 X 16 X 22 X
|
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
--echo #
|
|
--echo # MDEV-31618: Server crashes in
|
|
--echo # process_i_s_table_temporary_tables/get_all_tables
|
|
--echo #
|
|
|
|
CREATE TEMPORARY SEQUENCE seq1;
|
|
# Check show temp tables before alter
|
|
SHOW FULL TABLES;
|
|
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type='temporary sequence';
|
|
--error 4086
|
|
ALTER TABLE `seq1` CHANGE `cache_size` cache_size int;
|
|
# Check show temp tables after alter
|
|
SHOW FULL TABLES;
|
|
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type='temporary sequence';
|
|
|
|
CREATE OR REPLACE TEMPORARY SEQUENCE seq1;
|
|
# Check show temp tables after create/replace alter
|
|
SHOW FULL TABLES;
|
|
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type='temporary sequence';
|
|
DROP TABLE seq1;
|
|
DROP TABLE mysqltest.s1;
|
|
DROP TABLE mysqltest.s2;
|
|
|
|
--echo #
|
|
--echo # MDEV-31618: Server crashes in
|
|
--echo # process_i_s_table_temporary_tables/get_all_tables after alter in rename
|
|
--echo #
|
|
# Check on temporary tables
|
|
CREATE table seq1 (a bigint, b int);
|
|
CREATE TEMPORARY TABLE tmp LIKE seq1;
|
|
INSERT tmp SELECT * FROM seq1;
|
|
ALTER TABLE tmp RENAME TO seq1;
|
|
--error 4086,1060
|
|
ALTER TABLE seq1 CHANGE a b int ;
|
|
--error 1050
|
|
RENAME TABLE seq1 TO seq1;
|
|
show full tables;
|
|
drop table seq1;
|
|
drop table seq1;
|
|
|
|
# Check on sequences
|
|
CREATE SEQUENCE seq2;
|
|
CREATE TEMPORARY sequence tmp;
|
|
show full tables;
|
|
ALTER table `tmp` RENAME TO seq1;
|
|
show full tables;
|
|
--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
|
|
ALTER TABLE `seq1` CHANGE `cache_size` cache_size int ;
|
|
show full tables;
|
|
--error ER_TABLE_EXISTS_ERROR
|
|
RENAME TABLE seq1 TO seq1;
|
|
show full tables;
|
|
RENAME TABLE seq1 TO seq3;
|
|
show full tables;
|
|
drop table seq2;
|
|
show full tables;
|
|
drop table seq3;
|
|
show full tables;
|