Debian Codegraph @

mysql-test/suite/innodb/r/create_tablespace_debug.result

raw
1 SET DEFAULT_STORAGE_ENGINE=InnoDB;
2 #
3 # If CREATE TABLESPACE fails after the file is created, it should not remain.
4 #
5 SET SESSION DEBUG='+d,innodb_fail_to_update_tablespace_dict';
6 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB;
7 ERROR HY000: Failed to create TABLESPACE s_def
8 SET SESSION DEBUG='-d,innodb_fail_to_update_tablespace_dict';
9 === information_schema.innodb_tablespaces and innodb_datafiles ===
10 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
11 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
12 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
13 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
14 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
15 === information_schema.files ===
16 Space_Name File_Type Engine Status Tablespace_Name Path
17 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
18 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
19 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
20 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
21 # MYSQLD_DATADIR/
22 mysql.ibd
23 DROP TABLESPACE s_def;
24 ERROR HY000: Tablespace s_def doesn't exist.
25 #
26 # Assigning general tablespace to myisam,memory is igonred
27 #
28 USE test;
29 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
30 CREATE TABLE t1 (a int) ENGINE=innodb TABLESPACE=s1;
31 CREATE TABLE t2 (a int) ENGINE=memory TABLESPACE=s1;
32 CREATE TABLE t3 (a int) ENGINE=myisam TABLESPACE=s1;
33 INSERT INTO t1 VALUES ( 11);
34 INSERT INTO t2 VALUES ( 21);
35 INSERT INTO t3 VALUES ( 31);
36 === information_schema.innodb_tables and innodb_tablespaces ===
37 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
38 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
39 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
40 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
41 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
42 test/t1 s1 161 4 Dynamic 0 General
43 ALTER TABLE t2 TABLESPACE=s1;
44 ALTER TABLE t3 TABLESPACE=s1;
45 === information_schema.innodb_tables and innodb_tablespaces ===
46 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
47 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
48 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
49 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
50 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
51 test/t1 s1 161 4 Dynamic 0 General
52 # MYSQLD_DATADIR/
53 mysql.ibd
54 s1.ibd
55 show tables;
56 Tables_in_test
57 t1
58 t2
59 t3
60 #restart the server
61 # restart
62 USE test;
63 show tables;
64 Tables_in_test
65 t1
66 t2
67 t3
68 select * from t1;
69 a
70 11
71 select * from t2;
72 a
73 select * from t3;
74 a
75 31
76 DROP TABLE t2,t3;
77 #
78 # A general tablespace and its contents can be recovered.
79 #
80 CREATE TABLE t2 (a int) ENGINE=innodb TABLESPACE=s1;
81 CREATE TABLE t3 (a int) ENGINE=innodb TABLESPACE=innodb_system;
82 CREATE TABLE t4 (a int) ENGINE=innodb TABLESPACE=innodb_file_per_table;
83 INSERT INTO t2 VALUES (21);
84 INSERT INTO t3 VALUES (31);
85 INSERT INTO t4 VALUES (41);
86 BEGIN;
87 INSERT INTO t1 VALUES (12);
88 INSERT INTO t2 VALUES (22);
89 INSERT INTO t3 VALUES (32);
90 INSERT INTO t4 VALUES (42);
91 === information_schema.innodb_tables and innodb_tablespaces ===
92 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
93 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
94 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
95 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
96 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
97 test/t1 s1 161 4 Dynamic 0 General
98 test/t2 s1 161 4 Dynamic 0 General
99 test/t3 innodb_system 161 4 Dynamic 0 System
100 test/t4 test/t4 33 4 Dynamic 0 Single
101 # Kill and restart
102 === information_schema.innodb_tables and innodb_tablespaces ===
103 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
104 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
105 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
106 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
107 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
108 test/t1 s1 161 4 Dynamic 0 General
109 test/t2 s1 161 4 Dynamic 0 General
110 test/t3 innodb_system 161 4 Dynamic 0 System
111 test/t4 test/t4 33 4 Dynamic 0 Single
112 select * from t1;
113 a
114 11
115 select * from t2;
116 a
117 21
118 select * from t3;
119 a
120 31
121 select * from t4;
122 a
123 41
124 SHOW CREATE TABLE t1;
125 Table Create Table
126 t1 CREATE TABLE `t1` (
127 `a` int DEFAULT NULL
128 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
129 SHOW CREATE TABLE t2;
130 Table Create Table
131 t2 CREATE TABLE `t2` (
132 `a` int DEFAULT NULL
133 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
134 SHOW CREATE TABLE t3;
135 Table Create Table
136 t3 CREATE TABLE `t3` (
137 `a` int DEFAULT NULL
138 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
139 SHOW CREATE TABLE t4;
140 Table Create Table
141 t4 CREATE TABLE `t4` (
142 `a` int DEFAULT NULL
143 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
144 BEGIN;
145 INSERT INTO t1 VALUES (13);
146 INSERT INTO t2 VALUES (23);
147 INSERT INTO t3 VALUES (33);
148 INSERT INTO t4 VALUES (43);
149 RENAME TABLE t1 TO tr1;
150 RENAME TABLE t2 TO tr2;
151 RENAME TABLE t3 TO tr3;
152 RENAME TABLE t4 TO tr4;
153 === information_schema.innodb_tables and innodb_tablespaces ===
154 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
155 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
156 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
157 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
158 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
159 test/tr1 s1 161 4 Dynamic 0 General
160 test/tr2 s1 161 4 Dynamic 0 General
161 test/tr3 innodb_system 161 4 Dynamic 0 System
162 test/tr4 test/tr4 33 4 Dynamic 0 Single
163 # Kill and restart
164 === information_schema.innodb_tables and innodb_tablespaces ===
165 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
166 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
167 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
168 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
169 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
170 test/tr1 s1 161 4 Dynamic 0 General
171 test/tr2 s1 161 4 Dynamic 0 General
172 test/tr3 innodb_system 161 4 Dynamic 0 System
173 test/tr4 test/tr4 33 4 Dynamic 0 Single
174 select * from tr1;
175 a
176 11
177 13
178 select * from tr2;
179 a
180 21
181 23
182 select * from tr3;
183 a
184 31
185 33
186 select * from tr4;
187 a
188 41
189 43
190 SHOW CREATE TABLE tr1;
191 Table Create Table
192 tr1 CREATE TABLE `tr1` (
193 `a` int DEFAULT NULL
194 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
195 SHOW CREATE TABLE tr2;
196 Table Create Table
197 tr2 CREATE TABLE `tr2` (
198 `a` int DEFAULT NULL
199 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
200 SHOW CREATE TABLE tr3;
201 Table Create Table
202 tr3 CREATE TABLE `tr3` (
203 `a` int DEFAULT NULL
204 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
205 SHOW CREATE TABLE tr4;
206 Table Create Table
207 tr4 CREATE TABLE `tr4` (
208 `a` int DEFAULT NULL
209 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
210 DROP TABLE tr1,tr2,tr3,tr4;
211 DROP TABLESPACE s1;
212 SET SESSION debug= '+d,skip_dd_table_access_check';
213 SELECT count(*) INTO @dd_tbs_row_cnt FROM mysql.tablespaces;
214 SELECT count(*) INTO @matching_dd_tbs_row_cnt FROM mysql.tablespaces WHERE se_private_data RLIKE 'version=[0-9]+;';
215 SELECT @dd_tbs_row_cnt = @matching_dd_tbs_row_cnt ;
216 @dd_tbs_row_cnt = @matching_dd_tbs_row_cnt
217 1
218 SELECT CONCAT(sys.version_major(),".",sys.version_minor(),".",sys.version_patch()) INTO @server_version;
219 SELECT count(*) INTO @is_tbs_row_cnt FROM information_schema.innodb_tablespaces;
220 SELECT count(*) INTO @matching_is_tbs_row_cnt FROM information_schema.innodb_tablespaces WHERE server_version LIKE CONCAT('%',@server_version,'%');
221 SELECT @is_tbs_row_cnt = @matching_is_tbs_row_cnt ;
222 @is_tbs_row_cnt = @matching_is_tbs_row_cnt
223 1