1
##############################################################
4
# Purpose: To test using ndb memory and disk tables together.
5
##############################################################
7
##############################################################
10
# Purpose: To test using ndb memory and disk tables together.
12
# Select from disk into memory table
13
# Select from disk into memory table
14
# Create test that loads data, use mysql dump to dump data, drop table,
15
# create table and load from mysql dump.
16
# Use group by asc and dec; Use having; Use order by
17
# ALTER Tests (Meta data testing):
18
# ALTER from InnoDB to Cluster Disk Data
19
# ALTER from MyISAM to Cluster Disk Data
20
# ALTER from Cluster Disk Data to InnoDB
21
# ALTER from Cluster Disk Data to MyISAM
22
# ALTER DD Tables and add columns
23
# ALTER DD Tables and add Indexes
24
# ALTER DD Tables and drop columns
26
##############################################################
28
-- source include/have_ndb.inc
29
-- source include/not_embedded.inc
32
DROP TABLE IF EXISTS test.t1;
33
DROP TABLE IF EXISTS test.t2;
36
############ Test Setup Section #############
37
-- echo **** Test Setup Section ****
39
CREATE LOGFILE GROUP log_group1
40
ADD UNDOFILE './log_group1/undofile.dat'
45
CREATE TABLESPACE table_space1
46
ADD DATAFILE './table_space1/datafile.dat'
47
USE LOGFILE GROUP log_group1
53
(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
54
TABLESPACE table_space1 STORAGE DISK
58
(pk2 INT NOT NULL PRIMARY KEY, b2 INT NOT NULL, c2 INT NOT NULL)
62
##################### Data load for first test ####################
63
--echo **** Data load for first test ****
65
INSERT INTO test.t1 VALUES
66
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
67
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
68
(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
69
(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
70
(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
71
(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
72
(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
73
(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
74
(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
75
(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
76
(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
77
(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
78
(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
79
(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
80
(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
83
INSERT INTO test.t2 VALUES
84
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
85
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
86
(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
87
(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
88
(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
89
(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
90
(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
91
(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
92
(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
93
(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
94
(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
95
(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
96
(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
97
(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
98
(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
101
##################### Test 1 Section Begins ###############
102
--echo *** Test 1 Section Begins ***
103
SELECT COUNT(*) FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
104
SELECT * FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
105
SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 WHERE b IN (4);
106
SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2 WHERE pk1 IN (75);
107
SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
109
####################### Test 1 Section End ################
111
##################### Setup for test 2 ####################
112
--echo *** Setup for test 2 ****
114
INSERT INTO test.t1 VALUES
115
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
116
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
117
(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
118
(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
119
(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
120
(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
121
(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
122
(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
123
(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45);
125
############################# Test Section 2 ###############
126
--echo **** Test Section 2 ****
127
SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
128
SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2;
129
SELECT COUNT(*) FROM test.t1 RIGHT JOIN test.t2 ON b=b2;
130
SHOW CREATE TABLE test.t2;
131
SHOW CREATE TABLE test.t1;
132
ALTER TABLE test.t2 TABLESPACE table_space1 STORAGE DISK
134
SHOW CREATE TABLE test.t2;
135
ALTER TABLE test.t1 ENGINE=NDBCLUSTER;
136
SHOW CREATE TABLE test.t1;
138
######################### End Test Section 2 #################
141
##################### Setup for Test Section 3 ###############
142
--echo *** Setup for Test Section 3 ***
143
CREATE TABLE test.t1 (
144
usr_id INT unsigned NOT NULL,
145
uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
146
start_num INT unsigned NOT NULL DEFAULT 1,
147
increment INT unsigned NOT NULL DEFAULT 1,
148
PRIMARY KEY (uniq_id),
149
INDEX usr_uniq_idx (usr_id, uniq_id),
150
INDEX uniq_usr_idx (uniq_id, usr_id))
151
TABLESPACE table_space1 STORAGE DISK
155
CREATE TABLE test.t2 (
156
id INT unsigned NOT NULL DEFAULT 0,
157
usr2_id INT unsigned NOT NULL DEFAULT 0,
158
max INT unsigned NOT NULL DEFAULT 0,
159
c_amount INT unsigned NOT NULL DEFAULT 0,
160
d_max INT unsigned NOT NULL DEFAULT 0,
161
d_num INT unsigned NOT NULL DEFAULT 0,
162
orig_time INT unsigned NOT NULL DEFAULT 0,
163
c_time INT unsigned NOT NULL DEFAULT 0,
164
active ENUM ("no","yes") NOT NULL,
165
PRIMARY KEY (id,usr2_id),
167
INDEX usr2_idx (usr2_id))
170
INSERT INTO test.t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
173
###################### Test Section 3 ######################
174
--echo **** Test Section 3 ****
175
SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
176
test.t2.usr2_id,test.t2.c_amount,test.t2.max
178
LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
179
WHERE test.t1.uniq_id = 4
180
ORDER BY test.t2.c_amount;
182
INSERT INTO test.t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
183
INSERT INTO test.t2 VALUES (4,3,3000,6000,0,0,746584,837484,'yes');
184
INSERT INTO test.t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
186
SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
187
test.t2.usr2_id,test.t2.c_amount,test.t2.max
189
LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
190
WHERE test.t1.uniq_id = 4
191
ORDER BY test.t2.c_amount;
193
####################### End Section 3 #########################
196
ALTER TABLESPACE table_space1
197
DROP DATAFILE './table_space1/datafile.dat'
200
DROP TABLESPACE table_space1
203
DROP LOGFILE GROUP log_group1
206
####################### Section 4 #########################
208
CREATE LOGFILE GROUP lg
209
ADD UNDOFILE './lg_group/undofile.dat'
211
UNDO_BUFFER_SIZE = 1M
214
CREATE TABLESPACE ts1
215
ADD DATAFILE './table_space1/datafile.dat'
220
CREATE TABLESPACE ts2
221
ADD DATAFILE './table_space2/datafile.dat'
227
### Select from disk into memory table ###
229
CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int)
230
TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
231
CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b int)
234
SHOW CREATE TABLE t1;
235
SHOW CREATE TABLE t2;
237
INSERT INTO t1 VALUES (1,1);
238
INSERT INTO t1 VALUES (2,2);
239
SELECT * FROM t1 order by a;
240
INSERT INTO t2(a,b) SELECT * FROM t1;
241
SELECT * FROM t2 order by a;
243
### Select from disk into memory table ###
247
INSERT INTO t2 VALUES (3,3);
248
INSERT INTO t2 VALUES (4,4);
249
INSERT INTO t1(a,b) SELECT * FROM t2;
250
SELECT * FROM t1 order by a;
255
DROP DATAFILE './table_space1/datafile.dat'
258
DROP TABLESPACE ts1 ENGINE NDB;
261
DROP DATAFILE './table_space2/datafile.dat'
264
DROP TABLESPACE ts2 ENGINE NDB;
266
DROP LOGFILE GROUP lg
269
#### Create test that loads data, use mysql dump to dump data, drop table,
270
#### create table and load from mysql dump.
272
# DROP DATABASE IF EXISTS test;
274
CREATE LOGFILE GROUP lg
275
ADD UNDOFILE './undofile.dat'
277
UNDO_BUFFER_SIZE = 1M
281
ADD DATAFILE './datafile.dat'
286
# CREATE DATABASE test;
288
CREATE TABLE test.t (
296
) TABLESPACE ts STORAGE DISK ENGINE=NDB;
298
ALTER TABLE test.t ADD INDEX (d), ADD INDEX (f);
299
SHOW CREATE TABLE test.t;
301
# insert records into tables
307
eval insert into test.t values($1, $1+1, $1+2, "aaa$1", "bbb$1", "ccccc$1");
312
SELECT * FROM test.t order by a;
313
--exec $MYSQL_DUMP --skip-comments --databases test > $MYSQLTEST_VARDIR/tmp/t_dump.sql
315
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/t_dump.sql
319
SELECT * FROM test.t order by a;
322
# DROP DATABASE test;
325
DROP DATAFILE './datafile.dat'
328
DROP TABLESPACE ts ENGINE NDB;
330
DROP LOGFILE GROUP lg
333
#### BUG 18856 test case comented out
334
##### Use "SELECT * INTO OUTFILE" to dump data and "LOAD DATA INFILE" to load ##### data back to the data file.
336
# CREATE LOGFILE GROUP lg
337
# ADD UNDOFILE './undofile.dat'
339
# UNDO_BUFFER_SIZE = 1M
342
# CREATE TABLESPACE ts
343
# ADD DATAFILE './datafile.dat'
344
# USE LOGFILE GROUP lg
348
#CREATE DATABASE test;
350
#CREATE TABLE test.t (
351
# a smallint NOT NULL,
358
#) TABLESPACE ts STORAGE DISK ENGINE=NDB;
360
# ALTER TABLE test.t ADD INDEX (d), ADD INDEX (f);
361
# SHOW CREATE TABLE test.t;
363
# insert records into tables
369
# eval insert into test.t values($1, $1+1, $1+2, "aaa$1", "bbb$1", "ccccc$1");
374
# SELECT * FROM test.t order by a;
376
# SELECT * INTO OUTFILE 't_backup' FROM test.t;
379
#'TRUNCATE test.t' failed: 1205: Lock wait timeout exceeded; try restarting #transaction. TABLESPACE ts STORAGE DISK ENGINE=NDB;
381
# SELECT count(*) FROM test.t;
382
# LOAD DATA INFILE 't_backup' INTO TABLE test.t;
384
# SELECT * FROM test.t order by a;
387
# DROP DATABASE test;
389
# ALTER TABLESPACE ts
390
# DROP DATAFILE './datafile.dat'
392
# DROP TABLESPACE ts ENGINE NDB;
393
# DROP LOGFILE GROUP lg
396
#### Use group by asc and dec; Use having; Use order by. ####
398
# DROP DATABASE IF EXISTS test;
399
DROP table IF EXISTS test.t1;
400
DROP table IF EXISTS test.t2;
402
CREATE LOGFILE GROUP lg
403
ADD UNDOFILE './lg_group/undofile.dat'
405
UNDO_BUFFER_SIZE = 1M
408
CREATE TABLESPACE ts1
409
ADD DATAFILE './table_space1/datafile.dat'
414
CREATE TABLESPACE ts2
415
ADD DATAFILE './table_space2/datafile.dat'
420
# CREATE DATABASE test;
422
CREATE TABLE test.t1 (
423
a1 smallint NOT NULL,
434
) TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
436
ALTER TABLE test.t1 ADD INDEX (a2), ADD INDEX (a3), ADD INDEX (a8);
437
SHOW CREATE TABLE test.t1;
439
CREATE TABLE test.t2 (
440
b1 smallint NOT NULL,
453
ALTER TABLE test.t2 ADD INDEX (b2), ADD INDEX (b3), ADD INDEX (b8);
454
SHOW CREATE TABLE test.t2;
460
eval insert into test.t1 values($1, $1+1, $1+2000000000, "aaa$1", 34.2, '4:3:2', '2006-1-1', '1971-5-28 16:55:03', "bbbbbbbbbbbbb$1", "binary data");
461
eval insert into test.t2 values($1+2, $1+3, $1+3000000000, "aaa$1", 35.2, '4:3:2', '2006-1-1', '1971-5-28 16:55:03', "bbbbbbbbbbbbb$1", "binary data");
466
SELECT * FROM test.t1 order by a1;
467
SELECT * FROM test.t2 order by b1;
468
SELECT COUNT(a1), a1, COUNT(a1)*a1 FROM test.t1 GROUP BY a1;
469
SELECT COUNT(a2), (a2+1), COUNT(a2)*(a2+0) FROM test.t1 GROUP BY a2;
474
create table test.t1 (a int not null,b char(5), c text) TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
476
insert into test.t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
477
select distinct a from test.t1 group by b,a having a > 2 order by a desc;
478
select distinct a,c from test.t1 group by b,c,a having a > 2 order by a desc;
479
select distinct a from test.t1 group by b,a having a > 2 order by a asc;
480
select distinct a,c from test.t1 group by b,c,a having a > 2 order by a asc;
483
create table test.t1 (a char(1), key(a)) TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
484
insert into test.t1 values('1'),('1'),('1'),('2'),('2'),('3'),('3');
485
select * from test.t1 where a >= '1' order by a;
486
select distinct a from test.t1 order by a desc;
487
select distinct a from test.t1 where a >= '1' order by a desc;
488
select distinct a from test.t1 where a >= '1' order by a asc;
491
CREATE TABLE test.t1 (email varchar(50), infoID BIGINT, dateentered DATETIME) TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
492
CREATE TABLE test.t2 (infoID BIGINT, shipcode varchar(10)) ENGINE=NDB;
494
INSERT INTO test.t1 (email, infoID, dateentered) VALUES
495
('test1@testdomain.com', 1, '2002-07-30 22:56:38'),
496
('test1@testdomain.com', 1, '2002-07-27 22:58:16'),
497
('test2@testdomain.com', 1, '2002-06-19 15:22:19'),
498
('test2@testdomain.com', 2, '2002-06-18 14:23:47'),
499
('test3@testdomain.com', 1, '2002-05-19 22:17:32');
501
INSERT INTO test.t2(infoID, shipcode) VALUES
505
SELECT DISTINCTROW email, shipcode FROM test.t1, test.t2 WHERE t1.infoID=t2.infoID order by email, shipcode;
506
SELECT DISTINCTROW email FROM test.t1 ORDER BY dateentered DESC;
507
SELECT DISTINCTROW email, shipcode FROM test.t1, test.t2 WHERE test.t1.infoID=test.t2.infoID ORDER BY dateentered DESC;
508
drop table test.t1,test.t2;
511
DROP DATAFILE './table_space1/datafile.dat'
513
DROP TABLESPACE ts1 ENGINE NDB;
515
DROP DATAFILE './table_space2/datafile.dat'
517
DROP TABLESPACE ts2 ENGINE NDB;
518
DROP LOGFILE GROUP lg
520
####################################################################
523
#### Customer posted order by test case
525
DROP TABLE IF EXISTS test.t;
526
create table test.t (f1 varchar(50) primary key, f2 text,f3 int) engine=NDB;
527
insert into test.t (f1,f2,f3)VALUES("111111","aaaaaa",1);
528
insert into test.t (f1,f2,f3)VALUES("222222","bbbbbb",2);
529
select * from test.t order by f1;
530
select f1,f2 from test.t order by f2;
531
select f2 from test.t order by f2;
532
select f1,f2 from test.t order by f1;
535
################## ALTER Tests (Meta data testing) ####################
537
CREATE LOGFILE GROUP lg
538
ADD UNDOFILE './lg_group/undofile.dat'
540
UNDO_BUFFER_SIZE = 1M
544
ADD DATAFILE './table_space/datafile.dat'
549
#### Try to ALTER from InnoDB to Cluster Disk Data
551
CREATE TABLE test.t1 (a1 INT, a2 BLOB, a3 TEXT) ENGINE=InnoDB;
552
SHOW CREATE TABLE test.t1;
553
ALTER TABLE test.t1 TABLESPACE ts STORAGE DISK ENGINE=NDB;
554
SHOW CREATE TABLE test.t1;
557
#### Try to ALTER from MyISAM to Cluster Disk Data
559
CREATE TABLE test.t1 (a1 INT, a2 BLOB, a3 TEXT) ENGINE=MyISAM;
560
SHOW CREATE TABLE test.t1;
561
ALTER TABLE test.t1 TABLESPACE ts STORAGE DISK ENGINE=NDB;
562
SHOW CREATE TABLE test.t1;
565
#### Try to ALTER from Cluster Disk Data to InnoDB
567
CREATE TABLE test.t1 (a1 INT PRIMARY KEY, a2 BLOB, a3 TEXT) TABLESPACE ts STORAGE DISK ENGINE=NDB;
568
SHOW CREATE TABLE test.t1;
569
ALTER TABLE test.t1 ENGINE=InnoDB;
570
SHOW CREATE TABLE test.t1;
573
#### Try to ALTER from Cluster Disk Data to MyISAM
575
CREATE TABLE test.t1 (a1 INT, a2 BLOB, a3 TEXT) TABLESPACE ts STORAGE DISK ENGINE=NDB;
576
SHOW CREATE TABLE test.t1;
577
ALTER TABLE test.t1 ENGINE=MyISAM;
578
SHOW CREATE TABLE test.t1;
581
#### Try to ALTER DD Tables and add columns
583
CREATE TABLE test.t1 (a1 INT PRIMARY KEY) TABLESPACE ts STORAGE DISK ENGINE=NDB;
585
ALTER TABLE test.t1 ADD a2 FLOAT, ADD a3 DOUBLE, ADD a4 BIT, ADD a5 TINYINT, ADD a6 BIGINT, ADD a7 DATE, ADD a8 TIME, ADD a9 DATETIME, ADD a10 TINYTEXT, ADD a11 MEDIUMTEXT, ADD a12 LONGTEXT, ADD a13 TEXT, ADD a14 BLOB;
587
SHOW CREATE TABLE test.t1;
589
#### Try to ALTER DD Tables and add Indexes
591
ALTER TABLE test.t1 ADD INDEX (a2), ADD INDEX (a3), ADD INDEX (a5), ADD INDEX (a6),
592
ADD INDEX (a7), ADD INDEX (a8);
594
SHOW CREATE TABLE test.t1;
598
#### Try to ALTER DD Tables and drop columns
600
CREATE TABLE test.t1 (a1 INT PRIMARY KEY) TABLESPACE ts STORAGE DISK ENGINE=NDB;
602
ALTER TABLE test.t1 ADD a2 FLOAT, ADD a3 DOUBLE, ADD a4 BIT, ADD a5 TINYINT, ADD a6 BIGINT, ADD a7 DATE, ADD a8 TIME, ADD a9 DATETIME, ADD a10 TINYTEXT, ADD a11 MEDIUMTEXT, ADD a12 LONGTEXT, ADD a13 TEXT, ADD a14 BLOB;
604
SHOW CREATE TABLE test.t1;
606
ALTER TABLE test.t1 DROP a14;
607
ALTER TABLE test.t1 DROP a13;
608
ALTER TABLE test.t1 DROP a12;
609
ALTER TABLE test.t1 DROP a11;
610
ALTER TABLE test.t1 DROP a10;
611
ALTER TABLE test.t1 DROP a9;
612
ALTER TABLE test.t1 DROP a8;
613
ALTER TABLE test.t1 DROP a7;
614
ALTER TABLE test.t1 DROP a6;
615
ALTER TABLE test.t1 DROP PRIMARY KEY;
617
SHOW CREATE TABLE test.t1;
622
DROP DATAFILE './table_space/datafile.dat'
624
DROP TABLESPACE ts ENGINE NDB;
625
DROP LOGFILE GROUP lg
628
####################### End section 4 #########################