Debian Codegraph @

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

raw
1 SELECT @@innodb_default_row_format;
2 @@innodb_default_row_format
3 dynamic
4 SELECT @@innodb_file_per_table;
5 @@innodb_file_per_table
6 1
7 #Create table with no tablespace
8 CREATE TABLE tab(c1 TEXT, c2 BLOB);
9 INSERT INTO tab VALUES('Check with no tablespace','Check with no tablespace');
10 === information_schema.innodb_tables and innodb_tablespaces ===
11 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
12 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
13 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
14 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
15 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
16 test/tab test/tab 33 5 Dynamic 0 Single
17 ALTER TABLE tab ROW_FORMAT=COMPACT;
18 === information_schema.innodb_tables and innodb_tablespaces ===
19 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
20 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
21 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
22 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
23 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
24 test/tab test/tab 1 5 Compact 0 Single
25 CHECK TABLE tab;
26 Table Op Msg_type Msg_text
27 test.tab check status OK
28 ALTER TABLE tab ROW_FORMAT=DYNAMIC;
29 === information_schema.innodb_tables and innodb_tablespaces ===
30 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
31 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
32 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
33 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
34 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
35 test/tab test/tab 33 5 Dynamic 0 Single
36 CHECK TABLE tab;
37 Table Op Msg_type Msg_text
38 test.tab check status OK
39 ALTER TABLE tab ROW_FORMAT=REDUNDANT;
40 === information_schema.innodb_tables and innodb_tablespaces ===
41 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
42 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
43 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
44 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
45 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
46 test/tab test/tab 0 5 Redundant 0 Single
47 CHECK TABLE tab;
48 Table Op Msg_type Msg_text
49 test.tab check status OK
50 ALTER TABLE tab ROW_FORMAT=COMPRESSED;
51 SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
52 WHERE NAME='test/tab';
53 NAME ROW_FORMAT
54 test/tab Compressed
55 CHECK TABLE tab;
56 Table Op Msg_type Msg_text
57 test.tab check status OK
58 ALTER TABLE tab ROW_FORMAT=Dynamic;
59 === information_schema.innodb_tables and innodb_tablespaces ===
60 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
61 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
62 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
63 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
64 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
65 test/tab test/tab 33 5 Dynamic 0 Single
66 CHECK TABLE tab;
67 Table Op Msg_type Msg_text
68 test.tab check status OK
69 DROP TABLE tab;
70 SET GLOBAL innodb_file_per_table=Default;
71 SELECT @@innodb_default_row_format;
72 @@innodb_default_row_format
73 dynamic
74 #Create table with tablespace
75 CREATE TABLESPACE tblsp ADD DATAFILE 'tblsp.ibd'
76 ENGINE=InnoDB;
77 CREATE TABLE tab(c1 TEXT, c2 BLOB) TABLESPACE tblsp;
78 CREATE TABLESPACE tblsp1 ADD DATAFILE 'tblsp1.ibd' FILE_BLOCK_SIZE=1k
79 ENGINE=InnoDB;
80 CREATE TABLE tab1(c1 TEXT, c2 BLOB) KEY_BLOCK_SIZE=1 TABLESPACE tblsp1;
81 INSERT INTO tab VALUES('Check with General tablespace',
82 'Check with General tablespace');
83 INSERT INTO tab1 VALUES('tablsp File Block size',
84 'tablsp File Block size');
85 === information_schema.innodb_tables and innodb_tablespaces ===
86 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
87 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
88 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
89 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
90 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
91 test/tab tblsp 161 5 Dynamic 0 General
92 test/tab1 tblsp1 163 5 Compressed 1024 General
93 ALTER TABLE tab ROW_FORMAT=COMPACT;
94 === information_schema.innodb_tables and innodb_tablespaces ===
95 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
96 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
97 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
98 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
99 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
100 test/tab tblsp 129 5 Compact 0 General
101 test/tab1 tblsp1 163 5 Compressed 1024 General
102 CHECK TABLE tab;
103 Table Op Msg_type Msg_text
104 test.tab check status OK
105 ALTER TABLE tab ROW_FORMAT=DYNAMIC;
106 === information_schema.innodb_tables and innodb_tablespaces ===
107 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
108 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
109 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
110 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
111 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
112 test/tab tblsp 161 5 Dynamic 0 General
113 test/tab1 tblsp1 163 5 Compressed 1024 General
114 CHECK TABLE tab;
115 Table Op Msg_type Msg_text
116 test.tab check status OK
117 ALTER TABLE tab ROW_FORMAT=REDUNDANT;
118 === information_schema.innodb_tables and innodb_tablespaces ===
119 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
120 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
121 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
122 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
123 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
124 test/tab tblsp 128 5 Redundant 0 General
125 test/tab1 tblsp1 163 5 Compressed 1024 General
126 CHECK TABLE tab;
127 Table Op Msg_type Msg_text
128 test.tab check status OK
129 ALTER TABLE tab ROW_FORMAT=COMPRESSED;
130 ERROR HY000: InnoDB: Tablespace `tblsp` cannot contain a COMPRESSED table
131 ALTER TABLE tab1 ROW_FORMAT=DYNAMIC;
132 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
133 SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
134 WHERE NAME='test/tab';
135 NAME ROW_FORMAT
136 test/tab Redundant
137 CHECK TABLE tab;
138 Table Op Msg_type Msg_text
139 test.tab check status OK
140 CHECK TABLE tab1;
141 Table Op Msg_type Msg_text
142 test.tab1 check status OK
143 ALTER TABLE tab ROW_FORMAT=Dynamic;
144 === information_schema.innodb_tables and innodb_tablespaces ===
145 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
146 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
147 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
148 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
149 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
150 test/tab tblsp 161 5 Dynamic 0 General
151 test/tab1 tblsp1 163 5 Compressed 1024 General
152 CHECK TABLE tab;
153 Table Op Msg_type Msg_text
154 test.tab check status OK
155 DROP TABLE tab;
156 DROP TABLE tab1;
157 DROP TABLESPACE tblsp;
158 DROP TABLESPACE tblsp1;
159 SET GLOBAL innodb_file_per_table=Default;
160 SELECT @@innodb_default_row_format;
161 @@innodb_default_row_format
162 dynamic
163 #Create table with innodb system tablespace
164 CREATE TABLE tab(c1 TEXT, c2 BLOB) TABLESPACE innodb_system;
165 INSERT INTO tab VALUES('Check with InnoDB system tablespace',
166 'Check with InnoDB system tablespace');
167 === information_schema.innodb_tables and innodb_tablespaces ===
168 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
169 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
170 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
171 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
172 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
173 test/tab innodb_system 161 5 Dynamic 0 System
174 ALTER TABLE tab ROW_FORMAT=COMPACT;
175 === information_schema.innodb_tables and innodb_tablespaces ===
176 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
177 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
178 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
179 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
180 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
181 test/tab innodb_system 129 5 Compact 0 System
182 CHECK TABLE tab;
183 Table Op Msg_type Msg_text
184 test.tab check status OK
185 ALTER TABLE tab ROW_FORMAT=DYNAMIC;
186 === information_schema.innodb_tables and innodb_tablespaces ===
187 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
188 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
189 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
190 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
191 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
192 test/tab innodb_system 161 5 Dynamic 0 System
193 CHECK TABLE tab;
194 Table Op Msg_type Msg_text
195 test.tab check status OK
196 ALTER TABLE tab ROW_FORMAT=REDUNDANT;
197 === information_schema.innodb_tables and innodb_tablespaces ===
198 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
199 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
200 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
201 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
202 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
203 test/tab innodb_system 128 5 Redundant 0 System
204 CHECK TABLE tab;
205 Table Op Msg_type Msg_text
206 test.tab check status OK
207 SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
208 WHERE NAME='test/tab';
209 NAME ROW_FORMAT
210 test/tab Redundant
211 CHECK TABLE tab;
212 Table Op Msg_type Msg_text
213 test.tab check status OK
214 ALTER TABLE tab ROW_FORMAT=Dynamic;
215 === information_schema.innodb_tables and innodb_tablespaces ===
216 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
217 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
218 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
219 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
220 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
221 test/tab innodb_system 161 5 Dynamic 0 System
222 CHECK TABLE tab;
223 Table Op Msg_type Msg_text
224 test.tab check status OK
225 DROP TABLE tab;
226 SET GLOBAL innodb_file_per_table=Default;
227 SELECT @@innodb_default_row_format;
228 @@innodb_default_row_format
229 dynamic
230 SET GLOBAL innodb_file_per_table=0;
231 SELECT @@innodb_file_per_table;
232 @@innodb_file_per_table
233 0
234 #Create table with file_per_table=0
235 CREATE TABLE tab(c1 TEXT, c2 BLOB);
236 INSERT INTO tab VALUES('File per table off','File per table off');
237 === information_schema.innodb_tables and innodb_tablespaces ===
238 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
239 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
240 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
241 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
242 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
243 test/tab innodb_system 33 5 Dynamic 0 System
244 ALTER TABLE tab ROW_FORMAT=COMPACT;
245 === information_schema.innodb_tables and innodb_tablespaces ===
246 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
247 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
248 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
249 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
250 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
251 test/tab innodb_system 129 5 Compact 0 System
252 CHECK TABLE tab;
253 Table Op Msg_type Msg_text
254 test.tab check status OK
255 ALTER TABLE tab ROW_FORMAT=DYNAMIC;
256 === information_schema.innodb_tables and innodb_tablespaces ===
257 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
258 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
259 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
260 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
261 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
262 test/tab innodb_system 161 5 Dynamic 0 System
263 CHECK TABLE tab;
264 Table Op Msg_type Msg_text
265 test.tab check status OK
266 ALTER TABLE tab ROW_FORMAT=REDUNDANT;
267 === information_schema.innodb_tables and innodb_tablespaces ===
268 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
269 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
270 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
271 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
272 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
273 test/tab innodb_system 128 5 Redundant 0 System
274 CHECK TABLE tab;
275 Table Op Msg_type Msg_text
276 test.tab check status OK
277 ALTER TABLE tab ROW_FORMAT=COMPRESSED;
278 ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
279 SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
280 WHERE NAME='test/tab';
281 NAME ROW_FORMAT
282 test/tab Redundant
283 CHECK TABLE tab;
284 Table Op Msg_type Msg_text
285 test.tab check status OK
286 ALTER TABLE tab ROW_FORMAT=Dynamic;
287 === information_schema.innodb_tables and innodb_tablespaces ===
288 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
289 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
290 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
291 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
292 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
293 test/tab innodb_system 161 5 Dynamic 0 System
294 CHECK TABLE tab;
295 Table Op Msg_type Msg_text
296 test.tab check status OK
297 DROP TABLE tab;
298 SET GLOBAL innodb_file_per_table=Default;