Debian Codegraph @

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

raw
1 CREATE TABLE tab
2 (col1 TEXT, col2 TEXT, col3 TEXT, col4 TEXT, col5 TEXT,
3 col6 TEXT, col7 TEXT, col8 TEXT, col9 TEXT, col10 TEXT,
4 col11 TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
5 === information_schema.innodb_tables and innodb_tablespaces ===
6 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
7 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
8 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
9 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
10 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
11 test/tab test/tab 33 14 Dynamic 0 Single
12 INSERT INTO tab SET
13 col1=REPEAT('a',1000),col2=REPEAT('b',1000),col3=REPEAT('c',1000),
14 col4=REPEAT('d',1000),col5=REPEAT('e',1000),col6=REPEAT('f',1000),
15 col7=REPEAT('g',1000),col8=REPEAT('h',1000),col9=REPEAT('i',1000),
16 col10=REPEAT('j',1000),col11=REPEAT('k',1000);
17 INSERT INTO tab SELECT * FROM tab;
18 ALTER TABLE tab ROW_FORMAT=COMPACT;
19 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
20 DROP TABLE tab;
21 # ###########################################################
22 # Check with Import/Export tablespace with Default_row_format
23 SET GLOBAL innodb_default_row_format=Compact;
24 SELECT @@innodb_default_row_format;
25 @@innodb_default_row_format
26 compact
27 SELECT @@innodb_file_per_table;
28 @@innodb_file_per_table
29 1
30 CREATE TABLE tab(a INT);
31 INSERT INTO tab VALUES(1);
32 INSERT INTO tab VALUES(2);
33 SELECT * FROM tab;
34 a
35 1
36 2
37 FLUSH TABLE tab FOR EXPORT;
38 UNLOCK TABLES;
39 DROP TABLE tab;
40 SET GLOBAL innodb_default_row_format=Dynamic;
41 CREATE TABLE tab(a INT);
42 ALTER TABLE tab DISCARD TABLESPACE;
43 ALTER TABLE tab IMPORT TABLESPACE;
44 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_DYNAMIC and the meta-data file has ROW_TYPE_COMPACT)
45 DROP TABLE tab;
46 SET GLOBAL innodb_default_row_format=Compact;
47 SELECT @@innodb_default_row_format;
48 @@innodb_default_row_format
49 compact
50 CREATE TABLE tab(a INT);
51 === information_schema.innodb_tables and innodb_tablespaces ===
52 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
53 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
54 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
55 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
56 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
57 test/tab test/tab 1 4 Compact 0 Single
58 ALTER TABLE tab DISCARD TABLESPACE;
59 ALTER TABLE tab IMPORT TABLESPACE;
60 SELECT * FROM tab;
61 a
62 1
63 2
64 DROP TABLE tab;
65 # ###########################################################
66 SET GLOBAL innodb_default_row_format=Dynamic;
67 SELECT @@innodb_default_row_format;
68 @@innodb_default_row_format
69 dynamic
70 CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(3070))) ENGINE= InnoDB charset latin1;
71 INSERT INTO tab(a,b) VALUES(1,'Check with max column size');
72 SELECT * FROM tab;
73 a b
74 1 Check with max column size
75 === information_schema.innodb_tables and innodb_tablespaces ===
76 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
77 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
78 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
79 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
80 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
81 test/tab test/tab 33 5 Dynamic 0 Single
82 SET GLOBAL innodb_default_row_format=COMPACT;
83 ALTER TABLE tab ROW_FORMAT=COMPACT;
84 ERROR 42000: Specified key was too long; max key length is 767 bytes
85 DROP TABLE tab;
86 SET GLOBAL innodb_default_row_format=Default;
87 SELECT @@innodb_default_row_format;
88 @@innodb_default_row_format
89 dynamic
90 CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(767))) ENGINE= InnoDB charset latin1;
91 INSERT INTO tab(a,b) VALUES(1,'Check with max column size');
92 SELECT * FROM tab;
93 a b
94 1 Check with max column size
95 === information_schema.innodb_tables and innodb_tablespaces ===
96 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
97 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
98 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
99 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
100 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
101 test/tab test/tab 33 5 Dynamic 0 Single
102 ALTER TABLE tab ROW_FORMAT=COMPACT;
103 === information_schema.innodb_tables and innodb_tablespaces ===
104 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
105 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
106 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
107 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
108 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
109 test/tab test/tab 1 5 Compact 0 Single
110 SELECT * FROM tab;
111 a b
112 1 Check with max column size
113 ALTER TABLE tab ROW_FORMAT=COMPRESSED;
114 === information_schema.innodb_tables and innodb_tablespaces ===
115 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
116 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
117 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
118 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
119 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
120 test/tab test/tab 41 5 Compressed 8192 Single
121 SELECT * FROM tab;
122 a b
123 1 Check with max column size
124 ALTER TABLE tab ROW_FORMAT=Dynamic;
125 === information_schema.innodb_tables and innodb_tablespaces ===
126 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
127 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
128 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
129 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
130 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
131 test/tab test/tab 33 5 Dynamic 0 Single
132 DROP TABLE tab;