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