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
##############################################################
29
# Purpose: To take out some of the test that are already
30
# Covered by other tests. Per Jonas
31
# The new purpose of this test is testing "Alter"
32
# Statements. Therefore the name is changed to
34
# Removed tests include:
35
# Select from disk into memory table
36
# Select from disk into memory table
37
# Create test that loads data, use mysql dump to dump data, drop table,
38
# create table and load from mysql dump.
39
# Use group by asc and dec; Use having; Use order by
40
##############################################################
42
-- source include/have_ndb.inc
43
-- source include/have_innodb.inc
44
-- source include/not_embedded.inc
47
DROP TABLE IF EXISTS test.t1;
48
DROP TABLE IF EXISTS test.t2;
51
############ Test Setup Section #############
52
-- echo **** Test Setup Section ****
53
################## ALTER Tests (Meta data testing) ####################
55
CREATE LOGFILE GROUP lg
56
ADD UNDOFILE './lg_group/undofile.dat'
62
ADD DATAFILE './table_space/datafile.dat'
67
#### Try to ALTER from InnoDB to Cluster Disk Data
69
CREATE TABLE test.t1 (
87
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");
92
SHOW CREATE TABLE test.t1;
93
SELECT * FROM test.t1 ORDER BY a1;
94
ALTER TABLE test.t1 TABLESPACE ts STORAGE DISK ENGINE=NDB;
95
SHOW CREATE TABLE test.t1;
96
# Check column storage
97
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
98
SELECT * FROM test.t1 ORDER BY a1;
101
#### Try to ALTER from MyISAM to Cluster Disk Data
103
CREATE TABLE test.t1 (
104
a1 smallint NOT NULL,
121
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");
126
SHOW CREATE TABLE test.t1;
127
SELECT * FROM test.t1 ORDER BY a1;
128
ALTER TABLE test.t1 TABLESPACE ts STORAGE DISK ENGINE=NDB;
129
SHOW CREATE TABLE test.t1;
130
# Check column storage
131
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
132
SELECT * FROM test.t1 ORDER BY a1;
134
#### Try to ALTER from Cluster Disk Data to InnoDB
136
ALTER TABLE test.t1 ENGINE=InnoDB;
137
SHOW CREATE TABLE test.t1;
138
SELECT * FROM test.t1 ORDER BY a1;
139
ALTER TABLE test.t1 TABLESPACE ts STORAGE DISK ENGINE=NDB;
140
SHOW CREATE TABLE test.t1;
141
# Check column storage
142
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
144
#### Try to ALTER from Cluster Disk Data to MyISAM
146
ALTER TABLE test.t1 ENGINE=MyISAM;
147
SHOW CREATE TABLE test.t1;
150
#### Try to ALTER DD Tables and add columns
152
CREATE TABLE test.t1 (a1 INT PRIMARY KEY) TABLESPACE ts STORAGE DISK ENGINE=NDB;
158
eval insert into test.t1 values($1);
163
SELECT * FROM test.t1 ORDER BY a1;
165
SELECT * FROM information_schema.partitions WHERE table_name= 't1' AND partition_name = 'p0';
167
ALTER TABLE test.t1 ADD a2 FLOAT, ADD a3 DOUBLE;
169
SELECT * FROM information_schema.partitions WHERE table_name= 't1' AND partition_name = 'p0';
175
eval update test.t1 set a2 = $1+1.2345, a3 = $1+20000000.00 where a1 = $1;
180
SELECT * FROM test.t1 ORDER BY a1;
182
ALTER TABLE test.t1 ADD a4 BIT, ADD a5 TINYINT, ADD a6 BIGINT, ADD a7 DATE, ADD a8 TIME;
188
eval update test.t1 set a4 = 0, a5 = 1, a6 = $1+23456, a7 = '2006-1-1',
189
a8 = '07:04:00' where a1 = $1;
194
SELECT a1,a2,a3,hex(a4), a5,a6,a7,a8 FROM test.t1 ORDER BY a1;
196
ALTER TABLE test.t1 ADD a9 DATETIME, ADD a10 TINYTEXT, ADD a11 MEDIUMTEXT, ADD a12 LONGTEXT, ADD a13 TEXT, ADD a14 BLOB;
197
SHOW CREATE TABLE test.t1;
198
# Check column storage
199
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
203
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
204
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
205
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
206
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
213
eval update test.t1 set a9 = '1971-5-28 16:55:03', a10 = 'abc', a11 = 'abcdefg',
214
a12 = 'LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL', a13 = 'Text Field',
215
a14 = @d2 where a1 = $1;
220
SELECT a1, a2,a3,hex(a4),a5,a6,a7,a8,a9,a10,a11,a12,a13 FROM test.t1 ORDER BY a1;
222
#### Try to ALTER DD Tables and add Indexes
224
ALTER TABLE test.t1 ADD INDEX a2_i (a2), ADD INDEX a3_i (a3);
226
SHOW CREATE TABLE test.t1;
228
# Check column storage
229
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
231
ALTER TABLE test.t1 DROP INDEX a2_i;
233
SHOW CREATE TABLE test.t1;
235
# Check column storage
236
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
238
TRUNCATE TABLE test.t1;
240
SHOW CREATE TABLE test.t1;
242
# Check column storage
243
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
245
#### Try to ALTER DD Tables and drop columns
248
ALTER TABLE test.t1 DROP a14;
249
ALTER TABLE test.t1 DROP a13;
250
ALTER TABLE test.t1 DROP a12;
251
ALTER TABLE test.t1 DROP a11;
252
ALTER TABLE test.t1 DROP a10;
253
ALTER TABLE test.t1 DROP a9;
254
ALTER TABLE test.t1 DROP a8;
255
ALTER TABLE test.t1 DROP a7;
256
ALTER TABLE test.t1 DROP a6;
257
ALTER TABLE test.t1 DROP PRIMARY KEY;
259
SHOW CREATE TABLE test.t1;
261
# Check column storage
262
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep 'ST='
267
DROP DATAFILE './table_space/datafile.dat'
269
DROP TABLESPACE ts ENGINE NDB;
270
DROP LOGFILE GROUP lg ENGINE=NDB;
272
####################### End section 4 #########################