Debian Codegraph @

mysql-test/suite/ndb/r/ndb_blob.result

raw
1 set autocommit=0;
2 create table t1 (
3 a int not null primary key,
4 b text not null,
5 c int not null,
6 d longblob,
7 key (c)
8 ) engine=ndbcluster
9 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
10 set @x0 = '01234567012345670123456701234567';
11 set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
12 set @b1 = 'b1';
13 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
14 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
15 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
16 set @b1 = concat(@b1,@x0);
17 set @d1 = 'dd1';
18 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
19 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
20 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
21 set @b2 = 'b2';
22 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
23 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
24 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
25 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
26 set @d2 = 'dd2';
27 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
28 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
29 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
30 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
31 select length(@x0),length(@b1),length(@d1) from dual;
32 length(@x0) length(@b1) length(@d1)
33 256 2256 3000
34 select length(@x0),length(@b2),length(@d2) from dual;
35 length(@x0) length(@b2) length(@d2)
36 256 20000 30000
37 insert into t1 values(1,@b1,111,@d1);
38 insert into t1 values(2,@b2,222,@d2);
39 commit;
40 explain select * from t1 where a = 1;
41 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
42 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const # # NULL
43 Warnings:
44 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`a` = 1)
45 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
46 from t1 where a=1;
47 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
48 1 2256 b1 3000 dd1
49 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
50 from t1 where a=2;
51 a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
52 2 20000 b2 30000 dd2
53 update t1 set b=@b2,d=@d2 where a=1;
54 update t1 set b=@b1,d=@d1 where a=2;
55 commit;
56 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
57 from t1 where a=1;
58 a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
59 1 20000 b2 30000 dd2
60 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
61 from t1 where a=2;
62 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
63 2 2256 b1 3000 dd1
64 update t1 set b=concat(b,b),d=concat(d,d) where a=1;
65 update t1 set b=concat(b,b),d=concat(d,d) where a=2;
66 commit;
67 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
68 from t1 where a=1;
69 a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
70 1 40000 b2 60000 dd2
71 select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
72 from t1 where a=2;
73 a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3)
74 2 4512 b1 6000 dd1
75 update t1 set d=null where a=1;
76 commit;
77 select a from t1 where d is null;
78 a
79 1
80 delete from t1 where a=45567;
81 commit;
82 delete from t1 where a=1;
83 delete from t1 where a=2;
84 commit;
85 select count(*) from t1;
86 count(*)
87 0
88 replace t1 set a=1,b=@b1,c=111,d=@d1;
89 replace t1 set a=2,b=@b2,c=222,d=@d2;
90 commit;
91 explain select * from t1 where a = 1;
92 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
93 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const # # NULL
94 Warnings:
95 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`a` = 1)
96 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
97 from t1 where a=1;
98 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
99 1 2256 b1 3000 dd1
100 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
101 from t1 where a=2;
102 a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
103 2 20000 b2 30000 dd2
104 replace t1 set a=1,b=@b2,c=111,d=@d2;
105 replace t1 set a=2,b=@b1,c=222,d=@d1;
106 commit;
107 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
108 from t1 where a=1;
109 a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
110 1 20000 b2 30000 dd2
111 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
112 from t1 where a=2;
113 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
114 2 2256 b1 3000 dd1
115 replace t1 set a=1,b=concat(@b2,@b2),c=111,d=concat(@d2,@d2);
116 replace t1 set a=2,b=concat(@b1,@b1),c=222,d=concat(@d1,@d1);
117 commit;
118 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
119 from t1 where a=1;
120 a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
121 1 40000 b2 60000 dd2
122 select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
123 from t1 where a=2;
124 a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3)
125 2 4512 b1 6000 dd1
126 replace t1 set a=1,b='xyz',c=111,d=null;
127 commit;
128 select a,b from t1 where d is null;
129 a b
130 1 xyz
131 delete from t1 where a=1;
132 delete from t1 where a=2;
133 commit;
134 select count(*) from t1;
135 count(*)
136 0
137 insert into t1 values(1,@b1,111,@d1);
138 insert into t1 values(2,@b2,222,@d2);
139 commit;
140 explain select * from t1 where c = 111;
141 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
142 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref c c 4 const # # NULL
143 Warnings:
144 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` = 111)
145 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
146 from t1 where c=111;
147 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
148 1 2256 b1 3000 dd1
149 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
150 from t1 where c=222;
151 a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
152 2 20000 b2 30000 dd2
153 update t1 set b=@b2,d=@d2 where c=111;
154 update t1 set b=@b1,d=@d1 where c=222;
155 commit;
156 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
157 from t1 where c=111;
158 a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
159 1 20000 b2 30000 dd2
160 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
161 from t1 where c=222;
162 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
163 2 2256 b1 3000 dd1
164 update t1 set d=null where c=111;
165 commit;
166 select a from t1 where d is null;
167 a
168 1
169 delete from t1 where c=111;
170 delete from t1 where c=222;
171 commit;
172 select count(*) from t1;
173 count(*)
174 0
175 insert into t1 values(1,'b1',111,'dd1');
176 insert into t1 values(2,'b2',222,'dd2');
177 insert into t1 values(3,'b3',333,'dd3');
178 insert into t1 values(4,'b4',444,'dd4');
179 insert into t1 values(5,'b5',555,'dd5');
180 insert into t1 values(6,'b6',666,'dd6');
181 insert into t1 values(7,'b7',777,'dd7');
182 insert into t1 values(8,'b8',888,'dd8');
183 insert into t1 values(9,'b9',999,'dd9');
184 commit;
185 explain select * from t1;
186 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
187 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # NULL
188 Warnings:
189 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1`
190 select * from t1 order by a;
191 a b c d
192 1 b1 111 dd1
193 2 b2 222 dd2
194 3 b3 333 dd3
195 4 b4 444 dd4
196 5 b5 555 dd5
197 6 b6 666 dd6
198 7 b7 777 dd7
199 8 b8 888 dd8
200 9 b9 999 dd9
201 update t1 set b=concat(a,'x',b),d=concat(a,'x',d);
202 commit;
203 select * from t1 order by a;
204 a b c d
205 1 1xb1 111 1xdd1
206 2 2xb2 222 2xdd2
207 3 3xb3 333 3xdd3
208 4 4xb4 444 4xdd4
209 5 5xb5 555 5xdd5
210 6 6xb6 666 6xdd6
211 7 7xb7 777 7xdd7
212 8 8xb8 888 8xdd8
213 9 9xb9 999 9xdd9
214 delete from t1;
215 commit;
216 select count(*) from t1;
217 count(*)
218 0
219 insert into t1 values(1,@b1,111,@d1);
220 insert into t1 values(2,@b2,222,@d2);
221 commit;
222 explain select * from t1;
223 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
224 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # NULL
225 Warnings:
226 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1`
227 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
228 from t1 order by a;
229 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
230 1 2256 b1 3000 dd1
231 2 20000 b2 30000 dd2
232 update t1 set b=concat(b,b),d=concat(d,d);
233 commit;
234 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
235 from t1 order by a;
236 a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
237 1 4512 6000
238 2 40000 b2 60000 dd2
239 delete from t1;
240 commit;
241 select count(*) from t1;
242 count(*)
243 0
244 insert into t1 values(1,'b1',111,'dd1');
245 insert into t1 values(2,'b2',222,'dd2');
246 insert into t1 values(3,'b3',333,'dd3');
247 insert into t1 values(4,'b4',444,'dd4');
248 insert into t1 values(5,'b5',555,'dd5');
249 insert into t1 values(6,'b6',666,'dd6');
250 insert into t1 values(7,'b7',777,'dd7');
251 insert into t1 values(8,'b8',888,'dd8');
252 insert into t1 values(9,'b9',999,'dd9');
253 commit;
254 explain select * from t1 where c >= 100 order by a;
255 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
256 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range c c 4 NULL # # Using pushed condition (`test`.`t1`.`c` >= 100); Using filesort
257 Warnings:
258 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` >= 100) order by `test`.`t1`.`a`
259 select * from t1 where c >= 100 order by a;
260 a b c d
261 1 b1 111 dd1
262 2 b2 222 dd2
263 3 b3 333 dd3
264 4 b4 444 dd4
265 5 b5 555 dd5
266 6 b6 666 dd6
267 7 b7 777 dd7
268 8 b8 888 dd8
269 9 b9 999 dd9
270 update t1 set b=concat(a,'x',b),d=concat(a,'x',d)
271 where c >= 100;
272 commit;
273 select * from t1 where c >= 100 order by a;
274 a b c d
275 1 1xb1 111 1xdd1
276 2 2xb2 222 2xdd2
277 3 3xb3 333 3xdd3
278 4 4xb4 444 4xdd4
279 5 5xb5 555 5xdd5
280 6 6xb6 666 6xdd6
281 7 7xb7 777 7xdd7
282 8 8xb8 888 8xdd8
283 9 9xb9 999 9xdd9
284 delete from t1 where c >= 100;
285 commit;
286 select count(*) from t1;
287 count(*)
288 0
289 insert into t1 values(1,@b1,111,@d1);
290 insert into t1 values(2,@b2,222,@d2);
291 commit;
292 explain select * from t1 where c >= 100 order by a;
293 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
294 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c PRIMARY 4 NULL # # Using pushed condition (`test`.`t1`.`c` >= 100)
295 Warnings:
296 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`c` >= 100) order by `test`.`t1`.`a`
297 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
298 from t1 where c >= 100 order by a;
299 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
300 1 2256 b1 3000 dd1
301 2 20000 b2 30000 dd2
302 update t1 set b=concat(b,b),d=concat(d,d);
303 commit;
304 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
305 from t1 where c >= 100 order by a;
306 a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
307 1 4512 6000
308 2 40000 b2 60000 dd2
309 delete from t1 where c >= 100;
310 commit;
311 select count(*) from t1;
312 count(*)
313 0
314 insert into t1 values(1,@b1,111,@d1);
315 insert into t1 values(2,@b2,222,@d2);
316 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
317 from t1 where a = 0;
318 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
319 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
320 from t1 where a = 1;
321 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
322 1 2256 b1 3000 dd1
323 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
324 from t1 where a = 2;
325 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
326 2 20000 b2 30000 dd2
327 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
328 from t1 order by a;
329 a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
330 1 2256 b1 3000 dd1
331 2 20000 b2 30000 dd2
332 rollback;
333 select count(*) from t1;
334 count(*)
335 0
336 insert into t1 values(1,'b1',111,'dd1');
337 insert into t1 values(2,'b2',222,'dd2');
338 insert into t1 values(3,'b3',333,'dd3');
339 insert into t1 values(4,'b4',444,'dd4');
340 insert into t1 values(5,'b5',555,'dd5');
341 insert into t1 values(6,'b6',666,'dd6');
342 insert into t1 values(7,'b7',777,'dd7');
343 insert into t1 values(8,'b8',888,'dd8');
344 insert into t1 values(9,'b9',999,'dd9');
345 commit;
346 select * from t1 order by a;
347 a b c d
348 1 b1 111 dd1
349 2 b2 222 dd2
350 3 b3 333 dd3
351 4 b4 444 dd4
352 5 b5 555 dd5
353 6 b6 666 dd6
354 7 b7 777 dd7
355 8 b8 888 dd8
356 9 b9 999 dd9
357 alter table t1 add x int;
358 select * from t1 order by a;
359 a b c d x
360 1 b1 111 dd1 NULL
361 2 b2 222 dd2 NULL
362 3 b3 333 dd3 NULL
363 4 b4 444 dd4 NULL
364 5 b5 555 dd5 NULL
365 6 b6 666 dd6 NULL
366 7 b7 777 dd7 NULL
367 8 b8 888 dd8 NULL
368 9 b9 999 dd9 NULL
369 alter table t1 drop x;
370 select * from t1 order by a;
371 a b c d
372 1 b1 111 dd1
373 2 b2 222 dd2
374 3 b3 333 dd3
375 4 b4 444 dd4
376 5 b5 555 dd5
377 6 b6 666 dd6
378 7 b7 777 dd7
379 8 b8 888 dd8
380 9 b9 999 dd9
381 create database test2;
382 use test2;
383 CREATE TABLE t2 (
384 a bigint unsigned NOT NULL PRIMARY KEY,
385 b int unsigned not null,
386 c int unsigned
387 ) engine=ndbcluster;
388 insert into t2 values (1,1,1),(2,2,2);
389 select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a;
390 a b c d a b c
391 1 b1 111 dd1 1 1 1
392 2 b2 222 dd2 2 2 2
393 drop table t2;
394 use test;
395 select * from t1 order by a;
396 a b c d
397 1 b1 111 dd1
398 2 b2 222 dd2
399 3 b3 333 dd3
400 4 b4 444 dd4
401 5 b5 555 dd5
402 6 b6 666 dd6
403 7 b7 777 dd7
404 8 b8 888 dd8
405 9 b9 999 dd9
406 alter table t1 add x int;
407 select * from t1 order by a;
408 a b c d x
409 1 b1 111 dd1 NULL
410 2 b2 222 dd2 NULL
411 3 b3 333 dd3 NULL
412 4 b4 444 dd4 NULL
413 5 b5 555 dd5 NULL
414 6 b6 666 dd6 NULL
415 7 b7 777 dd7 NULL
416 8 b8 888 dd8 NULL
417 9 b9 999 dd9 NULL
418 alter table t1 drop x;
419 select * from t1 order by a;
420 a b c d
421 1 b1 111 dd1
422 2 b2 222 dd2
423 3 b3 333 dd3
424 4 b4 444 dd4
425 5 b5 555 dd5
426 6 b6 666 dd6
427 7 b7 777 dd7
428 8 b8 888 dd8
429 9 b9 999 dd9
430 drop table t1;
431 drop database test2;
432 set autocommit=0;
433 create table t1 (
434 a int not null primary key,
435 b tinytext
436 ) engine=ndbcluster;
437 insert into t1 values(1, 'x');
438 update t1 set b = 'y';
439 select * from t1;
440 a b
441 1 y
442 delete from t1;
443 select * from t1;
444 a b
445 commit;
446 replace t1 set a=2, b='y';
447 select * from t1;
448 a b
449 2 y
450 delete from t1;
451 select * from t1;
452 a b
453 drop table t1;
454 set autocommit=0;
455 create table t1 (
456 a int not null primary key,
457 b text not null
458 ) engine=ndbcluster;
459 insert into t1 values(1, '');
460 select * from t1;
461 a b
462 1
463 commit;
464 begin;
465 insert into t1 values (3, repeat("w", 8000));
466 insert into t1 values (2, repeat("x", 10000)),
467 (3, repeat("y", 5000)),
468 (4, repeat("z", 15000));
469 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
470 rollback;
471 commit;
472 drop table t1;
473 set autocommit=1;
474 use test;
475 CREATE TABLE t1 (
476 a int,
477 b text,
478 PRIMARY KEY (a)
479 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
480 INSERT INTO t1 VALUES
481 (1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
482 INSERT INTO t1 VALUES
483 (2,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
484 select * from t1 order by a;
485 a b
486 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
487 2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
488 alter table t1 engine=ndb;
489 select * from t1 order by a;
490 a b
491 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
492 2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
493 set autocommit=1;
494 alter table t1 engine=myisam;
495 select * from t1 order by a;
496 a b
497 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
498 2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
499 drop table t1;
500 create table t1 (
501 id int(11) unsigned primary key NOT NULL auto_increment,
502 msg text NOT NULL
503 ) engine=ndbcluster default charset=utf8;
504 Warnings:
505 Warning 1681 Integer display width is deprecated and will be removed in a future release.
506 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.
507 insert into t1 (msg) values(
508 'Tries to validate (8 byte length + inline bytes) as UTF8 :(
509 Fast fix: removed validation for Text. It is not yet indexable
510 so bad data will not crash kernel.');
511 select * from t1;
512 id msg
513 1 Tries to validate (8 byte length + inline bytes) as UTF8 :(
514 Fast fix: removed validation for Text. It is not yet indexable
515 so bad data will not crash kernel.
516 drop table t1;
517 create table t1 (
518 a int primary key not null auto_increment,
519 b text
520 ) engine=ndbcluster;
521 select count(*) from t1;
522 count(*)
523 500
524 truncate t1;
525 select count(*) from t1;
526 count(*)
527 0
528 drop table t1;
529 create table t1 (
530 a varchar(40) not null,
531 b mediumint not null,
532 t text,
533 c varchar(2) not null,
534 d bigint not null,
535 primary key (a,b,c),
536 key (c,a),
537 unique key (d)
538 ) engine=ndb;
539 insert into t1 (a,b,c,d,t) values ('a',1110,'a',1,@v1);
540 insert into t1 (a,b,c,d,t) values ('b',1110,'a',2,@v2);
541 insert into t1 (a,b,c,d,t) values ('a',1110,'b',3,@v3);
542 insert into t1 (a,b,c,d,t) values ('b',1110,'b',4,@v4);
543 select a,b,c,d,sha1(t) from t1 order by c,a;
544 a b c d sha1(t)
545 a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
546 b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e
547 a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
548 b 1110 b 4 NULL
549 select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='a';
550 a b c d sha1(t)
551 a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
552 select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='b';
553 a b c d sha1(t)
554 a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
555 update t1 set t=@v4 where a='b' and b=1110 and c='a';
556 update t1 set t=@v2 where a='b' and b=1110 and c='b';
557 select a,b,c,d,sha1(t) from t1 order by c,a;
558 a b c d sha1(t)
559 a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
560 b 1110 a 2 NULL
561 a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
562 b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e
563 update t1 set t=@v2 where d=2;
564 update t1 set t=@v4 where d=4;
565 select a,b,c,d,sha1(t) from t1 order by c,a;
566 a b c d sha1(t)
567 a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
568 b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e
569 a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
570 b 1110 b 4 NULL
571 update t1 set t=@v4 where a='b' and c='a';
572 update t1 set t=@v2 where a='b' and c='b';
573 select a,b,c,d,sha1(t) from t1 order by c,a;
574 a b c d sha1(t)
575 a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
576 b 1110 a 2 NULL
577 a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
578 b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e
579 update t1 set t=@v2 where b+d=1112;
580 update t1 set t=@v4 where b+d=1114;
581 select a,b,c,d,sha1(t) from t1 order by c,a;
582 a b c d sha1(t)
583 a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
584 b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e
585 a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
586 b 1110 b 4 NULL
587 delete from t1 where a='a' and b=1110 and c='a';
588 delete from t1 where a='b' and c='a';
589 delete from t1 where d=3;
590 delete from t1 where b+d=1114;
591 select count(*) from t1;
592 count(*)
593 0
594 drop table t1;
595 create table t1(
596 a int,
597 blob_nn blob not null,
598 text_nn text not null,
599 blob_nl blob,
600 text_nl text,
601 primary key(a)
602 ) engine=ndb;
603 insert ignore into t1(a) values (1);
604 Warnings:
605 Warning 1364 Field 'blob_nn' doesn't have a default value
606 Warning 1364 Field 'text_nn' doesn't have a default value
607 insert ignore into t1(a, text_nl) values (2, 'MySQL Cluster NDB');
608 Warnings:
609 Warning 1364 Field 'blob_nn' doesn't have a default value
610 Warning 1364 Field 'text_nn' doesn't have a default value
611 select a, length(blob_nn), length(text_nn), blob_nl, text_nl from t1 order by a;
612 a length(blob_nn) length(text_nn) blob_nl text_nl
613 1 0 0 NULL NULL
614 2 0 0 NULL MySQL Cluster NDB
615 drop table t1;
616 create table `t1` (
617 `f1` int(11) not null default -1,
618 `f11` text,
619 unique key `i1` (`f1`)
620 ) engine=ndbcluster default charset=utf8;
621 Warnings:
622 Warning 1681 Integer display width is deprecated and will be removed in a future release.
623 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.
624 insert into `t1` values (123,null);
625 create table `t2` (
626 `f1` int(11) not null default -1,
627 unique key `i2` (`f1`)
628 ) engine=ndbcluster default charset=utf8;
629 Warnings:
630 Warning 1681 Integer display width is deprecated and will be removed in a future release.
631 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.
632 begin;
633 delete from t2 where f1=5;
634 delete from t1 where f1=123;
635 delete from t2 where f1=5;
636 commit;
637 show warnings;
638 Level Code Message
639 drop table t1;
640 drop table t2;
641 create table t1 (
642 a int primary key,
643 giga longblob)
644 engine=ndb;
645 set @stuff = '1234567890';
646 insert into t1 values (0, repeat(@stuff, 2000));
647 select sha1(repeat(@stuff, 2000));
648 sha1(repeat(@stuff, 2000))
649 c8d94eb4127361ac22cf1c8a8f1178a37fb25e41
650 select sha1(giga) from t1;
651 sha1(giga)
652 c8d94eb4127361ac22cf1c8a8f1178a37fb25e41
653 select (giga = repeat(@stuff, 2000)) from t1 where a=0;
654 (giga = repeat(@stuff, 2000))
655 1
656 drop table t1;
657 Test that automatic lock upgrade on Blobs behaves as expected
658 w.r.t other operations on the table.
659 create table t1 (a int primary key, b longblob, c int, unique(c)) engine=ndb;
660 insert into t1 values (1, repeat('1', 256), 1);
661 insert into t1 values (2, repeat('2', 2256), 2);
662 insert into t1 values (3, repeat('3', 4256), 3);
663 insert into t1 values (4, repeat('4', 8256), 4);
664 insert into t1 values (5, repeat('5', 20000), 5);
665 No Deadlock scenarios
666 Transaction 1 reading PK with no lock. Transaction 2 reading PK with EX lock
667 begin;
668 select a, length(b) from t1 where a=2;
669 a length(b)
670 2 2256
671 select a, length(b) from t1 where a=2 FOR UPDATE;
672 a length(b)
673 2 2256
674 Transaction 2 scanning with EX lock - will not deadlock with transaction 1
675 select a, length(b) from t1 ORDER BY a FOR UPDATE;
676 a length(b)
677 1 256
678 2 2256
679 3 4256
680 4 8256
681 5 20000
682 commit;
683 Transaction1 reading PK with SH lock. Transaction 2 reading PK with no lock
684 begin;
685 select a, length(b) from t1 where a=3 LOCK IN SHARE MODE;
686 a length(b)
687 3 4256
688 select a, length(b) from t1 where a=3;
689 a length(b)
690 3 4256
691 Transaction 2 scanning with SH lock - will not deadlock with transaction 1
692 select a, length(b) from t1 ORDER BY a;
693 a length(b)
694 1 256
695 2 2256
696 3 4256
697 4 8256
698 5 20000
699 commit;
700 Transaction1 reading PK with SH lock. Transaction 2 reading PK with SH lock
701 begin;
702 select a, length(b) from t1 where a=4 LOCK IN SHARE MODE;
703 a length(b)
704 4 8256
705 select a, length(b) from t1 where a=4 LOCK IN SHARE MODE;
706 a length(b)
707 4 8256
708 Transaction 2 scanning with SH lock - will not deadlock with transaction 1
709 select a, length(b) from t1 ORDER BY a LOCK IN SHARE MODE;
710 a length(b)
711 1 256
712 2 2256
713 3 4256
714 4 8256
715 5 20000
716 commit;
717 Deadlock scenarios on PK access :
718 Transaction 1 reading PK with EX lock. Transaction 2 reading PK with no lock
719 begin;
720 select a, length(b) from t1 where a=5 FOR UPDATE;
721 a length(b)
722 5 20000
723 select a, length(b) from t1 where a=5;
724 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
725 commit;
726 Transaction 1 reading PK with EX lock. Transaction 2 reading PK with SH lock
727 begin;
728 select a, length(b) from t1 where a=1 FOR UPDATE;
729 a length(b)
730 1 256
731 select a, length(b) from t1 where a=1 LOCK IN SHARE MODE;
732 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
733 commit;
734 Verify that Nolock Unique index access still deadlocks
735 as unique index accesses always upgrade to SH
736 begin;
737 select a, length(b) from t1 where c=2;
738 a length(b)
739 2 2256
740 select a, length(b) from t1 FOR UPDATE;
741 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
742 commit;
743 Test autocommit committedread round trips
744 delete from t1;
745 insert into t1 values (1, repeat('P', 20000), 1);
746 set autocommit=1;
747 select @start_count:=variable_value from performance_schema.global_status where variable_name="ndb_api_wait_exec_complete_count";
748 Autocommit CommittedRead
749 select a, length(b) from t1 where a=1;
750 select @end_count:=variable_value from performance_schema.global_status where variable_name="ndb_api_wait_exec_complete_count";
751 Expect 3 round trips currently :
752 1. Lock and read inline head
753 2. Read parts
754 3. Rollback
755 select @end_count-@start_count as round_trips;
756 round_trips
757 3
758 set autocommit=0;
759 drop table t1;
760 5.6 tests
761 create table `t1` (`a` int,`b` blob,primary key (`a`))
762 engine=ndbcluster
763 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
764 insert into t1 values (2, 'Hei og hopp! ');
765 update t1 set b=concat(b,b,b,b,b);
766 update t1 set b=concat(b,b,b,b,b);
767 update t1 set b=concat(b,b,b,b,b);
768 explain update `t1` set `a`=1 where `a`!= 1;
769 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
770 1 UPDATE t1 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 const 4 100.00 Using where; Using pushed condition (`test`.`t1`.`a` <> 1); Using temporary
771 Warnings:
772 Note 1003 update `test`.`t1` set `test`.`t1`.`a` = 1 where (`test`.`t1`.`a` <> 1)
773 update `t1` set `a`=1 where `a`!= 1;
774 select * from t1;
775 a b
776 1 Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp! Hei og hopp!
777 drop table t1;
778 create table t1(256 blobs) engine=ndbcluster
779 drop table t1;
780 End of 5.6 tests