| 1 |
### create tables and insert/load data |
| 2 |
create table t1(id int NOT NULL PRIMARY KEY, data char(8), other int, |
| 3 |
unique(other)) engine=ndb; |
| 4 |
create table t2(id int, data char(8), id2 binary(80), primary key (id, id2)) |
| 5 |
engine=ndb; |
| 6 |
create table t3(id int NOT NULL PRIMARY KEY, data char(8)) engine=ndb; |
| 7 |
create table t4(id int NOT NULL PRIMARY KEY, data char(8)) engine=ndb; |
| 8 |
create table t5(id int primary key, data int, other int, unique(other)) |
| 9 |
engine=ndb; |
| 10 |
create table t6(id int primary key, data int, other int, unique(other)) |
| 11 |
engine=ndb; |
| 12 |
insert into t1 values (1,"data",1), (2,"data",2), (3,"data",3), (4,"data",4), |
| 13 |
(5,"data",5), (6,"data",6),(7,"data",7), (8,"data",8); |
| 14 |
load data local infile 'suite/ndb/data/table_data10000.dat' into table t2 |
| 15 |
fields terminated by ' ' lines terminated by '\n' (id, @data) set data = @data, |
| 16 |
id2 = repeat(@data,10); |
| 17 |
load data local infile 'suite/ndb/data/table_data10000.dat' into table t3 |
| 18 |
fields terminated by ' ' lines terminated by '\n'; |
| 19 |
load data local infile 'suite/ndb/data/table_data10000.dat' into table t4 |
| 20 |
fields terminated by ' ' lines terminated by '\n'; |
| 21 |
insert into t5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), |
| 22 |
(6,6,6),(7,7,7), (8,8,8); |
| 23 |
insert into t6 values (1,1,1), (2,2,2), (3,3,3), |
| 24 |
(4,4,4), (5,5,5), (6,6,6),(7,7,7), (8,8,8); |
| 25 |
### verify that the number of preferred primary is equal on each |
| 26 |
node (2 nodes) |
| 27 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 28 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 29 |
and root_name like '%t1' and type = 2 group by preferred_primary; |
| 30 |
preferred_primary count(*) |
| 31 |
1 2 |
| 32 |
2 2 |
| 33 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 34 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 35 |
and root_name like '%t2' and type = 2 group by preferred_primary; |
| 36 |
preferred_primary count(*) |
| 37 |
1 2 |
| 38 |
2 2 |
| 39 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 40 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 41 |
and root_name like '%t3' and type = 2 group by preferred_primary; |
| 42 |
preferred_primary count(*) |
| 43 |
1 2 |
| 44 |
2 2 |
| 45 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 46 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 47 |
and root_name like '%t4' and type = 2 group by preferred_primary; |
| 48 |
preferred_primary count(*) |
| 49 |
1 2 |
| 50 |
2 2 |
| 51 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 52 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 53 |
and root_name like '%t5' and type = 2 group by preferred_primary; |
| 54 |
preferred_primary count(*) |
| 55 |
1 2 |
| 56 |
2 2 |
| 57 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 58 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 59 |
and root_name like '%t6' and type = 2 group by preferred_primary; |
| 60 |
preferred_primary count(*) |
| 61 |
1 2 |
| 62 |
2 2 |
| 63 |
### verify that the Unique hash index fragments are balanced across all |
| 64 |
data nodes (2 nodes) |
| 65 |
select fragment_num,parent_fq_name,type,node_id from ndbinfo.memory_per_fragment |
| 66 |
where type like 'Unique hash index' and parent_fq_name like '%t1%' |
| 67 |
order by fragment_num; |
| 68 |
fragment_num parent_fq_name type node_id |
| 69 |
0 test/def/t1 Unique hash index 1 |
| 70 |
0 test/def/t1 Unique hash index 2 |
| 71 |
1 test/def/t1 Unique hash index 1 |
| 72 |
1 test/def/t1 Unique hash index 2 |
| 73 |
2 test/def/t1 Unique hash index 1 |
| 74 |
2 test/def/t1 Unique hash index 2 |
| 75 |
3 test/def/t1 Unique hash index 1 |
| 76 |
3 test/def/t1 Unique hash index 2 |
| 77 |
### Create nodegroup for nodes 3 and 4 |
| 78 |
Nodegroup 1 created |
| 79 |
### Create one more table and insert data |
| 80 |
create table t7(id int primary key, data int, other int, unique(other)) |
| 81 |
engine=ndb; |
| 82 |
insert into t7 values (1,1,1), (2,2,2), (3,3,3), |
| 83 |
(4,4,4), (5,5,5), (6,6,6),(7,7,7), (8,8,8); |
| 84 |
### Cluster running after adding two data nodes |
| 85 |
ndbd(s) |
| 86 |
id=1 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 0, *) |
| 87 |
id=2 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 0) |
| 88 |
id=3 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 1) |
| 89 |
id=4 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 1) |
| 90 |
ndb_mgmd(s) |
| 91 |
id=5 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 92 |
mysqld(s) |
| 93 |
id=16 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 94 |
id=32 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 95 |
id=48 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 96 |
id=49 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 97 |
id=63 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 98 |
id=127 LOCALHOST (MYSQL_VERSION NDB_VERSION) |
| 99 |
id=192 (not connected, accepting connect from localhost) |
| 100 |
id=228 (not connected, accepting connect from localhost) |
| 101 |
id=229 (not connected, accepting connect from localhost) |
| 102 |
id=230 (not connected, accepting connect from localhost) |
| 103 |
id=231 (not connected, accepting connect from localhost) |
| 104 |
id=232 (not connected, accepting connect from localhost) |
| 105 |
id=233 (not connected, accepting connect from localhost) |
| 106 |
id=255 (not connected, accepting connect from localhost) |
| 107 |
### Reorganize partitions |
| 108 |
ALTER TABLE t1 ALGORITHM=INPLACE, REORGANIZE PARTITION; |
| 109 |
ALTER TABLE t2 ALGORITHM=INPLACE, REORGANIZE PARTITION; |
| 110 |
ALTER TABLE t3 ALGORITHM=INPLACE, REORGANIZE PARTITION; |
| 111 |
ALTER TABLE t4 ALGORITHM=COPY, REORGANIZE PARTITION; |
| 112 |
ALTER TABLE t5 ALGORITHM=COPY, REORGANIZE PARTITION; |
| 113 |
ALTER TABLE t6 ALGORITHM=COPY, REORGANIZE PARTITION; |
| 114 |
### verify that the number of preferred primary is equal on each node |
| 115 |
(2 nodes +2 nodes online) |
| 116 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 117 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 118 |
and root_name like '%t1' and type = 2 group by preferred_primary; |
| 119 |
preferred_primary count(*) |
| 120 |
1 2 |
| 121 |
2 2 |
| 122 |
3 2 |
| 123 |
4 2 |
| 124 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 125 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 126 |
and root_name like '%t2' and type = 2 group by preferred_primary; |
| 127 |
preferred_primary count(*) |
| 128 |
1 2 |
| 129 |
2 2 |
| 130 |
3 2 |
| 131 |
4 2 |
| 132 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 133 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 134 |
and root_name like '%t3' and type = 2 group by preferred_primary; |
| 135 |
preferred_primary count(*) |
| 136 |
1 2 |
| 137 |
2 2 |
| 138 |
3 2 |
| 139 |
4 2 |
| 140 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 141 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 142 |
and root_name like '%t4' and type = 2 group by preferred_primary; |
| 143 |
preferred_primary count(*) |
| 144 |
1 2 |
| 145 |
3 2 |
| 146 |
2 2 |
| 147 |
4 2 |
| 148 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 149 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 150 |
and root_name like '%t5' and type = 2 group by preferred_primary; |
| 151 |
preferred_primary count(*) |
| 152 |
1 2 |
| 153 |
3 2 |
| 154 |
2 2 |
| 155 |
4 2 |
| 156 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 157 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 158 |
and root_name like '%t6' and type = 2 group by preferred_primary; |
| 159 |
preferred_primary count(*) |
| 160 |
1 2 |
| 161 |
3 2 |
| 162 |
2 2 |
| 163 |
4 2 |
| 164 |
select preferred_primary, count(*) from ndbinfo.table_fragments as tf join |
| 165 |
ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id |
| 166 |
and root_name like '%t7' and type = 2 group by preferred_primary; |
| 167 |
preferred_primary count(*) |
| 168 |
1 2 |
| 169 |
3 2 |
| 170 |
2 2 |
| 171 |
4 2 |
| 172 |
### verify that the Unique hash index fragments are balanced across all |
| 173 |
data nodes (2 nodes +2 nodes online) |
| 174 |
select fragment_num,parent_fq_name,type,node_id from ndbinfo.memory_per_fragment |
| 175 |
where type like 'Unique hash index' and parent_fq_name like '%t1%' |
| 176 |
order by fragment_num, node_id; |
| 177 |
fragment_num parent_fq_name type node_id |
| 178 |
0 test/def/t1 Unique hash index 1 |
| 179 |
0 test/def/t1 Unique hash index 2 |
| 180 |
1 test/def/t1 Unique hash index 1 |
| 181 |
1 test/def/t1 Unique hash index 2 |
| 182 |
2 test/def/t1 Unique hash index 1 |
| 183 |
2 test/def/t1 Unique hash index 2 |
| 184 |
3 test/def/t1 Unique hash index 1 |
| 185 |
3 test/def/t1 Unique hash index 2 |
| 186 |
4 test/def/t1 Unique hash index 3 |
| 187 |
4 test/def/t1 Unique hash index 4 |
| 188 |
5 test/def/t1 Unique hash index 3 |
| 189 |
5 test/def/t1 Unique hash index 4 |
| 190 |
6 test/def/t1 Unique hash index 3 |
| 191 |
6 test/def/t1 Unique hash index 4 |
| 192 |
7 test/def/t1 Unique hash index 3 |
| 193 |
7 test/def/t1 Unique hash index 4 |
| 194 |
drop table t1,t2,t3,t4,t5,t6,t7; |
| 195 |
### Drop nodegroup with "new" nodes |
| 196 |
Drop Node Group 1 done |