1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
--source include/have_ndb.inc
#
# Test adding an index using inplace alter table
#
CREATE TABLE t1 (
a int PRIMARY KEY,
b varchar(32),
c int NOT NULL
) ENGINE = NDB;
INSERT INTO t1 VALUES
(1, "MySQL Server with NDB", 3),
(3, "The third row", 9),
(37, "And of course number 37", 37);
SELECT a FROM t1 WHERE c > 8 ORDER by c;
ALTER TABLE t1 ALGORITHM=INPLACE, ADD INDEX c_idx1(c);
SELECT a FROM t1 WHERE c > 8 ORDER by c;
DROP TABLE t1;
--echo #
--echo # Bug#30766579 ADDING AN INDEX WITH INPLACE GENERATES
--echo # - test adding an index on first pk column using inplace alter
--echo #
CREATE TABLE t1 (
col1 varbinary(80) NOT NULL,
pk2 varchar(46) NOT NULL,
pk1 bigint(20) NOT NULL,
col2 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
col3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
col4 varchar(320) CHARACTER SET utf8 NOT NULL,
col5 varbinary(16) NOT NULL,
col6 int(11) NOT NULL,
col7 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
col8 bigint(20) NOT NULL,
col9 timestamp NULL DEFAULT NULL,
col10 int(11) NOT NULL,
col11 bit(1) NOT NULL,
col12 binary(32) NOT NULL,
col13 bit(1) NOT NULL DEFAULT b'0',
col14 varchar(32) CHARACTER SET utf8 DEFAULT NULL,
col15 bigint(10) DEFAULT NULL,
col16 varchar(3) DEFAULT NULL,
col17 bit(1) NOT NULL DEFAULT b'0',
col18 bit(1) NOT NULL DEFAULT b'0',
col19 varbinary(250) DEFAULT NULL,
col20 varchar(20) DEFAULT NULL,
col21 varchar(3) DEFAULT NULL,
col22 int(11) DEFAULT NULL,
PRIMARY KEY (pk1, pk2),
UNIQUE KEY ix_pk2 (pk2),
KEY ix_t1_col2 (col2),
KEY ix_t1_col3 (col3),
KEY ix_t1_pk1_col20 (pk1,col20),
KEY ix_t1_pk1_col6 (pk1,col6)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
PARTITION BY KEY (pk1);
--echo # Show indexes on t1
SELECT index_name, columns FROM ndbinfo.index_columns
WHERE table_name = 't1' ORDER BY index_name;
ALTER TABLE t1 ALGORITHM = INPLACE, ADD INDEX ix_test (pk1);
--echo # Show that new index 'ix_test' was added to t1
SELECT index_name, columns, index_type FROM ndbinfo.index_columns
WHERE table_name = 't1' AND index_name = 'ix_test' ORDER BY index_name;
DROP TABLE t1;
|
