Debian Codegraph @

mysql-test/suite/innodb/t/default_row_format_compatibility.test

raw
1 # *******************************************************************
2 # Case 1 :
3 # To test, ROW_FORMAT functionality of Intrinsic Temporary tables
4 # The intrinsic temp tables will be tested implicitly only, by
5 # using Order by,Group by or INSERT INTO SELECT statements
6 # This testcase fails/throws error on mysql-5.6 and passes on
7 # mysql-5.7 onwards.
8 # ROW_FORMAT=Compact support up to 10 BLOB/TEXT columns
9 # ROW_FORMAT=Dynamic support up to 199 BLOB/TEXT columns
10 # case 2 :
11 # Check with Import/Export tablespace with Default_row_format
12 # Check with different row_formats Compact vs Dynamic
13 # Check with same row_format Compact duing export/import
14 # case 3 :
15 # Check with Index column size is too long 3070 & 767 Bytes
16 # Modify table Dynamic to Compact to Compressed to Dynamic
17 # *******************************************************************
18
19 # set the variables
20 let $MYSQLD_TMPDIR = `SELECT @@tmpdir`;
21 let $MYSQLD_DATADIR = `SELECT @@datadir`;
22
23 # The following tescases works with page_size=16k only
24 --source include/no_valgrind_without_big.inc
25
26 --disable_query_log
27 call mtr.add_suppression("Cannot alter the table .* because the record size will exceed the maximum allowed size for a record .*");
28 --enable_query_log
29
30 CREATE TABLE tab
31 (col1 TEXT, col2 TEXT, col3 TEXT, col4 TEXT, col5 TEXT,
32 col6 TEXT, col7 TEXT, col8 TEXT, col9 TEXT, col10 TEXT,
33 col11 TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
34
35 # Check row_fromat=Dynamic
36 --source ../include/default_row_format_show.inc
37
38 INSERT INTO tab SET
39 col1=REPEAT('a',1000),col2=REPEAT('b',1000),col3=REPEAT('c',1000),
40 col4=REPEAT('d',1000),col5=REPEAT('e',1000),col6=REPEAT('f',1000),
41 col7=REPEAT('g',1000),col8=REPEAT('h',1000),col9=REPEAT('i',1000),
42 col10=REPEAT('j',1000),col11=REPEAT('k',1000);
43
44 # The following query throws error in mysql5.6 version
45 # 1118: Row size too large (> 8126) and No error on ,mysql-5.7
46 INSERT INTO tab SELECT * FROM tab;
47
48 --error ER_TOO_BIG_ROWSIZE
49 ALTER TABLE tab ROW_FORMAT=COMPACT;
50
51 #Cleaup
52 DROP TABLE tab;
53
54 --echo # ###########################################################
55 --echo # Check with Import/Export tablespace with Default_row_format
56
57 # Set row_format=Compact
58 SET GLOBAL innodb_default_row_format=Compact;
59
60 # Check row_format=Compact
61 SELECT @@innodb_default_row_format;
62
63 # Check file_per_table=1
64 SELECT @@innodb_file_per_table;
65
66 CREATE TABLE tab(a INT);
67
68 INSERT INTO tab VALUES(1);
69 INSERT INTO tab VALUES(2);
70
71 # Check the rows
72 SELECT * FROM tab;
73
74 FLUSH TABLE tab FOR EXPORT;
75
76 # Take the backup of the ibd and cfg files
77 --copy_file $MYSQLD_DATADIR/test/tab.cfg $MYSQLD_TMPDIR/tab.cfg
78 --copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_TMPDIR/tab.ibd
79
80 UNLOCK TABLES;
81
82 # Cleanup
83 DROP TABLE tab;
84
85 # Set the default_row_format=Dynamic
86 SET GLOBAL innodb_default_row_format=Dynamic;
87
88 CREATE TABLE tab(a INT);
89
90 # Remove the *.ibd file
91 ALTER TABLE tab DISCARD TABLESPACE;
92
93 # Move the *.ibd,*.cfg file into orginal location
94 --move_file $MYSQLD_TMPDIR/tab.cfg $MYSQLD_DATADIR/test/tab.cfg
95 --move_file $MYSQLD_TMPDIR/tab.ibd $MYSQLD_DATADIR/test/tab.ibd
96
97 --error ER_TABLE_SCHEMA_MISMATCH
98 ALTER TABLE tab IMPORT TABLESPACE;
99
100 # Take the backup of the ibd and cfg files
101 --copy_file $MYSQLD_DATADIR/test/tab.cfg $MYSQLD_TMPDIR/tab.cfg
102 --copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_TMPDIR/tab.ibd
103
104 # Cleanup
105 DROP TABLE tab;
106
107 # Remove orphan files
108 --remove_file $MYSQLD_DATADIR/test/tab.cfg
109 --remove_file $MYSQLD_DATADIR/test/tab.ibd
110
111 # Set the default_row_format=Compact
112 SET GLOBAL innodb_default_row_format=Compact;
113
114 # Check row_format=Compact
115 SELECT @@innodb_default_row_format;
116
117 CREATE TABLE tab(a INT);
118
119 # Check row_fromat=Dynamic
120 --source ../include/default_row_format_show.inc
121
122 # Remove the *.ibd file
123 ALTER TABLE tab DISCARD TABLESPACE;
124
125 # Move the *ibd,*.cfg file into orginal location
126 --move_file $MYSQLD_TMPDIR/tab.cfg $MYSQLD_DATADIR/test/tab.cfg
127 --move_file $MYSQLD_TMPDIR/tab.ibd $MYSQLD_DATADIR/test/tab.ibd
128
129 # Check import is successful (because same row_format)
130 ALTER TABLE tab IMPORT TABLESPACE;
131
132 # Check the rows
133 SELECT * FROM tab;
134
135 # Cleanup
136 DROP TABLE tab;
137
138 --echo # ###########################################################
139 # Check when Index Column size (3070 bytes) is too long, Change row_format
140 # Check when Index Column size (767 bytes), Change row_format
141 # Dynamic to Compact to Dynamic
142
143 # Set the default_row_format=Dynamic
144 SET GLOBAL innodb_default_row_format=Dynamic;
145
146 SELECT @@innodb_default_row_format;
147
148 CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(3070))) ENGINE= InnoDB charset latin1;
149
150 INSERT INTO tab(a,b) VALUES(1,'Check with max column size');
151
152 # Check by SELECT, no errors
153 SELECT * FROM tab;
154
155 # Check row_format=dynamic
156 --source ../include/default_row_format_show.inc
157
158 # Change row_format to Compact
159 SET GLOBAL innodb_default_row_format=COMPACT;
160
161 # Check error ERROR 1709 (HY000): Index column size too large
162 -- error ER_TOO_LONG_KEY
163 ALTER TABLE tab ROW_FORMAT=COMPACT;
164
165 # Cleanup
166 DROP TABLE tab;
167
168 # Change the default_row_format to default
169 SET GLOBAL innodb_default_row_format=Default;
170
171 # Change row_format to Dynamic
172 SELECT @@innodb_default_row_format;
173
174 CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(767))) ENGINE= InnoDB charset latin1;
175
176 INSERT INTO tab(a,b) VALUES(1,'Check with max column size');
177
178 # Check by SELECT, no errors
179 SELECT * FROM tab;
180
181 # Check row_format=dynamic
182 --source ../include/default_row_format_show.inc
183
184 # Check no errors because Compact allows 767 bytes
185 ALTER TABLE tab ROW_FORMAT=COMPACT;
186
187 # Check row_format=Compact
188 --source ../include/default_row_format_show.inc
189
190 # Check by SELECT, no errors
191 SELECT * FROM tab;
192
193 # Check no errors
194 ALTER TABLE tab ROW_FORMAT=COMPRESSED;
195
196 # Check row_format=Compressed
197 --source ../include/default_row_format_show.inc
198
199 # Check by SELECT, no errors
200 SELECT * FROM tab;
201
202 # Check no errors
203 ALTER TABLE tab ROW_FORMAT=Dynamic;
204
205 # Check row_format=Dynamic
206 --source ../include/default_row_format_show.inc
207
208 # Cleanup
209 DROP TABLE tab;