| 1 |
--result_format 2 |
| 2 |
--source include/have_ndb.inc |
| 3 |
|
| 4 |
SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE, |
| 5 |
PLUGIN_LIBRARY,PLUGIN_LIBRARY_VERSION,PLUGIN_AUTHOR,PLUGIN_DESCRIPTION |
| 6 |
FROM information_schema.plugins WHERE PLUGIN_NAME = 'ndbinfo'; |
| 7 |
|
| 8 |
## Creation of temporary tables should not be supported by NDBINFO engine |
| 9 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 10 |
CREATE TEMPORARY TABLE `t1` ( |
| 11 |
`dummy` INT UNSIGNED |
| 12 |
) ENGINE=NDBINFO; |
| 13 |
|
| 14 |
USE ndbinfo; |
| 15 |
|
| 16 |
# Current usage may vary. Therefor the test only checks that if falls in the |
| 17 |
# <0%,5%> range. |
| 18 |
SELECT node_id, memory_type, used>0 AND used < total/20 AS used_ok, |
| 19 |
used_pages>0 AND used_pages < total_pages/20 AS used_pages_ok, |
| 20 |
total, total_pages |
| 21 |
FROM ndbinfo.memoryusage WHERE memory_type="Long message buffer" |
| 22 |
ORDER BY node_id; |
| 23 |
|
| 24 |
# Current usage may vary. Therefor the test only checks that if falls in the |
| 25 |
# <0%,5%> range. |
| 26 |
SELECT node_id, block_number, block_instance, pool_name, |
| 27 |
used>0 AND used < total/20 AS used_ok, |
| 28 |
total, high>=used AND high < total/20 AS high_ok, entry_size, config_param1, |
| 29 |
config_param2, config_param3, config_param4 |
| 30 |
FROM ndbinfo.ndb$pools WHERE pool_name="Long message buffer" |
| 31 |
ORDER BY node_id; |
| 32 |
|
| 33 |
SHOW CREATE TABLE ndb$tables; |
| 34 |
|
| 35 |
SELECT table_id, table_name, comment FROM ndb$tables; |
| 36 |
SELECT COUNT(*) FROM ndb$tables; |
| 37 |
SELECT * FROM ndb$tables WHERE table_id = 2; |
| 38 |
SELECT table_id, table_name, comment FROM ndb$tables WHERE table_id > 5; |
| 39 |
SELECT * FROM ndb$tables WHERE table_name = 'LOGDESTINATION'; |
| 40 |
# Show a simple join is working |
| 41 |
SELECT COUNT(*) FROM ndb$tables t, ndb$columns c |
| 42 |
WHERE t.table_id = c.table_id AND |
| 43 |
t.table_id in (1,2,3,4,5,6); |
| 44 |
|
| 45 |
SELECT table_id, table_name, comment from ndb$tables |
| 46 |
WHERE table_id > 2 AND table_id <= 5 ORDER BY table_id; |
| 47 |
SELECT table_id FROM ndb$tables WHERE table_id = 2 ORDER BY table_name; |
| 48 |
SELECT table_id, table_name FROM ndb$tables ORDER BY table_name; |
| 49 |
|
| 50 |
SELECT table_id, column_id, column_name FROM ndb$columns LIMIT 7; |
| 51 |
|
| 52 |
--error ER_OPEN_AS_READONLY |
| 53 |
UPDATE ndb$tables SET table_id=2 WHERE table_id=3; |
| 54 |
|
| 55 |
--error ER_OPEN_AS_READONLY |
| 56 |
UPDATE ndb$tables SET table_id=9 WHERE 1=0; |
| 57 |
|
| 58 |
--error ER_OPEN_AS_READONLY |
| 59 |
UPDATE ndb$tables SET table_id=9 WHERE table_id > 1; |
| 60 |
|
| 61 |
--error ER_OPEN_AS_READONLY |
| 62 |
DELETE FROM ndb$tables WHERE table_id=3; |
| 63 |
|
| 64 |
--error ER_OPEN_AS_READONLY |
| 65 |
DELETE FROM ndb$tables WHERE 1=0; |
| 66 |
|
| 67 |
--error ER_OPEN_AS_READONLY |
| 68 |
DELETE FROM ndb$tables WHERE table_id > 1; |
| 69 |
|
| 70 |
--error ER_ILLEGAL_HA |
| 71 |
ALTER TABLE ndb$test ADD COLUMN another_col varchar(255); |
| 72 |
|
| 73 |
FLUSH TABLES; |
| 74 |
SELECT table_id FROM ndb$tables; |
| 75 |
|
| 76 |
--error ER_OPEN_AS_READONLY |
| 77 |
TRUNCATE ndb$tables; |
| 78 |
|
| 79 |
## Variables and status |
| 80 |
SHOW GLOBAL STATUS LIKE 'ndbinfo\_%'; |
| 81 |
let $current_version = `select @@ndbinfo_version`; |
| 82 |
--replace_result $current_version NDB_VERSION_D |
| 83 |
SHOW GLOBAL VARIABLES LIKE 'ndbinfo\_%'; |
| 84 |
|
| 85 |
SELECT counter, HEX(counter2) FROM ndb$test LIMIT 10; |
| 86 |
|
| 87 |
# All tables that contain data are hidden by default |
| 88 |
# and becomes visible with ndbinfo_show_hidden |
| 89 |
SHOW TABLES LIKE 'ndb$te%'; |
| 90 |
set @@ndbinfo_show_hidden=TRUE; |
| 91 |
SHOW TABLES LIKE 'ndb$te%'; |
| 92 |
set @@ndbinfo_show_hidden=default; |
| 93 |
|
| 94 |
# Check that ndbinfo_table_prefix is readonly |
| 95 |
--error ER_INCORRECT_GLOBAL_LOCAL_VAR |
| 96 |
set @@ndbinfo_table_prefix="somethingelse"; |
| 97 |
|
| 98 |
# Check that ndbinfo_database is readonly |
| 99 |
--error ER_INCORRECT_GLOBAL_LOCAL_VAR |
| 100 |
set @@ndbinfo_database="somethingelse"; |
| 101 |
|
| 102 |
# Check that block table has been created and contain data |
| 103 |
SELECT count(*) >= 20 FROM blocks; |
| 104 |
|
| 105 |
# Test incompatible table definition between NDB and MySQL Server |
| 106 |
# using the ndb$test table which originally looks like |
| 107 |
show create table ndb$test; |
| 108 |
# CREATE TABLE `ndb$test` ( |
| 109 |
# `node_id` int unsigned DEFAULT NULL, |
| 110 |
# `block_number` int unsigned DEFAULT NULL, |
| 111 |
# `block_instance` int unsigned DEFAULT NULL, |
| 112 |
# `counter` int unsigned DEFAULT NULL, |
| 113 |
# `counter2` bigint unsigned DEFAULT NULL |
| 114 |
# ) ENGINE=NDBINFO COMMENT='for testing' |
| 115 |
|
| 116 |
## 1) More columns in NDB -> allowed, with warning |
| 117 |
DROP TABLE ndb$test; |
| 118 |
CREATE TABLE ndb$test (node_id int unsigned) ENGINE = ndbinfo; |
| 119 |
SELECT node_id != 0 FROM ndb$test LIMIT 1; |
| 120 |
DROP TABLE ndb$test; |
| 121 |
|
| 122 |
## 2) Column does not exist in NDB -> allowed, with warning, non existing |
| 123 |
## column(s) return NULL |
| 124 |
## 2a) Extra column at end |
| 125 |
CREATE TABLE ndb$test (node_id int, non_existing int) ENGINE = ndbinfo; |
| 126 |
SELECT DISTINCT node_id, non_existing FROM ndb$test; |
| 127 |
DROP TABLE ndb$test; |
| 128 |
|
| 129 |
## 2b) Extra column(s) in middle |
| 130 |
CREATE TABLE ndb$test ( |
| 131 |
node_id int unsigned, |
| 132 |
non_existing int unsigned, |
| 133 |
block_number int unsigned, |
| 134 |
block_instance int unsigned, |
| 135 |
counter int unsigned, |
| 136 |
counter2 bigint unsigned |
| 137 |
) ENGINE = ndbinfo; |
| 138 |
SELECT DISTINCT node_id, non_existing, block_number FROM ndb$test; |
| 139 |
DROP TABLE ndb$test; |
| 140 |
|
| 141 |
## 2c) Extra column first |
| 142 |
CREATE TABLE ndb$test (non_existing int, node_id int) ENGINE = ndbinfo; |
| 143 |
SELECT DISTINCT node_id, non_existing FROM ndb$test; |
| 144 |
SELECT DISTINCT non_existing, node_id FROM ndb$test; |
| 145 |
DROP TABLE ndb$test; |
| 146 |
|
| 147 |
## 3) Incompatible column type -> error, with warning |
| 148 |
## 3a) int instead of bigint |
| 149 |
CREATE TABLE ndb$test (counter2 int) ENGINE = ndbinfo; |
| 150 |
--error ER_GET_ERRMSG |
| 151 |
SELECT * FROM ndb$test; |
| 152 |
SHOW WARNINGS; |
| 153 |
## 3b) bigint instead of int |
| 154 |
DROP TABLE ndb$test; |
| 155 |
CREATE TABLE ndb$test (node_id bigint) ENGINE = ndbinfo; |
| 156 |
--error ER_GET_ERRMSG |
| 157 |
SELECT * FROM ndb$test; |
| 158 |
SHOW WARNINGS; |
| 159 |
## 3c) varchar instead of int |
| 160 |
DROP TABLE ndb$test; |
| 161 |
CREATE TABLE ndb$test (node_id varchar(255)) ENGINE = ndbinfo; |
| 162 |
--error ER_GET_ERRMSG |
| 163 |
SELECT * FROM ndb$test; |
| 164 |
SHOW WARNINGS; |
| 165 |
DROP TABLE ndb$test; |
| 166 |
## 3d) column which is NOT NULL |
| 167 |
CREATE TABLE ndb$test (node_id int unsigned NOT NULL) ENGINE = ndbinfo; |
| 168 |
--error ER_GET_ERRMSG |
| 169 |
SELECT * FROM ndb$test; |
| 170 |
SHOW WARNINGS; |
| 171 |
DROP TABLE ndb$test; |
| 172 |
## 3e) non existing column which is NOT NULL |
| 173 |
CREATE TABLE ndb$test ( |
| 174 |
block_number int unsigned, |
| 175 |
non_existing int NOT NULL) ENGINE = ndbinfo; |
| 176 |
--error ER_GET_ERRMSG |
| 177 |
SELECT * FROM ndb$test; |
| 178 |
SHOW WARNINGS; |
| 179 |
DROP TABLE ndb$test; |
| 180 |
|
| 181 |
|
| 182 |
## 4) Primary key is supported after the fix for bug#28658625, |
| 183 |
## but tables are still limited to a single index. |
| 184 |
--error ER_TOO_MANY_KEYS |
| 185 |
CREATE TABLE ndb$test (node_id int, block_number int PRIMARY KEY, |
| 186 |
index(node_id)) ENGINE = ndbinfo; |
| 187 |
|
| 188 |
## 5) Table with blobs now supported |
| 189 |
CREATE TABLE ndb$test (node_id int, block_number blob) ENGINE = ndbinfo; |
| 190 |
DROP TABLE ndb$test; |
| 191 |
|
| 192 |
## 6) Table with autoincrement not supported |
| 193 |
--error ER_TABLE_CANT_HANDLE_AUTO_INCREMENT |
| 194 |
CREATE TABLE ndb$test (node_id int AUTO_INCREMENT) ENGINE = ndbinfo; |
| 195 |
|
| 196 |
## Restore original ndb$test table to avoid test side-effects |
| 197 |
CREATE TABLE ndb$test ( |
| 198 |
node_id int(10) unsigned DEFAULT NULL, |
| 199 |
block_number int(10) unsigned DEFAULT NULL, |
| 200 |
block_instance int(10) unsigned DEFAULT NULL, |
| 201 |
counter int(10) unsigned DEFAULT NULL, |
| 202 |
counter2 bigint(20) unsigned DEFAULT NULL |
| 203 |
) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='for testing'; |
| 204 |
|
| 205 |
|
| 206 |
# wl#5567 - exercise table... |
| 207 |
# only node_id is guranteed to be same... |
| 208 |
# (unless we change cluster config for test) |
| 209 |
# but this will anyway pull results from datanode... |
| 210 |
# |
| 211 |
--sorted_result |
| 212 |
select distinct node_id |
| 213 |
from ndbinfo.diskpagebuffer; |
| 214 |
|
| 215 |
|
| 216 |
# |
| 217 |
# BUG#11885602 |
| 218 |
# - It was allowed to CREATE TABLE which was not in NDB, but |
| 219 |
# creating a view on that table failed. Implement ndbinfo_offline |
| 220 |
# mode which allows tables to be created and opened although they |
| 221 |
# don't exists or have different table definition. |
| 222 |
# This is exactly the same behaviour as when NDBCLUSTER |
| 223 |
# is disabled |
| 224 |
# |
| 225 |
|
| 226 |
# Check ndbinfo_offline is GLOBAL variable |
| 227 |
--error ER_GLOBAL_VARIABLE |
| 228 |
set @@ndbinfo_offline=1; |
| 229 |
|
| 230 |
# Query used to check that open tables are closed |
| 231 |
# when offline mode is turned on and off |
| 232 |
let $q1 = SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id; |
| 233 |
eval $q1; |
| 234 |
|
| 235 |
# new views |
| 236 |
desc threadblocks; |
| 237 |
select distinct block_name from threadblocks order by 1; |
| 238 |
desc threadstat; |
| 239 |
select count(*) > 0 block_name from threadstat; |
| 240 |
|
| 241 |
desc disk_write_speed_aggregate; |
| 242 |
select count(*) from disk_write_speed_aggregate; |
| 243 |
|
| 244 |
desc disk_write_speed_base; |
| 245 |
select node_id from disk_write_speed_base group by node_id, thr_no; |
| 246 |
|
| 247 |
select count(*) from disk_write_speed_aggregate_node; |
| 248 |
|
| 249 |
desc restart_info; |
| 250 |
select * from restart_info where node_restart_status <> 'Restart completed'; |
| 251 |
|
| 252 |
desc tc_time_track_stats; |
| 253 |
select distinct node_id from tc_time_track_stats; |
| 254 |
|
| 255 |
desc threads; |
| 256 |
|
| 257 |
desc cluster_transactions; |
| 258 |
desc server_transactions; |
| 259 |
desc cluster_operations; |
| 260 |
desc server_operations; |
| 261 |
|
| 262 |
select count(*) > 0 from table_distribution_status; |
| 263 |
select count(*) > 0 from ndb$table_distribution_status_all; |
| 264 |
select count(*) > 0 from table_fragments; |
| 265 |
select count(*) > 0 from ndb$table_fragments_all; |
| 266 |
select count(*) > 0 from table_replicas; |
| 267 |
select count(*) > 0 from ndb$table_replicas_all; |
| 268 |
|
| 269 |
# DESCRIBE columns of the views |
| 270 |
desc table_distribution_status; |
| 271 |
desc table_fragments; |
| 272 |
desc table_replicas; |
| 273 |
|
| 274 |
# DESCRIBE columns of the hidden table |
| 275 |
# (turn show_hidden on since otherwise columns |
| 276 |
# of the table is supposed to be hidden) |
| 277 |
set @@ndbinfo_show_hidden=TRUE; |
| 278 |
desc ndb$table_distribution_status_all; |
| 279 |
desc ndb$table_fragments_all; |
| 280 |
desc ndb$table_replicas_all; |
| 281 |
set @@ndbinfo_show_hidden=default; |
| 282 |
|
| 283 |
create table t1 (a int primary key) engine = ndb; |
| 284 |
begin; |
| 285 |
insert into t1 values (1); |
| 286 |
select state, count_operations, outstanding_operations, |
| 287 |
IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>") |
| 288 |
client_node_id |
| 289 |
from server_transactions; |
| 290 |
select node_id, operation_type, state, |
| 291 |
IF(tc_node_id <= 48, "<tc_node_id>", "<incorrect nodeid>") tc_node_id, |
| 292 |
IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>") |
| 293 |
client_node_id |
| 294 |
from server_operations |
| 295 |
order by 1; |
| 296 |
|
| 297 |
--sorted_result |
| 298 |
select st.state, st.count_operations, st.outstanding_operations, |
| 299 |
so.node_id, so.state, so.operation_type |
| 300 |
from server_transactions st, |
| 301 |
server_operations so |
| 302 |
where st.transid = so.transid |
| 303 |
and so.tc_node_id = st.node_id |
| 304 |
and so.tc_block_instance = st.block_instance |
| 305 |
and so.client_node_id = st.client_node_id |
| 306 |
and so.client_block_ref = st.client_block_ref; |
| 307 |
rollback; |
| 308 |
drop table t1; |
| 309 |
|
| 310 |
# |
| 311 |
select distinct resource_name |
| 312 |
from resources |
| 313 |
order by 1; |
| 314 |
|
| 315 |
select distinct counter_name |
| 316 |
from counters |
| 317 |
order by 1; |
| 318 |
|
| 319 |
# Turn on ndbinfo_offline |
| 320 |
set @@global.ndbinfo_offline=TRUE; |
| 321 |
select @@ndbinfo_offline; |
| 322 |
|
| 323 |
CREATE TABLE ndb$does_not_exist_in_ndb( |
| 324 |
node_id int, |
| 325 |
message varchar(255) |
| 326 |
) ENGINE = ndbinfo CHARACTER SET latin1; |
| 327 |
|
| 328 |
CREATE VIEW view_on_table_which_does_not_exist_in_ndb AS |
| 329 |
SELECT node_id, message |
| 330 |
FROM ndbinfo.ndb$does_not_exist_in_ndb; |
| 331 |
|
| 332 |
SHOW CREATE TABLE ndb$does_not_exist_in_ndb; |
| 333 |
|
| 334 |
# SELECTs return no rows in offline mode |
| 335 |
SELECT * FROM view_on_table_which_does_not_exist_in_ndb; |
| 336 |
SELECT * FROM ndb$does_not_exist_in_ndb; |
| 337 |
eval $q1; |
| 338 |
|
| 339 |
DROP VIEW view_on_table_which_does_not_exist_in_ndb; |
| 340 |
DROP TABLE ndb$does_not_exist_in_ndb; |
| 341 |
|
| 342 |
# Restore original value |
| 343 |
set @@global.ndbinfo_offline = FALSE; |
| 344 |
|
| 345 |
eval $q1; |
| 346 |
|
| 347 |
# |
| 348 |
# Bug#17018500 BOGUS DEFINER VALUE FOR NDBINFO VIEWS REPORTED |
| 349 |
# IN INFORMATION_SCHEMA.VIEW |
| 350 |
# |
| 351 |
--echo Verify there are no ndbinfo views without 'root@localhost' as definer |
| 352 |
select table_schema, table_name, definer from information_schema.views |
| 353 |
where table_schema = 'ndbinfo' and |
| 354 |
definer != 'root@localhost'; |
| 355 |
|
| 356 |
|
| 357 |
# |
| 358 |
# Test buffered joining |
| 359 |
# |
| 360 |
# BUG#20075747 RND_INIT() ON AN OPEN SCAN IS USED TO REPOSITION THE CURSOR |
| 361 |
# - rnd_init() on an open scan is used to reposition the "cursor" |
| 362 |
# back to first row |
| 363 |
# |
| 364 |
set @@optimizer_switch='block_nested_loop=off'; |
| 365 |
|
| 366 |
|
| 367 |
--disable_result_log |
| 368 |
|
| 369 |
select @a:=0; |
| 370 |
|
| 371 |
select |
| 372 |
@a:=@a+1, |
| 373 |
a.node_id, b.node_id, |
| 374 |
a.block_name, b.block_name, |
| 375 |
a.block_instance, b.block_instance, |
| 376 |
a.counter_id, b.counter_id, |
| 377 |
a.counter_name, b.counter_name, |
| 378 |
a.val, b.val |
| 379 |
from |
| 380 |
ndbinfo.counters a join |
| 381 |
ndbinfo.counters b |
| 382 |
on |
| 383 |
a.node_id = b.node_id; |
| 384 |
|
| 385 |
select @b:=0; |
| 386 |
|
| 387 |
select |
| 388 |
@b:=@b+1, |
| 389 |
a.node_id, b.node_id, |
| 390 |
a.block_name, b.block_name, |
| 391 |
a.block_instance, b.block_instance, |
| 392 |
a.counter_id, b.counter_id, |
| 393 |
a.counter_name, b.counter_name, |
| 394 |
a.val, b.val |
| 395 |
from |
| 396 |
ndbinfo.counters a join |
| 397 |
ndbinfo.counters b |
| 398 |
on |
| 399 |
a.node_id = b.node_id; |
| 400 |
|
| 401 |
--enable_result_log |
| 402 |
|
| 403 |
--echo Expect both joins to give same row count |
| 404 |
--echo (0 diff) |
| 405 |
select @a - @b; |
| 406 |
|
| 407 |
|
| 408 |
create temporary table old_count |
| 409 |
select counter_name, sum(val) as val |
| 410 |
from ndbinfo.counters |
| 411 |
where block_name='DBSPJ' |
| 412 |
group by counter_name; |
| 413 |
drop table old_count; |
| 414 |
set @@optimizer_switch=default; |
| 415 |
|
| 416 |
# ################################ |
| 417 |
# Test memory_per_fragment table. |
| 418 |
# ################################ |
| 419 |
|
| 420 |
# A helper table for populating t1. |
| 421 |
create table numbers( |
| 422 |
i int |
| 423 |
); |
| 424 |
|
| 425 |
insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
| 426 |
|
| 427 |
create table t1( |
| 428 |
a int not null primary key, |
| 429 |
b int not null, |
| 430 |
c1 char(255), |
| 431 |
vc1 varchar(1024), |
| 432 |
tx1 text |
| 433 |
)engine=ndbcluster character set latin1; |
| 434 |
|
| 435 |
# Popultate t1 with 1000 rows, including som sizable varsize data. |
| 436 |
insert into t1 select x1.i+x2.i*10+x3.i*100, x1.i-x2.i*10-x3.i*100, repeat('x', 1+x1.i), repeat('y', 1000+x1.i), repeat('z', x1.i*100) from numbers as x1, numbers as x2, numbers as x3; |
| 437 |
|
| 438 |
drop table numbers; |
| 439 |
|
| 440 |
# Create some extra indexes to test different schema object types. |
| 441 |
create index ix1 on t1(c1); |
| 442 |
create unique index ix2 on t1(b) using hash; |
| 443 |
|
| 444 |
# The table id of 't1' may vary from run to run. We need to make sure that the |
| 445 |
# output from the subsequent tests does not depend on the numrical value of |
| 446 |
# that table id. |
| 447 |
set @t1_id = (select cast(id as signed integer) from ndbinfo.ndb$dict_obj_info where fq_name REGEXP "def/t1$"); |
| 448 |
|
| 449 |
|
| 450 |
# Test the dict_obj_info table by showing t1 and it's four releated objects. |
| 451 |
# Replace table id of t1 in 'fq_name' values that contain it (index and blob |
| 452 |
# table names do), since the table id may vary between runs. |
| 453 |
select type, state, parent_obj_type, |
| 454 |
replace(replace(fq_name, |
| 455 |
concat("/", cast(@t1_id as char(10)), "/"), "/<t1 id>/"), |
| 456 |
concat("_", cast(@t1_id as char(10)), "_"), "_<t1 id>_") as fq_name, |
| 457 |
if(parent_obj_id=@t1_id, "child_of_t1", |
| 458 |
if(parent_obj_id=0, "root", "error!")) as nesting |
| 459 |
from ndbinfo.ndb$dict_obj_info |
| 460 |
where type <= 6 |
| 461 |
and (id = @t1_id or |
| 462 |
instr(fq_name, concat("/", cast(@t1_id as char(10)), "/")) or |
| 463 |
instr(fq_name, concat("_", cast(@t1_id as char(10)), "_"))) |
| 464 |
order by type, state, parent_obj_type, fq_name; |
| 465 |
|
| 466 |
# Disable query log while defining the view below, since view definition |
| 467 |
# conatins the table id of t1, which may vary between runs. |
| 468 |
disable_query_log; |
| 469 |
let $t1_id = query_get_value(select @t1_id as id, id, 1); |
| 470 |
|
| 471 |
# Create som views that we reuse after modifying t1 below. |
| 472 |
# This one sums usage counts for all fragments of each table. |
| 473 |
# Some values, such as fixed_elem_free_count and fixed_elem_count for ordered |
| 474 |
# indexes may vary depending on the number of fragments and other factors. |
| 475 |
# These are therefore not returned directly, but used in calculations that |
| 476 |
# should provide stable results. |
| 477 |
# Observe that memory_per_fragment.fq_name, parent_fq_name, type are all |
| 478 |
# functionally dependent on 'table_id', but since this cannot be deduced from |
| 479 |
# the schema, they are all included in the 'group by' clause. |
| 480 |
# Exclude system tables and mysql schema since those may have different |
| 481 |
# contents depending on earlier test runs. |
| 482 |
eval create view sum_per_tab as |
| 483 |
select replace(fq_name, cast($t1_id as char(10)), "<t1 id>") as fq_name, |
| 484 |
parent_fq_name, type, |
| 485 |
if (type="Ordered index", |
| 486 |
-(round(sum(fixed_elem_count)/165)), |
| 487 |
sum(fixed_elem_count)) as fixed_elem_count, |
| 488 |
max(fixed_elem_size_bytes) as fixed_elem_size_bytes, |
| 489 |
round(sum(fixed_elem_alloc_bytes/fixed_elem_size_bytes) |
| 490 |
/sum(fixed_elem_count+fixed_elem_free_count),1) as fixed_elem_alloc_ok, |
| 491 |
sum(fixed_elem_free_bytes/fixed_elem_size_bytes) = |
| 492 |
sum(fixed_elem_free_count) as fixed_elem_free_bytes_ok, |
| 493 |
sum(var_elem_count) as var_elem_count, |
| 494 |
sum(var_elem_alloc_bytes) >= sum(var_elem_free_bytes) as var_bytes_ok |
| 495 |
from ndbinfo.memory_per_fragment mpf |
| 496 |
where not fq_name like "mysql/%" and |
| 497 |
(parent_fq_name is null or not parent_fq_name like "mysql/%") and |
| 498 |
type <> 'System table' |
| 499 |
group by table_id, mpf.fq_name, parent_fq_name, type |
| 500 |
order by ifnull(parent_fq_name, mpf.fq_name), mpf.fq_name, type; |
| 501 |
|
| 502 |
enable_query_log; |
| 503 |
|
| 504 |
|
| 505 |
# Sum usage counts for all fragments of all tables. |
| 506 |
create view frag_sum as |
| 507 |
select |
| 508 |
sum(fixed_elem_alloc_bytes) as fixed_elem_alloc_bytes, |
| 509 |
sum(var_elem_alloc_bytes) as var_elem_alloc_bytes, |
| 510 |
sum(hash_index_alloc_bytes) as hash_index_alloc_bytes |
| 511 |
from ndbinfo.memory_per_fragment; |
| 512 |
|
| 513 |
# Sum pool values across all block instances. |
| 514 |
# 'Data memory' appears twice in 'pools' therefore a 'where' clause is needed. |
| 515 |
create view pool_sum as |
| 516 |
select pool_name, block_number, max(entry_size) * sum(used) as used_bytes |
| 517 |
from ndbinfo.ndb$pools where pool_name <> "Data memory" |
| 518 |
or block_number=249 group by pool_name, block_number; |
| 519 |
|
| 520 |
# Check that the combined size of all L2PMap nodes equals the total size |
| 521 |
# of the allocated L2PMap pages minus a small (<1%) page overhead. |
| 522 |
create view check_l2pmap_pools as |
| 523 |
select min(floor(1000*nodes.total*nodes.entry_size/ |
| 524 |
(pages.used*pages.entry_size))) as l2pmap_ratio |
| 525 |
from ndbinfo.ndb$pools as pages join ndbinfo.ndb$pools as nodes |
| 526 |
where pages.node_id=nodes.node_id |
| 527 |
and pages.block_instance=nodes.block_instance |
| 528 |
and pages.block_number=nodes.block_number |
| 529 |
and pages.pool_name="L2PMap pages" |
| 530 |
and nodes.pool_name="L2PMap nodes"; |
| 531 |
|
| 532 |
create view l2p_frag_sum as |
| 533 |
select sum(tuple_l2pmap_alloc_bytes) as l2p_sum, 'TUP' as block |
| 534 |
from ndbinfo.ndb$frag_mem_use |
| 535 |
union |
| 536 |
select sum(hash_index_l2pmap_alloc_bytes) as l2p_sum, 'ACC' as block |
| 537 |
from ndbinfo.ndb$frag_mem_use; |
| 538 |
|
| 539 |
# Dump some extra info for solving Bug #28892753 |
| 540 |
let $DUMP_INFO=0; |
| 541 |
|
| 542 |
# Check that we get the same result from the common pool as when we add up |
| 543 |
# the values for each fragment. |
| 544 |
|
| 545 |
select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum |
| 546 |
where pool_name='Index memory'; |
| 547 |
|
| 548 |
select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes |
| 549 |
into @diff |
| 550 |
from pool_sum, frag_sum |
| 551 |
where pool_name='Data memory'; |
| 552 |
select @diff; |
| 553 |
|
| 554 |
let $DIFF=query_get_value("select @diff as d", d, 1); |
| 555 |
if ($DIFF) |
| 556 |
{ |
| 557 |
let $DUMP_INFO=1; |
| 558 |
} |
| 559 |
|
| 560 |
select l2p_sum-used_bytes from l2p_frag_sum, pool_sum |
| 561 |
where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or |
| 562 |
(block='TUP' and block_number=249)); |
| 563 |
|
| 564 |
select * from check_l2pmap_pools; |
| 565 |
|
| 566 |
select * from sum_per_tab; |
| 567 |
if ($DUMP_INFO) |
| 568 |
{ |
| 569 |
# Extra debugging info |
| 570 |
select * from sum_per_tab; |
| 571 |
select * from frag_sum; |
| 572 |
select * from pool_sum; |
| 573 |
select * from check_l2pmap_pools; |
| 574 |
select * from l2p_frag_sum; |
| 575 |
select * from ndbinfo.ndb$pools; |
| 576 |
select * from ndbinfo.resources; |
| 577 |
} |
| 578 |
|
| 579 |
# Now delete som scattered rows and check memory usage again. |
| 580 |
delete from t1 where a%10=0; |
| 581 |
|
| 582 |
select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum |
| 583 |
where pool_name='Index memory'; |
| 584 |
|
| 585 |
select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes |
| 586 |
from pool_sum, frag_sum |
| 587 |
where pool_name='Data memory'; |
| 588 |
|
| 589 |
select l2p_sum-used_bytes from l2p_frag_sum, pool_sum |
| 590 |
where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or |
| 591 |
(block='TUP' and block_number=249)); |
| 592 |
|
| 593 |
select * from check_l2pmap_pools; |
| 594 |
|
| 595 |
select * from sum_per_tab; |
| 596 |
if ($DUMP_INFO) |
| 597 |
{ |
| 598 |
# Extra debugging info |
| 599 |
select * from sum_per_tab; |
| 600 |
select * from frag_sum; |
| 601 |
select * from pool_sum; |
| 602 |
select * from check_l2pmap_pools; |
| 603 |
select * from l2p_frag_sum; |
| 604 |
select * from ndbinfo.ndb$pools; |
| 605 |
select * from ndbinfo.resources; |
| 606 |
} |
| 607 |
|
| 608 |
# Now update some var-sized fields and check memory usage again. |
| 609 |
update t1 set vc1=repeat('x', a%300), tx1=repeat('y', a*2); |
| 610 |
|
| 611 |
select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum |
| 612 |
where pool_name='Index memory'; |
| 613 |
|
| 614 |
select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes |
| 615 |
from pool_sum, frag_sum |
| 616 |
where pool_name='Data memory'; |
| 617 |
|
| 618 |
select l2p_sum-used_bytes from l2p_frag_sum, pool_sum |
| 619 |
where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or |
| 620 |
(block='TUP' and block_number=249)); |
| 621 |
|
| 622 |
select * from check_l2pmap_pools; |
| 623 |
|
| 624 |
select * from sum_per_tab; |
| 625 |
if ($DUMP_INFO) |
| 626 |
{ |
| 627 |
# Extra debugging info |
| 628 |
select * from sum_per_tab; |
| 629 |
select * from frag_sum; |
| 630 |
select * from pool_sum; |
| 631 |
select * from check_l2pmap_pools; |
| 632 |
select * from l2p_frag_sum; |
| 633 |
select * from ndbinfo.ndb$pools; |
| 634 |
select * from ndbinfo.resources; |
| 635 |
} |
| 636 |
|
| 637 |
drop table t1; |
| 638 |
drop view l2p_frag_sum; |
| 639 |
drop view check_l2pmap_pools; |
| 640 |
drop view sum_per_tab; |
| 641 |
drop view pool_sum; |
| 642 |
drop view frag_sum; |
| 643 |
|
| 644 |
# ################################ |
| 645 |
# Test operations_per_fragment table. |
| 646 |
# ################################ |
| 647 |
|
| 648 |
# Create a view that sums counters across all fragments, so that results |
| 649 |
# will not depend on endianness. |
| 650 |
# Observe that operations_per_fragment.fq_name, parent_fq_name, type are all |
| 651 |
# functionally dependent on 'table_id', but since this cannot be deduced from |
| 652 |
# the schema, they are all included in the 'group by' clause. |
| 653 |
create view frag_ops as select |
| 654 |
if(type in ('Ordered index', 'Unique hash index'), |
| 655 |
substring(fq_name from 1+locate('/', fq_name, 9)), |
| 656 |
if(fq_name like '%BLOB%', left(fq_name, locate('_', fq_name)), fq_name)) |
| 657 |
as fq_name, |
| 658 |
parent_fq_name, |
| 659 |
type, |
| 660 |
count(fragment_num) as frag_count, |
| 661 |
sum(tot_key_reads) as tot_key_reads, |
| 662 |
sum(tot_key_inserts) as tot_key_inserts, |
| 663 |
sum(tot_key_updates) as tot_key_updates, |
| 664 |
sum(tot_key_writes) as tot_key_writes, |
| 665 |
sum(tot_key_deletes) as tot_key_deletes, |
| 666 |
sum(tot_key_refs) as tot_key_refs, |
| 667 |
round(log2(1+sum(tot_key_attrinfo_bytes))) as log_tot_key_attrinfo_bytes, |
| 668 |
sum(tot_key_keyinfo_bytes) as tot_key_keyinfo_bytes, |
| 669 |
sum(tot_key_prog_bytes) as tot_key_prog_bytes, |
| 670 |
sum(tot_key_inst_exec) as tot_key_inst_exec, |
| 671 |
sum(tot_key_bytes_returned) as tot_key_bytes_returned, |
| 672 |
sum(tot_frag_scans) as tot_frag_scans, |
| 673 |
sum(tot_scan_rows_examined) as tot_scan_rows_examined, |
| 674 |
sum(tot_scan_rows_returned) as tot_scan_rows_returned, |
| 675 |
sum(tot_scan_bytes_returned) as tot_scan_bytes_returned, |
| 676 |
sum(tot_scan_prog_bytes) as tot_scan_prog_bytes, |
| 677 |
sum(tot_scan_bound_bytes) as tot_scan_bound_bytes, |
| 678 |
sum(tot_scan_inst_exec) as tot_scan_inst_exec, |
| 679 |
# Ignore concurrent frag_scans, as all tests below expect them to be 0 |
| 680 |
# but this is not stable as LCP scans are counted as concurrent |
| 681 |
# frag scans while they are running (but not counted as tot_frag_scans |
| 682 |
# when they complete). |
| 683 |
# Arguably it is useful to see a live LCP scan, and this test has no |
| 684 |
# use for conc_frag_scans!=0, so avoid looking at it for determinism |
| 685 |
#sum(conc_frag_scans) as conc_frag_scans, |
| 686 |
sum(conc_qd_frag_scans) as conc_qd_frag_scans, |
| 687 |
sum(tot_commits) as tot_commits |
| 688 |
from ndbinfo.operations_per_fragment opf |
| 689 |
where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%' |
| 690 |
group by table_id, type, opf.fq_name, parent_fq_name |
| 691 |
order by ifnull(parent_fq_name, opf.fq_name), opf.fq_name; |
| 692 |
|
| 693 |
|
| 694 |
# Create and populate test tables. |
| 695 |
create table t1 ( |
| 696 |
a int not null, |
| 697 |
b int not null, |
| 698 |
c int not null, |
| 699 |
d int not null, |
| 700 |
t text not null, |
| 701 |
primary key (b,a) |
| 702 |
) engine=ndbcluster |
| 703 |
partition by key(b,a) partitions 8; |
| 704 |
|
| 705 |
create unique index ix1 on t1(d) using hash; |
| 706 |
|
| 707 |
|
| 708 |
# A helper table for populating t1. |
| 709 |
create table numbers( |
| 710 |
i int |
| 711 |
); |
| 712 |
|
| 713 |
insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
| 714 |
|
| 715 |
insert into t1 select x1.i+x2.i*10, 1, 1+x1.i+x2.i*10, 1+x1.i+x2.i*10, |
| 716 |
repeat('x', 512) |
| 717 |
from numbers as x1, numbers as x2; |
| 718 |
|
| 719 |
drop table numbers; |
| 720 |
|
| 721 |
create table t2 ( |
| 722 |
a int, |
| 723 |
b int, |
| 724 |
primary key(a) using hash) |
| 725 |
engine = ndb |
| 726 |
partition by key(a) partitions 8; |
| 727 |
|
| 728 |
insert into t2 values (1, 2); |
| 729 |
insert into t2 values (2, 3); |
| 730 |
insert into t2 values (3, 1); |
| 731 |
|
| 732 |
# Check counters before the test queries. |
| 733 |
--query_vertical select * from frag_ops; |
| 734 |
|
| 735 |
# Do a table scan with a pushed condition. |
| 736 |
--replace_column 4 # |
| 737 |
explain select count(*) from t1 where d<10; |
| 738 |
select count(*) from t1 where d<10; |
| 739 |
|
| 740 |
# Do an index scan. |
| 741 |
--replace_column 4 # |
| 742 |
explain select count(*) from t1 where b=1 and a<10; |
| 743 |
select count(*) from t1 where b=1 and a<10; |
| 744 |
|
| 745 |
# Do a PK read. |
| 746 |
--replace_column 4 # |
| 747 |
explain select * from t1 where b=1 and a = -5; |
| 748 |
select * from t1 where b=1 and a = -5; |
| 749 |
|
| 750 |
# Try to insert a duplicate key. |
| 751 |
--error ER_DUP_ENTRY |
| 752 |
insert into t1 values (1,1,1,1,''); |
| 753 |
|
| 754 |
# Use replace to generate PK write operations. |
| 755 |
replace t1 values (-1,-1,-1,-1,''); |
| 756 |
replace t1 values (1,1,5,500,''); |
| 757 |
|
| 758 |
# Do update and delete. |
| 759 |
update t1 set d = -d, t = repeat('a', 300) where b=1 and a > 90; |
| 760 |
|
| 761 |
delete from t1 where a%30=0; |
| 762 |
|
| 763 |
# check_ndb_debug sets have_ndb_debug to 1 if the |
| 764 |
# Ndb binaries have debug support built in |
| 765 |
# This is needed for error-inserts etc. |
| 766 |
source include/check_ndb_debug.inc; |
| 767 |
|
| 768 |
# Disable part of test due to bug: |
| 769 |
# Bug#30754643 NDB.NDBINFO LOCK WAIT TIMEOUT EXCEEDED |
| 770 |
if (0) { |
| 771 |
# This error insert causes the next index scan from SPJ to be queued on node 2. |
| 772 |
# This command may fail if error inserts is not available on this build, |
| 773 |
# therefore we permit return code 255. |
| 774 |
--error 0,255 |
| 775 |
--exec $NDB_MGM -e "2 error 5084" >> $NDB_TOOLS_OUTPUT |
| 776 |
|
| 777 |
# Do a pushed query that starts multiple index scans on each t1 fragment. |
| 778 |
# That way the first scan (on node 2) will be queued until the next finishes. |
| 779 |
--replace_column 4 # |
| 780 |
explain select count(*) from t1 as x1 join t1 as x2 on x1.c=x2.b; |
| 781 |
select count(*) from t1 as x1 join t1 as x2 on x1.c=x2.b; |
| 782 |
|
| 783 |
# Check number of queued scans. Test for debug build |
| 784 |
# to handle builds without error inserts (i.e. non-debug builds). |
| 785 |
|
| 786 |
--disable_query_log |
| 787 |
eval select |
| 788 |
if ($have_ndb_debug, |
| 789 |
sum(tot_qd_frag_scans), |
| 790 |
4) as tot_qd_frag_scans |
| 791 |
from ndbinfo.operations_per_fragment |
| 792 |
where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%'; |
| 793 |
--enable_query_log |
| 794 |
|
| 795 |
# Check how these operations changed the counters. |
| 796 |
--query_vertical select * from frag_ops; |
| 797 |
} |
| 798 |
|
| 799 |
# This query should give PK read operations with an interpreted program. |
| 800 |
# (Multi-range read mapped to PK reads.) |
| 801 |
--replace_column 4 # |
| 802 |
explain select count(*) |
| 803 |
from t2 as tx, t2 as ty |
| 804 |
where tx.a in (1,3,5) |
| 805 |
and ty.a = tx.b; |
| 806 |
|
| 807 |
# The 'explain' above triggers an implicit 'analyze table'. That increments |
| 808 |
# operations_per_fragment counters in a way that is endian-dependent. |
| 809 |
# We therefore save the current values of the counters to isolate the |
| 810 |
# effect of the query below. |
| 811 |
create table save_counters as select * from frag_ops; |
| 812 |
|
| 813 |
select count(*) |
| 814 |
from t2 as tx, t2 as ty |
| 815 |
where tx.a in (1,3,5) |
| 816 |
and ty.a = tx.b; |
| 817 |
|
| 818 |
create view changes as select |
| 819 |
new.fq_name, |
| 820 |
new.parent_fq_name, |
| 821 |
new.type, |
| 822 |
new.frag_count, |
| 823 |
new.tot_key_reads - old.tot_key_reads, |
| 824 |
new.tot_key_inserts - old.tot_key_inserts, |
| 825 |
new.tot_key_updates - old.tot_key_updates, |
| 826 |
new.tot_key_writes - old.tot_key_writes, |
| 827 |
new.tot_key_deletes - old.tot_key_deletes, |
| 828 |
new.tot_key_refs - old.tot_key_refs, |
| 829 |
new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes, |
| 830 |
new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes, |
| 831 |
new.tot_key_prog_bytes - old.tot_key_prog_bytes, |
| 832 |
new.tot_key_inst_exec - old.tot_key_inst_exec, |
| 833 |
new.tot_key_bytes_returned - old.tot_key_bytes_returned, |
| 834 |
new.tot_frag_scans - old.tot_frag_scans, |
| 835 |
new.tot_scan_rows_examined - old.tot_scan_rows_examined, |
| 836 |
new.tot_scan_rows_returned - old.tot_scan_rows_returned, |
| 837 |
new.tot_scan_bytes_returned - old.tot_scan_bytes_returned, |
| 838 |
new.tot_scan_prog_bytes - old.tot_scan_prog_bytes, |
| 839 |
new.tot_scan_bound_bytes - old.tot_scan_bound_bytes, |
| 840 |
new.tot_scan_inst_exec - old.tot_scan_inst_exec, |
| 841 |
new.tot_commits - old.tot_commits |
| 842 |
from frag_ops as new, save_counters as old |
| 843 |
where new.fq_name=old.fq_name and new.fq_name like '%t2'; |
| 844 |
|
| 845 |
--query_vertical select * from changes |
| 846 |
|
| 847 |
|
| 848 |
|
| 849 |
# Drop test views and tables. |
| 850 |
drop view changes; |
| 851 |
drop table save_counters; |
| 852 |
drop view frag_ops; |
| 853 |
drop table t1; |
| 854 |
drop table t2; |
| 855 |
|
| 856 |
|
| 857 |
|
| 858 |
# |
| 859 |
# WL#7575 Remove ndbinfo's usage of other engine |
| 860 |
# - the five lookup tables which resided in another engine |
| 861 |
# was converted to virtual(aka. hardcoded) tables inside ha_ndbinfo |
| 862 |
# - three new views was added to preserve backwards compatibility |
| 863 |
# for those tabes which was changed to prefix ndb$ |
| 864 |
|
| 865 |
## Check that the new tables return data and exists. |
| 866 |
select count(*) > 10 from blocks; |
| 867 |
select param_number, param_name, param_description, param_default, |
| 868 |
param_min, param_max from ndb$config_params |
| 869 |
where param_name = "DataMemory"; |
| 870 |
select * from ndb$dict_obj_types where type_id < 3; |
| 871 |
--disable_result_log |
| 872 |
select * from ndb$dbtc_apiconnect_state; |
| 873 |
select * from ndb$dblqh_tcconnect_state; |
| 874 |
--enable_result_log |
| 875 |
|
| 876 |
## Check that the new views return data and exists. |
| 877 |
select block_number from blocks where block_name = "DBTC"; |
| 878 |
select param_name from config_params where param_number = 101; |
| 879 |
select type_name from ndb$dict_obj_types where type_id = 3; |
| 880 |
|
| 881 |
## All tables in ndbinfo/ should now be using engine=NDBINFO |
| 882 |
select count(*) from information_schema.tables |
| 883 |
where table_schema = 'ndbinfo' and engine != 'ndbinfo'; |
| 884 |
|
| 885 |
# |
| 886 |
# Bug#16731538 MYSQLD CRITICAL FAILURE DURING ORDERED SELECT FROM NDBINFO.CLUSTER_OPERATIONS |
| 887 |
# - ha_ndbinfo::estimate_rows_upper_bound() returned 0 rows |
| 888 |
# |
| 889 |
--disable_result_log |
| 890 |
select * from ndbinfo.config_params order by param_number; |
| 891 |
--enable_result_log |
| 892 |
|
| 893 |
# |
| 894 |
# Bug#11762750 TABLE NDBINFO.CONFIG_PARAMS SHOULD BE READ-ONLY (FOR NOW) |
| 895 |
# |
| 896 |
# |
| 897 |
# Checking the table |
| 898 |
--error ER_OPEN_AS_READONLY |
| 899 |
UPDATE ndb$config_params SET param_number=1 WHERE param_name = "NoOfReplicas"; |
| 900 |
|
| 901 |
# Checking the view |
| 902 |
--error ER_OPEN_AS_READONLY |
| 903 |
UPDATE config_params SET param_number=1 WHERE param_name = "NoOfReplicas"; |
| 904 |
|
| 905 |
|
| 906 |
# |
| 907 |
# WL#8703 ndbinfo config parameter tables |
| 908 |
# |
| 909 |
select node_id, param_name, config_param, config_value |
| 910 |
from ndbinfo.config_values cv, ndbinfo.config_params cp |
| 911 |
where cv.config_param = cp.param_number and |
| 912 |
cp.param_name = 'MaxNoOfConcurrentTransactions' |
| 913 |
order by node_id; |
| 914 |
|
| 915 |
select node_id, param_name, config_value |
| 916 |
from ndbinfo.config_values cv, ndbinfo.config_params cp |
| 917 |
where cv.config_param = cp.param_number and |
| 918 |
cp.param_name = 'NodeId' |
| 919 |
order by node_id; |
| 920 |
|
| 921 |
select node_id, concat(config_value / 1024 / 1024, 'M') as DataMemory |
| 922 |
from ndbinfo.config_values |
| 923 |
where config_param = 112 /* DataMemory */ |
| 924 |
order by node_id; |
| 925 |
|
| 926 |
# |
| 927 |
# wl#9819 ndbinfo.processes |
| 928 |
# |
| 929 |
# Because port number and host address vary in the test environment, |
| 930 |
# we can not test the content of the service_URI column. |
| 931 |
|
| 932 |
# (A) Normative tests of table and view definition |
| 933 |
# (turn show_hidden on since otherwise columns |
| 934 |
# of the table is supposed to be hidden) |
| 935 |
set @@ndbinfo_show_hidden=TRUE; |
| 936 |
desc ndb$processes; |
| 937 |
set @@ndbinfo_show_hidden=default; |
| 938 |
SELECT view_definition from information_schema.views |
| 939 |
WHERE table_schema = 'ndbinfo' and table_name = 'processes'; |
| 940 |
|
| 941 |
# (B) Tests of table content. |
| 942 |
# These results will change if the cluster config used when running |
| 943 |
# this test case changes. |
| 944 |
|
| 945 |
# Query the base table |
| 946 |
SELECT reporting_node_id, node_id, node_type, |
| 947 |
replace(replace(process_name,"-debug",""),".exe","") AS process_name |
| 948 |
FROM ndb$processes |
| 949 |
ORDER BY reporting_node_id, node_id; |
| 950 |
|
| 951 |
# Query the view |
| 952 |
# |
| 953 |
# Note, on Windows mysqld processes will have an angel_process_id while |
| 954 |
# on other platforms there will be none. |
| 955 |
# |
| 956 |
# The Windows angel_process_id will be reported in monitor_proc_id |
| 957 |
# instead. |
| 958 |
# |
| 959 |
# On Windows the angel_proc_id will always be set to "null" for API. |
| 960 |
# On other platforms monitor_proc_id will instead always be set to |
| 961 |
# "not_null" for API. |
| 962 |
# |
| 963 |
# Test for node_type = 'API' is used since checking for process_name = |
| 964 |
# 'mysqld' would be much more cumbersome, and the only API node running |
| 965 |
# in this case are the mysqld servers. |
| 966 |
|
| 967 |
set @windows = convert(@@version_compile_os using latin1) |
| 968 |
in ('Win32', 'Win64', 'Windows'); |
| 969 |
|
| 970 |
SELECT node_id, |
| 971 |
node_type, |
| 972 |
replace(replace(process_name,"-debug",""),".exe","") AS process_name, |
| 973 |
if(process_id is null, "null", "not_null") as proc_id, |
| 974 |
if(@windows AND node_type = 'API', |
| 975 |
"null", |
| 976 |
if(angel_process_id is null, "null", "not_null")) as angel_proc_id, |
| 977 |
if(NOT @windows AND node_type = 'API', |
| 978 |
"not_null", |
| 979 |
if(angel_process_id is null, "null", "not_null")) as monitor_proc_id |
| 980 |
FROM processes |
| 981 |
ORDER BY node_id; |
| 982 |
|
| 983 |
# |
| 984 |
# wl#10147 ndbinfo.config_nodes |
| 985 |
# |
| 986 |
# (A) Normative tests of table and view definition |
| 987 |
# (turn show_hidden on since otherwise columns |
| 988 |
# of the table is supposed to be hidden) |
| 989 |
set @@ndbinfo_show_hidden=TRUE; |
| 990 |
desc ndb$config_nodes; |
| 991 |
set @@ndbinfo_show_hidden=default; |
| 992 |
select view_definition, is_updatable, security_type |
| 993 |
FROM information_schema.views |
| 994 |
WHERE table_schema = 'ndbinfo' AND table_name = 'config_nodes'; |
| 995 |
|
| 996 |
# (B) Tests of table content. |
| 997 |
# These results will change if the cluster config used when running |
| 998 |
# this test case changes. |
| 999 |
SELECT * from ndb$config_nodes order by reporting_node_id, node_id; |
| 1000 |
|
| 1001 |
# (C) Tests of view: SELECT DISTINCT, removing reporting_node_id |
| 1002 |
SELECT node_type, count(*), |
| 1003 |
GROUP_CONCAT(node_id order by 1 asc separator " ") ids |
| 1004 |
FROM config_nodes group by node_type order by node_type; |
| 1005 |
|
| 1006 |
|
| 1007 |
|
| 1008 |
--echo # |
| 1009 |
--echo # Bug#26048272 IMPLEMENT NDBINFO TABLE FOR LOOKUP OF ERROR CODES AND MESSAGES |
| 1010 |
--echo # |
| 1011 |
|
| 1012 |
# Check that row is returned for NDB error 920 |
| 1013 |
select * |
| 1014 |
from ndbinfo.error_messages |
| 1015 |
where error_code = 920; |
| 1016 |
|
| 1017 |
|
| 1018 |
# Check that row is returned for NDB exit code 2304 |
| 1019 |
select * |
| 1020 |
from ndbinfo.error_messages |
| 1021 |
where error_code = 2304; |
| 1022 |
|
| 1023 |
# Check that row is returned for MgmApi error code 5001 |
| 1024 |
select * |
| 1025 |
from ndbinfo.error_messages |
| 1026 |
where error_code = 5001; |
| 1027 |
|
| 1028 |
# Check that there are at last 700 rows in the error_message table, don't |
| 1029 |
# use a hard limit as it will grow when someone add a new error message |
| 1030 |
select count(*) > 700 |
| 1031 |
from ndbinfo.error_messages; |
| 1032 |
|
| 1033 |
desc hwinfo; |
| 1034 |
desc cpuinfo; |
| 1035 |
desc cpudata; |
| 1036 |
desc cpudata_50ms; |
| 1037 |
desc cpudata_1sec; |
| 1038 |
desc cpudata_20sec; |
| 1039 |
|
| 1040 |
--disable_result_log |
| 1041 |
select * from hwinfo; |
| 1042 |
select * from cpuinfo; |
| 1043 |
select * from cpudata; |
| 1044 |
select * from cpudata_50ms; |
| 1045 |
select * from cpudata_1sec; |
| 1046 |
select * from cpudata_20sec; |
| 1047 |
--enable_result_log |
| 1048 |
|
| 1049 |
--echo # |
| 1050 |
--echo # BUG#32073640 Add ndbinfo.backup_id |
| 1051 |
--echo # - the table have one column and one row |
| 1052 |
--echo # |
| 1053 |
|
| 1054 |
# Check that one column and one row is returned |
| 1055 |
--replace_column 1 <backup_id> |
| 1056 |
select * from backup_id; |
| 1057 |
|
| 1058 |
--echo # |
| 1059 |
--echo # Bug#32198754 EXTEND MTR CHECK TESTCASES TO CHECK NDB DICTIONARY ... |
| 1060 |
--echo # - adds a new ndbinfo.dict_obj_tree view which can show |
| 1061 |
--echo # the object tree of objects in NDB dictionary |
| 1062 |
--echo # |
| 1063 |
# Create some objects and show the NDB dictionary object tree |
| 1064 |
CREATE LOGFILE GROUP first_lg |
| 1065 |
ADD UNDOFILE 'lg1_undofile.dat' INITIAL_SIZE 1M UNDO_BUFFER_SIZE = 1M |
| 1066 |
ENGINE=NDB; |
| 1067 |
|
| 1068 |
CREATE TABLESPACE first_ts |
| 1069 |
ADD DATAFILE 'ts1_datafile.dat' |
| 1070 |
USE LOGFILE GROUP first_lg INITIAL_SIZE 2M |
| 1071 |
ENGINE NDB; |
| 1072 |
|
| 1073 |
CREATE TABLE test.t1 ( |
| 1074 |
a int PRIMARY KEY |
| 1075 |
) ENGINE = NDB TABLESPACE first_ts STORAGE DISK; |
| 1076 |
|
| 1077 |
CREATE TABLE test.t2 ( |
| 1078 |
a int PRIMARY KEY, |
| 1079 |
b int, |
| 1080 |
UNIQUE(b) |
| 1081 |
) engine = NDB; |
| 1082 |
|
| 1083 |
# Show report for objects created above |
| 1084 |
# - mask internal ids in the object names |
| 1085 |
--replace_regex /def\/[0-9]+/def\/<id>/ /INDEX_[0-9]+/INDEX_<id>/ |
| 1086 |
SELECT name, parent_name, root_name, level, indented_name, path |
| 1087 |
FROM ndbinfo.dict_obj_tree |
| 1088 |
WHERE root_name IN ('first_lg', 'first_ts', 'test/def/t1', 'test/def/t2'); |
| 1089 |
|
| 1090 |
# Show report for table t2 |
| 1091 |
# - mask internal ids in the object names |
| 1092 |
--replace_regex /def\/[0-9]+/def\/<id>/ /INDEX_[0-9]+/INDEX_<id>/ |
| 1093 |
SELECT indented_name, root_name FROM ndbinfo.dict_obj_tree |
| 1094 |
WHERE root_name = 'test/def/t2'; |
| 1095 |
|
| 1096 |
DROP TABLE test.t2; |
| 1097 |
DROP TABLE test.t1; |
| 1098 |
ALTER TABLESPACE first_ts DROP DATAFILE 'ts1_datafile.dat'; |
| 1099 |
DROP TABLESPACE first_ts; |
| 1100 |
DROP LOGFILE GROUP first_lg ENGINE=NDB; |