210 lines
7.2 KiB
Plaintext
210 lines
7.2 KiB
Plaintext
SET DEFAULT_STORAGE_ENGINE=InnoDB;
|
|
#
|
|
# If CREATE TABLESPACE fails after the file is created, it should not remain.
|
|
#
|
|
SET SESSION DEBUG='+d,innodb_fail_to_update_tablespace_dict';
|
|
CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB;
|
|
ERROR HY000: Failed to create TABLESPACE s_def
|
|
SET SESSION DEBUG='-d,innodb_fail_to_update_tablespace_dict';
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
# MYSQLD_DATADIR/
|
|
lizard.ibd
|
|
mysql.ibd
|
|
DROP TABLESPACE s_def;
|
|
ERROR HY000: Tablespace s_def doesn't exist.
|
|
#
|
|
# Assigning general tablespace to myisam,memory is igonred
|
|
#
|
|
USE test;
|
|
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
|
|
CREATE TABLE t1 (a int) ENGINE=innodb TABLESPACE=s1;
|
|
CREATE TABLE t2 (a int) ENGINE=memory TABLESPACE=s1;
|
|
CREATE TABLE t3 (a int) ENGINE=myisam TABLESPACE=s1;
|
|
INSERT INTO t1 VALUES ( 11);
|
|
INSERT INTO t2 VALUES ( 21);
|
|
INSERT INTO t3 VALUES ( 31);
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 s1 161 7 Dynamic 0 General
|
|
ALTER TABLE t2 TABLESPACE=s1;
|
|
ALTER TABLE t3 TABLESPACE=s1;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 s1 161 7 Dynamic 0 General
|
|
# MYSQLD_DATADIR/
|
|
lizard.ibd
|
|
mysql.ibd
|
|
s1.ibd
|
|
show tables;
|
|
Tables_in_test
|
|
t1
|
|
t2
|
|
t3
|
|
#restart the server
|
|
# restart
|
|
USE test;
|
|
show tables;
|
|
Tables_in_test
|
|
t1
|
|
t2
|
|
t3
|
|
select * from t1;
|
|
a
|
|
11
|
|
select * from t2;
|
|
a
|
|
select * from t3;
|
|
a
|
|
31
|
|
DROP TABLE t2,t3;
|
|
#
|
|
# A general tablespace and its contents can be recovered.
|
|
#
|
|
CREATE TABLE t2 (a int) ENGINE=innodb TABLESPACE=s1;
|
|
CREATE TABLE t3 (a int) ENGINE=innodb TABLESPACE=innodb_system;
|
|
CREATE TABLE t4 (a int) ENGINE=innodb TABLESPACE=innodb_file_per_table;
|
|
INSERT INTO t2 VALUES (21);
|
|
INSERT INTO t3 VALUES (31);
|
|
INSERT INTO t4 VALUES (41);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (12);
|
|
INSERT INTO t2 VALUES (22);
|
|
INSERT INTO t3 VALUES (32);
|
|
INSERT INTO t4 VALUES (42);
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 s1 161 7 Dynamic 0 General
|
|
test/t2 s1 161 7 Dynamic 0 General
|
|
test/t3 innodb_system 161 7 Dynamic 0 System
|
|
test/t4 test/t4 33 7 Dynamic 0 Single
|
|
# Kill and restart
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 s1 161 7 Dynamic 0 General
|
|
test/t2 s1 161 7 Dynamic 0 General
|
|
test/t3 innodb_system 161 7 Dynamic 0 System
|
|
test/t4 test/t4 33 7 Dynamic 0 Single
|
|
select * from t1;
|
|
a
|
|
11
|
|
select * from t2;
|
|
a
|
|
21
|
|
select * from t3;
|
|
a
|
|
31
|
|
select * from t4;
|
|
a
|
|
41
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (13);
|
|
INSERT INTO t2 VALUES (23);
|
|
INSERT INTO t3 VALUES (33);
|
|
INSERT INTO t4 VALUES (43);
|
|
RENAME TABLE t1 TO tr1;
|
|
RENAME TABLE t2 TO tr2;
|
|
RENAME TABLE t3 TO tr3;
|
|
RENAME TABLE t4 TO tr4;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tr1 s1 161 7 Dynamic 0 General
|
|
test/tr2 s1 161 7 Dynamic 0 General
|
|
test/tr3 innodb_system 161 7 Dynamic 0 System
|
|
test/tr4 test/tr4 33 7 Dynamic 0 Single
|
|
# Kill and restart
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tr1 s1 161 7 Dynamic 0 General
|
|
test/tr2 s1 161 7 Dynamic 0 General
|
|
test/tr3 innodb_system 161 7 Dynamic 0 System
|
|
test/tr4 test/tr4 33 7 Dynamic 0 Single
|
|
select * from tr1;
|
|
a
|
|
11
|
|
13
|
|
select * from tr2;
|
|
a
|
|
21
|
|
23
|
|
select * from tr3;
|
|
a
|
|
31
|
|
33
|
|
select * from tr4;
|
|
a
|
|
41
|
|
43
|
|
SHOW CREATE TABLE tr1;
|
|
Table Create Table
|
|
tr1 CREATE TABLE `tr1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tr2;
|
|
Table Create Table
|
|
tr2 CREATE TABLE `tr2` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tr3;
|
|
Table Create Table
|
|
tr3 CREATE TABLE `tr3` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tr4;
|
|
Table Create Table
|
|
tr4 CREATE TABLE `tr4` (
|
|
`a` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE tr1,tr2,tr3,tr4;
|
|
DROP TABLESPACE s1;
|
|
SET SESSION debug= '+d,skip_dd_table_access_check';
|
|
SELECT count(*) INTO @dd_tbs_row_cnt FROM mysql.tablespaces;
|
|
SELECT count(*) INTO @matching_dd_tbs_row_cnt FROM mysql.tablespaces WHERE se_private_data RLIKE 'version=[0-9]+;';
|
|
SELECT @dd_tbs_row_cnt = @matching_dd_tbs_row_cnt ;
|
|
@dd_tbs_row_cnt = @matching_dd_tbs_row_cnt
|
|
1
|
|
SELECT CONCAT(sys.version_major(),".",sys.version_minor(),".",sys.version_patch()) INTO @server_version;
|
|
SELECT count(*) INTO @is_tbs_row_cnt FROM information_schema.innodb_tablespaces;
|
|
SELECT count(*) INTO @matching_is_tbs_row_cnt FROM information_schema.innodb_tablespaces WHERE server_version LIKE CONCAT('%',@server_version,'%');
|
|
SELECT @is_tbs_row_cnt = @matching_is_tbs_row_cnt ;
|
|
@is_tbs_row_cnt = @matching_is_tbs_row_cnt
|
|
1
|