Debian Codegraph @

mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result

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