Debian Codegraph @

mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test

raw
1 ######## suite/innodb/t/innodb_prefix_index_liftedlimit.test ##########
2 # #
3 # Testcase for worklog WL#5743: Lift the limit of index key prefixes #
4 # Accorrding to WL#5743 - prefix index limit is increased from 767 #
5 # to 3072 for innodb. This change is applicable with Barracuda file #
6 # format. #
7 # All sub-test in this file focus on prefix index along with other #
8 # operations #
9 # #
10 # #
11 # Creation: #
12 # 2011-05-19 Implemented this test as part of WL#5743 #
13 # #
14 ######################################################################
15
16 --disable_query_log
17 call mtr.add_suppression("Cannot alter the table .* because the record size will exceed the maximum allowed size for a record .*");
18 --enable_query_log
19
20 # Save innodb variables
21 --disable_query_log
22 let $innodb_file_per_table_orig = `select @@innodb_file_per_table`;
23 --enable_query_log
24
25 set global innodb_file_per_table=1;
26
27 # Save the initial number of concurrent sessions.
28 --source include/count_sessions.inc
29 #------------------------------------------------------------------------------
30 # Prefix index with VARCHAR data type , primary/secondary index and DML ops
31 CREATE TABLE worklog5743 (
32 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
33 PRIMARY KEY (col_1_varchar(3072))
34 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
35 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
36 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
37 # check IS
38 --sorted_result
39 SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM
40 INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ;
41 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
42 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
43 FROM worklog5743;
44 UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000)
45 WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
46 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743
47 WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
48 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
49 DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000);
50 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743;
51 --error ER_TOO_LONG_KEY
52 ALTER TABLE worklog5743 ROW_FORMAT=REDUNDANT;
53 --error ER_TOO_LONG_KEY
54 ALTER TABLE worklog5743 ROW_FORMAT=COMPACT;
55 ALTER TABLE worklog5743 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
56 DROP TABLE worklog5743;
57
58
59 #------------------------------------------------------------------------------
60 # Prefix index with TEXT data type , primary/secondary index and DML ops
61 CREATE TABLE worklog5743 (
62 col_1_text TEXT (4000) , col_2_text TEXT (4000) ,
63 PRIMARY KEY (col_1_text(3072))
64 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
65 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
66 CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072));
67 # check IS
68 --sorted_result
69 SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM
70 INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ;
71 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
72 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000)
73 FROM worklog5743;
74 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
75 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
76 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
77 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
78 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
79 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000);
80 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743;
81 DROP TABLE worklog5743;
82
83 #------------------------------------------------------------------------------
84 # Prefix index with MEDIUMTEXT data type , primary/secondary index and DML ops
85 CREATE TABLE worklog5743 (
86 col_1_mediumtext MEDIUMTEXT , col_2_mediumtext MEDIUMTEXT ,
87 PRIMARY KEY (col_1_mediumtext(3072))
88 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
89 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
90 CREATE INDEX prefix_idx ON worklog5743(col_1_mediumtext (3072));
91 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
92 SELECT col_1_mediumtext = REPEAT("a", 4000),col_2_mediumtext = REPEAT("o", 4000)
93 FROM worklog5743;
94 UPDATE worklog5743 SET col_1_mediumtext = REPEAT("c", 4000)
95 WHERE col_1_mediumtext = REPEAT("a", 4000)
96 AND col_2_mediumtext = REPEAT("o", 4000);
97 SELECT col_1_mediumtext = REPEAT("c", 4000) FROM worklog5743
98 WHERE col_1_mediumtext = REPEAT("c", 4000)
99 AND col_2_mediumtext = REPEAT("o", 4000);
100 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
101 DELETE FROM worklog5743 WHERE col_1_mediumtext = REPEAT("b", 4000);
102 SELECT col_1_mediumtext = REPEAT("c", 4000) FROM worklog5743;
103 DROP TABLE worklog5743;
104
105
106 #------------------------------------------------------------------------------
107 # Prefix index with LONGTEXT data type , primary/secondary index and DML ops
108 CREATE TABLE worklog5743 (
109 col_1_longtext LONGTEXT , col_2_longtext LONGTEXT ,
110 PRIMARY KEY (col_1_longtext(3072))
111 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
112 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
113 CREATE INDEX prefix_idx ON worklog5743(col_1_longtext (3072));
114 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
115 SELECT col_1_longtext = REPEAT("a", 4000) , col_2_longtext = REPEAT("o", 4000)
116 FROM worklog5743;
117 UPDATE worklog5743 SET col_1_longtext = REPEAT("c", 4000)
118 WHERE col_1_longtext = REPEAT("a", 4000)
119 AND col_2_longtext = REPEAT("o", 4000);
120 SELECT col_1_longtext = REPEAT("c", 4000) FROM worklog5743
121 WHERE col_1_longtext = REPEAT("c", 4000)
122 AND col_2_longtext = REPEAT("o", 4000);
123 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
124 DELETE FROM worklog5743 WHERE col_1_longtext = REPEAT("b", 4000);
125 SELECT col_1_longtext = REPEAT("c", 4000) FROM worklog5743;
126 DROP TABLE worklog5743;
127
128
129 #------------------------------------------------------------------------------
130 # Prefix index with BLOB data type , primary/secondary index and DML ops
131 CREATE TABLE worklog5743 (
132 col_1_blob BLOB (4000) , col_2_blob BLOB (4000) ,
133 PRIMARY KEY (col_1_blob(3072))
134 ) ROW_FORMAT=DYNAMIC, engine = innodb;
135 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
136 CREATE INDEX prefix_idx ON worklog5743(col_1_blob (3072));
137 # check IS
138 --sorted_result
139 SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE FROM
140 INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ;
141 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
142 SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000)
143 FROM worklog5743;
144 UPDATE worklog5743 SET col_1_blob = REPEAT("c", 4000)
145 WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000);
146 SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743
147 WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000);
148 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
149 DELETE FROM worklog5743 WHERE col_1_blob = REPEAT("b", 4000);
150 SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743;
151 DROP TABLE worklog5743;
152
153
154 #------------------------------------------------------------------------------
155 # Prefix index with MEDIUMBLOB data type , primary/secondary index and DML ops
156 CREATE TABLE worklog5743 (
157 col_1_mediumblob MEDIUMBLOB , col_2_mediumblob MEDIUMBLOB ,
158 PRIMARY KEY (col_1_mediumblob(3072))
159 ) ROW_FORMAT=DYNAMIC, engine = innodb;
160 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
161 CREATE INDEX prefix_idx ON worklog5743(col_1_mediumblob (3072));
162 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
163 SELECT col_1_mediumblob = REPEAT("a", 4000),col_2_mediumblob = REPEAT("o", 4000)
164 FROM worklog5743;
165 UPDATE worklog5743 SET col_1_mediumblob = REPEAT("c", 4000)
166 WHERE col_1_mediumblob = REPEAT("a", 4000)
167 AND col_2_mediumblob = REPEAT("o", 4000);
168 SELECT col_1_mediumblob = REPEAT("c", 4000) FROM worklog5743
169 WHERE col_1_mediumblob = REPEAT("c", 4000)
170 AND col_2_mediumblob = REPEAT("o", 4000);
171 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
172 DELETE FROM worklog5743 WHERE col_1_mediumblob = REPEAT("b", 4000);
173 SELECT col_1_mediumblob = REPEAT("c", 4000) FROM worklog5743;
174 DROP TABLE worklog5743;
175
176 #------------------------------------------------------------------------------
177 # Prefix index with LONGBLOB data type , primary/secondary index and DML ops
178 CREATE TABLE worklog5743 (
179 col_1_longblob LONGBLOB , col_2_longblob LONGBLOB ,
180 PRIMARY KEY (col_1_longblob(3072))
181 ) ROW_FORMAT=DYNAMIC, engine = innodb;
182 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
183 CREATE INDEX prefix_idx ON worklog5743(col_1_longblob (3072));
184 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
185 SELECT col_1_longblob = REPEAT("a", 4000) , col_2_longblob = REPEAT("o", 4000)
186 FROM worklog5743;
187 UPDATE worklog5743 SET col_1_longblob = REPEAT("c", 4000)
188 WHERE col_1_longblob = REPEAT("a", 4000)
189 AND col_2_longblob = REPEAT("o", 4000);
190 SELECT col_1_longblob = REPEAT("c", 4000) FROM worklog5743
191 WHERE col_1_longblob = REPEAT("c", 4000)
192 AND col_2_longblob = REPEAT("o", 4000);
193 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
194 DELETE FROM worklog5743 WHERE col_1_longblob = REPEAT("b", 4000);
195 SELECT col_1_longblob = REPEAT("c", 4000) FROM worklog5743;
196 DROP TABLE worklog5743;
197
198 #------------------------------------------------------------------------------
199 # Prefix index with VARBINARY data type , primary/secondary index and DML ops
200 CREATE TABLE worklog5743 (
201 col_1_varbinary VARBINARY (4000) , col_2_varbinary VARBINARY (4000) ,
202 PRIMARY KEY (col_1_varbinary(3072))
203 ) ROW_FORMAT=DYNAMIC, engine = innodb;
204 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
205 CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (3072));
206 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
207 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varbinary = REPEAT("o", 4000)
208 FROM worklog5743;
209 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
210 WHERE col_1_varbinary = REPEAT("a", 4000)
211 AND col_2_varbinary = REPEAT("o", 4000);
212 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
213 WHERE col_1_varbinary = REPEAT("c", 4000)
214 AND col_2_varbinary = REPEAT("o", 4000);
215 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
216 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000);
217 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743;
218 DROP TABLE worklog5743;
219
220 #------------------------------------------------------------------------------
221 # Prefix index with CHAR data type , composite index and DML ops
222 CREATE TABLE worklog5743 (col_1_char CHAR (255) , col_2_char CHAR (255),
223 col_3_char CHAR (255), col_4_char CHAR (255),col_5_char CHAR (255),
224 col_6_char CHAR (255), col_7_char CHAR (255),col_8_char CHAR (255),
225 col_9_char CHAR (255), col_10_char CHAR (255),col_11_char CHAR (255),
226 col_12_char CHAR (255), col_13_char CHAR (255),col_14_char CHAR (255)
227 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
228 INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255),
229 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
230 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
231 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
232 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255)
233 );
234 # Create index with total prefix index length = 3072
235 CREATE INDEX prefix_idx ON worklog5743(col_1_char(250),col_2_char(250),
236 col_3_char(250),col_4_char(250),col_5_char(250),col_6_char(250),
237 col_7_char(250),col_8_char(250),col_9_char(250),col_10_char(250),
238 col_11_char(250),col_12_char(250),col_13_char(72)
239 );
240 INSERT INTO worklog5743 VALUES(REPEAT("b", 255) , REPEAT("p", 255),
241 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
242 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
243 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
244 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255)
245 );
246 SELECT col_1_char = REPEAT("a", 255) , col_2_char = REPEAT("o", 255) FROM worklog5743;
247 UPDATE worklog5743 SET col_1_char = REPEAT("c", 255)
248 WHERE col_1_char = REPEAT("a", 255) AND col_2_char = REPEAT("o", 255);
249 SELECT col_1_char = REPEAT("c", 255) FROM worklog5743
250 WHERE col_1_char = REPEAT("c", 255) AND col_2_char = REPEAT("o", 255);
251 INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255),
252 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
253 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
254 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255),
255 REPEAT("a", 255) , REPEAT("o", 255),REPEAT("a", 255)
256 );
257 DELETE FROM worklog5743 WHERE col_1_char = REPEAT("b", 255);
258 SELECT col_1_char = REPEAT("c", 255) FROM worklog5743;
259 DROP TABLE worklog5743;
260
261 #------------------------------------------------------------------------------
262 # Prefix index with BINARY data type , composite index and DML ops
263 CREATE TABLE worklog5743 (col_1_binary BINARY (255) , col_2_binary BINARY (255),
264 col_3_binary BINARY(255),col_4_binary BINARY (255),col_5_binary BINARY (255),
265 col_6_binary BINARY(255),col_7_binary BINARY (255),col_8_binary BINARY (255),
266 col_9_binary BINARY(255),col_10_binary BINARY (255),col_11_binary BINARY (255),
267 col_12_binary BINARY(255),col_13_binary BINARY (255),col_14_binary BINARY (255)
268 ) ROW_FORMAT=DYNAMIC, engine = innodb;
269 INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255),
270 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
271 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
272 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
273 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255)
274 );
275 # Create index with total prefix index length = 3072
276 CREATE INDEX prefix_idx ON worklog5743(col_1_binary (250),col_2_binary (250),
277 col_3_binary (250),col_4_binary (250),col_5_binary (250),
278 col_6_binary (250),col_7_binary (250),col_8_binary (250),
279 col_9_binary (250),col_10_binary (250),col_11_binary (250),
280 col_12_binary (250),col_13_binary (72)
281 );
282 INSERT INTO worklog5743 VALUES(REPEAT("b", 255) , REPEAT("p", 255),
283 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
284 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
285 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
286 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255)
287 );
288 SELECT col_1_binary = REPEAT("a", 255) , col_2_binary = REPEAT("o", 255) FROM worklog5743;
289 UPDATE worklog5743 SET col_1_binary = REPEAT("c", 255)
290 WHERE col_1_binary = REPEAT("a", 255)
291 AND col_2_binary = REPEAT("o", 255);
292 SELECT col_1_binary = REPEAT("c", 255) FROM worklog5743
293 WHERE col_1_binary = REPEAT("c", 255)
294 AND col_2_binary = REPEAT("o", 255);
295 INSERT INTO worklog5743 VALUES(REPEAT("a", 255) , REPEAT("o", 255),
296 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
297 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
298 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255),
299 REPEAT("a", 255) , REPEAT("o", 255), REPEAT("a", 255)
300 );
301 DELETE FROM worklog5743 WHERE col_1_binary = REPEAT("b", 255);
302 SELECT col_1_binary = REPEAT("c", 255) FROM worklog5743;
303 DROP TABLE worklog5743;
304
305 #------------------------------------------------------------------------------
306 # Prefix index with VARCHAR data type , primary/seconday index , DML ops
307 # and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8.
308
309 # With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following
310 CREATE TABLE worklog5743_key2 (
311 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
312 PRIMARY KEY (col_1_varchar(948))
313 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb;
314 INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
315 #CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_varchar (767));
316 INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
317 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
318 FROM worklog5743_key2;
319 UPDATE worklog5743_key2 SET col_1_varchar = REPEAT("c", 4000)
320 WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
321 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key2
322 WHERE col_2_varchar = REPEAT("o", 4000);
323 INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
324 DELETE FROM worklog5743_key2 WHERE col_1_varchar = REPEAT("b", 4000);
325 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key2;
326 DROP TABLE worklog5743_key2;
327
328 # With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following
329 CREATE TABLE worklog5743_key4 (
330 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
331 PRIMARY KEY (col_1_varchar(1964))
332 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb;
333 INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
334 #CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_varchar (767));
335 INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
336 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
337 FROM worklog5743_key4;
338 UPDATE worklog5743_key4 SET col_1_varchar = REPEAT("c", 4000)
339 WHERE col_1_varchar = REPEAT("a", 4000)
340 AND col_2_varchar = REPEAT("o", 4000);
341 SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743_key4
342 WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
343 INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
344 DELETE FROM worklog5743_key4 WHERE col_1_varchar = REPEAT("b", 4000);
345 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key4;
346 DROP TABLE worklog5743_key4;
347
348 # With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following
349 CREATE TABLE worklog5743_key8 (
350 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
351 PRIMARY KEY (col_1_varchar(3072))
352 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb;
353 INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
354 #CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_varchar (767));
355 INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
356 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
357 FROM worklog5743_key8;
358 UPDATE worklog5743_key8 SET col_1_varchar = REPEAT("c", 4000)
359 WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
360 SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743_key8
361 WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
362 INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
363 DELETE FROM worklog5743_key8 WHERE col_1_varchar = REPEAT("b", 4000);
364 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743_key8;
365 DROP TABLE worklog5743_key8;
366
367 # Prefix index with TEXT data type , primary/seconday index , DML ops
368 # and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8.
369
370 # With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following
371 CREATE TABLE worklog5743_key2 (
372 col_1_text TEXT (4000) , col_2_text TEXT (4000) ,
373 PRIMARY KEY (col_1_text(948))
374 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb;
375 INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
376 #CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_text (767));
377 INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
378 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000)
379 FROM worklog5743_key2;
380 UPDATE worklog5743_key2 SET col_1_text = REPEAT("c", 4000)
381 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
382 SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key2
383 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
384 INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
385 DELETE FROM worklog5743_key2 WHERE col_1_text = REPEAT("b", 4000);
386 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key2;
387 DROP TABLE worklog5743_key2;
388
389 # With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following
390 CREATE TABLE worklog5743_key4 (
391 col_1_text TEXT (4000) , col_2_text TEXT (4000) ,
392 PRIMARY KEY (col_1_text(1964))
393 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb;
394 INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
395 #CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_text (767));
396 INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
397 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000)
398 FROM worklog5743_key4;
399 UPDATE worklog5743_key4 SET col_1_text = REPEAT("c", 4000)
400 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
401 SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key4
402 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
403 INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
404 DELETE FROM worklog5743_key4 WHERE col_1_text = REPEAT("b", 4000);
405 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key4;
406 DROP TABLE worklog5743_key4;
407
408 # With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following
409 CREATE TABLE worklog5743_key8 (
410 col_1_text TEXT (4000) , col_2_text TEXT (4000) ,
411 PRIMARY KEY (col_1_text(3072))
412 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb;
413 INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
414 #CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_text (767));
415 INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
416 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000)
417 FROM worklog5743_key8;
418 UPDATE worklog5743_key8 SET col_1_text = REPEAT("c", 4000)
419 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
420 SELECT col_1_text = REPEAT("b", 3500) FROM worklog5743_key8
421 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
422 INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
423 DELETE FROM worklog5743_key8 WHERE col_1_text = REPEAT("b", 4000);
424 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743_key8;
425 DROP TABLE worklog5743_key8;
426
427 # Prefix index with BLOB data type , primary/seconday index , DML ops
428 # and COMPRESSED row format. KEY_BLOCK_SIZE is varied as 2 , 4 , 8.
429
430 # With KEY_BLOCK_SIZE = 2,prefix index limit comes around ~948 for following
431 CREATE TABLE worklog5743_key2 (
432 col_1_blob BLOB (4000) , col_2_blob BLOB (4000) ,
433 PRIMARY KEY (col_1_blob(948))
434 ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb;
435 INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
436 #CREATE INDEX prefix_idx ON worklog5743_key2 (col_1_blob (767));
437 INSERT INTO worklog5743_key2 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
438 SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000)
439 FROM worklog5743_key2;
440 UPDATE worklog5743_key2 SET col_1_blob = REPEAT("c", 4000)
441 WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000);
442 SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key2
443 WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000);
444 INSERT INTO worklog5743_key2 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
445 DELETE FROM worklog5743_key2 WHERE col_1_blob = REPEAT("b", 4000);
446 SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key2;
447 DROP TABLE worklog5743_key2;
448
449 # With KEY_BLOCK_SIZE = 4,prefix index limit comes around ~1964 for following
450 CREATE TABLE worklog5743_key4 (
451 col_1_blob BLOB (4000) , col_2_blob BLOB (4000) ,
452 PRIMARY KEY (col_1_blob(1964))
453 ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb;
454 INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
455 #CREATE INDEX prefix_idx ON worklog5743_key4 (col_1_blob (767));
456 INSERT INTO worklog5743_key4 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
457 SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000)
458 FROM worklog5743_key4;
459 UPDATE worklog5743_key4 SET col_1_blob = REPEAT("c", 4000)
460 WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000);
461 SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key4
462 WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000);
463 INSERT INTO worklog5743_key4 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
464 DELETE FROM worklog5743_key4 WHERE col_1_blob = REPEAT("b", 4000);
465 SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key4;
466 DROP TABLE worklog5743_key4;
467
468 # With KEY_BLOCK_SIZE = 8,prefix index limit comes around ~3072 for following
469 CREATE TABLE worklog5743_key8 (
470 col_1_blob BLOB (4000) , col_2_blob BLOB (4000) ,
471 PRIMARY KEY (col_1_blob(3072))
472 ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, engine = innodb;
473 INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
474 #CREATE INDEX prefix_idx ON worklog5743_key8 (col_1_blob (767));
475 INSERT INTO worklog5743_key8 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
476 SELECT col_1_blob = REPEAT("a", 4000) , col_2_blob = REPEAT("o", 4000)
477 FROM worklog5743_key8;
478 UPDATE worklog5743_key8 SET col_1_blob = REPEAT("c", 4000)
479 WHERE col_1_blob = REPEAT("a", 4000) AND col_2_blob = REPEAT("o", 4000);
480 SELECT col_1_blob = REPEAT("b", 3500) FROM worklog5743_key8
481 WHERE col_1_blob = REPEAT("c", 4000) AND col_2_blob = REPEAT("o", 4000);
482 INSERT INTO worklog5743_key8 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
483 DELETE FROM worklog5743_key8 WHERE col_1_blob = REPEAT("b", 4000);
484 SELECT col_1_blob = REPEAT("c", 4000) FROM worklog5743_key8;
485 DROP TABLE worklog5743_key8;
486
487
488 #------------------------------------------------------------------------------
489 # Create mutiple prefix index. We can not create prefix index length > 16K
490 # as index is written in undo log page which of 16K size.
491 # So we can create max 2 prefix index of length 3072 on table
492 CREATE TABLE worklog5743 (
493 col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) ,
494 col_3_text TEXT (4000), col_4_blob BLOB (4000), col_5_text TEXT (4000),
495 col_6_varchar VARCHAR (4000), col_7_binary BINARY (255)
496 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
497 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
498 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
499 REPEAT("a", 4000) , REPEAT("a", 255)
500 );
501
502 # Update reports ER_UNDO_RECORD_TOO_BIG if we create more than 2 indexes.
503 # Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
504 CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072));
505 CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072));
506
507 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000),
508 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
509 REPEAT("a", 4000) , REPEAT("a", 255)
510 );
511 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
512 FROM worklog5743;
513 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
514 WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
515 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
516 WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
517
518 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
519 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
520 REPEAT("a", 4000) , REPEAT("a", 255)
521 );
522 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000);
523 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743;
524
525 # Add 3 more indexes.
526 # Update used to hang but now ER_UNDO_RECORD_TOO_BIG is reported;
527 # Bug#12547647 - UPDATE LOGGING COULD EXCEED UNDO LOG PAGE SIZE
528 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
529 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
530 REPEAT("a", 4000) , REPEAT("a", 255)
531 );
532 CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072));
533 CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072));
534 CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072));
535 --error ER_UNDO_RECORD_TOO_BIG
536 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
537 WHERE col_1_varbinary = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
538 --skip_if_hypergraph # Different warnings.
539 SHOW WARNINGS;
540 DROP TABLE worklog5743;
541
542 #------------------------------------------------------------------------------
543 # Create mutiple prefix index. We can not create prefix index length > 16K as
544 # we write in undo log page which of 16K size.
545 # so we can create max 5 prefix index of length 3072 on table.
546 # Similar to above case but with transactions
547 CREATE TABLE worklog5743 (
548 col_1_varbinary VARBINARY (4000) , col_2_varchar VARCHAR (4000) ,
549 col_3_text TEXT (4000), col_4_blob BLOB (4000),col_5_text TEXT (4000),
550 col_6_varchar VARCHAR (4000), col_7_binary BINARY (255)
551 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
552
553
554 # Update used to hang if we create following 5 indexes. Fixed in;
555 # Bug#12547647 - UPDATE LOGGING COULD EXCEED UNDO LOG PAGE SIZE
556 CREATE INDEX prefix_idx1 ON worklog5743(col_1_varbinary (3072));
557 CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (3072));
558 CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (3072));
559 CREATE INDEX prefix_idx4 ON worklog5743(col_4_blob (3072));
560 CREATE INDEX prefix_idx5 ON worklog5743(col_5_text (3072));
561
562 START TRANSACTION;
563 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
564 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
565 REPEAT("a", 4000) , REPEAT("a", 255)
566 );
567 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
568 FROM worklog5743;
569 ROLLBACK;
570 START TRANSACTION;
571 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
572 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
573 REPEAT("a", 4000) , REPEAT("a", 255)
574 );
575 COMMIT;
576 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
577 FROM worklog5743;
578
579 START TRANSACTION;
580 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000),
581 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
582 REPEAT("a", 4000) , REPEAT("a", 255)
583 );
584 ROLLBACK;
585 # Bug#12547647 - UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
586 # Instead of this error, it would hang before this fix.
587 --error ER_UNDO_RECORD_TOO_BIG
588 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
589 WHERE col_1_varbinary = REPEAT("a", 4000)
590 AND col_2_varchar = REPEAT("o", 4000);
591 SHOW WARNINGS;
592 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
593 WHERE col_1_varbinary = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
594 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
595 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
596 REPEAT("a", 4000) , REPEAT("a", 255)
597 );
598 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000);
599 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743;
600 DROP TABLE worklog5743;
601
602 #------------------------------------------------------------------------------
603 # Prefix index with utf8 charset
604 # utf8 charcter takes 3 bytes in mysql so prefix index limit is 3072/3 = 1024
605 CREATE TABLE worklog5743 (
606 col_1_text TEXT (4000) CHARACTER SET 'utf8',
607 col_2_text TEXT (4000) CHARACTER SET 'utf8',
608 PRIMARY KEY (col_1_text(1024))
609 ) ROW_FORMAT=DYNAMIC, engine = innodb;
610 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
611 CREATE INDEX prefix_idx ON worklog5743(col_1_text (1024));
612 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
613 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) FROM worklog5743;
614 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
615 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
616 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
617 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
618 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
619 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000);
620 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743;
621 DROP TABLE worklog5743;
622
623 # Prefix index with utf8 charset + varchar.
624 CREATE TABLE worklog5743 (col_1_varchar VARCHAR (4000) CHARACTER SET 'utf8',
625 col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8' ,
626 PRIMARY KEY (col_1_varchar(1024))
627 ) ROW_FORMAT=DYNAMIC, engine = innodb;
628 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
629 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1024));
630 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
631 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000) FROM worklog5743;
632 UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000)
633 WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
634 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743
635 WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
636 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
637 DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000);
638 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743;
639 DROP TABLE worklog5743;
640
641 #------------------------------------------------------------------------------
642 # prefinx index on utf8 charset with transaction
643 CREATE TABLE worklog5743 (
644 col_1_varbinary VARBINARY (4000) ,
645 col_2_varchar VARCHAR (4000) CHARACTER SET 'utf8',
646 col_3_text TEXT (4000) CHARACTER SET 'utf8',
647 col_4_blob BLOB (4000),col_5_text TEXT (4000),
648 col_6_varchar VARCHAR (4000), col_7_binary BINARY (255)
649 ) ROW_FORMAT=DYNAMIC, engine = innodb;
650
651
652 CREATE INDEX prefix_idx2 ON worklog5743(col_2_varchar (500));
653 CREATE INDEX prefix_idx3 ON worklog5743(col_3_text (500));
654
655 START TRANSACTION;
656 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
657 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
658 REPEAT("a", 4000) , REPEAT("a", 255)
659 );
660 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
661 FROM worklog5743;
662 ROLLBACK;
663 START TRANSACTION;
664 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
665 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
666 REPEAT("a", 4000) , REPEAT("a", 255)
667 );
668 COMMIT;
669 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
670 FROM worklog5743;
671
672 START TRANSACTION;
673 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000),
674 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
675 REPEAT("a", 4000) , REPEAT("a", 255)
676 );
677 ROLLBACK;
678 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
679 WHERE col_1_varbinary = REPEAT("c", 4000)
680 AND col_2_varchar = REPEAT("o", 4000);
681 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000),
682 REPEAT("a", 4000) , REPEAT("o", 4000), REPEAT("a", 4000),
683 REPEAT("a", 4000) , REPEAT("a", 255)
684 );
685 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("b", 4000);
686 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743;
687 DROP TABLE worklog5743;
688
689
690 #------------------------------------------------------------------------------
691 # Prefix index with utf8 charset on TEXT data type with actual utf8 character
692 # like "स" and "क"
693 CREATE TABLE worklog5743 (
694 col_1_text TEXT (4000) CHARACTER SET 'utf8',
695 col_2_text TEXT (4000) ,
696 PRIMARY KEY (col_1_text(1024))
697 ) ROW_FORMAT=DYNAMIC, engine = innodb;
698 INSERT INTO worklog5743 VALUES(REPEAT("स", 4000) , REPEAT("o", 4000));
699 CREATE INDEX prefix_idx ON worklog5743(col_1_text (1024));
700 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
701 SELECT col_1_text = REPEAT("स", 4000) , col_2_text = REPEAT("o", 4000)
702 FROM worklog5743;
703 UPDATE worklog5743 SET col_1_text = REPEAT("क", 4000)
704 WHERE col_1_text = REPEAT("स", 4000) AND col_2_text = REPEAT("o", 4000);
705 SELECT col_1_text = REPEAT("क", 4000) FROM worklog5743
706 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
707 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
708 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("b", 4000);
709 SELECT col_1_text = REPEAT("क", 4000) FROM worklog5743;
710 DROP TABLE worklog5743;
711
712
713 #------------------------------------------------------------------------------
714 # Prefix index with transaction when 2 client are ruuning there transaction
715 # in different sessions.With ISOLATION LEVEL as REPEATABLE READ and
716 # READ UNCOMMITTED.
717 CREATE TABLE worklog5743 (
718 col_1_text TEXT(4000) , col_2_text TEXT(4000) ,
719 PRIMARY KEY (col_1_text(3072))
720 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
721 INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200));
722 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
723 worklog5743;
724
725 --echo "In connection 1"
726 --connect (con1,localhost,root,,)
727 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
728 worklog5743;
729 SELECT COUNT(*) FROM worklog5743;
730
731
732 --echo "In connection 2"
733 --connect (con2,localhost,root,,)
734 START TRANSACTION;
735 INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200));
736 # Uncomment after Bug#12552164 - TRANSACTION CAN NOT SEE OLD VERSION ROWS THAT
737 # BEING UPDATED
738 #UPDATE worklog5743 SET col_1_varchar = REPEAT("d", 200) WHERE col_1_varchar =
739 #REPEAT("a", 200) AND col_2_varchar = REPEAT("o", 200);
740 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
741 worklog5743;
742
743
744 --echo "In connection 1"
745 --connection con1
746 select @@session.transaction_isolation;
747 SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM
748 worklog5743;
749 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
750 select @@session.transaction_isolation;
751 SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM
752 worklog5743;
753 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
754
755 START TRANSACTION;
756
757 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
758 worklog5743;
759 SELECT COUNT(*) FROM worklog5743;
760
761 --echo "In connection 2"
762 --connection con2
763 COMMIT;
764 # Wait for commit
765 let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist
766 WHERE info='COMMIT';
767 --source include/wait_condition.inc
768
769 --echo "In connection 1"
770 --connection con1
771 SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM
772 worklog5743;
773 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
774 worklog5743;
775 SELECT COUNT(*) FROM worklog5743;
776 COMMIT;
777
778 --connection default
779 DROP TABLE worklog5743;
780
781
782 #------------------------------------------------------------------------------
783 # Prefix index with transaction when 2 client are ruuning there transaction
784 # in different sessions.With ISOLATION LEVEL as REPEATABLE READ and
785 # READ UNCOMMITTED. Same as above case but con2 starts tnx before con1
786
787 CREATE TABLE worklog5743 (
788 col_1_text TEXT(4000) , col_2_text TEXT(4000) ,
789 PRIMARY KEY (col_1_text(3072))
790 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
791 INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200));
792 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
793 worklog5743;
794
795 --echo "In connection 1"
796 --connection con1
797 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
798 worklog5743;
799 SELECT COUNT(*) FROM worklog5743;
800 START TRANSACTION;
801
802
803 --echo "In connection 2"
804 --connection con2
805 START TRANSACTION;
806 INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200));
807 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("a", 200);
808 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
809 worklog5743;
810 COMMIT;
811 # Wait for commit
812 let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist
813 WHERE info='COMMIT';
814 --source include/wait_condition.inc
815
816
817 --echo "In connection 1"
818 --connection con1
819 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
820 select @@session.transaction_isolation;
821 SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM
822 worklog5743;
823 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
824
825 SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM
826 worklog5743;
827 SELECT COUNT(*) FROM worklog5743;
828 COMMIT;
829
830 --connection default
831 DROP TABLE worklog5743;
832
833 #------------------------------------------------------------------------------
834
835 # Prefix index with transaction when 2 client are ruuning there transaction
836 # in different sessions.With ISOLATION LEVEL as REPEATABLE READ and
837 # READ UNCOMMITTED. Same as above cases but with ROLLBACK
838
839 CREATE TABLE worklog5743 (
840 col_1_text TEXT(4000) , col_2_text TEXT(4000) ,
841 PRIMARY KEY (col_1_text(3072))
842 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
843 INSERT INTO worklog5743 VALUES(REPEAT("a", 200) , REPEAT("o", 200));
844 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
845 worklog5743;
846
847 --echo "In connection 1"
848 --connection con1
849 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
850 worklog5743;
851 SELECT COUNT(*) FROM worklog5743;
852 START TRANSACTION;
853
854
855 --echo "In connection 2"
856 --connection con2
857 START TRANSACTION;
858 INSERT INTO worklog5743 VALUES(REPEAT("b", 200) , REPEAT("o", 200));
859 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("a", 200);
860 SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM
861 worklog5743;
862 ROLLBACK;
863 # Wait for rollback
864 let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist
865 WHERE info='COMMIT';
866 --source include/wait_condition.inc
867
868
869 --echo "In connection 1"
870 --connection con1
871 SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM
872 worklog5743;
873 SELECT COUNT(*) FROM worklog5743;
874 COMMIT;
875
876 --echo "Disconnect the connections 1 and 2"
877 --disconnect con1
878 --source include/wait_until_disconnected.inc
879 --connection con2
880 --disconnect con2
881 --source include/wait_until_disconnected.inc
882
883 --connection default
884 DROP TABLE worklog5743;
885
886
887 #------------------------------------------------------------------------------
888 # Select queries on prefix index column as index will be used in queries.
889 # Use few select functions , join condition , subqueries.
890
891 CREATE TABLE worklog5743 (
892 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
893 PRIMARY KEY (col_1_varchar(3072))
894 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
895 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
896 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
897 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
898 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
899 FROM worklog5743;
900 UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000)
901 WHERE col_1_varchar = REPEAT("a", 4000)
902 AND col_2_varchar = REPEAT("o", 4000);
903 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743
904 WHERE col_1_varchar = REPEAT("c", 4000)
905 AND col_2_varchar = REPEAT("o", 4000);
906 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
907 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743;
908
909 # Select with Join
910 SELECT tbl1.col_1_varchar = tbl2.col_1_varchar
911 FROM worklog5743 tbl1 , worklog5743 tbl2
912 WHERE tbl1.col_1_varchar = tbl2.col_1_varchar ;
913
914 # Select in subquey
915 SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1
916 WHERE col_1_varchar IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) ;
917 SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1
918 WHERE col_1_varchar NOT IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2) ;
919 SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1 WHERE
920 col_1_varchar IN (SELECT tbl2.col_1_varchar FROM worklog5743 tbl2)
921 AND col_1_varchar = REPEAT("c", 4000);
922 SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1
923 WHERE col_1_varchar in (
924 SELECT tbl2.col_1_varchar FROM worklog5743 tbl2
925 WHERE tbl1.col_1_varchar != tbl2.col_1_varchar
926 ) ;
927 SELECT tbl1.col_1_varchar = REPEAT("c", 4000) FROM worklog5743 tbl1
928 WHERE col_1_varchar in (
929 SELECT tbl2.col_1_varchar FROM worklog5743 tbl2
930 WHERE tbl1.col_1_varchar = tbl2.col_1_varchar
931 ) ;
932
933 # function
934 SELECT
935 REVERSE(col_1_varchar) = REPEAT("c", 4000) ,
936 REVERSE(REVERSE(col_1_varchar)) = REPEAT("c", 4000)
937 FROM worklog5743;
938 SELECT
939 UPPER(col_1_varchar) = REPEAT("c", 4000) ,
940 UPPER(col_1_varchar) = REPEAT("C", 4000) ,
941 LOWER(UPPER(col_1_varchar)) = REPEAT("c", 4000)
942 FROM worklog5743;
943 SELECT
944 col_1_varchar = REPEAT("c", 4000)
945 FROM worklog5743 WHERE col_1_varchar like '%c__%';
946 SELECT SUBSTRING(INSERT(col_1_varchar, 1, 4, 'kkkk'),1,10) FROM worklog5743 ;
947 SELECT CONCAT(SUBSTRING(col_1_varchar,-5,3),'append') FROM worklog5743 ;
948
949
950 DROP TABLE worklog5743;
951
952 #------------------------------------------------------------------------------
953 # Prefix index with NULL values
954 CREATE TABLE worklog5743 (
955 col_1_varchar VARCHAR (4000) ,
956 col_2_varchar VARCHAR (4000) ,
957 UNIQUE INDEX (col_1_varchar(3072))
958 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
959 INSERT INTO worklog5743
960 VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)), REPEAT("o", 4000));
961 INSERT INTO worklog5743
962 VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000));
963 INSERT INTO worklog5743 VALUES(NULL,NULL);
964 INSERT INTO worklog5743 VALUES(NULL,NULL);
965 # check IS
966 --sorted_result
967 SELECT COLUMN_NAME,INDEX_NAME,SUB_PART,INDEX_TYPE
968 FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'worklog5743' ;
969 SELECT col_1_varchar FROM worklog5743 WHERE col_1_varchar IS NULL;
970 SELECT col_1_varchar = concat(REPEAT("a", 2000),REPEAT("b", 2000))
971 FROM worklog5743 WHERE col_1_varchar IS NOT NULL ORDER BY 1;
972
973
974 DROP TABLE worklog5743;
975
976 # -----------------------------------------------------------------------------
977 # Try drop and add secondary prefix index
978 CREATE TABLE worklog5743 (
979 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
980 PRIMARY KEY (col_1_varchar(3072))) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
981 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
982 # Create index
983 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
984 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
985 # Drop index
986 DROP INDEX prefix_idx ON worklog5743;
987
988 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
989 FROM worklog5743;
990 UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000)
991 WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
992 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743
993 WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
994 # Again add index
995 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
996 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
997 DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000);
998 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743;
999 DROP TABLE worklog5743;
1000
1001 # -----------------------------------------------------------------------------
1002
1003 # Try drop and add primary prefix index
1004 CREATE TABLE worklog5743 (
1005 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
1006 PRIMARY KEY `prefix_primary` (col_1_varchar(3072))
1007 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1008 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
1009 # Create index
1010 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
1011 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
1012 # Drop index
1013 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1014
1015 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
1016 FROM worklog5743;
1017 UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000)
1018 WHERE col_1_varchar = REPEAT("a", 4000)
1019 AND col_2_varchar = REPEAT("o", 4000);
1020 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743
1021 WHERE col_1_varchar = REPEAT("c", 4000)
1022 AND col_2_varchar = REPEAT("o", 4000);
1023 # Again add index
1024 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072));
1025
1026 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1027 --error ER_DUP_ENTRY
1028 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1029 DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000);
1030 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743;
1031 DROP TABLE worklog5743;
1032
1033
1034 # -----------------------------------------------------------------------------
1035
1036 # Try drop and add both (primary/secondary) prefix index
1037 CREATE TABLE worklog5743 (
1038 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
1039 PRIMARY KEY `prefix_primary` (col_1_varchar(3072))
1040 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1041 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
1042 # Create index
1043 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
1044 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
1045 # Drop primary index
1046 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1047 # Drop secondary index
1048 DROP INDEX prefix_idx ON worklog5743;
1049
1050 SELECT col_1_varchar = REPEAT("a", 4000) , col_2_varchar = REPEAT("o", 4000)
1051 FROM worklog5743;
1052 UPDATE worklog5743 SET col_1_varchar = REPEAT("c", 4000)
1053 WHERE col_1_varchar = REPEAT("a", 4000) AND col_2_varchar = REPEAT("o", 4000);
1054 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743
1055 WHERE col_1_varchar = REPEAT("c", 4000) AND col_2_varchar = REPEAT("o", 4000);
1056 # Again add index
1057 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072));
1058 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
1059
1060 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1061 --error ER_DUP_ENTRY
1062 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1063 DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 4000);
1064 SELECT col_1_varchar = REPEAT("c", 4000) FROM worklog5743;
1065 DROP TABLE worklog5743;
1066
1067
1068 # -----------------------------------------------------------------------------
1069 # Drop index from differnt session
1070 CREATE TABLE worklog5743 (
1071 col_1_varchar VARCHAR(4000) , col_2_varchar VARCHAR(4000) ,
1072 PRIMARY KEY (col_1_varchar (3072))
1073 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1074 INSERT INTO worklog5743 VALUES(REPEAT("c", 3500) , REPEAT("o", 3500));
1075 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072));
1076
1077 # Start new session
1078 --connect (con1,localhost,root,,)
1079
1080
1081 --echo " Switching to con1 connection For select "
1082 --connection con1
1083 SELECT col_1_varchar = REPEAT("c", 3500) , col_2_varchar = REPEAT("o", 3500)
1084 FROM worklog5743;
1085
1086 --echo " Switching to default connection For DMLs "
1087 --connection default
1088 START TRANSACTION;
1089 INSERT INTO worklog5743 VALUES(REPEAT("a", 3500) , REPEAT("o", 3500));
1090 SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743
1091 WHERE col_2_varchar = REPEAT("o", 3500);
1092 COMMIT;
1093
1094 --echo " Switching to con1 connection For Dropping index and some DMLs "
1095 --connection con1
1096 START TRANSACTION;
1097 INSERT INTO worklog5743 VALUES(REPEAT("k", 3500),REPEAT("p", 3500));
1098 # Drop primary index
1099 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1100 UPDATE worklog5743 SET col_1_varchar = REPEAT("b", 3500)
1101 WHERE col_1_varchar = REPEAT("a", 3500)
1102 AND col_2_varchar = REPEAT("o", 3500);
1103 SELECT col_1_varchar = REPEAT("b", 3500) FROM worklog5743
1104 WHERE col_2_varchar = REPEAT("o", 3500);
1105
1106 --echo " Switching to default connection For DELETE "
1107 --connection default
1108 DELETE FROM worklog5743 WHERE col_1_varchar = REPEAT("b", 3500);
1109 SELECT col_1_varchar = REPEAT("a", 3500) FROM worklog5743
1110 WHERE col_2_varchar = REPEAT("p", 3500);
1111
1112 --echo " Switching to con1 connection to commit changes "
1113 --connection con1
1114 COMMIT;
1115 --disconnect con1
1116
1117 --echo " Switching to default connection to drop and end sub-test "
1118 --connection default
1119 DROP TABLE worklog5743;
1120
1121
1122
1123 # -----------------------------------------------------------------------------
1124 # Create prefix index with length < 3072 , length = 3072 , length > 3072
1125 # - varbinary data type + secondary index
1126 CREATE TABLE worklog5743 (
1127 col_1_varbinary VARBINARY (4000) , col_2_varbinary VARBINARY (4000) ,
1128 PRIMARY KEY (col_1_varbinary(3072))) ROW_FORMAT=DYNAMIC, engine = innodb;
1129 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
1130 # Create index of 3072
1131 CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (3072));
1132 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
1133 SELECT col_1_varbinary = REPEAT("a", 4000) , col_2_varbinary = REPEAT("o", 4000)
1134 FROM worklog5743;
1135 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
1136 WHERE col_1_varbinary = REPEAT("a", 4000)
1137 AND col_2_varbinary = REPEAT("o", 4000);
1138 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
1139 WHERE col_1_varbinary = REPEAT("c", 4000)
1140 AND col_2_varbinary = REPEAT("o", 4000);
1141 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000);
1142 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
1143 WHERE col_1_varbinary = REPEAT("c", 4000)
1144 AND col_2_varbinary = REPEAT("o", 4000);
1145 # Drop index
1146 DROP INDEX prefix_idx ON worklog5743;
1147 SELECT col_1_varbinary = REPEAT("b", 4000) FROM worklog5743
1148 WHERE col_1_varbinary = REPEAT("b", 4000)
1149 AND col_2_varbinary = REPEAT("p", 4000);
1150
1151
1152 # Again add index length < 3072
1153 CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (2000));
1154 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1155 SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743;
1156 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
1157 WHERE col_1_varbinary = REPEAT("a", 4000)
1158 AND col_2_varbinary = REPEAT("o", 4000);
1159 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000);
1160 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
1161 WHERE col_1_varbinary = REPEAT("c", 4000)
1162 AND col_2_varbinary = REPEAT("o", 4000);
1163 # Drop index
1164 DROP INDEX prefix_idx ON worklog5743;
1165
1166 # Again add index length > 3072.
1167 # Index prefix larger than 3072 will be truncated to 3072.
1168 # If the table is REDUNDANT or COMPACT, which do
1169 # not support prefix > 767, the create index will be rejected.
1170 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
1171 CREATE INDEX prefix_idx ON worklog5743(col_1_varbinary (4000));
1172 SET sql_mode = default;
1173 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1174 SELECT col_1_varbinary = REPEAT("a", 4000) FROM worklog5743;
1175 UPDATE worklog5743 SET col_1_varbinary = REPEAT("c", 4000)
1176 WHERE col_1_varbinary = REPEAT("a", 4000)
1177 AND col_2_varbinary = REPEAT("o", 4000);
1178 DELETE FROM worklog5743 WHERE col_1_varbinary = REPEAT("c", 4000);
1179 SELECT col_1_varbinary = REPEAT("c", 4000) FROM worklog5743
1180 WHERE col_1_varbinary = REPEAT("c", 4000)
1181 AND col_2_varbinary = REPEAT("o", 4000);
1182
1183
1184 DROP TABLE worklog5743;
1185
1186 # -----------------------------------------------------------------------------
1187 # Create prefix index with length < 3072 , length = 3072 , length > 3072
1188 # text data type + secondary index
1189 CREATE TABLE worklog5743 (col_1_text TEXT (4000) , col_2_text TEXT (4000) ,
1190 PRIMARY KEY (col_1_text(500))
1191 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1192 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
1193 # Create index of 3072
1194 CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072));
1195 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
1196 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000)
1197 FROM worklog5743;
1198 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
1199 WHERE col_1_text = REPEAT("a", 4000)
1200 AND col_2_text = REPEAT("o", 4000);
1201 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1202 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
1203 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000);
1204 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1205 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
1206 # Drop index
1207 DROP INDEX prefix_idx ON worklog5743;
1208 SELECT col_1_text = REPEAT("b", 4000) FROM worklog5743
1209 WHERE col_1_text = REPEAT("b", 4000) AND col_2_text = REPEAT("p", 4000);
1210
1211 # Again add index length < 3072
1212 CREATE INDEX prefix_idx ON worklog5743(col_1_text (1000));
1213 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1214 SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743;
1215 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
1216 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
1217 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000);
1218 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1219 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
1220 # Drop index
1221 DROP INDEX prefix_idx ON worklog5743;
1222
1223 # Again add index length > 3072.
1224 # Index prefix larger than 3072 will be truncated to 3072.
1225 # In innodb_strict_mode, the statement will fail with error.
1226 # If the table is REDUNDANT or COMPACT, which do not support
1227 # prefix > 767, the create index will be rejected.
1228 --error ER_TOO_LONG_KEY
1229 CREATE INDEX prefix_idx ON worklog5743(col_1_text (4000));
1230 CREATE INDEX prefix_idx ON worklog5743(col_1_text (3072));
1231 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1232 SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743;
1233 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
1234 WHERE col_1_text = REPEAT("a", 4000) AND col_2_text = REPEAT("o", 4000);
1235 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000);
1236 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1237 WHERE col_1_text = REPEAT("c", 4000) AND col_2_text = REPEAT("o", 4000);
1238
1239 DROP TABLE worklog5743;
1240
1241
1242 # -----------------------------------------------------------------------------
1243 # Create prefix index with length < 948 , length = 948 , length > 948
1244 # For compressed row type + primary key
1245 CREATE TABLE worklog5743 (
1246 col_1_text TEXT (4000) , col_2_text TEXT (4000) ,
1247 PRIMARY KEY (col_1_text(948))
1248 ) charset latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb;
1249 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000));
1250 # Create index of 767
1251 INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000));
1252 SELECT col_1_text = REPEAT("a", 4000) , col_2_text = REPEAT("o", 4000) FROM worklog5743;
1253 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
1254 WHERE col_1_text = REPEAT("a", 4000)
1255 AND col_2_text = REPEAT("o", 4000);
1256 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1257 WHERE col_1_text = REPEAT("c", 4000)
1258 AND col_2_text = REPEAT("o", 4000);
1259 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000);
1260 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1261 WHERE col_1_text = REPEAT("c", 4000)
1262 AND col_2_text = REPEAT("o", 4000);
1263 # Drop index
1264 #DROP INDEX prefix_idx ON worklog5743;
1265 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1266 SELECT col_1_text = REPEAT("b", 4000) FROM worklog5743
1267 WHERE col_1_text = REPEAT("b", 4000)
1268 AND col_2_text = REPEAT("p", 4000);
1269
1270 # Again add index length < 767
1271 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (700));
1272 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1273 SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743;
1274 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
1275 WHERE col_1_text = REPEAT("a", 4000)
1276 AND col_2_text = REPEAT("o", 4000);
1277 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000);
1278 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1279 WHERE col_1_text = REPEAT("c", 4000)
1280 AND col_2_text = REPEAT("o", 4000);
1281 # Drop index
1282 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1283
1284 # Again add index length > 948. Expect error 'to big row ' due to exceed
1285 # in key length.
1286 -- error ER_TOO_BIG_ROWSIZE
1287 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_text (950));
1288 INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
1289 SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743;
1290 UPDATE worklog5743 SET col_1_text = REPEAT("c", 4000)
1291 WHERE col_1_text = REPEAT("a", 4000)
1292 AND col_2_text = REPEAT("o", 4000);
1293 DELETE FROM worklog5743 WHERE col_1_text = REPEAT("c", 4000);
1294 SELECT col_1_text = REPEAT("c", 4000) FROM worklog5743
1295 WHERE col_1_text = REPEAT("c", 4000)
1296 AND col_2_text = REPEAT("o", 4000);
1297
1298 DROP TABLE worklog5743;
1299
1300 # -----------------------------------------------------------------------------
1301 # Create prefix index with length < 3072 , length = 3072 , length > 3072
1302 # data types VARCHAR
1303 CREATE TABLE worklog5743 (
1304 col_1_varchar VARCHAR (4000) , PRIMARY KEY (col_1_varchar(3072))
1305 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1306 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1307 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar (900));
1308 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1309 # Again add index length > 3072. Expect error.Length exceeds maximum supported
1310 # key length
1311 # Again add index length > 3072.
1312 # Index prefix larger than 3072 will be truncated to 3072.
1313 # If the table is REDUNDANT or COMPACT, which do
1314 # not support prefix > 767, the create index will be rejected.
1315 # Index length is truncated only for 'create index' , but error if we add
1316 # prefix index with length > 3072
1317 --error ER_TOO_LONG_KEY
1318 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar (3073));
1319 DROP TABLE worklog5743;
1320
1321
1322 CREATE TABLE worklog5743 (
1323 col_1_BLOB BLOB (4000) , PRIMARY KEY (col_1_BLOB(3072))
1324 ) ROW_FORMAT=DYNAMIC, engine = innodb;
1325 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1326 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_BLOB (500));
1327 ALTER TABLE worklog5743 DROP PRIMARY KEY;
1328 # Negative case
1329 # Again add index length > 3072. Expect error.Length exceeds maximum supported
1330 # key length
1331 # Index length is truncated only for 'create index' , but error if we add
1332 # prefix index with length > 3072
1333 --error ER_TOO_LONG_KEY
1334 ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_BLOB (3073));
1335
1336 DROP TABLE worklog5743;
1337
1338 # -----------------------------------------------------------------------------
1339 # Error on adding larger prefix if violates unique index.
1340 CREATE TABLE worklog5743 (
1341 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000)
1342 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1343 INSERT INTO worklog5743
1344 VALUES(concat(REPEAT("a", 2000),REPEAT("b", 1000),REPEAT("c", 1000)),
1345 REPEAT("o", 4000));
1346 INSERT INTO worklog5743
1347 VALUES(concat(REPEAT("a", 2000),REPEAT("b", 2000)), REPEAT("o", 4000));
1348 --error ER_DUP_ENTRY
1349 ALTER TABLE worklog5743 ADD PRIMARY KEY `pk_idx` (col_1_varchar(3000));
1350 DROP TABLE worklog5743;
1351
1352 # -----------------------------------------------------------------------------
1353 CREATE TABLE worklog5743 (
1354 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
1355 PRIMARY KEY (col_1_varchar(3072))
1356 ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb;
1357 SHOW CREATE TABLE worklog5743;
1358 DROP TABLE worklog5743;
1359
1360
1361 # -----------------------------------------------------------------------------
1362 # Backward compatibility test - Index lenghth > 767 is truncated for REDUNDANT
1363 # and COMPACT
1364 CREATE TABLE worklog5743 (
1365 col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) ,
1366 PRIMARY KEY (col_1_varchar(767))
1367 ) charset latin1 engine = innodb ROW_FORMAT=COMPACT;
1368 INSERT INTO worklog5743 VALUES(REPEAT('a',4000),REPEAT('b',4000));
1369 # Prefix index > 767 is truncated with REDUNDANT and COMPACT
1370 --enable_info
1371 --error ER_TOO_LONG_KEY
1372 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (1000));
1373 CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (767));
1374 ALTER TABLE worklog5743 ROW_FORMAT=REDUNDANT;
1375 --disable_info
1376 SHOW CREATE TABLE worklog5743;
1377 DROP TABLE worklog5743;
1378 #------------------------------------------------------------------------------
1379
1380 # Check that all connections opened by test cases in this file are really
1381 # gone so execution of other tests won't be affected by their presence.
1382 --source include/wait_until_count_sessions.inc
1383
1384 --disable_query_log
1385 eval set global innodb_file_per_table = $innodb_file_per_table_orig;