Debian Codegraph @

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

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