| 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 |