Debian Codegraph @

mysql-test/suite/ndb/t/ndbinfo.test

raw
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;