Debian Codegraph @

mysql-test/suite/ndb/r/ndb_reorganize_partition_classic_fragmentation_off.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 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