| 1 |
-- source include/have_ndb.inc |
| 2 |
|
| 3 |
--disable_query_log |
| 4 |
call mtr.add_suppression("X Protocol won't be accessible"); |
| 5 |
call mtr.add_suppression("Do you already have another mysqld server running"); |
| 6 |
call mtr.add_suppression("X Plugin can't bind to it. Skipping"); |
| 7 |
--enable_query_log |
| 8 |
|
| 9 |
CREATE TABLE t1 ( |
| 10 |
a char(10) CHARSET latin1 NOT NULL, |
| 11 |
e varbinary(3000) not null, |
| 12 |
b int, |
| 13 |
c blob, |
| 14 |
d varbinary(26000), |
| 15 |
primary key(e,a), |
| 16 |
unique key (b) |
| 17 |
) comment='NDB_TABLE=FULLY_REPLICATED=1' ENGINE=ndbcluster; |
| 18 |
|
| 19 |
use ndbinfo; |
| 20 |
select tab_partitions, tab_fragments from table_distribution_status as tds join dict_obj_info as doi where tds.table_id = doi.id and doi.fq_name = 'test/def/t1'; |
| 21 |
select partition_id, fragment_id, partition_order, log_part_id, preferred_primary from table_fragments as tf join dict_obj_info as doi where tf.table_id = doi.id and doi.fq_name = 'test/def/t1' order by fragment_id; |
| 22 |
select fragment_id, replica_node_id from table_replicas as tr join dict_obj_info as doi where tr.table_id = doi.id and doi.fq_name = 'test/def/t1' order by fragment_id, replica_node_id; |
| 23 |
select read_backup, fully_replicated from table_info as ti join dict_obj_info as doi where ti.table_id = doi.id and doi.fq_name = 'test/def/t1'; |
| 24 |
use test; |
| 25 |
|
| 26 |
let $count = 24; |
| 27 |
while ($count) |
| 28 |
{ |
| 29 |
eval insert into t1 values ('$count', REPEAT('1', 3000), $count, REPEAT('$count', 3000), REPEAT('A', 26000)); |
| 30 |
dec $count; |
| 31 |
} |
| 32 |
|
| 33 |
# Save element count for each partition by using first nodes fragments. |
| 34 |
create temporary table t1_frag_size ( |
| 35 |
fragment_num int primary key, |
| 36 |
fixed_elem_count int |
| 37 |
); |
| 38 |
|
| 39 |
insert into t1_frag_size select fragment_num, fixed_elem_count from `ndbinfo`.`memory_per_fragment` |
| 40 |
where fq_name = 'test/def/t1' and node_id = 1; |
| 41 |
|
| 42 |
set @numpart = (select count(*) from t1_frag_size); |
| 43 |
|
| 44 |
# Verify that all fragments for a partition have same element count. |
| 45 |
# Any mismatch will be printed. |
| 46 |
select mpf.node_id, mpf.fragment_num, mpf.fixed_elem_count, tfs.fragment_num, tfs.fixed_elem_count |
| 47 |
from `ndbinfo`.`memory_per_fragment` mpf |
| 48 |
join t1_frag_size tfs |
| 49 |
on tfs.fragment_num = mpf.fragment_num mod @numpart |
| 50 |
where mpf.fq_name = 'test/def/t1' |
| 51 |
and mpf.fixed_elem_count <> tfs.fixed_elem_count |
| 52 |
order by tfs.fragment_num, mpf.node_id, mpf.fragment_num; |
| 53 |
|
| 54 |
create temporary table counters_at_startup |
| 55 |
select counter_name, sum(val) as val |
| 56 |
from ndbinfo.counters |
| 57 |
group by counter_name; |
| 58 |
|
| 59 |
select a,b,LENGTH(c) from t1 where a = '1' and e = REPEAT('1', 3000); |
| 60 |
select a,b,LENGTH(c) from t1 where a = '2' and e = REPEAT('1', 3000); |
| 61 |
select a,b,LENGTH(c) from t1 where a = '3' and e = REPEAT('1', 3000); |
| 62 |
select a,b,LENGTH(c) from t1 where a = '4' and e = REPEAT('1', 3000); |
| 63 |
select a,b,LENGTH(c) from t1 where a = '5' and e = REPEAT('1', 3000); |
| 64 |
select a,b,LENGTH(c) from t1 where a = '6' and e = REPEAT('1', 3000); |
| 65 |
select a,b,LENGTH(c) from t1 where a = '7' and e = REPEAT('1', 3000); |
| 66 |
select a,b,LENGTH(c) from t1 where a = '8' and e = REPEAT('1', 3000); |
| 67 |
|
| 68 |
--sorted_result |
| 69 |
select a,b,LENGTH(c) from t1 where a in ('1', '2', '3', '4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 70 |
--sorted_result |
| 71 |
select a,b,LENGTH(c) from t1 where a in ('2', '3', '4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 72 |
--sorted_result |
| 73 |
select a,b,LENGTH(c) from t1 where a in ('3', '4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 74 |
--sorted_result |
| 75 |
select a,b,LENGTH(c) from t1 where a in ('4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 76 |
--sorted_result |
| 77 |
select a,b,LENGTH(c) from t1 where a in ('5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 78 |
--sorted_result |
| 79 |
select a,b,LENGTH(c) from t1 where a in ('6', '7', '8') and e = REPEAT('1', 3000); |
| 80 |
--sorted_result |
| 81 |
select a,b,LENGTH(c) from t1 where a in ('7', '8') and e = REPEAT('1', 3000); |
| 82 |
|
| 83 |
select s1.counter_name, s2.val - s1.val as diff |
| 84 |
from counters_at_startup s1, |
| 85 |
(select counter_name, sum(val) as val |
| 86 |
from ndbinfo.counters |
| 87 |
group by counter_name) s2 |
| 88 |
where s1.counter_name = s2.counter_name |
| 89 |
and ( s1.counter_name = 'LOCAL_READS' or s1.counter_name = 'READS' ) |
| 90 |
order by 1; |
| 91 |
drop table counters_at_startup; |
| 92 |
|
| 93 |
# Due to yet unfiled bug: |
| 94 |
# PartitionBalance is wrong for unique index and blob table. |
| 95 |
# Remove this comment and re-record when indexes and blob table have: |
| 96 |
# PartitionBalance: FOR_RP_BY_LDM |
| 97 |
# Table options: readbackup, fullyreplicated |
| 98 |
# --let ndb_desc_opts= -b -i -d test t1 |
| 99 |
# --source suite/ndb/include/ndb_desc_print.inc |
| 100 |
# |
| 101 |
# Explictly check table options include fully_replicated |
| 102 |
# Remove this explicit test than complete ndb_desc above is correct. |
| 103 |
let $ndb_exec_command= $NDB_DESC -b -d test t1; |
| 104 |
let $ndb_exec_select= output_line like 'Table options:%' |
| 105 |
or output_line like '-- % --'; |
| 106 |
let $ndb_exec_replace= /([ _])[0-9][0-9]*/\1#/; |
| 107 |
source suite/ndb/include/ndb_exec.inc; |
| 108 |
|
| 109 |
## Create nodegroup for "new" nodes 3,4 |
| 110 |
--echo Creating nodegroup |
| 111 |
--exec $NDB_MGM -e "create nodegroup 3,4" |
| 112 |
|
| 113 |
alter table t1 algorithm=inplace, reorganize partition; |
| 114 |
|
| 115 |
use ndbinfo; |
| 116 |
select tab_partitions, tab_fragments from table_distribution_status as tds join dict_obj_info as doi where tds.table_id = doi.id and doi.fq_name = 'test/def/t1'; |
| 117 |
select partition_id, fragment_id, log_part_id, partition_order, preferred_primary from table_fragments as tf join dict_obj_info as doi where tf.table_id = doi.id and doi.fq_name = 'test/def/t1' order by fragment_id; |
| 118 |
select fragment_id, replica_node_id from table_replicas as tr join dict_obj_info as doi where tr.table_id = doi.id and doi.fq_name = 'test/def/t1' order by fragment_id, replica_node_id; |
| 119 |
use test; |
| 120 |
|
| 121 |
set global ndb_data_node_neighbour = 1; |
| 122 |
create table c as select node_id, fragment_num, tot_frag_scans |
| 123 |
from `ndbinfo`.`operations_per_fragment` |
| 124 |
where fq_name = 'test/def/t1'; |
| 125 |
select count(*) from t1; |
| 126 |
select distinct o.node_id |
| 127 |
from `ndbinfo`.`operations_per_fragment` o join c |
| 128 |
on o.node_id = c.node_id and o.fragment_num = c.fragment_num |
| 129 |
where fq_name = 'test/def/t1' and o.tot_frag_scans > c.tot_frag_scans; |
| 130 |
drop table c; |
| 131 |
|
| 132 |
set global ndb_data_node_neighbour = 2; |
| 133 |
create table c as select node_id, fragment_num, tot_frag_scans |
| 134 |
from `ndbinfo`.`operations_per_fragment` |
| 135 |
where fq_name = 'test/def/t1'; |
| 136 |
select count(*) from t1; |
| 137 |
select distinct o.node_id |
| 138 |
from `ndbinfo`.`operations_per_fragment` o join c |
| 139 |
on o.node_id = c.node_id and o.fragment_num = c.fragment_num |
| 140 |
where fq_name = 'test/def/t1' and o.tot_frag_scans > c.tot_frag_scans; |
| 141 |
drop table c; |
| 142 |
|
| 143 |
set global ndb_data_node_neighbour = 3; |
| 144 |
create table c as select node_id, fragment_num, tot_frag_scans |
| 145 |
from `ndbinfo`.`operations_per_fragment` |
| 146 |
where fq_name = 'test/def/t1'; |
| 147 |
--sorted_result |
| 148 |
select a,b,LENGTH(c) from t1 where a = 1; |
| 149 |
select distinct o.node_id |
| 150 |
from `ndbinfo`.`operations_per_fragment` o join c |
| 151 |
on o.node_id = c.node_id and o.fragment_num = c.fragment_num |
| 152 |
where fq_name = 'test/def/t1' and o.tot_frag_scans > c.tot_frag_scans; |
| 153 |
drop table c; |
| 154 |
|
| 155 |
set global ndb_data_node_neighbour = 4; |
| 156 |
create table c as select node_id, fragment_num, tot_frag_scans |
| 157 |
from `ndbinfo`.`operations_per_fragment` |
| 158 |
where fq_name = 'test/def/t1'; |
| 159 |
--sorted_result |
| 160 |
select a,b,LENGTH(c) from t1; |
| 161 |
select distinct o.node_id |
| 162 |
from `ndbinfo`.`operations_per_fragment` o join c |
| 163 |
on o.node_id = c.node_id and o.fragment_num = c.fragment_num |
| 164 |
where fq_name = 'test/def/t1' and o.tot_frag_scans > c.tot_frag_scans; |
| 165 |
drop table c; |
| 166 |
|
| 167 |
set global ndb_data_node_neighbour = 0; |
| 168 |
|
| 169 |
# Verify that all fragments for a partition have same element count. |
| 170 |
# Any mismatch will be printed. |
| 171 |
select mpf.node_id, mpf.fragment_num, mpf.fixed_elem_count, tfs.fragment_num, tfs.fixed_elem_count |
| 172 |
from `ndbinfo`.`memory_per_fragment` mpf |
| 173 |
join t1_frag_size tfs |
| 174 |
on tfs.fragment_num = mpf.fragment_num mod @numpart |
| 175 |
where mpf.fq_name = 'test/def/t1' |
| 176 |
and mpf.fixed_elem_count <> tfs.fixed_elem_count |
| 177 |
order by tfs.fragment_num, mpf.node_id, mpf.fragment_num; |
| 178 |
|
| 179 |
create temporary table counters_at_startup |
| 180 |
select counter_name, sum(val) as val |
| 181 |
from ndbinfo.counters |
| 182 |
group by counter_name; |
| 183 |
|
| 184 |
select a,b,LENGTH(c) from t1 where a = '1' and e = REPEAT('1', 3000); |
| 185 |
select a,b,LENGTH(c) from t1 where a = '2' and e = REPEAT('1', 3000); |
| 186 |
select a,b,LENGTH(c) from t1 where a = '3' and e = REPEAT('1', 3000); |
| 187 |
select a,b,LENGTH(c) from t1 where a = '4' and e = REPEAT('1', 3000); |
| 188 |
select a,b,LENGTH(c) from t1 where a = '5' and e = REPEAT('1', 3000); |
| 189 |
select a,b,LENGTH(c) from t1 where a = '6' and e = REPEAT('1', 3000); |
| 190 |
select a,b,LENGTH(c) from t1 where a = '7' and e = REPEAT('1', 3000); |
| 191 |
select a,b,LENGTH(c) from t1 where a = '8' and e = REPEAT('1', 3000); |
| 192 |
|
| 193 |
--sorted_result |
| 194 |
select a,b,LENGTH(c) from t1 where a in ('1', '2', '3', '4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 195 |
--sorted_result |
| 196 |
select a,b,LENGTH(c) from t1 where a in ('2', '3', '4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 197 |
--sorted_result |
| 198 |
select a,b,LENGTH(c) from t1 where a in ('3', '4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 199 |
--sorted_result |
| 200 |
select a,b,LENGTH(c) from t1 where a in ('4', '5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 201 |
--sorted_result |
| 202 |
select a,b,LENGTH(c) from t1 where a in ('5', '6', '7', '8') and e = REPEAT('1', 3000); |
| 203 |
--sorted_result |
| 204 |
select a,b,LENGTH(c) from t1 where a in ('6', '7', '8') and e = REPEAT('1', 3000); |
| 205 |
--sorted_result |
| 206 |
select a,b,LENGTH(c) from t1 where a in ('7', '8') and e = REPEAT('1', 3000); |
| 207 |
|
| 208 |
select s1.counter_name, s2.val - s1.val as diff |
| 209 |
from counters_at_startup s1, |
| 210 |
(select counter_name, sum(val) as val |
| 211 |
from ndbinfo.counters |
| 212 |
group by counter_name) s2 |
| 213 |
where s1.counter_name = s2.counter_name |
| 214 |
and ( s1.counter_name = 'LOCAL_READS' or s1.counter_name = 'READS' ) |
| 215 |
order by 1; |
| 216 |
drop table counters_at_startup; |
| 217 |
|
| 218 |
# Verify that all fragments for a partition have same element count. |
| 219 |
# Any mismatch will be printed. |
| 220 |
select mpf.node_id, mpf.fragment_num, mpf.fixed_elem_count, tfs.fragment_num, tfs.fixed_elem_count |
| 221 |
from `ndbinfo`.`memory_per_fragment` mpf |
| 222 |
join t1_frag_size tfs |
| 223 |
on tfs.fragment_num = mpf.fragment_num mod @numpart |
| 224 |
where mpf.fq_name = 'test/def/t1' |
| 225 |
and mpf.fixed_elem_count <> tfs.fixed_elem_count |
| 226 |
order by tfs.fragment_num, mpf.node_id, mpf.fragment_num; |
| 227 |
|
| 228 |
# Due to Bug#13714258 UNIQUE INDEXES NOT REORGANISED BY ONLINE TABLE REORG |
| 229 |
# FragmentCount and HashMap are wrong for t1/b$unique. |
| 230 |
# Remove this comment and re-record when t1/b$unique have: |
| 231 |
# FragmentCount: 8 |
| 232 |
# HashMap: DEFAULT-HASHMAP-3840-8 |
| 233 |
# Table options: readbackup, fullyreplicated |
| 234 |
# --let ndb_desc_opts= -b -i -d test t1 |
| 235 |
# --source suite/ndb/include/ndb_desc_print.inc |
| 236 |
# |
| 237 |
# Explictly check table options include fully_replicated |
| 238 |
# Remove this explicit test than complete ndb_desc above is correct. |
| 239 |
let $ndb_exec_command= $NDB_DESC -b -d test t1; |
| 240 |
let $ndb_exec_select= output_line like 'Table options:%' |
| 241 |
or output_line like '-- % --'; |
| 242 |
let $ndb_exec_replace= /([ _])[0-9][0-9]*/\1#/; |
| 243 |
source suite/ndb/include/ndb_exec.inc; |
| 244 |
|
| 245 |
## Create nodegroup for "new" nodes 5,6 |
| 246 |
--echo Creating nodegroup |
| 247 |
--exec $NDB_MGM -e "create nodegroup 5,6" |
| 248 |
|
| 249 |
## Create nodegroup for "new" nodes 7,8 |
| 250 |
--echo Creating nodegroup |
| 251 |
--exec $NDB_MGM -e "create nodegroup 7,8" |
| 252 |
|
| 253 |
alter table t1 algorithm=inplace, reorganize partition; |
| 254 |
|
| 255 |
use ndbinfo; |
| 256 |
select tab_partitions, tab_fragments from table_distribution_status as tds join dict_obj_info as doi where tds.table_id = doi.id and doi.fq_name = 'test/def/t1'; |
| 257 |
select partition_id, fragment_id, partition_order, log_part_id, preferred_primary from table_fragments as tf join dict_obj_info as doi where tf.table_id = doi.id and doi.fq_name = 'test/def/t1' order by fragment_id; |
| 258 |
select fragment_id, replica_node_id from table_replicas as tr join dict_obj_info as doi where tr.table_id = doi.id and doi.fq_name = 'test/def/t1' order by fragment_id, replica_node_id; |
| 259 |
use test; |
| 260 |
|
| 261 |
# Verify that all fragments for a partition have same element count. |
| 262 |
# Any mismatch will be printed. |
| 263 |
select mpf.node_id, mpf.fragment_num, mpf.fixed_elem_count, tfs.fragment_num, tfs.fixed_elem_count |
| 264 |
from `ndbinfo`.`memory_per_fragment` mpf |
| 265 |
join t1_frag_size tfs |
| 266 |
on tfs.fragment_num = mpf.fragment_num mod @numpart |
| 267 |
where mpf.fq_name = 'test/def/t1' |
| 268 |
and mpf.fixed_elem_count <> tfs.fixed_elem_count |
| 269 |
order by tfs.fragment_num, mpf.node_id, mpf.fragment_num; |
| 270 |
|
| 271 |
drop table t1; |
| 272 |
|
| 273 |
# Drop nodegroups to return cluster to initial state |
| 274 |
--echo Dropping nodegroups |
| 275 |
--exec $NDB_MGM -e "drop nodegroup 3" |
| 276 |
--exec $NDB_MGM -e "drop nodegroup 2" |
| 277 |
--exec $NDB_MGM -e "drop nodegroup 1" |