Debian Codegraph @

mysql-test/suite/ndb/t/ndb_blob.test

raw
1 --source include/have_ndb.inc
2
3 #
4 # Minimal NDB blobs test.
5 #
6 # On NDB API level there is an extensive test program "testBlobs".
7 # A prerequisite for this handler test is that "testBlobs" succeeds.
8 #
9
10 # -- general test starts --
11
12 # make test harder with autocommit off
13 set autocommit=0;
14
15 create table t1 (
16 a int not null primary key,
17 b text not null,
18 c int not null,
19 d longblob,
20 key (c)
21 ) engine=ndbcluster
22 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
23
24 # -- values --
25
26 # x0 size 256 (current inline size)
27 set @x0 = '01234567012345670123456701234567';
28 set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
29
30 # b1 length 2000+256 (blob part aligned)
31 set @b1 = 'b1';
32 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
33 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
34 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
35 set @b1 = concat(@b1,@x0);
36 # d1 length 3000
37 set @d1 = 'dd1';
38 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
39 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
40 set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
41
42 # b2 length 20000
43 set @b2 = 'b2';
44 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
45 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
46 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
47 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
48 # d2 length 30000
49 set @d2 = 'dd2';
50 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
51 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
52 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
53 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
54
55 select length(@x0),length(@b1),length(@d1) from dual;
56 select length(@x0),length(@b2),length(@d2) from dual;
57
58 # -- pk ops --
59
60 insert into t1 values(1,@b1,111,@d1);
61 insert into t1 values(2,@b2,222,@d2);
62 commit;
63 --replace_column 10 # 11 #
64 explain select * from t1 where a = 1;
65
66 # pk read
67 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
68 from t1 where a=1;
69 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
70 from t1 where a=2;
71
72 # pk update
73 update t1 set b=@b2,d=@d2 where a=1;
74 update t1 set b=@b1,d=@d1 where a=2;
75 commit;
76 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
77 from t1 where a=1;
78 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
79 from t1 where a=2;
80
81 # pk update
82 update t1 set b=concat(b,b),d=concat(d,d) where a=1;
83 update t1 set b=concat(b,b),d=concat(d,d) where a=2;
84 commit;
85 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
86 from t1 where a=1;
87 select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
88 from t1 where a=2;
89
90 # pk update to null
91 update t1 set d=null where a=1;
92 commit;
93 select a from t1 where d is null;
94
95 # bug#24028 - does not occur on MySQL level
96 # bug#17986 - not seen by us anymore but could show as warning here
97 delete from t1 where a=45567;
98 commit;
99
100 # pk delete
101 delete from t1 where a=1;
102 delete from t1 where a=2;
103 commit;
104 select count(*) from t1;
105
106 # -- replace ( bug-6018 ) --
107
108 # insert
109 replace t1 set a=1,b=@b1,c=111,d=@d1;
110 replace t1 set a=2,b=@b2,c=222,d=@d2;
111 commit;
112 --replace_column 10 # 11 #
113 explain select * from t1 where a = 1;
114
115 # pk read
116 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
117 from t1 where a=1;
118 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
119 from t1 where a=2;
120
121 # update
122 replace t1 set a=1,b=@b2,c=111,d=@d2;
123 replace t1 set a=2,b=@b1,c=222,d=@d1;
124 commit;
125 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
126 from t1 where a=1;
127 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
128 from t1 where a=2;
129
130 # update
131 replace t1 set a=1,b=concat(@b2,@b2),c=111,d=concat(@d2,@d2);
132 replace t1 set a=2,b=concat(@b1,@b1),c=222,d=concat(@d1,@d1);
133 commit;
134 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
135 from t1 where a=1;
136 select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
137 from t1 where a=2;
138
139 # update to null
140 replace t1 set a=1,b='xyz',c=111,d=null;
141 commit;
142 select a,b from t1 where d is null;
143
144 # pk delete
145 delete from t1 where a=1;
146 delete from t1 where a=2;
147 commit;
148 select count(*) from t1;
149
150 # -- hash index ops --
151
152 insert into t1 values(1,@b1,111,@d1);
153 insert into t1 values(2,@b2,222,@d2);
154 commit;
155 --replace_column 10 # 11 #
156 explain select * from t1 where c = 111;
157
158 # hash key read
159 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
160 from t1 where c=111;
161 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
162 from t1 where c=222;
163
164 # hash key update
165 update t1 set b=@b2,d=@d2 where c=111;
166 update t1 set b=@b1,d=@d1 where c=222;
167 commit;
168 select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
169 from t1 where c=111;
170 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
171 from t1 where c=222;
172
173 # hash key update to null
174 update t1 set d=null where c=111;
175 commit;
176 select a from t1 where d is null;
177
178 # hash key delete
179 delete from t1 where c=111;
180 delete from t1 where c=222;
181 commit;
182 select count(*) from t1;
183
184 # -- table scan ops, short values --
185
186 insert into t1 values(1,'b1',111,'dd1');
187 insert into t1 values(2,'b2',222,'dd2');
188 insert into t1 values(3,'b3',333,'dd3');
189 insert into t1 values(4,'b4',444,'dd4');
190 insert into t1 values(5,'b5',555,'dd5');
191 insert into t1 values(6,'b6',666,'dd6');
192 insert into t1 values(7,'b7',777,'dd7');
193 insert into t1 values(8,'b8',888,'dd8');
194 insert into t1 values(9,'b9',999,'dd9');
195 commit;
196 --replace_column 10 # 11 #
197 explain select * from t1;
198
199 # table scan read
200 select * from t1 order by a;
201
202 # table scan update
203 update t1 set b=concat(a,'x',b),d=concat(a,'x',d);
204 commit;
205 select * from t1 order by a;
206
207 # table scan delete
208 delete from t1;
209 commit;
210 select count(*) from t1;
211
212 # -- table scan ops, long values --
213
214 insert into t1 values(1,@b1,111,@d1);
215 insert into t1 values(2,@b2,222,@d2);
216 commit;
217 --replace_column 10 # 11 #
218 explain select * from t1;
219
220 # table scan read
221 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
222 from t1 order by a;
223
224 # table scan update
225 update t1 set b=concat(b,b),d=concat(d,d);
226 commit;
227 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
228 from t1 order by a;
229
230 # table scan delete
231 delete from t1;
232 commit;
233 select count(*) from t1;
234
235 # -- range scan ops, short values --
236
237 insert into t1 values(1,'b1',111,'dd1');
238 insert into t1 values(2,'b2',222,'dd2');
239 insert into t1 values(3,'b3',333,'dd3');
240 insert into t1 values(4,'b4',444,'dd4');
241 insert into t1 values(5,'b5',555,'dd5');
242 insert into t1 values(6,'b6',666,'dd6');
243 insert into t1 values(7,'b7',777,'dd7');
244 insert into t1 values(8,'b8',888,'dd8');
245 insert into t1 values(9,'b9',999,'dd9');
246 commit;
247 --replace_column 10 # 11 #
248 explain select * from t1 where c >= 100 order by a;
249
250 # range scan read
251 select * from t1 where c >= 100 order by a;
252
253 # range scan update
254 update t1 set b=concat(a,'x',b),d=concat(a,'x',d)
255 where c >= 100;
256 commit;
257 select * from t1 where c >= 100 order by a;
258
259 # range scan delete
260 delete from t1 where c >= 100;
261 commit;
262 select count(*) from t1;
263
264 # -- range scan ops, long values --
265
266 insert into t1 values(1,@b1,111,@d1);
267 insert into t1 values(2,@b2,222,@d2);
268 commit;
269 --replace_column 10 # 11 #
270 explain select * from t1 where c >= 100 order by a;
271
272 # range scan read
273 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
274 from t1 where c >= 100 order by a;
275
276 # range scan update
277 update t1 set b=concat(b,b),d=concat(d,d);
278 commit;
279 select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
280 from t1 where c >= 100 order by a;
281
282 # range scan delete
283 delete from t1 where c >= 100;
284 commit;
285 select count(*) from t1;
286
287 # -- rollback --
288
289 insert into t1 values(1,@b1,111,@d1);
290 insert into t1 values(2,@b2,222,@d2);
291 # 626
292 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
293 from t1 where a = 0;
294 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
295 from t1 where a = 1;
296 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
297 from t1 where a = 2;
298 select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
299 from t1 order by a;
300 rollback;
301 select count(*) from t1;
302
303 # -- alter table and multi db --
304
305 insert into t1 values(1,'b1',111,'dd1');
306 insert into t1 values(2,'b2',222,'dd2');
307 insert into t1 values(3,'b3',333,'dd3');
308 insert into t1 values(4,'b4',444,'dd4');
309 insert into t1 values(5,'b5',555,'dd5');
310 insert into t1 values(6,'b6',666,'dd6');
311 insert into t1 values(7,'b7',777,'dd7');
312 insert into t1 values(8,'b8',888,'dd8');
313 insert into t1 values(9,'b9',999,'dd9');
314 commit;
315
316 select * from t1 order by a;
317 --disable_warnings
318 alter table t1 add x int;
319 --enable_warnings
320 select * from t1 order by a;
321 alter table t1 drop x;
322 select * from t1 order by a;
323
324 create database test2;
325 use test2;
326
327 CREATE TABLE t2 (
328 a bigint unsigned NOT NULL PRIMARY KEY,
329 b int unsigned not null,
330 c int unsigned
331 ) engine=ndbcluster;
332
333 insert into t2 values (1,1,1),(2,2,2);
334 select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a;
335
336 drop table t2;
337 use test;
338
339 select * from t1 order by a;
340 --disable_warnings
341 alter table t1 add x int;
342 --enable_warnings
343 select * from t1 order by a;
344 alter table t1 drop x;
345 select * from t1 order by a;
346
347 # -- end general test --
348
349 drop table t1;
350 drop database test2;
351
352 # -- bug-5252 tinytext crashes + no-commit result + replace --
353
354 set autocommit=0;
355 create table t1 (
356 a int not null primary key,
357 b tinytext
358 ) engine=ndbcluster;
359
360 insert into t1 values(1, 'x');
361 update t1 set b = 'y';
362 select * from t1;
363 delete from t1;
364 select * from t1;
365 commit;
366 replace t1 set a=2, b='y';
367 select * from t1;
368 delete from t1;
369 select * from t1;
370 drop table t1;
371
372 # -- bug-5013 insert empty string to text --
373
374 set autocommit=0;
375 create table t1 (
376 a int not null primary key,
377 b text not null
378 ) engine=ndbcluster;
379
380 insert into t1 values(1, '');
381 select * from t1;
382 commit;
383
384 # -- bug #35593 Memory leak in failed NDB execute() with blobs.
385 connect (con1,localhost,root,,test);
386 connect (con2,localhost,root,,test);
387
388 # Force a deadlock.
389
390 connection con2;
391 begin;
392 insert into t1 values (3, repeat("w", 8000));
393
394 connection con1;
395 --error 1205
396 insert into t1 values (2, repeat("x", 10000)),
397 (3, repeat("y", 5000)),
398 (4, repeat("z", 15000));
399 rollback;
400
401 connection con2;
402 commit;
403
404 drop table t1;
405
406 # -- bug #5349 --
407 set autocommit=1;
408 use test;
409 CREATE TABLE t1 (
410 a int,
411 b text,
412 PRIMARY KEY (a)
413 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
414
415 INSERT INTO t1 VALUES
416 (1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
417 INSERT INTO t1 VALUES
418 (2,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
419
420 select * from t1 order by a;
421 alter table t1 engine=ndb;
422 select * from t1 order by a;
423
424 # -- bug #5872 --
425 set autocommit=1;
426 alter table t1 engine=myisam;
427 select * from t1 order by a;
428 drop table t1;
429
430 # -- bug #7340 --
431 create table t1 (
432 id int(11) unsigned primary key NOT NULL auto_increment,
433 msg text NOT NULL
434 ) engine=ndbcluster default charset=utf8;
435 insert into t1 (msg) values(
436 'Tries to validate (8 byte length + inline bytes) as UTF8 :(
437 Fast fix: removed validation for Text. It is not yet indexable
438 so bad data will not crash kernel.');
439 select * from t1;
440 drop table t1;
441
442 # -- bug #19201
443 create table t1 (
444 a int primary key not null auto_increment,
445 b text
446 ) engine=ndbcluster;
447 --disable_query_log
448 set autocommit=1;
449 # more rows than batch size (64)
450 # for this bug no blob parts would be necessary
451 let $1 = 500;
452 while ($1)
453 {
454 insert into t1 (b) values (repeat('x',4000));
455 dec $1;
456 }
457 --enable_query_log
458 select count(*) from t1;
459 truncate t1;
460 select count(*) from t1;
461 drop table t1;
462
463 # -- bug#19956 - var* key, complex key
464
465 create table t1 (
466 a varchar(40) not null,
467 b mediumint not null,
468 t text,
469 c varchar(2) not null,
470 d bigint not null,
471 primary key (a,b,c),
472 key (c,a),
473 unique key (d)
474 ) engine=ndb;
475
476 --disable_query_log
477 set @s1 = 'rggurloniukyehuxdbfkkyzlceixzrehqhvxvxbpwizzvjzpucqmzrhzxzfau';
478 set @s2 = 'ykyymbzqgqlcjhlhmyqelfoaaohvtbekvifukdtnvcrrjveevfakxarxexomz';
479 set @s3 = 'dbnfqyzgtqxalcrwtfsqabknvtfcbpoonxsjiqvmhnfikxxhcgoexlkoezvah';
480 set @v1 = repeat(@s1,123);
481 set @v2 = repeat(@s2,234);
482 set @v3 = repeat(@s3,345);
483 set @v4 = NULL;
484 --enable_query_log
485
486 insert into t1 (a,b,c,d,t) values ('a',1110,'a',1,@v1);
487 insert into t1 (a,b,c,d,t) values ('b',1110,'a',2,@v2);
488 insert into t1 (a,b,c,d,t) values ('a',1110,'b',3,@v3);
489 insert into t1 (a,b,c,d,t) values ('b',1110,'b',4,@v4);
490 select a,b,c,d,sha1(t) from t1 order by c,a;
491
492 select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='a';
493 select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='b';
494
495 update t1 set t=@v4 where a='b' and b=1110 and c='a';
496 update t1 set t=@v2 where a='b' and b=1110 and c='b';
497 select a,b,c,d,sha1(t) from t1 order by c,a;
498
499 update t1 set t=@v2 where d=2;
500 update t1 set t=@v4 where d=4;
501 select a,b,c,d,sha1(t) from t1 order by c,a;
502
503 update t1 set t=@v4 where a='b' and c='a';
504 update t1 set t=@v2 where a='b' and c='b';
505 select a,b,c,d,sha1(t) from t1 order by c,a;
506
507 update t1 set t=@v2 where b+d=1112;
508 update t1 set t=@v4 where b+d=1114;
509 select a,b,c,d,sha1(t) from t1 order by c,a;
510
511 delete from t1 where a='a' and b=1110 and c='a';
512 delete from t1 where a='b' and c='a';
513 delete from t1 where d=3;
514 delete from t1 where b+d=1114;
515 select count(*) from t1;
516
517 drop table t1;
518
519 # End of 4.1 tests
520
521
522 # bug # 30674 :
523 # NOT NULL Blobs should default to zero-length. Not NULL TEXT
524 # should default to zero-chars
525 create table t1(
526 a int,
527 blob_nn blob not null,
528 text_nn text not null,
529 blob_nl blob,
530 text_nl text,
531 primary key(a)
532 ) engine=ndb;
533
534 # NOTE! using IGNORE to force the two inserts despite of warnings
535 insert ignore into t1(a) values (1);
536 insert ignore into t1(a, text_nl) values (2, 'MySQL Cluster NDB');
537
538 select a, length(blob_nn), length(text_nn), blob_nl, text_nl from t1 order by a;
539
540 drop table t1;
541
542 # bug # 36756
543 # Behaviour of delete of non existing row should not be affected
544 # by presence of Blob operations in the same transaction
545 # Specifically, transaction should not be aborted.
546 create table `t1` (
547 `f1` int(11) not null default -1,
548 `f11` text,
549 unique key `i1` (`f1`)
550 ) engine=ndbcluster default charset=utf8;
551
552 insert into `t1` values (123,null);
553
554 create table `t2` (
555 `f1` int(11) not null default -1,
556 unique key `i2` (`f1`)
557 ) engine=ndbcluster default charset=utf8;
558
559 begin;
560 delete from t2 where f1=5; # No such row, no problem
561 delete from t1 where f1=123; # Blob operation
562 delete from t2 where f1=5; # No such row, no problem
563 commit;
564 show warnings;
565
566 drop table t1;
567 drop table t2;
568
569 # bug # 31284
570 # Long Blob (8000 byte parts) with Blob v2 (+2 bytes length)
571 # exposed buffer overrun for old Api setValue() call used for
572 # writing parts.
573 # Check that data written into long Blob can be retrieved
574 # correctly
575 create table t1 (
576 a int primary key,
577 giga longblob)
578 engine=ndb;
579
580 # length 10
581 set @stuff = '1234567890';
582
583 # 20,000 bytes = 3 parts
584 insert into t1 values (0, repeat(@stuff, 2000));
585
586 # Check that we get the same data back that we put in
587 select sha1(repeat(@stuff, 2000));
588 select sha1(giga) from t1;
589 select (giga = repeat(@stuff, 2000)) from t1 where a=0;
590
591 drop table t1;
592
593 --echo Test that automatic lock upgrade on Blobs behaves as expected
594 --echo w.r.t other operations on the table.
595 connection con1;
596
597 create table t1 (a int primary key, b longblob, c int, unique(c)) engine=ndb;
598
599 insert into t1 values (1, repeat('1', 256), 1);
600 insert into t1 values (2, repeat('2', 2256), 2);
601 insert into t1 values (3, repeat('3', 4256), 3);
602 insert into t1 values (4, repeat('4', 8256), 4);
603 insert into t1 values (5, repeat('5', 20000), 5);
604
605 --echo No Deadlock scenarios
606 --echo Transaction 1 reading PK with no lock. Transaction 2 reading PK with EX lock
607 begin;
608 select a, length(b) from t1 where a=2;
609
610 connection con2;
611 select a, length(b) from t1 where a=2 FOR UPDATE;
612
613 --echo Transaction 2 scanning with EX lock - will not deadlock with transaction 1
614 select a, length(b) from t1 ORDER BY a FOR UPDATE;
615
616 connection con1;
617 commit;
618
619 --echo Transaction1 reading PK with SH lock. Transaction 2 reading PK with no lock
620 begin;
621 select a, length(b) from t1 where a=3 LOCK IN SHARE MODE;
622
623 connection con2;
624 select a, length(b) from t1 where a=3;
625
626 --echo Transaction 2 scanning with SH lock - will not deadlock with transaction 1
627 select a, length(b) from t1 ORDER BY a;
628
629 connection con1;
630 commit;
631
632 --echo Transaction1 reading PK with SH lock. Transaction 2 reading PK with SH lock
633 begin;
634 select a, length(b) from t1 where a=4 LOCK IN SHARE MODE;
635
636 connection con2;
637 select a, length(b) from t1 where a=4 LOCK IN SHARE MODE;
638
639 --echo Transaction 2 scanning with SH lock - will not deadlock with transaction 1
640 select a, length(b) from t1 ORDER BY a LOCK IN SHARE MODE;
641
642 connection con1;
643 commit;
644
645 --echo Deadlock scenarios on PK access :
646 connection con1;
647 --echo Transaction 1 reading PK with EX lock. Transaction 2 reading PK with no lock
648 begin;
649 select a, length(b) from t1 where a=5 FOR UPDATE;
650
651 connection con2;
652 --error 1205
653 select a, length(b) from t1 where a=5;
654
655 connection con1;
656 commit;
657
658 connection con1;
659 --echo Transaction 1 reading PK with EX lock. Transaction 2 reading PK with SH lock
660 begin;
661 select a, length(b) from t1 where a=1 FOR UPDATE;
662
663 connection con2;
664 --error 1205
665 select a, length(b) from t1 where a=1 LOCK IN SHARE MODE;
666
667 connection con1;
668 commit;
669 --echo Verify that Nolock Unique index access still deadlocks
670 --echo as unique index accesses always upgrade to SH
671
672 begin;
673 select a, length(b) from t1 where c=2;
674
675 connection con2;
676 --error 1205
677 select a, length(b) from t1 FOR UPDATE;
678
679 connection con1;
680 commit;
681
682 connection con1;
683 --echo Test autocommit committedread round trips
684 delete from t1;
685 insert into t1 values (1, repeat('P', 20000), 1);
686
687 set autocommit=1;
688
689 --disable_result_log
690 select @start_count:=variable_value from performance_schema.global_status where variable_name="ndb_api_wait_exec_complete_count";
691
692 --echo Autocommit CommittedRead
693 select a, length(b) from t1 where a=1;
694
695 select @end_count:=variable_value from performance_schema.global_status where variable_name="ndb_api_wait_exec_complete_count";
696
697 --enable_result_log
698
699 --echo Expect 3 round trips currently :
700 --echo 1. Lock and read inline head
701 --echo 2. Read parts
702 --echo 3. Rollback
703 select @end_count-@start_count as round_trips;
704
705 set autocommit=0;
706
707 drop table t1;
708
709 --echo 5.6 tests
710
711 # Bug#16749788
712 # ASSERTION: !USES_BLOB_VALUE(TABLE->READ_SET) IN MULTI_RANGE_START_RETRIEVALS
713 # table->read_set was calculated to late to detect Blob read.
714
715 create table `t1` (`a` int,`b` blob,primary key (`a`))
716 engine=ndbcluster
717 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
718 insert into t1 values (2, 'Hei og hopp! ');
719
720 update t1 set b=concat(b,b,b,b,b);
721 update t1 set b=concat(b,b,b,b,b);
722 update t1 set b=concat(b,b,b,b,b);
723
724 #Expect use of (default) MRR implementation for update
725
726 explain update `t1` set `a`=1 where `a`!= 1;
727 update `t1` set `a`=1 where `a`!= 1;
728 select * from t1;
729
730 drop table t1;
731
732 # Bug#37201818
733
734 let $n=256;
735 let $i=1;
736 let $columns=pk INT PRIMARY KEY;
737 while ($i <= $n) {
738 let $columns=$columns, blob$i BLOB COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=100";
739 inc $i;
740 }
741 echo create table t1(256 blobs) engine=ndbcluster;
742 disable_query_log;
743 eval create table t1($columns) engine=ndbcluster;
744 enable_query_log;
745 drop table t1;
746
747 --echo End of 5.6 tests