1
##############################################################
4
# Purpose: Specific Blob and Varchar testing using disk tables.
5
##############################################################
6
# Create Stored procedures that use disk based tables.
7
# Create function that operate on disk based tables.
8
# Create triggers that operate on disk based tables.
9
# Create views that operate on disk based tables.
10
# Try to create FK constraints on disk based tables.
11
# Create and use disk based table that use auto inc.
12
# Create test that use transaction (commit, rollback)
13
# Create large disk base table, do random queries, check cache hits,
14
# do same query 10 times check cache hits.
15
# Create test that uses COUNT(), SUM(), MAX(), MIN(),
16
# NOW(), USER(), ... other built in # SQL functions
17
# Create test that uses locks.
18
# Create test using truncate.
19
##############################################################
22
# Change: Remove all the create and drop LFG and TS except
23
# The first create and the last drop to make test
25
# In addition, renamed test to be to a name that
26
# gives a better idea of what the test is about
27
###########################################################
28
-- source include/have_ndb.inc
31
DROP TABLE IF EXISTS test.t1;
32
DROP TABLE IF EXISTS test.t2;
33
DROP TABLE IF EXISTS test.t3;
36
#### Copy data from table in one table space to table in ###
37
#### different table space. ####
39
--echo **** Copy data from table in one table space to table
40
--echo **** in different table space
43
CREATE LOGFILE GROUP lg
44
ADD UNDOFILE './lg_group/undofile.dat'
50
ADD DATAFILE './table_space1/datafile.dat'
56
ADD DATAFILE './table_space2/datafile.dat'
61
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
62
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
63
CREATE TABLE test.t2 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
64
TABLESPACE ts2 STORAGE DISK ENGINE=NDB;
66
SHOW CREATE TABLE test.t1;
67
SHOW CREATE TABLE test.t2;
69
INSERT INTO test.t1 VALUES (1,'111111','aaaaaaaa');
70
INSERT INTO test.t1 VALUES (2,'222222','bbbbbbbb');
71
SELECT * FROM test.t1 ORDER BY a1;
72
INSERT INTO test.t2(a1,a2,a3) SELECT * FROM test.t1;
73
SELECT * FROM test.t2 ORDER BY a1;
75
DROP TABLE test.t1, test.t2;
77
# populate BLOB field with large data
79
set @vc1 = repeat('a', 200);
80
set @vc2 = repeat('b', 500);
81
set @vc3 = repeat('c', 1000);
82
set @vc4 = repeat('d', 4000);
85
set @x0 = '01234567012345670123456701234567';
86
set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
90
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
91
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
92
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
93
set @b1 = concat(@b1,@x0);
96
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
97
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
98
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
102
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
103
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
104
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
105
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
108
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
109
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
110
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
111
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
113
select length(@x0),length(@b1),length(@d1) from dual;
114
select length(@x0),length(@b2),length(@d2) from dual;
116
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB)
117
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
118
CREATE TABLE test.t2 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB)
119
TABLESPACE ts2 STORAGE DISK ENGINE=NDB;
121
SHOW CREATE TABLE test.t1;
122
SHOW CREATE TABLE test.t2;
124
INSERT INTO test.t1 VALUES (1,@vc1,@d1);
125
INSERT INTO test.t1 VALUES (2,@vc2,@b1);
126
INSERT INTO test.t1 VALUES (3,@vc3,@d2);
127
INSERT INTO test.t1 VALUES (4,@vc4,@b2);
129
SELECT a1,length(a2),substr(a2,180,2),length(a3),substr(a3,1+3*900,3)
130
FROM test.t1 WHERE a1=1;
131
SELECT a1,length(a2),substr(a2,480,2),length(a3),substr(a3,1+2*900,3)
132
FROM test.t1 where a1=2;
134
INSERT INTO test.t2(a1,a2,a3) SELECT * FROM test.t1;
135
SELECT a1,length(a2),substr(a2,180,2),length(a3),substr(a3,1+3*900,3)
136
FROM test.t2 WHERE a1=1;
137
SELECT a1,length(a2),substr(a2,480,2),length(a3),substr(a3,1+2*900,3)
138
FROM test.t2 where a1=2;
141
DROP TABLE test.t1, test.t2;
143
#### Insert, Update, Delete from NDB table with BLOB fields ####
145
--echo **** Insert, Update, Delete from NDB table with BLOB fields
148
set @vc1 = repeat('a', 200);
149
set @vc2 = repeat('b', 500);
150
set @vc3 = repeat('c', 1000);
151
set @vc4 = repeat('d', 4000);
152
set @vc5 = repeat('d', 5000);
154
set @bb1 = repeat('1', 2000);
155
set @bb2 = repeat('2', 5000);
156
set @bb3 = repeat('3', 10000);
157
set @bb4 = repeat('4', 40000);
158
set @bb5 = repeat('5', 50000);
160
select length(@vc1),length(@vc2),length(@vc3),length(@vc4),length(@vc5)
162
select length(@bb1),length(@bb2),length(@bb3),length(@bb4),length(@bb5)
165
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(5000), a3 BLOB)
166
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
167
# CREATE TABLE test.t2 (a1 int NOT NULL, a2 VARCHAR(5000), a3 BLOB)
168
# TABLESPACE ts2 STORAGE DISK ENGINE=NDB;
170
INSERT INTO test.t1 VALUES (1,@vc1,@bb1);
171
INSERT INTO test.t1 VALUES (2,@vc2,@bb2);
172
INSERT INTO test.t1 VALUES (3,@vc3,@bb3);
173
INSERT INTO test.t1 VALUES (4,@vc4,@bb4);
174
INSERT INTO test.t1 VALUES (5,@vc5,@bb5);
176
UPDATE test.t1 SET a2=@vc5, a3=@bb5 WHERE a1=1;
177
SELECT a1,length(a2),substr(a2,4998,2),length(a3),substr(a3,49997,3)
178
FROM test.t1 WHERE a1=1;
180
UPDATE test.t1 SET a2=@vc4, a3=@bb4 WHERE a1=2;
181
SELECT a1,length(a2),substr(a2,3998,2),length(a3),substr(a3,39997,3)
182
FROM test.t1 WHERE a1=2;
184
UPDATE test.t1 SET a2=@vc2, a3=@bb2 WHERE a1=3;
185
SELECT a1,length(a2),substr(a2,498,2),length(a3),substr(a3,3997,3)
186
FROM test.t1 WHERE a1=3;
188
UPDATE test.t1 SET a2=@vc3, a3=@bb3 WHERE a1=4;
189
SELECT a1,length(a2),substr(a2,998,2),length(a3),substr(a3,9997,3)
190
FROM test.t1 WHERE a1=4;
192
UPDATE test.t1 SET a2=@vc1, a3=@bb1 WHERE a1=5;
193
SELECT a1,length(a2),substr(a2,198,2),length(a3),substr(a3,1997,3)
194
FROM test.t1 WHERE a1=5;
196
DELETE FROM test.t1 where a1=5;
197
SELECT count(*) from test.t1;
198
DELETE FROM test.t1 where a1=4;
199
SELECT count(*) from test.t1;
200
DELETE FROM test.t1 where a1=3;
201
SELECT count(*) from test.t1;
202
DELETE FROM test.t1 where a1=2;
203
SELECT count(*) from test.t1;
204
DELETE FROM test.t1 where a1=1;
205
SELECT count(*) from test.t1;
209
##### Create Stored procedures that use disk based tables #####
211
--echo **** Create Stored procedures that use disk based tables
215
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
216
TABLESPACE ts1 STORAGE DISK ENGINE=NDB//
217
CREATE PROCEDURE test.sp1()
219
INSERT INTO test.t1 values (1,'111111','aaaaaaaa');
224
SELECT * FROM test.t1;
227
CREATE PROCEDURE test.sp2(n INT, vc VARCHAR(256), blb BLOB)
229
UPDATE test.t1 SET a2=vc, a3=blb where a1=n;
233
CALL test.sp2(1,'222222','bbbbbbbb');
234
SELECT * FROM test.t1;
237
DROP PROCEDURE test.sp1;
238
DROP PROCEDURE test.sp2;
242
#### Create function that operate on disk based tables ####
244
--echo ***** Create function that operate on disk based tables
247
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
248
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
253
eval insert into test.t1 values($1, "aaaaa$1", "bbbbb$1");
259
CREATE FUNCTION test.fn1(n INT) RETURNS INT
262
SELECT a1 INTO v FROM test.t1 WHERE a1=n;
268
CREATE FUNCTION test.fn2(n INT, blb BLOB) RETURNS BLOB
271
UPDATE test.t1 SET a3=blb where a1=n;
272
SELECT a3 INTO vv FROM test.t1 WHERE a1=n;
277
SELECT test.fn1(10) FROM DUAL;
278
SELECT test.fn2(50, 'new BLOB content') FROM DUAL;
281
DROP FUNCTION test.fn1;
282
DROP FUNCTION test.fn2;
285
#### Create triggers that operate on disk based tables ####
287
--echo ***** Create triggers that operate on disk based tables
290
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
291
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
294
CREATE TRIGGER test.trg1 BEFORE INSERT ON test.t1 FOR EACH ROW
296
if isnull(new.a2) then
297
set new.a2:= 'trg1 works on a2 field';
299
if isnull(new.a3) then
300
set new.a3:= 'trg1 works on a3 field';
303
insert into test.t1 (a1) values (1)//
304
insert into test.t1 (a1,a2) values (2, 'ccccccc')//
305
select * from test.t1 order by a1//
309
DROP TRIGGER test.trg1;
312
#### Create, update views that operate on disk based tables ####
314
--echo ***** Create, update views that operate on disk based tables
318
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256), a3 BLOB)
319
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
324
eval insert into test.t1 values($1, "aaaaa$1", "bbbbb$1");
328
CREATE VIEW test.v1 AS SELECT * FROM test.t1;
329
SELECT * FROM test.v1 order by a1;
330
CHECK TABLE test.v1, test.t1;
332
UPDATE test.v1 SET a2='zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' WHERE a1=5;
333
SELECT * FROM test.v1 order by a1;
339
#### Create and use disk based table that use auto inc ####
341
--echo ***** Create and use disk based table that use auto inc
344
CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
345
a2 VARCHAR(256), a3 BLOB)
346
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
351
eval insert into test.t1 values(NULL, "aaaaa$1", "bbbbb$1");
355
SELECT * FROM test.t1 ORDER BY a1;
360
#### Create test that use transaction (commit, rollback) ####
362
--echo ***** Create test that use transaction (commit, rollback)
366
CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
367
a2 VARCHAR(256), a3 BLOB)
368
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
370
INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
372
SELECT * FROM test.t1 ORDER BY a1;
373
INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
375
SELECT * FROM test.t1 ORDER BY a1;
381
# Now do the same thing with START TRANSACTION without using AUTOCOMMIT.
383
CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
384
a2 VARCHAR(256), a3 BLOB)
385
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
388
INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
390
SELECT * FROM test.t1 ORDER BY a1;
393
INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
395
SELECT * FROM test.t1 ORDER BY a1;
400
#### Create test that uses locks ####
402
--echo ***** Create test that uses locks
405
connect (con1,localhost,root,,);
406
connect (con2,localhost,root,,);
410
drop table if exists test.t1;
411
CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
412
a2 VARCHAR(256), a3 BLOB)
413
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
416
LOCK TABLES test.t1 write;
417
INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
418
INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
419
SELECT * FROM test.t1 ORDER BY a1;
422
SELECT * FROM test.t1 ORDER BY a1;
423
INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3");
429
INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3");
430
SELECT * FROM test.t1 ORDER BY a1;
436
#### Create large disk base table, do random queries, check cache hits ####
438
--echo ***** Create large disk base table, do random queries, check cache hits
441
set @vc1 = repeat('a', 200);
442
SELECT @vc1 FROM DUAL;
443
set @vc2 = repeat('b', 500);
444
set @vc3 = repeat('b', 998);
447
set @x0 = '01234567012345670123456701234567';
448
set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
450
# b1 length 2000+256 (blob part aligned)
452
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
453
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
454
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
455
set @b1 = concat(@b1,@x0);
458
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
459
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
460
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
464
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
465
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
466
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
467
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
470
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
471
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
472
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
473
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
475
select length(@x0),length(@b1),length(@d1) from dual;
476
select length(@x0),length(@b2),length(@d2) from dual;
478
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(1000), a3 BLOB)
479
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
481
INSERT INTO test.t1 values(1,@vc1,@d1);
482
INSERT INTO test.t1 values(2,@vc2,@d2);
484
explain SELECT * from test.t1 WHERE a1 = 1;
486
SELECT a1,length(a2),substr(a2,1+2*900,2),length(a3),substr(a3,1+3*900,3)
487
FROM test.t1 WHERE a1=1 ORDER BY a1;
488
SELECT a1,length(a2),substr(a2,1+2*9000,2),length(a3),substr(a3,1+3*9000,3)
489
FROM test.t1 where a1=2 ORDER BY a1;
491
UPDATE test.t1 set a2=@vc2,a3=@d2 where a1=1;
492
UPDATE test.t1 set a2=@vc1,a3=@d1 where a1=2;
494
SELECT a1,length(a2),substr(a2,1+2*9000,2),length(a3),substr(a3,1+3*9000,3)
495
FROM test.t1 where a1=1;
496
SELECT a1,length(a2),substr(a2,1+2*900,2),length(a3),substr(a3,1+3*900,3)
497
FROM test.t1 where a1=2;
499
#SHOW VARIABLES LIKE 'have_query_cache';
500
#SHOW STATUS LIKE 'Qcache%';
505
#### Create test that uses COUNT(), SUM(), MAX(), ####
506
##### MIN(), NOW(), USER(), TRUNCATE ####
508
--echo ***** Create test that uses COUNT(), SUM(), MAX(), MIN(), NOW(),
509
--echo ***** USER(), TRUNCATE
512
CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY, a2 VARCHAR(256),
513
a3 BLOB, a4 DATE, a5 CHAR(250))
514
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
519
eval insert into test.t1 values($1, "aaaaaaaaaaaaaaaa$1",
520
"bbbbbbbbbbbbbbbbbb$1", '2006-06-20' , USER());
525
SELECT COUNT(*) from test.t1;
526
SELECT SUM(a1) from test.t1;
527
SELECT MIN(a1) from test.t1;
528
SELECT MAX(a1) from test.t1;
529
SELECT a5 from test.t1 where a1=50;
532
SELECT * from test.t1 order by a1;
537
DROP DATAFILE './table_space1/datafile.dat'
539
DROP TABLESPACE ts1 ENGINE=NDB;
542
DROP DATAFILE './table_space2/datafile.dat'
544
DROP TABLESPACE ts2 ENGINE=NDB;
546
DROP LOGFILE GROUP lg