1drop table if exists t1; 2drop database if exists test2; 3set autocommit=0; 4create table t1 ( 5a int not null primary key, 6b text not null, 7c int not null, 8d longblob, 9key (c) 10) engine=ndbcluster; 11set @x0 = '01234567012345670123456701234567'; 12set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0); 13set @b1 = 'b1'; 14set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); 15set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); 16set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); 17set @b1 = concat(@b1,@x0); 18set @d1 = 'dd1'; 19set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); 20set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); 21set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); 22set @b2 = 'b2'; 23set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); 24set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); 25set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); 26set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); 27set @d2 = 'dd2'; 28set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); 29set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); 30set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); 31set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); 32select length(@x0),length(@b1),length(@d1) from dual; 33length(@x0) length(@b1) length(@d1) 34256 2256 3000 35select length(@x0),length(@b2),length(@d2) from dual; 36length(@x0) length(@b2) length(@d2) 37256 20000 30000 38insert into t1 values(1,@b1,111,@d1); 39insert into t1 values(2,@b2,222,@d2); 40commit; 41explain select * from t1 where a = 1; 42id select_type table partitions type possible_keys key key_len ref rows filtered Extra 431 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const # # NULL 44Warnings: 45Note 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) 46select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 47from t1 where a=1; 48a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 491 2256 b1 3000 dd1 50select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) 51from t1 where a=2; 52a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) 532 20000 b2 30000 dd2 54update t1 set b=@b2,d=@d2 where a=1; 55update t1 set b=@b1,d=@d1 where a=2; 56commit; 57select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) 58from t1 where a=1; 59a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) 601 20000 b2 30000 dd2 61select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 62from t1 where a=2; 63a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 642 2256 b1 3000 dd1 65update t1 set b=concat(b,b),d=concat(d,d) where a=1; 66update t1 set b=concat(b,b),d=concat(d,d) where a=2; 67commit; 68select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) 69from t1 where a=1; 70a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) 711 40000 b2 60000 dd2 72select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3) 73from t1 where a=2; 74a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3) 752 4512 b1 6000 dd1 76update t1 set d=null where a=1; 77commit; 78select a from t1 where d is null; 79a 801 81delete from t1 where a=45567; 82commit; 83delete from t1 where a=1; 84delete from t1 where a=2; 85commit; 86select count(*) from t1; 87count(*) 880 89replace t1 set a=1,b=@b1,c=111,d=@d1; 90replace t1 set a=2,b=@b2,c=222,d=@d2; 91commit; 92explain select * from t1 where a = 1; 93id select_type table partitions type possible_keys key key_len ref rows filtered Extra 941 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const # # NULL 95Warnings: 96Note 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) 97select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 98from t1 where a=1; 99a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 1001 2256 b1 3000 dd1 101select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) 102from t1 where a=2; 103a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) 1042 20000 b2 30000 dd2 105replace t1 set a=1,b=@b2,c=111,d=@d2; 106replace t1 set a=2,b=@b1,c=222,d=@d1; 107commit; 108select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) 109from t1 where a=1; 110a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) 1111 20000 b2 30000 dd2 112select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 113from t1 where a=2; 114a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 1152 2256 b1 3000 dd1 116replace t1 set a=1,b=concat(@b2,@b2),c=111,d=concat(@d2,@d2); 117replace t1 set a=2,b=concat(@b1,@b1),c=222,d=concat(@d1,@d1); 118commit; 119select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) 120from t1 where a=1; 121a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) 1221 40000 b2 60000 dd2 123select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3) 124from t1 where a=2; 125a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3) 1262 4512 b1 6000 dd1 127replace t1 set a=1,b='xyz',c=111,d=null; 128commit; 129select a,b from t1 where d is null; 130a b 1311 xyz 132delete from t1 where a=1; 133delete from t1 where a=2; 134commit; 135select count(*) from t1; 136count(*) 1370 138insert into t1 values(1,@b1,111,@d1); 139insert into t1 values(2,@b2,222,@d2); 140commit; 141explain select * from t1 where c = 111; 142id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1431 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref c c 4 const # # NULL 144Warnings: 145Note 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) 146select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 147from t1 where c=111; 148a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 1491 2256 b1 3000 dd1 150select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) 151from t1 where c=222; 152a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) 1532 20000 b2 30000 dd2 154update t1 set b=@b2,d=@d2 where c=111; 155update t1 set b=@b1,d=@d1 where c=222; 156commit; 157select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) 158from t1 where c=111; 159a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) 1601 20000 b2 30000 dd2 161select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 162from t1 where c=222; 163a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 1642 2256 b1 3000 dd1 165update t1 set d=null where c=111; 166commit; 167select a from t1 where d is null; 168a 1691 170delete from t1 where c=111; 171delete from t1 where c=222; 172commit; 173select count(*) from t1; 174count(*) 1750 176insert into t1 values(1,'b1',111,'dd1'); 177insert into t1 values(2,'b2',222,'dd2'); 178insert into t1 values(3,'b3',333,'dd3'); 179insert into t1 values(4,'b4',444,'dd4'); 180insert into t1 values(5,'b5',555,'dd5'); 181insert into t1 values(6,'b6',666,'dd6'); 182insert into t1 values(7,'b7',777,'dd7'); 183insert into t1 values(8,'b8',888,'dd8'); 184insert into t1 values(9,'b9',999,'dd9'); 185commit; 186explain select * from t1; 187id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1881 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # NULL 189Warnings: 190Note 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` 191select * from t1 order by a; 192a b c d 1931 b1 111 dd1 1942 b2 222 dd2 1953 b3 333 dd3 1964 b4 444 dd4 1975 b5 555 dd5 1986 b6 666 dd6 1997 b7 777 dd7 2008 b8 888 dd8 2019 b9 999 dd9 202update t1 set b=concat(a,'x',b),d=concat(a,'x',d); 203commit; 204select * from t1 order by a; 205a b c d 2061 1xb1 111 1xdd1 2072 2xb2 222 2xdd2 2083 3xb3 333 3xdd3 2094 4xb4 444 4xdd4 2105 5xb5 555 5xdd5 2116 6xb6 666 6xdd6 2127 7xb7 777 7xdd7 2138 8xb8 888 8xdd8 2149 9xb9 999 9xdd9 215delete from t1; 216commit; 217select count(*) from t1; 218count(*) 2190 220insert into t1 values(1,@b1,111,@d1); 221insert into t1 values(2,@b2,222,@d2); 222commit; 223explain select * from t1; 224id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2251 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL # # NULL 226Warnings: 227Note 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` 228select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 229from t1 order by a; 230a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 2311 2256 b1 3000 dd1 2322 20000 b2 30000 dd2 233update t1 set b=concat(b,b),d=concat(d,d); 234commit; 235select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) 236from t1 order by a; 237a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) 2381 4512 6000 2392 40000 b2 60000 dd2 240delete from t1; 241commit; 242select count(*) from t1; 243count(*) 2440 245insert into t1 values(1,'b1',111,'dd1'); 246insert into t1 values(2,'b2',222,'dd2'); 247insert into t1 values(3,'b3',333,'dd3'); 248insert into t1 values(4,'b4',444,'dd4'); 249insert into t1 values(5,'b5',555,'dd5'); 250insert into t1 values(6,'b6',666,'dd6'); 251insert into t1 values(7,'b7',777,'dd7'); 252insert into t1 values(8,'b8',888,'dd8'); 253insert into t1 values(9,'b9',999,'dd9'); 254commit; 255explain select * from t1 where c >= 100 order by a; 256id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2571 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range c c 4 NULL # # Using where with pushed condition (`test`.`t1`.`c` >= 100); Using filesort 258Warnings: 259Note 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` 260select * from t1 where c >= 100 order by a; 261a b c d 2621 b1 111 dd1 2632 b2 222 dd2 2643 b3 333 dd3 2654 b4 444 dd4 2665 b5 555 dd5 2676 b6 666 dd6 2687 b7 777 dd7 2698 b8 888 dd8 2709 b9 999 dd9 271update t1 set b=concat(a,'x',b),d=concat(a,'x',d) 272where c >= 100; 273commit; 274select * from t1 where c >= 100 order by a; 275a b c d 2761 1xb1 111 1xdd1 2772 2xb2 222 2xdd2 2783 3xb3 333 3xdd3 2794 4xb4 444 4xdd4 2805 5xb5 555 5xdd5 2816 6xb6 666 6xdd6 2827 7xb7 777 7xdd7 2838 8xb8 888 8xdd8 2849 9xb9 999 9xdd9 285delete from t1 where c >= 100; 286commit; 287select count(*) from t1; 288count(*) 2890 290insert into t1 values(1,@b1,111,@d1); 291insert into t1 values(2,@b2,222,@d2); 292commit; 293explain select * from t1 where c >= 100 order by a; 294id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2951 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c PRIMARY 4 NULL # # Using where with pushed condition (`test`.`t1`.`c` >= 100) 296Warnings: 297Note 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` 298select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 299from t1 where c >= 100 order by a; 300a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 3011 2256 b1 3000 dd1 3022 20000 b2 30000 dd2 303update t1 set b=concat(b,b),d=concat(d,d); 304commit; 305select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) 306from t1 where c >= 100 order by a; 307a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) 3081 4512 6000 3092 40000 b2 60000 dd2 310delete from t1 where c >= 100; 311commit; 312select count(*) from t1; 313count(*) 3140 315insert into t1 values(1,@b1,111,@d1); 316insert into t1 values(2,@b2,222,@d2); 317select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 318from t1 where a = 0; 319a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 320select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 321from t1 where a = 1; 322a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 3231 2256 b1 3000 dd1 324select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 325from t1 where a = 2; 326a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 3272 20000 b2 30000 dd2 328select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) 329from t1 order by a; 330a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) 3311 2256 b1 3000 dd1 3322 20000 b2 30000 dd2 333rollback; 334select count(*) from t1; 335count(*) 3360 337insert into t1 values(1,'b1',111,'dd1'); 338insert into t1 values(2,'b2',222,'dd2'); 339insert into t1 values(3,'b3',333,'dd3'); 340insert into t1 values(4,'b4',444,'dd4'); 341insert into t1 values(5,'b5',555,'dd5'); 342insert into t1 values(6,'b6',666,'dd6'); 343insert into t1 values(7,'b7',777,'dd7'); 344insert into t1 values(8,'b8',888,'dd8'); 345insert into t1 values(9,'b9',999,'dd9'); 346commit; 347select * from t1 order by a; 348a b c d 3491 b1 111 dd1 3502 b2 222 dd2 3513 b3 333 dd3 3524 b4 444 dd4 3535 b5 555 dd5 3546 b6 666 dd6 3557 b7 777 dd7 3568 b8 888 dd8 3579 b9 999 dd9 358alter table t1 add x int; 359select * from t1 order by a; 360a b c d x 3611 b1 111 dd1 NULL 3622 b2 222 dd2 NULL 3633 b3 333 dd3 NULL 3644 b4 444 dd4 NULL 3655 b5 555 dd5 NULL 3666 b6 666 dd6 NULL 3677 b7 777 dd7 NULL 3688 b8 888 dd8 NULL 3699 b9 999 dd9 NULL 370alter table t1 drop x; 371select * from t1 order by a; 372a b c d 3731 b1 111 dd1 3742 b2 222 dd2 3753 b3 333 dd3 3764 b4 444 dd4 3775 b5 555 dd5 3786 b6 666 dd6 3797 b7 777 dd7 3808 b8 888 dd8 3819 b9 999 dd9 382create database test2; 383use test2; 384CREATE TABLE t2 ( 385a bigint unsigned NOT NULL PRIMARY KEY, 386b int unsigned not null, 387c int unsigned 388) engine=ndbcluster; 389insert into t2 values (1,1,1),(2,2,2); 390select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a; 391a b c d a b c 3921 b1 111 dd1 1 1 1 3932 b2 222 dd2 2 2 2 394drop table t2; 395use test; 396select * from t1 order by a; 397a b c d 3981 b1 111 dd1 3992 b2 222 dd2 4003 b3 333 dd3 4014 b4 444 dd4 4025 b5 555 dd5 4036 b6 666 dd6 4047 b7 777 dd7 4058 b8 888 dd8 4069 b9 999 dd9 407alter table t1 add x int; 408select * from t1 order by a; 409a b c d x 4101 b1 111 dd1 NULL 4112 b2 222 dd2 NULL 4123 b3 333 dd3 NULL 4134 b4 444 dd4 NULL 4145 b5 555 dd5 NULL 4156 b6 666 dd6 NULL 4167 b7 777 dd7 NULL 4178 b8 888 dd8 NULL 4189 b9 999 dd9 NULL 419alter table t1 drop x; 420select * from t1 order by a; 421a b c d 4221 b1 111 dd1 4232 b2 222 dd2 4243 b3 333 dd3 4254 b4 444 dd4 4265 b5 555 dd5 4276 b6 666 dd6 4287 b7 777 dd7 4298 b8 888 dd8 4309 b9 999 dd9 431drop table t1; 432drop database test2; 433set autocommit=0; 434create table t1 ( 435a int not null primary key, 436b tinytext 437) engine=ndbcluster; 438insert into t1 values(1, 'x'); 439update t1 set b = 'y'; 440select * from t1; 441a b 4421 y 443delete from t1; 444select * from t1; 445a b 446commit; 447replace t1 set a=2, b='y'; 448select * from t1; 449a b 4502 y 451delete from t1; 452select * from t1; 453a b 454drop table t1; 455set autocommit=0; 456create table t1 ( 457a int not null primary key, 458b text not null 459) engine=ndbcluster; 460insert into t1 values(1, ''); 461select * from t1; 462a b 4631 464commit; 465begin; 466insert into t1 values (3, repeat("w", 8000)); 467insert into t1 values (2, repeat("x", 10000)), 468(3, repeat("y", 5000)), 469(4, repeat("z", 15000)); 470ERROR HY000: Lock wait timeout exceeded; try restarting transaction 471rollback; 472commit; 473drop table t1; 474set autocommit=1; 475use test; 476CREATE TABLE t1 ( 477a int, 478b text, 479PRIMARY KEY (a) 480) ENGINE=MyISAM DEFAULT CHARSET=latin1; 481INSERT INTO t1 VALUES 482(1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); 483INSERT INTO t1 VALUES 484(2,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'); 485select * from t1 order by a; 486a b 4871 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 4882 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 489alter table t1 engine=ndb; 490select * from t1 order by a; 491a b 4921 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 4932 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 494set autocommit=1; 495alter table t1 engine=myisam; 496select * from t1 order by a; 497a b 4981 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 4992 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 500drop table t1; 501create table t1 ( 502id int(11) unsigned primary key NOT NULL auto_increment, 503msg text NOT NULL 504) engine=ndbcluster default charset=utf8; 505insert into t1 (msg) values( 506'Tries to validate (8 byte length + inline bytes) as UTF8 :( 507Fast fix: removed validation for Text. It is not yet indexable 508so bad data will not crash kernel.'); 509select * from t1; 510id msg 5111 Tries to validate (8 byte length + inline bytes) as UTF8 :( 512Fast fix: removed validation for Text. It is not yet indexable 513so bad data will not crash kernel. 514drop table t1; 515create table t1 ( 516a int primary key not null auto_increment, 517b text 518) engine=ndbcluster; 519select count(*) from t1; 520count(*) 521500 522truncate t1; 523select count(*) from t1; 524count(*) 5250 526drop table t1; 527create table t1 ( 528a varchar(40) not null, 529b mediumint not null, 530t text, 531c varchar(2) not null, 532d bigint not null, 533primary key (a,b,c), 534key (c,a), 535unique key (d) 536) engine=ndb; 537insert into t1 (a,b,c,d,t) values ('a',1110,'a',1,@v1); 538insert into t1 (a,b,c,d,t) values ('b',1110,'a',2,@v2); 539insert into t1 (a,b,c,d,t) values ('a',1110,'b',3,@v3); 540insert into t1 (a,b,c,d,t) values ('b',1110,'b',4,@v4); 541select a,b,c,d,sha1(t) from t1 order by c,a; 542a b c d sha1(t) 543a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 544b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e 545a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 546b 1110 b 4 NULL 547select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='a'; 548a b c d sha1(t) 549a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 550select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='b'; 551a b c d sha1(t) 552a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 553update t1 set t=@v4 where a='b' and b=1110 and c='a'; 554update t1 set t=@v2 where a='b' and b=1110 and c='b'; 555select a,b,c,d,sha1(t) from t1 order by c,a; 556a b c d sha1(t) 557a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 558b 1110 a 2 NULL 559a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 560b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e 561update t1 set t=@v2 where d=2; 562update t1 set t=@v4 where d=4; 563select a,b,c,d,sha1(t) from t1 order by c,a; 564a b c d sha1(t) 565a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 566b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e 567a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 568b 1110 b 4 NULL 569update t1 set t=@v4 where a='b' and c='a'; 570update t1 set t=@v2 where a='b' and c='b'; 571select a,b,c,d,sha1(t) from t1 order by c,a; 572a b c d sha1(t) 573a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 574b 1110 a 2 NULL 575a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 576b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e 577update t1 set t=@v2 where b+d=1112; 578update t1 set t=@v4 where b+d=1114; 579select a,b,c,d,sha1(t) from t1 order by c,a; 580a b c d sha1(t) 581a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 582b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e 583a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 584b 1110 b 4 NULL 585delete from t1 where a='a' and b=1110 and c='a'; 586delete from t1 where a='b' and c='a'; 587delete from t1 where d=3; 588delete from t1 where b+d=1114; 589select count(*) from t1; 590count(*) 5910 592drop table t1; 593create table t1( 594a int, 595blob_nn blob not null, 596text_nn text not null, 597blob_nl blob, 598text_nl text, 599primary key(a) 600) engine=ndb; 601insert ignore into t1(a) values (1); 602Warnings: 603Warning 1364 Field 'blob_nn' doesn't have a default value 604Warning 1364 Field 'text_nn' doesn't have a default value 605insert ignore into t1(a, text_nl) values (2, 'MySQL Cluster NDB'); 606Warnings: 607Warning 1364 Field 'blob_nn' doesn't have a default value 608Warning 1364 Field 'text_nn' doesn't have a default value 609select a, length(blob_nn), length(text_nn), blob_nl, text_nl from t1 order by a; 610a length(blob_nn) length(text_nn) blob_nl text_nl 6111 0 0 NULL NULL 6122 0 0 NULL MySQL Cluster NDB 613drop table t1; 614create table `t1` ( 615`f1` int(11) not null default -1, 616`f11` text, 617unique key `i1` (`f1`) 618) engine=ndbcluster default charset=utf8; 619insert into `t1` values (123,null); 620create table `t2` ( 621`f1` int(11) not null default -1, 622unique key `i2` (`f1`) 623) engine=ndbcluster default charset=utf8; 624begin; 625delete from t2 where f1=5; 626delete from t1 where f1=123; 627delete from t2 where f1=5; 628commit; 629show warnings; 630Level Code Message 631drop table t1; 632drop table t2; 633create table t1 ( 634a int primary key, 635giga longblob) 636engine=ndb; 637set @stuff = '1234567890'; 638insert into t1 values (0, repeat(@stuff, 2000)); 639select sha1(repeat(@stuff, 2000)); 640sha1(repeat(@stuff, 2000)) 641c8d94eb4127361ac22cf1c8a8f1178a37fb25e41 642select sha1(giga) from t1; 643sha1(giga) 644c8d94eb4127361ac22cf1c8a8f1178a37fb25e41 645select (giga = repeat(@stuff, 2000)) from t1 where a=0; 646(giga = repeat(@stuff, 2000)) 6471 648drop table t1; 649Test that automatic lock upgrade on Blobs behaves as expected 650w.r.t other operations on the table. 651create table t1 (a int primary key, b longblob, c int, unique(c)) engine=ndb; 652insert into t1 values (1, repeat('1', 256), 1); 653insert into t1 values (2, repeat('2', 2256), 2); 654insert into t1 values (3, repeat('3', 4256), 3); 655insert into t1 values (4, repeat('4', 8256), 4); 656insert into t1 values (5, repeat('5', 20000), 5); 657No Deadlock scenarios 658Transaction 1 reading PK with no lock. Transaction 2 reading PK with EX lock 659begin; 660select a, length(b) from t1 where a=2; 661a length(b) 6622 2256 663select a, length(b) from t1 where a=2 FOR UPDATE; 664a length(b) 6652 2256 666Transaction 2 scanning with EX lock - will not deadlock with transaction 1 667select a, length(b) from t1 ORDER BY a FOR UPDATE; 668a length(b) 6691 256 6702 2256 6713 4256 6724 8256 6735 20000 674commit; 675Transaction1 reading PK with SH lock. Transaction 2 reading PK with no lock 676begin; 677select a, length(b) from t1 where a=3 LOCK IN SHARE MODE; 678a length(b) 6793 4256 680select a, length(b) from t1 where a=3; 681a length(b) 6823 4256 683Transaction 2 scanning with SH lock - will not deadlock with transaction 1 684select a, length(b) from t1 ORDER BY a; 685a length(b) 6861 256 6872 2256 6883 4256 6894 8256 6905 20000 691commit; 692Transaction1 reading PK with SH lock. Transaction 2 reading PK with SH lock 693begin; 694select a, length(b) from t1 where a=4 LOCK IN SHARE MODE; 695a length(b) 6964 8256 697select a, length(b) from t1 where a=4 LOCK IN SHARE MODE; 698a length(b) 6994 8256 700Transaction 2 scanning with SH lock - will not deadlock with transaction 1 701select a, length(b) from t1 ORDER BY a LOCK IN SHARE MODE; 702a length(b) 7031 256 7042 2256 7053 4256 7064 8256 7075 20000 708commit; 709Deadlock scenarios on PK access : 710Transaction 1 reading PK with EX lock. Transaction 2 reading PK with no lock 711begin; 712select a, length(b) from t1 where a=5 FOR UPDATE; 713a length(b) 7145 20000 715select a, length(b) from t1 where a=5; 716ERROR HY000: Lock wait timeout exceeded; try restarting transaction 717commit; 718Transaction 1 reading PK with EX lock. Transaction 2 reading PK with SH lock 719begin; 720select a, length(b) from t1 where a=1 FOR UPDATE; 721a length(b) 7221 256 723select a, length(b) from t1 where a=1 LOCK IN SHARE MODE; 724ERROR HY000: Lock wait timeout exceeded; try restarting transaction 725commit; 726Verify that Nolock Unique index access still deadlocks 727as unique index accesses always upgrade to SH 728begin; 729select a, length(b) from t1 where c=2; 730a length(b) 7312 2256 732select a, length(b) from t1 FOR UPDATE; 733ERROR HY000: Lock wait timeout exceeded; try restarting transaction 734commit; 735drop table t1; 7365.6 tests 737create table `t1` (`a` int,`b` blob,primary key (`a`)) engine=ndbcluster; 738insert into t1 values (2, 'Hei og hopp! '); 739update t1 set b=concat(b,b,b,b,b); 740update t1 set b=concat(b,b,b,b,b); 741update t1 set b=concat(b,b,b,b,b); 742explain update `t1` set `a`=1 where `a`!= 1; 743id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7441 UPDATE t1 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 const 4 100.00 Using where; Using temporary 745update `t1` set `a`=1 where `a`!= 1; 746select * from t1; 747a b 7481 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! 749drop table t1; 750End of 5.6 tests 751