Debian Codegraph @

mysql-test/suite/ndb/r/ndb_reorganize_partition.result

raw
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 4
32 2 4
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 4
38 2 4
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 4
44 2 4
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 4
50 2 4
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 4
56 2 4
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 4
62 2 4
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 4 test/def/t1 Unique hash index 1
78 4 test/def/t1 Unique hash index 2
79 5 test/def/t1 Unique hash index 1
80 5 test/def/t1 Unique hash index 2
81 6 test/def/t1 Unique hash index 1
82 6 test/def/t1 Unique hash index 2
83 7 test/def/t1 Unique hash index 1
84 7 test/def/t1 Unique hash index 2
85 ### Create nodegroup for nodes 3 and 4
86 Nodegroup 1 created
87 ### Create one more table and insert data
88 create table t7(id int primary key, data int, other int, unique(other))
89 engine=ndb;
90 insert into t7 values (1,1,1), (2,2,2), (3,3,3),
91 (4,4,4), (5,5,5), (6,6,6),(7,7,7), (8,8,8);
92 ### Cluster running after adding two data nodes
93 ndbd(s)
94 id=1 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 0, *)
95 id=2 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 0)
96 id=3 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 1)
97 id=4 LOCALHOST (MYSQL_VERSION NDB_VERSION, Nodegroup: 1)
98 ndb_mgmd(s)
99 id=5 LOCALHOST (MYSQL_VERSION NDB_VERSION)
100 mysqld(s)
101 id=16 LOCALHOST (MYSQL_VERSION NDB_VERSION)
102 id=32 LOCALHOST (MYSQL_VERSION NDB_VERSION)
103 id=48 LOCALHOST (MYSQL_VERSION NDB_VERSION)
104 id=49 LOCALHOST (MYSQL_VERSION NDB_VERSION)
105 id=63 LOCALHOST (MYSQL_VERSION NDB_VERSION)
106 id=127 LOCALHOST (MYSQL_VERSION NDB_VERSION)
107 id=192 (not connected, accepting connect from localhost)
108 id=228 (not connected, accepting connect from localhost)
109 id=229 (not connected, accepting connect from localhost)
110 id=230 (not connected, accepting connect from localhost)
111 id=231 (not connected, accepting connect from localhost)
112 id=232 (not connected, accepting connect from localhost)
113 id=233 (not connected, accepting connect from localhost)
114 id=255 (not connected, accepting connect from localhost)
115 ### Reorganize partitions
116 ALTER TABLE t1 ALGORITHM=INPLACE, REORGANIZE PARTITION;
117 ALTER TABLE t2 ALGORITHM=INPLACE, REORGANIZE PARTITION;
118 ALTER TABLE t3 ALGORITHM=INPLACE, REORGANIZE PARTITION;
119 ALTER TABLE t4 ALGORITHM=COPY, REORGANIZE PARTITION;
120 ALTER TABLE t5 ALGORITHM=COPY, REORGANIZE PARTITION;
121 ALTER TABLE t6 ALGORITHM=COPY, REORGANIZE PARTITION;
122 ### verify that the number of preferred primary is equal on each node
123 (2 nodes +2 nodes online)
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 '%t1' and type = 2 group by preferred_primary;
127 preferred_primary count(*)
128 1 4
129 2 4
130 3 4
131 4 4
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 '%t2' and type = 2 group by preferred_primary;
135 preferred_primary count(*)
136 1 4
137 2 4
138 3 4
139 4 4
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 '%t3' and type = 2 group by preferred_primary;
143 preferred_primary count(*)
144 1 4
145 2 4
146 3 4
147 4 4
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 '%t4' and type = 2 group by preferred_primary;
151 preferred_primary count(*)
152 1 4
153 3 4
154 2 4
155 4 4
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 '%t5' and type = 2 group by preferred_primary;
159 preferred_primary count(*)
160 1 4
161 3 4
162 2 4
163 4 4
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 '%t6' and type = 2 group by preferred_primary;
167 preferred_primary count(*)
168 1 4
169 3 4
170 2 4
171 4 4
172 select preferred_primary, count(*) from ndbinfo.table_fragments as tf join
173 ndbinfo.dict_obj_tree as dot where tf.table_id = dot.id
174 and root_name like '%t7' and type = 2 group by preferred_primary;
175 preferred_primary count(*)
176 1 4
177 3 4
178 2 4
179 4 4
180 ### verify that the Unique hash index fragments are balanced across all
181 data nodes (2 nodes +2 nodes online)
182 select fragment_num,parent_fq_name,type,node_id from ndbinfo.memory_per_fragment
183 where type like 'Unique hash index' and parent_fq_name like '%t1%'
184 order by fragment_num, node_id;
185 fragment_num parent_fq_name type node_id
186 0 test/def/t1 Unique hash index 1
187 0 test/def/t1 Unique hash index 2
188 1 test/def/t1 Unique hash index 1
189 1 test/def/t1 Unique hash index 2
190 2 test/def/t1 Unique hash index 1
191 2 test/def/t1 Unique hash index 2
192 3 test/def/t1 Unique hash index 1
193 3 test/def/t1 Unique hash index 2
194 4 test/def/t1 Unique hash index 1
195 4 test/def/t1 Unique hash index 2
196 5 test/def/t1 Unique hash index 1
197 5 test/def/t1 Unique hash index 2
198 6 test/def/t1 Unique hash index 1
199 6 test/def/t1 Unique hash index 2
200 7 test/def/t1 Unique hash index 1
201 7 test/def/t1 Unique hash index 2
202 8 test/def/t1 Unique hash index 3
203 8 test/def/t1 Unique hash index 4
204 9 test/def/t1 Unique hash index 3
205 9 test/def/t1 Unique hash index 4
206 10 test/def/t1 Unique hash index 3
207 10 test/def/t1 Unique hash index 4
208 11 test/def/t1 Unique hash index 3
209 11 test/def/t1 Unique hash index 4
210 12 test/def/t1 Unique hash index 3
211 12 test/def/t1 Unique hash index 4
212 13 test/def/t1 Unique hash index 3
213 13 test/def/t1 Unique hash index 4
214 14 test/def/t1 Unique hash index 3
215 14 test/def/t1 Unique hash index 4
216 15 test/def/t1 Unique hash index 3
217 15 test/def/t1 Unique hash index 4
218 drop table t1,t2,t3,t4,t5,t6,t7;
219 ### Drop nodegroup with "new" nodes
220 Drop Node Group 1 done