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