Debian Codegraph @

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

raw
1 result_format: 2
2
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 PLUGIN_NAME PLUGIN_VERSION PLUGIN_STATUS PLUGIN_TYPE PLUGIN_LIBRARY PLUGIN_LIBRARY_VERSION PLUGIN_AUTHOR PLUGIN_DESCRIPTION
8 ndbinfo 0.1 ACTIVE STORAGE ENGINE NULL NULL Oracle Corporation MySQL Cluster system information storage engine
9
10 ## Creation of temporary tables should not be supported by NDBINFO engine
11 CREATE TEMPORARY TABLE `t1` (
12 `dummy` INT UNSIGNED
13 ) ENGINE=NDBINFO;
14 ERROR HY000: Table storage engine 'ndbinfo' does not support the create option 'TEMPORARY'
15
16 USE ndbinfo;
17
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 node_id memory_type used_ok used_pages_ok total total_pages
24 1 Long message buffer 0 0 4194304 16384
25 2 Long message buffer 0 0 4194304 16384
26
27 SELECT node_id, block_number, block_instance, pool_name,
28 used>0 AND used < total/20 AS used_ok,
29 total, high>=used AND high < total/20 AS high_ok, entry_size, config_param1,
30 config_param2, config_param3, config_param4
31 FROM ndbinfo.ndb$pools WHERE pool_name="Long message buffer"
32 ORDER BY node_id;
33 node_id block_number block_instance pool_name used_ok total high_ok entry_size config_param1 config_param2 config_param3 config_param4
34 1 254 0 Long message buffer 0 16384 0 256 157 0 0 0
35 2 254 0 Long message buffer 0 16384 0 256 157 0 0 0
36
37 SHOW CREATE TABLE ndb$tables;
38 Table Create Table
39 ndb$tables CREATE TABLE `ndb$tables` (
40 `table_id` int unsigned DEFAULT NULL,
41 `table_name` varchar(512) DEFAULT NULL,
42 `comment` varchar(512) DEFAULT NULL,
43 `rows_estimate` int unsigned DEFAULT NULL
44 ) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='metadata for tables available through ndbinfo'
45
46 SELECT table_id, table_name, comment FROM ndb$tables;
47 table_id table_name comment
48 0 tables metadata for tables available through ndbinfo
49 1 columns metadata for columns available through ndbinfo
50 2 test for testing
51 3 pools pool usage
52 4 transporters transporter status
53 5 logspaces logspace usage
54 6 logbuffers logbuffer usage
55 7 resources resources usage (a.k.a superpool)
56 8 counters monotonic counters
57 9 nodes node status
58 10 diskpagebuffer disk page buffer info
59 11 threadblocks which blocks are run in which threads
60 12 threadstat Statistics on execution threads
61 13 transactions transactions
62 14 operations operations
63 15 membership membership
64 16 dict_obj_info Dictionary object info
65 17 frag_mem_use Per fragment space information
66 18 disk_write_speed_base Actual speed of disk writes per LDM thread, base data
67 19 disk_write_speed_aggregate Actual speed of disk writes per LDM thread, aggregate data
68 20 frag_operations Per fragment operational information
69 21 restart_info Times of restart phases in seconds and current state
70 22 tc_time_track_stats Time tracking of transaction, key operations and scan ops
71 23 config_values Configuration parameter values
72 24 threads Base table for threads
73 25 cpustat_50ms Thread CPU stats at 50 milliseconds intervals
74 26 cpustat_1sec Thread CPU stats at 1 second intervals
75 27 cpustat_20sec Thread CPU stats at 20 seconds intervals
76 28 cpustat Thread CPU stats for last second
77 29 frag_locks Per fragment lock information
78 30 acc_operations ACC operation info
79 31 table_distribution_status Table status in distribution handler
80 32 table_fragments Partitions of the tables
81 33 table_replicas Fragment replicas of the tables
82 34 table_distribution_status_all Table status in distribution handler
83 35 table_fragments_all Partitions of the tables
84 36 table_replicas_all Fragment replicas of the tables
85 37 stored_tables Information about stored tables
86 38 processes Process ID and Name information for connected nodes
87 39 config_nodes All nodes of current cluster configuration
88 40 pgman_time_track_stats Time tracking of reads and writes of disk data pages
89 41 diskstat Disk data statistics for last second
90 42 diskstats_1sec Disk data statistics history for last few seconds
91 43 hwinfo HW information where node executes
92 44 cpuinfo CPU information where node executes
93 45 cpudata Data about CPU usage last second
94 46 cpudata_50ms Data about CPU usage per 50ms last second
95 47 cpudata_1sec Data about CPU usage per second last 20 seconds
96 48 cpudata_20sec Data about CPU usage per 20 sec last 400 seconds
97 50 threadblock_details which blocks are run in which threads and some internal state details
98 51 transporter_details detailed transporter status
99 52 transactions_full transactions_full
100 53 transporter_activity Histogram over activity on heartbeated transporters
101 SELECT COUNT(*) FROM ndb$tables;
102 COUNT(*)
103 53
104 SELECT * FROM ndb$tables WHERE table_id = 2;
105 table_id table_name comment rows_estimate
106 2 test for testing 8000
107 SELECT table_id, table_name, comment FROM ndb$tables WHERE table_id > 5;
108 table_id table_name comment
109 6 logbuffers logbuffer usage
110 7 resources resources usage (a.k.a superpool)
111 8 counters monotonic counters
112 9 nodes node status
113 10 diskpagebuffer disk page buffer info
114 11 threadblocks which blocks are run in which threads
115 12 threadstat Statistics on execution threads
116 13 transactions transactions
117 14 operations operations
118 15 membership membership
119 16 dict_obj_info Dictionary object info
120 17 frag_mem_use Per fragment space information
121 18 disk_write_speed_base Actual speed of disk writes per LDM thread, base data
122 19 disk_write_speed_aggregate Actual speed of disk writes per LDM thread, aggregate data
123 20 frag_operations Per fragment operational information
124 21 restart_info Times of restart phases in seconds and current state
125 22 tc_time_track_stats Time tracking of transaction, key operations and scan ops
126 23 config_values Configuration parameter values
127 24 threads Base table for threads
128 25 cpustat_50ms Thread CPU stats at 50 milliseconds intervals
129 26 cpustat_1sec Thread CPU stats at 1 second intervals
130 27 cpustat_20sec Thread CPU stats at 20 seconds intervals
131 28 cpustat Thread CPU stats for last second
132 29 frag_locks Per fragment lock information
133 30 acc_operations ACC operation info
134 31 table_distribution_status Table status in distribution handler
135 32 table_fragments Partitions of the tables
136 33 table_replicas Fragment replicas of the tables
137 34 table_distribution_status_all Table status in distribution handler
138 35 table_fragments_all Partitions of the tables
139 36 table_replicas_all Fragment replicas of the tables
140 37 stored_tables Information about stored tables
141 38 processes Process ID and Name information for connected nodes
142 39 config_nodes All nodes of current cluster configuration
143 40 pgman_time_track_stats Time tracking of reads and writes of disk data pages
144 41 diskstat Disk data statistics for last second
145 42 diskstats_1sec Disk data statistics history for last few seconds
146 43 hwinfo HW information where node executes
147 44 cpuinfo CPU information where node executes
148 45 cpudata Data about CPU usage last second
149 46 cpudata_50ms Data about CPU usage per 50ms last second
150 47 cpudata_1sec Data about CPU usage per second last 20 seconds
151 48 cpudata_20sec Data about CPU usage per 20 sec last 400 seconds
152 50 threadblock_details which blocks are run in which threads and some internal state details
153 51 transporter_details detailed transporter status
154 52 transactions_full transactions_full
155 53 transporter_activity Histogram over activity on heartbeated transporters
156 SELECT * FROM ndb$tables WHERE table_name = 'LOGDESTINATION';
157 table_id table_name comment rows_estimate
158 SELECT COUNT(*) FROM ndb$tables t, ndb$columns c
159 WHERE t.table_id = c.table_id AND
160 t.table_id in (1,2,3,4,5,6);
161 COUNT(*)
162 49
163
164 SELECT table_id, table_name, comment from ndb$tables
165 WHERE table_id > 2 AND table_id <= 5 ORDER BY table_id;
166 table_id table_name comment
167 3 pools pool usage
168 4 transporters transporter status
169 5 logspaces logspace usage
170 SELECT table_id FROM ndb$tables WHERE table_id = 2 ORDER BY table_name;
171 table_id
172 2
173 SELECT table_id, table_name FROM ndb$tables ORDER BY table_name;
174 table_id table_name
175 30 acc_operations
176 1 columns
177 39 config_nodes
178 23 config_values
179 8 counters
180 45 cpudata
181 47 cpudata_1sec
182 48 cpudata_20sec
183 46 cpudata_50ms
184 44 cpuinfo
185 28 cpustat
186 26 cpustat_1sec
187 27 cpustat_20sec
188 25 cpustat_50ms
189 16 dict_obj_info
190 10 diskpagebuffer
191 41 diskstat
192 42 diskstats_1sec
193 19 disk_write_speed_aggregate
194 18 disk_write_speed_base
195 29 frag_locks
196 17 frag_mem_use
197 20 frag_operations
198 43 hwinfo
199 6 logbuffers
200 5 logspaces
201 15 membership
202 9 nodes
203 14 operations
204 40 pgman_time_track_stats
205 3 pools
206 38 processes
207 7 resources
208 21 restart_info
209 37 stored_tables
210 0 tables
211 31 table_distribution_status
212 34 table_distribution_status_all
213 32 table_fragments
214 35 table_fragments_all
215 33 table_replicas
216 36 table_replicas_all
217 22 tc_time_track_stats
218 2 test
219 11 threadblocks
220 50 threadblock_details
221 24 threads
222 12 threadstat
223 13 transactions
224 52 transactions_full
225 4 transporters
226 53 transporter_activity
227 51 transporter_details
228
229 SELECT table_id, column_id, column_name FROM ndb$columns LIMIT 7;
230 table_id column_id column_name
231 0 0 table_id
232 0 1 table_name
233 0 2 comment
234 0 3 rows_estimate
235 1 0 table_id
236 1 1 column_id
237 1 2 column_name
238
239 UPDATE ndb$tables SET table_id=2 WHERE table_id=3;
240 ERROR HY000: Table 'ndb$tables' is read only
241
242 UPDATE ndb$tables SET table_id=9 WHERE 1=0;
243 ERROR HY000: Table 'ndb$tables' is read only
244
245 UPDATE ndb$tables SET table_id=9 WHERE table_id > 1;
246 ERROR HY000: Table 'ndb$tables' is read only
247
248 DELETE FROM ndb$tables WHERE table_id=3;
249 ERROR HY000: Table 'ndb$tables' is read only
250
251 DELETE FROM ndb$tables WHERE 1=0;
252 ERROR HY000: Table 'ndb$tables' is read only
253
254 DELETE FROM ndb$tables WHERE table_id > 1;
255 ERROR HY000: Table 'ndb$tables' is read only
256
257 ALTER TABLE ndb$test ADD COLUMN another_col varchar(255);
258 ERROR HY000: Table storage engine for 'ndb$test' doesn't have this option
259
260 FLUSH TABLES;
261 SELECT table_id FROM ndb$tables;
262 table_id
263 0
264 1
265 2
266 3
267 4
268 5
269 6
270 7
271 8
272 9
273 10
274 11
275 12
276 13
277 14
278 15
279 16
280 17
281 18
282 19
283 20
284 21
285 22
286 23
287 24
288 25
289 26
290 27
291 28
292 29
293 30
294 31
295 32
296 33
297 34
298 35
299 36
300 37
301 38
302 39
303 40
304 41
305 42
306 43
307 44
308 45
309 46
310 47
311 48
312 50
313 51
314 52
315 53
316
317 TRUNCATE ndb$tables;
318 ERROR HY000: Table 'ndb$tables' is read only
319
320 ## Variables and status
321 SHOW GLOBAL STATUS LIKE 'ndbinfo\_%';
322 Variable_name Value
323 SHOW GLOBAL VARIABLES LIKE 'ndbinfo\_%';
324 Variable_name Value
325 ndbinfo_database ndbinfo
326 ndbinfo_max_bytes 0
327 ndbinfo_max_rows 10
328 ndbinfo_offline OFF
329 ndbinfo_show_hidden OFF
330 ndbinfo_table_prefix ndb$
331 ndbinfo_version NDB_VERSION_D
332
333 SELECT counter, HEX(counter2) FROM ndb$test LIMIT 10;
334 counter HEX(counter2)
335 0 0
336 1 100000000
337 2 200000000
338 3 300000000
339 4 400000000
340 5 500000000
341 6 600000000
342 7 700000000
343 8 800000000
344 9 900000000
345
346 SHOW TABLES LIKE 'ndb$te%';
347 Tables_in_ndbinfo (ndb$te%)
348 set @@ndbinfo_show_hidden=TRUE;
349 SHOW TABLES LIKE 'ndb$te%';
350 Tables_in_ndbinfo (ndb$te%)
351 ndb$test
352 set @@ndbinfo_show_hidden=default;
353
354 set @@ndbinfo_table_prefix="somethingelse";
355 ERROR HY000: Variable 'ndbinfo_table_prefix' is a read only variable
356
357 set @@ndbinfo_database="somethingelse";
358 ERROR HY000: Variable 'ndbinfo_database' is a read only variable
359
360 SELECT count(*) >= 20 FROM blocks;
361 count(*) >= 20
362 1
363
364 show create table ndb$test;
365 Table Create Table
366 ndb$test CREATE TABLE `ndb$test` (
367 `node_id` int unsigned DEFAULT NULL,
368 `block_number` int unsigned DEFAULT NULL,
369 `block_instance` int unsigned DEFAULT NULL,
370 `counter` int unsigned DEFAULT NULL,
371 `counter2` bigint unsigned DEFAULT NULL
372 ) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='for testing'
373 ## 1) More columns in NDB -> allowed, with warning
374 DROP TABLE ndb$test;
375 CREATE TABLE ndb$test (node_id int unsigned) ENGINE = ndbinfo;
376 SELECT node_id != 0 FROM ndb$test LIMIT 1;
377 node_id != 0
378 1
379 Warnings:
380 Note 40001 Table 'ndb$test' is defined differently in NDB, there are more columns available. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
381 DROP TABLE ndb$test;
382
383 ## 2) Column does not exist in NDB -> allowed, with warning, non existing
384 ## column(s) return NULL
385 ## 2a) Extra column at end
386 CREATE TABLE ndb$test (node_id int, non_existing int) ENGINE = ndbinfo;
387 SELECT DISTINCT node_id, non_existing FROM ndb$test;
388 node_id non_existing
389 1 NULL
390 2 NULL
391 Warnings:
392 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' does not exist. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
393 Note 40001 Table 'ndb$test' is defined differently in NDB, there are more columns available. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
394 DROP TABLE ndb$test;
395
396 ## 2b) Extra column(s) in middle
397 CREATE TABLE ndb$test (
398 node_id int unsigned,
399 non_existing int unsigned,
400 block_number int unsigned,
401 block_instance int unsigned,
402 counter int unsigned,
403 counter2 bigint unsigned
404 ) ENGINE = ndbinfo;
405 SELECT DISTINCT node_id, non_existing, block_number FROM ndb$test;
406 node_id non_existing block_number
407 1 NULL 249
408 2 NULL 249
409 Warnings:
410 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' does not exist. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
411 DROP TABLE ndb$test;
412
413 ## 2c) Extra column first
414 CREATE TABLE ndb$test (non_existing int, node_id int) ENGINE = ndbinfo;
415 SELECT DISTINCT node_id, non_existing FROM ndb$test;
416 node_id non_existing
417 1 NULL
418 2 NULL
419 Warnings:
420 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' does not exist. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
421 Note 40001 Table 'ndb$test' is defined differently in NDB, there are more columns available. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
422 SELECT DISTINCT non_existing, node_id FROM ndb$test;
423 non_existing node_id
424 NULL 1
425 NULL 2
426 DROP TABLE ndb$test;
427
428 ## 3) Incompatible column type -> error, with warning
429 ## 3a) int instead of bigint
430 CREATE TABLE ndb$test (counter2 int) ENGINE = ndbinfo;
431 SELECT * FROM ndb$test;
432 ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
433 SHOW WARNINGS;
434 Level Code Message
435 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'counter2' is not compatible. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
436 Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO
437 ## 3b) bigint instead of int
438 DROP TABLE ndb$test;
439 CREATE TABLE ndb$test (node_id bigint) ENGINE = ndbinfo;
440 SELECT * FROM ndb$test;
441 ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
442 SHOW WARNINGS;
443 Level Code Message
444 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'node_id' is not compatible. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
445 Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO
446 ## 3c) varchar instead of int
447 DROP TABLE ndb$test;
448 CREATE TABLE ndb$test (node_id varchar(255)) ENGINE = ndbinfo;
449 SELECT * FROM ndb$test;
450 ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
451 SHOW WARNINGS;
452 Level Code Message
453 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'node_id' is not compatible. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
454 Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO
455 DROP TABLE ndb$test;
456 ## 3d) column which is NOT NULL
457 CREATE TABLE ndb$test (node_id int unsigned NOT NULL) ENGINE = ndbinfo;
458 SELECT * FROM ndb$test;
459 ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
460 SHOW WARNINGS;
461 Level Code Message
462 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'node_id' is NOT NULL. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
463 Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO
464 DROP TABLE ndb$test;
465 ## 3e) non existing column which is NOT NULL
466 CREATE TABLE ndb$test (
467 block_number int unsigned,
468 non_existing int NOT NULL) ENGINE = ndbinfo;
469 SELECT * FROM ndb$test;
470 ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
471 SHOW WARNINGS;
472 Level Code Message
473 Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' is NOT NULL. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO'
474 Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO
475 DROP TABLE ndb$test;
476
477 ## 4) Primary key is supported after the fix for bug#28658625,
478 ## but tables are still limited to a single index.
479 CREATE TABLE ndb$test (node_id int, block_number int PRIMARY KEY,
480 index(node_id)) ENGINE = ndbinfo;
481 ERROR 42000: Too many keys specified; max 1 keys allowed
482
483 ## 5) Table with blobs now supported
484 CREATE TABLE ndb$test (node_id int, block_number blob) ENGINE = ndbinfo;
485 DROP TABLE ndb$test;
486
487 ## 6) Table with autoincrement not supported
488 CREATE TABLE ndb$test (node_id int AUTO_INCREMENT) ENGINE = ndbinfo;
489 ERROR 42000: The used table type doesn't support AUTO_INCREMENT columns
490
491 ## Restore original ndb$test table to avoid test side-effects
492 CREATE TABLE ndb$test (
493 node_id int(10) unsigned DEFAULT NULL,
494 block_number int(10) unsigned DEFAULT NULL,
495 block_instance int(10) unsigned DEFAULT NULL,
496 counter int(10) unsigned DEFAULT NULL,
497 counter2 bigint(20) unsigned DEFAULT NULL
498 ) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='for testing';
499 Warnings:
500 Warning 1681 Integer display width is deprecated and will be removed in a future release.
501 Warning 1681 Integer display width is deprecated and will be removed in a future release.
502 Warning 1681 Integer display width is deprecated and will be removed in a future release.
503 Warning 1681 Integer display width is deprecated and will be removed in a future release.
504 Warning 1681 Integer display width is deprecated and will be removed in a future release.
505
506 select distinct node_id
507 from ndbinfo.diskpagebuffer;
508 node_id
509 1
510 2
511
512 set @@ndbinfo_offline=1;
513 ERROR HY000: Variable 'ndbinfo_offline' is a GLOBAL variable and should be set with SET GLOBAL
514
515 SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id;
516 node_id
517 1
518 2
519
520 desc threadblocks;
521 Field Type Null Key Default Extra
522 node_id int unsigned YES NULL
523 thr_no int unsigned YES NULL
524 block_name varchar(512) YES NULL
525 block_instance int unsigned YES NULL
526 select distinct block_name from threadblocks order by 1;
527 block_name
528 BACKUP
529 CMVMI
530 DBACC
531 DBDICT
532 DBDIH
533 DBINFO
534 DBLQH
535 DBQACC
536 DBQLQH
537 DBQTUP
538 DBQTUX
539 DBSPJ
540 DBTC
541 DBTUP
542 DBTUX
543 DBUTIL
544 LGMAN
545 NDBCNTR
546 NDBFS
547 PGMAN
548 QBACKUP
549 QMGR
550 QRESTORE
551 RESTORE
552 SUMA
553 THRMAN
554 TRIX
555 TRPMAN
556 TSMAN
557 desc threadstat;
558 Field Type Null Key Default Extra
559 node_id int unsigned YES NULL
560 thr_no int unsigned YES NULL
561 thr_nm varchar(512) YES NULL
562 c_loop bigint unsigned YES NULL
563 c_exec bigint unsigned YES NULL
564 c_wait bigint unsigned YES NULL
565 c_l_sent_prioa bigint unsigned YES NULL
566 c_l_sent_priob bigint unsigned YES NULL
567 c_r_sent_prioa bigint unsigned YES NULL
568 c_r_sent_priob bigint unsigned YES NULL
569 os_tid bigint unsigned YES NULL
570 os_now bigint unsigned YES NULL
571 os_ru_utime bigint unsigned YES NULL
572 os_ru_stime bigint unsigned YES NULL
573 os_ru_minflt bigint unsigned YES NULL
574 os_ru_majflt bigint unsigned YES NULL
575 os_ru_nvcsw bigint unsigned YES NULL
576 os_ru_nivcsw bigint unsigned YES NULL
577 select count(*) > 0 block_name from threadstat;
578 block_name
579 1
580
581 desc disk_write_speed_aggregate;
582 Field Type Null Key Default Extra
583 node_id int unsigned YES NULL
584 thr_no int unsigned YES NULL
585 backup_lcp_speed_last_sec bigint unsigned YES NULL
586 redo_speed_last_sec bigint unsigned YES NULL
587 backup_lcp_speed_last_10sec bigint unsigned YES NULL
588 redo_speed_last_10sec bigint unsigned YES NULL
589 std_dev_backup_lcp_speed_last_10sec bigint unsigned YES NULL
590 std_dev_redo_speed_last_10sec bigint unsigned YES NULL
591 backup_lcp_speed_last_60sec bigint unsigned YES NULL
592 redo_speed_last_60sec bigint unsigned YES NULL
593 std_dev_backup_lcp_speed_last_60sec bigint unsigned YES NULL
594 std_dev_redo_speed_last_60sec bigint unsigned YES NULL
595 slowdowns_due_to_io_lag bigint unsigned YES NULL
596 slowdowns_due_to_high_cpu bigint unsigned YES NULL
597 disk_write_speed_set_to_min bigint unsigned YES NULL
598 current_target_disk_write_speed bigint unsigned YES NULL
599 select count(*) from disk_write_speed_aggregate;
600 count(*)
601 8
602
603 desc disk_write_speed_base;
604 Field Type Null Key Default Extra
605 node_id int unsigned YES NULL
606 thr_no int unsigned YES NULL
607 millis_ago bigint unsigned YES NULL
608 millis_passed bigint unsigned YES NULL
609 backup_lcp_bytes_written bigint unsigned YES NULL
610 redo_bytes_written bigint unsigned YES NULL
611 target_disk_write_speed bigint unsigned YES NULL
612 select node_id from disk_write_speed_base group by node_id, thr_no;
613 node_id
614 1
615 1
616 1
617 1
618 2
619 2
620 2
621 2
622
623 select count(*) from disk_write_speed_aggregate_node;
624 count(*)
625 2
626
627 desc restart_info;
628 Field Type Null Key Default Extra
629 node_id int unsigned YES NULL
630 node_restart_status varchar(512) YES NULL
631 node_restart_status_int int unsigned YES NULL
632 secs_to_complete_node_failure int unsigned YES NULL
633 secs_to_allocate_node_id int unsigned YES NULL
634 secs_to_include_in_heartbeat_protocol int unsigned YES NULL
635 secs_until_wait_for_ndbcntr_master int unsigned YES NULL
636 secs_wait_for_ndbcntr_master int unsigned YES NULL
637 secs_to_get_start_permitted int unsigned YES NULL
638 secs_to_wait_for_lcp_for_copy_meta_data int unsigned YES NULL
639 secs_to_copy_meta_data int unsigned YES NULL
640 secs_to_include_node int unsigned YES NULL
641 secs_starting_node_to_request_local_recovery int unsigned YES NULL
642 secs_for_local_recovery int unsigned YES NULL
643 secs_restore_fragments int unsigned YES NULL
644 secs_undo_disk_data int unsigned YES NULL
645 secs_exec_redo_log int unsigned YES NULL
646 secs_index_rebuild int unsigned YES NULL
647 secs_to_synchronize_starting_node int unsigned YES NULL
648 secs_wait_lcp_for_restart int unsigned YES NULL
649 secs_wait_subscription_handover int unsigned YES NULL
650 total_restart_secs int unsigned YES NULL
651 select * from restart_info where node_restart_status <> 'Restart completed';
652 node_id node_restart_status node_restart_status_int secs_to_complete_node_failure secs_to_allocate_node_id secs_to_include_in_heartbeat_protocol secs_until_wait_for_ndbcntr_master secs_wait_for_ndbcntr_master secs_to_get_start_permitted secs_to_wait_for_lcp_for_copy_meta_data secs_to_copy_meta_data secs_to_include_node secs_starting_node_to_request_local_recovery secs_for_local_recovery secs_restore_fragments secs_undo_disk_data secs_exec_redo_log secs_index_rebuild secs_to_synchronize_starting_node secs_wait_lcp_for_restart secs_wait_subscription_handover total_restart_secs
653
654 desc tc_time_track_stats;
655 Field Type Null Key Default Extra
656 node_id int unsigned YES NULL
657 block_number int unsigned YES NULL
658 block_instance int unsigned YES NULL
659 comm_node_id int unsigned YES NULL
660 upper_bound bigint unsigned YES NULL
661 scans bigint unsigned YES NULL
662 scan_errors bigint unsigned YES NULL
663 scan_fragments bigint unsigned YES NULL
664 scan_fragment_errors bigint unsigned YES NULL
665 transactions bigint unsigned YES NULL
666 transaction_errors bigint unsigned YES NULL
667 read_key_ops bigint unsigned YES NULL
668 write_key_ops bigint unsigned YES NULL
669 index_key_ops bigint unsigned YES NULL
670 key_op_errors bigint unsigned YES NULL
671 select distinct node_id from tc_time_track_stats;
672 node_id
673 1
674 2
675
676 desc threads;
677 Field Type Null Key Default Extra
678 node_id int unsigned YES NULL
679 thr_no int unsigned YES NULL
680 thread_name varchar(512) YES NULL
681 thread_description varchar(512) YES NULL
682
683 desc cluster_transactions;
684 Field Type Null Key Default Extra
685 node_id int unsigned YES NULL
686 block_instance int unsigned YES NULL
687 transid bigint unsigned YES NULL
688 state varchar(256) YES NULL
689 count_operations int unsigned YES NULL
690 outstanding_operations int unsigned YES NULL
691 inactive_seconds int unsigned YES NULL
692 client_node_id bigint unsigned YES NULL
693 client_block_ref bigint unsigned YES NULL
694 desc server_transactions;
695 Field Type Null Key Default Extra
696 mysql_connection_id bigint unsigned NO 0
697 node_id int unsigned YES NULL
698 block_instance int unsigned YES NULL
699 transid bigint unsigned YES NULL
700 state varchar(256) YES NULL
701 count_operations int unsigned YES NULL
702 outstanding_operations int unsigned YES NULL
703 inactive_seconds int unsigned YES NULL
704 client_node_id bigint unsigned YES NULL
705 client_block_ref bigint unsigned YES NULL
706 desc cluster_operations;
707 Field Type Null Key Default Extra
708 node_id int unsigned YES NULL
709 block_instance int unsigned YES NULL
710 transid bigint unsigned YES NULL
711 operation_type varchar(9) NO
712 state varchar(256) YES NULL
713 tableid int unsigned YES NULL
714 fragmentid int unsigned YES NULL
715 client_node_id bigint unsigned YES NULL
716 client_block_ref bigint unsigned YES NULL
717 tc_node_id bigint unsigned YES NULL
718 tc_block_no bigint unsigned YES NULL
719 tc_block_instance bigint unsigned YES NULL
720 desc server_operations;
721 Field Type Null Key Default Extra
722 mysql_connection_id bigint unsigned NO 0
723 node_id int unsigned YES NULL
724 block_instance int unsigned YES NULL
725 transid bigint unsigned YES NULL
726 operation_type varchar(9) NO
727 state varchar(256) YES NULL
728 tableid int unsigned YES NULL
729 fragmentid int unsigned YES NULL
730 client_node_id bigint unsigned YES NULL
731 client_block_ref bigint unsigned YES NULL
732 tc_node_id bigint unsigned YES NULL
733 tc_block_no bigint unsigned YES NULL
734 tc_block_instance bigint unsigned YES NULL
735
736 select count(*) > 0 from table_distribution_status;
737 count(*) > 0
738 1
739 select count(*) > 0 from ndb$table_distribution_status_all;
740 count(*) > 0
741 1
742 select count(*) > 0 from table_fragments;
743 count(*) > 0
744 1
745 select count(*) > 0 from ndb$table_fragments_all;
746 count(*) > 0
747 1
748 select count(*) > 0 from table_replicas;
749 count(*) > 0
750 1
751 select count(*) > 0 from ndb$table_replicas_all;
752 count(*) > 0
753 1
754
755 desc table_distribution_status;
756 Field Type Null Key Default Extra
757 node_id int unsigned YES NULL
758 table_id int unsigned YES NULL
759 tab_copy_status varchar(21) NO
760 tab_update_status varchar(23) NO
761 tab_lcp_status varchar(15) NO
762 tab_status varchar(13) NO
763 tab_storage varchar(13) NO
764 tab_partitions int unsigned YES NULL
765 tab_fragments int unsigned YES NULL
766 current_scan_count int unsigned YES NULL
767 scan_count_wait int unsigned YES NULL
768 is_reorg_ongoing int unsigned YES NULL
769 desc table_fragments;
770 Field Type Null Key Default Extra
771 node_id int unsigned YES NULL
772 table_id int unsigned YES NULL
773 partition_id int unsigned YES NULL
774 fragment_id int unsigned YES NULL
775 partition_order int unsigned YES NULL
776 log_part_id int unsigned YES NULL
777 no_of_replicas int unsigned YES NULL
778 current_primary int unsigned YES NULL
779 preferred_primary int unsigned YES NULL
780 current_first_backup int unsigned YES NULL
781 current_second_backup int unsigned YES NULL
782 current_third_backup int unsigned YES NULL
783 num_alive_replicas int unsigned YES NULL
784 num_dead_replicas int unsigned YES NULL
785 num_lcp_replicas int unsigned YES NULL
786 desc table_replicas;
787 Field Type Null Key Default Extra
788 node_id int unsigned YES NULL
789 table_id int unsigned YES NULL
790 fragment_id int unsigned YES NULL
791 initial_gci int unsigned YES NULL
792 replica_node_id int unsigned YES NULL
793 is_lcp_ongoing int unsigned YES NULL
794 num_crashed_replicas int unsigned YES NULL
795 last_max_gci_started int unsigned YES NULL
796 last_max_gci_completed int unsigned YES NULL
797 last_lcp_id int unsigned YES NULL
798 prev_lcp_id int unsigned YES NULL
799 prev_max_gci_started int unsigned YES NULL
800 prev_max_gci_completed int unsigned YES NULL
801 last_create_gci int unsigned YES NULL
802 last_replica_gci int unsigned YES NULL
803 is_replica_alive int unsigned YES NULL
804
805 set @@ndbinfo_show_hidden=TRUE;
806 desc ndb$table_distribution_status_all;
807 Field Type Null Key Default Extra
808 node_id int unsigned YES NULL
809 table_id int unsigned YES NULL
810 tab_copy_status int unsigned YES NULL
811 tab_update_status int unsigned YES NULL
812 tab_lcp_status int unsigned YES NULL
813 tab_status int unsigned YES NULL
814 tab_storage int unsigned YES NULL
815 tab_type int unsigned YES NULL
816 tab_partitions int unsigned YES NULL
817 tab_fragments int unsigned YES NULL
818 current_scan_count int unsigned YES NULL
819 scan_count_wait int unsigned YES NULL
820 is_reorg_ongoing int unsigned YES NULL
821 desc ndb$table_fragments_all;
822 Field Type Null Key Default Extra
823 node_id int unsigned YES NULL
824 table_id int unsigned YES NULL
825 partition_id int unsigned YES NULL
826 fragment_id int unsigned YES NULL
827 partition_order int unsigned YES NULL
828 log_part_id int unsigned YES NULL
829 no_of_replicas int unsigned YES NULL
830 current_primary int unsigned YES NULL
831 preferred_primary int unsigned YES NULL
832 current_first_backup int unsigned YES NULL
833 current_second_backup int unsigned YES NULL
834 current_third_backup int unsigned YES NULL
835 num_alive_replicas int unsigned YES NULL
836 num_dead_replicas int unsigned YES NULL
837 num_lcp_replicas int unsigned YES NULL
838 desc ndb$table_replicas_all;
839 Field Type Null Key Default Extra
840 node_id int unsigned YES NULL
841 table_id int unsigned YES NULL
842 fragment_id int unsigned YES NULL
843 initial_gci int unsigned YES NULL
844 replica_node_id int unsigned YES NULL
845 is_lcp_ongoing int unsigned YES NULL
846 num_crashed_replicas int unsigned YES NULL
847 last_max_gci_started int unsigned YES NULL
848 last_max_gci_completed int unsigned YES NULL
849 last_lcp_id int unsigned YES NULL
850 prev_lcp_id int unsigned YES NULL
851 prev_max_gci_started int unsigned YES NULL
852 prev_max_gci_completed int unsigned YES NULL
853 last_create_gci int unsigned YES NULL
854 last_replica_gci int unsigned YES NULL
855 is_replica_alive int unsigned YES NULL
856 set @@ndbinfo_show_hidden=default;
857
858 create table t1 (a int primary key) engine = ndb;
859 begin;
860 insert into t1 values (1);
861 select state, count_operations, outstanding_operations,
862 IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>")
863 client_node_id
864 from server_transactions;
865 state count_operations outstanding_operations client_node_id
866 Started 1 0 <client_node_id>
867 select node_id, operation_type, state,
868 IF(tc_node_id <= 48, "<tc_node_id>", "<incorrect nodeid>") tc_node_id,
869 IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>")
870 client_node_id
871 from server_operations
872 order by 1;
873 node_id operation_type state tc_node_id client_node_id
874 1 INSERT Prepared <tc_node_id> <client_node_id>
875 2 INSERT Prepared <tc_node_id> <client_node_id>
876
877 select st.state, st.count_operations, st.outstanding_operations,
878 so.node_id, so.state, so.operation_type
879 from server_transactions st,
880 server_operations so
881 where st.transid = so.transid
882 and so.tc_node_id = st.node_id
883 and so.tc_block_instance = st.block_instance
884 and so.client_node_id = st.client_node_id
885 and so.client_block_ref = st.client_block_ref;
886 state count_operations outstanding_operations node_id state operation_type
887 Started 1 0 1 Prepared INSERT
888 Started 1 0 2 Prepared INSERT
889 rollback;
890 drop table t1;
891
892 select distinct resource_name
893 from resources
894 order by 1;
895 resource_name
896 DATA_MEMORY
897 DISK_PAGE_BUFFER
898 DISK_RECORDS
899 FILE_BUFFERS
900 JOBBUFFER
901 QUERY_MEMORY
902 SCHEMA_TRANS_MEMORY
903 TRANSACTION_MEMORY
904 TRANSPORTER_BUFFERS
905
906 select distinct counter_name
907 from counters
908 order by 1;
909 counter_name
910 ABORTS
911 ATTRINFO
912 COMMITS
913 CONST_PRUNED_RANGE_SCANS_RECEIVED
914 LOCAL_RANGE_SCANS_SENT
915 LOCAL_READS
916 LOCAL_READS_SENT
917 LOCAL_TABLE_SCANS_SENT
918 LOCAL_WRITES
919 LQHKEY_OVERLOAD
920 LQHKEY_OVERLOAD_NODE_PEER
921 LQHKEY_OVERLOAD_READER
922 LQHKEY_OVERLOAD_SUBSCRIBER
923 LQHKEY_OVERLOAD_TC
924 LQHSCAN_SLOWDOWNS
925 OPERATIONS
926 PRUNED_RANGE_SCANS_RECEIVED
927 RANGE_SCANS
928 RANGE_SCANS_RECEIVED
929 READS
930 READS_NOT_FOUND
931 READS_RECEIVED
932 REMOTE_RANGE_SCANS_SENT
933 REMOTE_READS_SENT
934 SCAN_BATCHES_RETURNED
935 SCAN_ROWS_RETURNED
936 SIMPLE_READS
937 TABLE_SCANS
938 TABLE_SCANS_RECEIVED
939 TRANSACTIONS
940 WRITES
941
942 set @@global.ndbinfo_offline=TRUE;
943 select @@ndbinfo_offline;
944 @@ndbinfo_offline
945 1
946
947 CREATE TABLE ndb$does_not_exist_in_ndb(
948 node_id int,
949 message varchar(255)
950 ) ENGINE = ndbinfo CHARACTER SET latin1;
951
952 CREATE VIEW view_on_table_which_does_not_exist_in_ndb AS
953 SELECT node_id, message
954 FROM ndbinfo.ndb$does_not_exist_in_ndb;
955
956 SHOW CREATE TABLE ndb$does_not_exist_in_ndb;
957 Table Create Table
958 ndb$does_not_exist_in_ndb CREATE TABLE `ndb$does_not_exist_in_ndb` (
959 `node_id` int DEFAULT NULL,
960 `message` varchar(255) DEFAULT NULL
961 ) ENGINE=NDBINFO DEFAULT CHARSET=latin1
962
963 SELECT * FROM view_on_table_which_does_not_exist_in_ndb;
964 node_id message
965 Warnings:
966 Note 1 'NDBINFO' has been started in offline mode since the 'NDBCLUSTER' engine is disabled or @@global.ndbinfo_offline is turned on - no rows can be returned
967 SELECT * FROM ndb$does_not_exist_in_ndb;
968 node_id message
969 Warnings:
970 Note 1 'NDBINFO' has been started in offline mode since the 'NDBCLUSTER' engine is disabled or @@global.ndbinfo_offline is turned on - no rows can be returned
971 SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id;
972 node_id
973 Warnings:
974 Note 1 'NDBINFO' has been started in offline mode since the 'NDBCLUSTER' engine is disabled or @@global.ndbinfo_offline is turned on - no rows can be returned
975
976 DROP VIEW view_on_table_which_does_not_exist_in_ndb;
977 DROP TABLE ndb$does_not_exist_in_ndb;
978
979 set @@global.ndbinfo_offline = FALSE;
980
981 SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id;
982 node_id
983 1
984 2
985
986 Verify there are no ndbinfo views without 'root@localhost' as definer
987 select table_schema, table_name, definer from information_schema.views
988 where table_schema = 'ndbinfo' and
989 definer != 'root@localhost';
990 TABLE_SCHEMA TABLE_NAME DEFINER
991
992 set @@optimizer_switch='block_nested_loop=off';
993
994 select @a:=0;
995
996 select
997 @a:=@a+1,
998 a.node_id, b.node_id,
999 a.block_name, b.block_name,
1000 a.block_instance, b.block_instance,
1001 a.counter_id, b.counter_id,
1002 a.counter_name, b.counter_name,
1003 a.val, b.val
1004 from
1005 ndbinfo.counters a join
1006 ndbinfo.counters b
1007 on
1008 a.node_id = b.node_id;
1009
1010 select @b:=0;
1011
1012 select
1013 @b:=@b+1,
1014 a.node_id, b.node_id,
1015 a.block_name, b.block_name,
1016 a.block_instance, b.block_instance,
1017 a.counter_id, b.counter_id,
1018 a.counter_name, b.counter_name,
1019 a.val, b.val
1020 from
1021 ndbinfo.counters a join
1022 ndbinfo.counters b
1023 on
1024 a.node_id = b.node_id;
1025
1026 Expect both joins to give same row count
1027 (0 diff)
1028 select @a - @b;
1029 @a - @b
1030 0
1031
1032 create temporary table old_count
1033 select counter_name, sum(val) as val
1034 from ndbinfo.counters
1035 where block_name='DBSPJ'
1036 group by counter_name;
1037 drop table old_count;
1038 set @@optimizer_switch=default;
1039
1040 create table numbers(
1041 i int
1042 );
1043
1044 insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1045
1046 create table t1(
1047 a int not null primary key,
1048 b int not null,
1049 c1 char(255),
1050 vc1 varchar(1024),
1051 tx1 text
1052 )engine=ndbcluster character set latin1;
1053
1054 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;
1055
1056 drop table numbers;
1057
1058 create index ix1 on t1(c1);
1059 create unique index ix2 on t1(b) using hash;
1060
1061 set @t1_id = (select cast(id as signed integer) from ndbinfo.ndb$dict_obj_info where fq_name REGEXP "def/t1$");
1062
1063 select type, state, parent_obj_type,
1064 replace(replace(fq_name,
1065 concat("/", cast(@t1_id as char(10)), "/"), "/<t1 id>/"),
1066 concat("_", cast(@t1_id as char(10)), "_"), "_<t1 id>_") as fq_name,
1067 if(parent_obj_id=@t1_id, "child_of_t1",
1068 if(parent_obj_id=0, "root", "error!")) as nesting
1069 from ndbinfo.ndb$dict_obj_info
1070 where type <= 6
1071 and (id = @t1_id or
1072 instr(fq_name, concat("/", cast(@t1_id as char(10)), "/")) or
1073 instr(fq_name, concat("_", cast(@t1_id as char(10)), "_")))
1074 order by type, state, parent_obj_type, fq_name;
1075 type state parent_obj_type fq_name nesting
1076 2 4 0 ndbinfo/def/t1 root
1077 2 4 2 ndbinfo/def/NDB$BLOB_<t1 id>_4 child_of_t1
1078 3 4 2 sys/def/<t1 id>/ix2$unique child_of_t1
1079 6 4 2 sys/def/<t1 id>/ix1 child_of_t1
1080 6 4 2 sys/def/<t1 id>/PRIMARY child_of_t1
1081
1082
1083 create view frag_sum as
1084 select
1085 sum(fixed_elem_alloc_bytes) as fixed_elem_alloc_bytes,
1086 sum(var_elem_alloc_bytes) as var_elem_alloc_bytes,
1087 sum(hash_index_alloc_bytes) as hash_index_alloc_bytes
1088 from ndbinfo.memory_per_fragment;
1089
1090 create view pool_sum as
1091 select pool_name, block_number, max(entry_size) * sum(used) as used_bytes
1092 from ndbinfo.ndb$pools where pool_name <> "Data memory"
1093 or block_number=249 group by pool_name, block_number;
1094
1095 create view check_l2pmap_pools as
1096 select min(floor(1000*nodes.total*nodes.entry_size/
1097 (pages.used*pages.entry_size))) as l2pmap_ratio
1098 from ndbinfo.ndb$pools as pages join ndbinfo.ndb$pools as nodes
1099 where pages.node_id=nodes.node_id
1100 and pages.block_instance=nodes.block_instance
1101 and pages.block_number=nodes.block_number
1102 and pages.pool_name="L2PMap pages"
1103 and nodes.pool_name="L2PMap nodes";
1104
1105 create view l2p_frag_sum as
1106 select sum(tuple_l2pmap_alloc_bytes) as l2p_sum, 'TUP' as block
1107 from ndbinfo.ndb$frag_mem_use
1108 union
1109 select sum(hash_index_l2pmap_alloc_bytes) as l2p_sum, 'ACC' as block
1110 from ndbinfo.ndb$frag_mem_use;
1111
1112
1113 select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum
1114 where pool_name='Index memory';
1115 used_bytes - hash_index_alloc_bytes
1116 0
1117
1118 select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
1119 into @diff
1120 from pool_sum, frag_sum
1121 where pool_name='Data memory';
1122 select @diff;
1123 @diff
1124 0
1125
1126
1127 select l2p_sum-used_bytes from l2p_frag_sum, pool_sum
1128 where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or
1129 (block='TUP' and block_number=249));
1130 l2p_sum-used_bytes
1131 0
1132 0
1133
1134 select * from check_l2pmap_pools;
1135 l2pmap_ratio
1136 996
1137
1138 select * from sum_per_tab;
1139 fq_name parent_fq_name type fixed_elem_count fixed_elem_size_bytes fixed_elem_alloc_ok fixed_elem_free_bytes_ok var_elem_count var_bytes_ok
1140 ndbinfo/def/NDB$BLOB_<t1 id>_4 ndbinfo/def/t1 User table 1400 36 1.0 1 1400 1
1141 ndbinfo/def/t1 NULL User table 2000 292 1.0 1 2000 1
1142 sys/def/<t1 id>/ix1 ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1
1143 sys/def/<t1 id>/ix2$unique ndbinfo/def/t1 Unique hash index 2000 28 1.0 1 0 1
1144 sys/def/<t1 id>/PRIMARY ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1
1145
1146 delete from t1 where a%10=0;
1147
1148 select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum
1149 where pool_name='Index memory';
1150 used_bytes - hash_index_alloc_bytes
1151 0
1152
1153 select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
1154 from pool_sum, frag_sum
1155 where pool_name='Data memory';
1156 used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
1157 0
1158
1159 select l2p_sum-used_bytes from l2p_frag_sum, pool_sum
1160 where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or
1161 (block='TUP' and block_number=249));
1162 l2p_sum-used_bytes
1163 0
1164 0
1165
1166 select * from check_l2pmap_pools;
1167 l2pmap_ratio
1168 996
1169
1170 select * from sum_per_tab;
1171 fq_name parent_fq_name type fixed_elem_count fixed_elem_size_bytes fixed_elem_alloc_ok fixed_elem_free_bytes_ok var_elem_count var_bytes_ok
1172 ndbinfo/def/NDB$BLOB_<t1 id>_4 ndbinfo/def/t1 User table 1400 36 1.0 1 1400 1
1173 ndbinfo/def/t1 NULL User table 1800 292 1.0 1 1800 1
1174 sys/def/<t1 id>/ix1 ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1
1175 sys/def/<t1 id>/ix2$unique ndbinfo/def/t1 Unique hash index 1800 28 1.0 1 0 1
1176 sys/def/<t1 id>/PRIMARY ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1
1177
1178 update t1 set vc1=repeat('x', a%300), tx1=repeat('y', a*2);
1179
1180 select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum
1181 where pool_name='Index memory';
1182 used_bytes - hash_index_alloc_bytes
1183 0
1184
1185 select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
1186 from pool_sum, frag_sum
1187 where pool_name='Data memory';
1188 used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
1189 0
1190
1191 select l2p_sum-used_bytes from l2p_frag_sum, pool_sum
1192 where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or
1193 (block='TUP' and block_number=249));
1194 l2p_sum-used_bytes
1195 0
1196 0
1197
1198 select * from check_l2pmap_pools;
1199 l2pmap_ratio
1200 996
1201
1202 select * from sum_per_tab;
1203 fq_name parent_fq_name type fixed_elem_count fixed_elem_size_bytes fixed_elem_alloc_ok fixed_elem_free_bytes_ok var_elem_count var_bytes_ok
1204 ndbinfo/def/NDB$BLOB_<t1 id>_4 ndbinfo/def/t1 User table 1568 36 1.0 1 1568 1
1205 ndbinfo/def/t1 NULL User table 1800 292 1.0 1 1800 1
1206 sys/def/<t1 id>/ix1 ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1
1207 sys/def/<t1 id>/ix2$unique ndbinfo/def/t1 Unique hash index 1800 28 1.0 1 0 1
1208 sys/def/<t1 id>/PRIMARY ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1
1209
1210 drop table t1;
1211 drop view l2p_frag_sum;
1212 drop view check_l2pmap_pools;
1213 drop view sum_per_tab;
1214 drop view pool_sum;
1215 drop view frag_sum;
1216
1217 create view frag_ops as select
1218 if(type in ('Ordered index', 'Unique hash index'),
1219 substring(fq_name from 1+locate('/', fq_name, 9)),
1220 if(fq_name like '%BLOB%', left(fq_name, locate('_', fq_name)), fq_name))
1221 as fq_name,
1222 parent_fq_name,
1223 type,
1224 count(fragment_num) as frag_count,
1225 sum(tot_key_reads) as tot_key_reads,
1226 sum(tot_key_inserts) as tot_key_inserts,
1227 sum(tot_key_updates) as tot_key_updates,
1228 sum(tot_key_writes) as tot_key_writes,
1229 sum(tot_key_deletes) as tot_key_deletes,
1230 sum(tot_key_refs) as tot_key_refs,
1231 round(log2(1+sum(tot_key_attrinfo_bytes))) as log_tot_key_attrinfo_bytes,
1232 sum(tot_key_keyinfo_bytes) as tot_key_keyinfo_bytes,
1233 sum(tot_key_prog_bytes) as tot_key_prog_bytes,
1234 sum(tot_key_inst_exec) as tot_key_inst_exec,
1235 sum(tot_key_bytes_returned) as tot_key_bytes_returned,
1236 sum(tot_frag_scans) as tot_frag_scans,
1237 sum(tot_scan_rows_examined) as tot_scan_rows_examined,
1238 sum(tot_scan_rows_returned) as tot_scan_rows_returned,
1239 sum(tot_scan_bytes_returned) as tot_scan_bytes_returned,
1240 sum(tot_scan_prog_bytes) as tot_scan_prog_bytes,
1241 sum(tot_scan_bound_bytes) as tot_scan_bound_bytes,
1242 sum(tot_scan_inst_exec) as tot_scan_inst_exec,
1243 # Ignore concurrent frag_scans, as all tests below expect them to be 0
1244 # but this is not stable as LCP scans are counted as concurrent
1245 # frag scans while they are running (but not counted as tot_frag_scans
1246 # when they complete).
1247 # Arguably it is useful to see a live LCP scan, and this test has no
1248 # use for conc_frag_scans!=0, so avoid looking at it for determinism
1249 #sum(conc_frag_scans) as conc_frag_scans,
1250 sum(conc_qd_frag_scans) as conc_qd_frag_scans,
1251 sum(tot_commits) as tot_commits
1252 from ndbinfo.operations_per_fragment opf
1253 where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%'
1254 group by table_id, type, opf.fq_name, parent_fq_name
1255 order by ifnull(parent_fq_name, opf.fq_name), opf.fq_name;
1256
1257 create table t1 (
1258 a int not null,
1259 b int not null,
1260 c int not null,
1261 d int not null,
1262 t text not null,
1263 primary key (b,a)
1264 ) engine=ndbcluster
1265 partition by key(b,a) partitions 8;
1266
1267 create unique index ix1 on t1(d) using hash;
1268
1269 create table numbers(
1270 i int
1271 );
1272
1273 insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1274
1275 insert into t1 select x1.i+x2.i*10, 1, 1+x1.i+x2.i*10, 1+x1.i+x2.i*10,
1276 repeat('x', 512)
1277 from numbers as x1, numbers as x2;
1278
1279 drop table numbers;
1280
1281 create table t2 (
1282 a int,
1283 b int,
1284 primary key(a) using hash)
1285 engine = ndb
1286 partition by key(a) partitions 8;
1287
1288 insert into t2 values (1, 2);
1289 insert into t2 values (2, 3);
1290 insert into t2 values (3, 1);
1291
1292 select * from frag_ops;;
1293 fq_name ndbinfo/def/NDB$BLOB_
1294 parent_fq_name ndbinfo/def/t1
1295 type User table
1296 frag_count 16
1297 tot_key_reads 0
1298 tot_key_inserts 100
1299 tot_key_updates 0
1300 tot_key_writes 100
1301 tot_key_deletes 0
1302 tot_key_refs 0
1303 log_tot_key_attrinfo_bytes 16
1304 tot_key_keyinfo_bytes 2400
1305 tot_key_prog_bytes 0
1306 tot_key_inst_exec 0
1307 tot_key_bytes_returned 0
1308 tot_frag_scans 0
1309 tot_scan_rows_examined 0
1310 tot_scan_rows_returned 0
1311 tot_scan_bytes_returned 0
1312 tot_scan_prog_bytes 0
1313 tot_scan_bound_bytes 0
1314 tot_scan_inst_exec 0
1315 conc_qd_frag_scans 0
1316 tot_commits 200
1317 fq_name ndbinfo/def/t1
1318 parent_fq_name NULL
1319 type User table
1320 frag_count 16
1321 tot_key_reads 0
1322 tot_key_inserts 200
1323 tot_key_updates 200
1324 tot_key_writes 0
1325 tot_key_deletes 0
1326 tot_key_refs 0
1327 log_tot_key_attrinfo_bytes 16
1328 tot_key_keyinfo_bytes 3200
1329 tot_key_prog_bytes 0
1330 tot_key_inst_exec 0
1331 tot_key_bytes_returned 0
1332 tot_frag_scans 24
1333 tot_scan_rows_examined 0
1334 tot_scan_rows_returned 0
1335 tot_scan_bytes_returned 0
1336 tot_scan_prog_bytes 64
1337 tot_scan_bound_bytes 0
1338 tot_scan_inst_exec 0
1339 conc_qd_frag_scans 0
1340 tot_commits 400
1341 fq_name ix1$unique
1342 parent_fq_name ndbinfo/def/t1
1343 type Unique hash index
1344 frag_count 16
1345 tot_key_reads 0
1346 tot_key_inserts 200
1347 tot_key_updates 0
1348 tot_key_writes 0
1349 tot_key_deletes 0
1350 tot_key_refs 0
1351 log_tot_key_attrinfo_bytes 12
1352 tot_key_keyinfo_bytes 800
1353 tot_key_prog_bytes 0
1354 tot_key_inst_exec 0
1355 tot_key_bytes_returned 0
1356 tot_frag_scans 0
1357 tot_scan_rows_examined 0
1358 tot_scan_rows_returned 0
1359 tot_scan_bytes_returned 0
1360 tot_scan_prog_bytes 0
1361 tot_scan_bound_bytes 0
1362 tot_scan_inst_exec 0
1363 conc_qd_frag_scans 0
1364 tot_commits 200
1365 fq_name PRIMARY
1366 parent_fq_name ndbinfo/def/t1
1367 type Ordered index
1368 frag_count 16
1369 tot_key_reads 0
1370 tot_key_inserts 0
1371 tot_key_updates 0
1372 tot_key_writes 0
1373 tot_key_deletes 0
1374 tot_key_refs 0
1375 log_tot_key_attrinfo_bytes 0
1376 tot_key_keyinfo_bytes 0
1377 tot_key_prog_bytes 0
1378 tot_key_inst_exec 0
1379 tot_key_bytes_returned 0
1380 tot_frag_scans 1
1381 tot_scan_rows_examined 0
1382 tot_scan_rows_returned 0
1383 tot_scan_bytes_returned 0
1384 tot_scan_prog_bytes 0
1385 tot_scan_bound_bytes 0
1386 tot_scan_inst_exec 0
1387 conc_qd_frag_scans 0
1388 tot_commits 0
1389 fq_name ndbinfo/def/t2
1390 parent_fq_name NULL
1391 type User table
1392 frag_count 16
1393 tot_key_reads 0
1394 tot_key_inserts 6
1395 tot_key_updates 0
1396 tot_key_writes 0
1397 tot_key_deletes 0
1398 tot_key_refs 0
1399 log_tot_key_attrinfo_bytes 7
1400 tot_key_keyinfo_bytes 24
1401 tot_key_prog_bytes 0
1402 tot_key_inst_exec 0
1403 tot_key_bytes_returned 0
1404 tot_frag_scans 8
1405 tot_scan_rows_examined 0
1406 tot_scan_rows_returned 0
1407 tot_scan_bytes_returned 0
1408 tot_scan_prog_bytes 32
1409 tot_scan_bound_bytes 0
1410 tot_scan_inst_exec 0
1411 conc_qd_frag_scans 0
1412 tot_commits 6
1413 explain select count(*) from t1 where d<10;
1414 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1415 1 SIMPLE t1 # ALL ix1 NULL NULL NULL 100 33.33 Using pushed condition (`ndbinfo`.`t1`.`d` < 10)
1416 Warnings:
1417 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` where (`ndbinfo`.`t1`.`d` < 10)
1418 select count(*) from t1 where d<10;
1419 count(*)
1420 9
1421
1422 explain select count(*) from t1 where b=1 and a<10;
1423 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1424 1 SIMPLE t1 # range PRIMARY PRIMARY 8 NULL 3 100.00 Using pushed condition ((`ndbinfo`.`t1`.`b` = 1) and (`ndbinfo`.`t1`.`a` < 10)); Using MRR
1425 Warnings:
1426 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` where ((`ndbinfo`.`t1`.`b` = 1) and (`ndbinfo`.`t1`.`a` < 10))
1427 select count(*) from t1 where b=1 and a<10;
1428 count(*)
1429 10
1430
1431 explain select * from t1 where b=1 and a = -5;
1432 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1433 1 SIMPLE t1 # eq_ref PRIMARY PRIMARY 8 const,const 1 100.00 NULL
1434 Warnings:
1435 Note 1003 /* select#1 */ select `ndbinfo`.`t1`.`a` AS `a`,`ndbinfo`.`t1`.`b` AS `b`,`ndbinfo`.`t1`.`c` AS `c`,`ndbinfo`.`t1`.`d` AS `d`,`ndbinfo`.`t1`.`t` AS `t` from `ndbinfo`.`t1` where ((`ndbinfo`.`t1`.`a` = -(5)) and (`ndbinfo`.`t1`.`b` = 1))
1436 select * from t1 where b=1 and a = -5;
1437 a b c d t
1438
1439 insert into t1 values (1,1,1,1,'');
1440 ERROR 23000: Duplicate entry '1-1' for key 't1.PRIMARY'
1441
1442 replace t1 values (-1,-1,-1,-1,'');
1443 replace t1 values (1,1,5,500,'');
1444
1445 update t1 set d = -d, t = repeat('a', 300) where b=1 and a > 90;
1446
1447 delete from t1 where a%30=0;
1448
1449
1450
1451
1452 explain select count(*)
1453 from t2 as tx, t2 as ty
1454 where tx.a in (1,3,5)
1455 and ty.a = tx.b;
1456 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1457 1 SIMPLE tx # range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using pushed condition ((`ndbinfo`.`tx`.`a` in (1,3,5)) and (`ndbinfo`.`tx`.`b` is not null)); Using MRR
1458 1 SIMPLE ty # eq_ref PRIMARY PRIMARY 4 ndbinfo.tx.b 1 100.00 Child of 'tx' in pushed join@1
1459 Warnings:
1460 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t2` `tx` join `ndbinfo`.`t2` `ty` where ((`ndbinfo`.`ty`.`a` = `ndbinfo`.`tx`.`b`) and (`ndbinfo`.`tx`.`a` in (1,3,5)))
1461
1462 create table save_counters as select * from frag_ops;
1463
1464 select count(*)
1465 from t2 as tx, t2 as ty
1466 where tx.a in (1,3,5)
1467 and ty.a = tx.b;
1468 count(*)
1469 2
1470
1471 create view changes as select
1472 new.fq_name,
1473 new.parent_fq_name,
1474 new.type,
1475 new.frag_count,
1476 new.tot_key_reads - old.tot_key_reads,
1477 new.tot_key_inserts - old.tot_key_inserts,
1478 new.tot_key_updates - old.tot_key_updates,
1479 new.tot_key_writes - old.tot_key_writes,
1480 new.tot_key_deletes - old.tot_key_deletes,
1481 new.tot_key_refs - old.tot_key_refs,
1482 new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes,
1483 new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes,
1484 new.tot_key_prog_bytes - old.tot_key_prog_bytes,
1485 new.tot_key_inst_exec - old.tot_key_inst_exec,
1486 new.tot_key_bytes_returned - old.tot_key_bytes_returned,
1487 new.tot_frag_scans - old.tot_frag_scans,
1488 new.tot_scan_rows_examined - old.tot_scan_rows_examined,
1489 new.tot_scan_rows_returned - old.tot_scan_rows_returned,
1490 new.tot_scan_bytes_returned - old.tot_scan_bytes_returned,
1491 new.tot_scan_prog_bytes - old.tot_scan_prog_bytes,
1492 new.tot_scan_bound_bytes - old.tot_scan_bound_bytes,
1493 new.tot_scan_inst_exec - old.tot_scan_inst_exec,
1494 new.tot_commits - old.tot_commits
1495 from frag_ops as new, save_counters as old
1496 where new.fq_name=old.fq_name and new.fq_name like '%t2';
1497
1498 select * from changes;
1499 fq_name ndbinfo/def/t2
1500 parent_fq_name NULL
1501 type User table
1502 frag_count 16
1503 new.tot_key_reads - old.tot_key_reads 5
1504 new.tot_key_inserts - old.tot_key_inserts 0
1505 new.tot_key_updates - old.tot_key_updates 0
1506 new.tot_key_writes - old.tot_key_writes 0
1507 new.tot_key_deletes - old.tot_key_deletes 0
1508 new.tot_key_refs - old.tot_key_refs 1
1509 new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes 2
1510 new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes 20
1511 new.tot_key_prog_bytes - old.tot_key_prog_bytes 168
1512 new.tot_key_inst_exec - old.tot_key_inst_exec 7
1513 new.tot_key_bytes_returned - old.tot_key_bytes_returned 56
1514 new.tot_frag_scans - old.tot_frag_scans 0
1515 new.tot_scan_rows_examined - old.tot_scan_rows_examined 0
1516 new.tot_scan_rows_returned - old.tot_scan_rows_returned 0
1517 new.tot_scan_bytes_returned - old.tot_scan_bytes_returned 0
1518 new.tot_scan_prog_bytes - old.tot_scan_prog_bytes 0
1519 new.tot_scan_bound_bytes - old.tot_scan_bound_bytes 0
1520 new.tot_scan_inst_exec - old.tot_scan_inst_exec 0
1521 new.tot_commits - old.tot_commits 0
1522
1523 drop view changes;
1524 drop table save_counters;
1525 drop view frag_ops;
1526 drop table t1;
1527 drop table t2;
1528
1529
1530 ## Check that the new tables return data and exists.
1531 select count(*) > 10 from blocks;
1532 count(*) > 10
1533 1
1534 select param_number, param_name, param_description, param_default,
1535 param_min, param_max from ndb$config_params
1536 where param_name = "DataMemory";
1537 param_number param_name param_description param_default param_min param_max
1538 112 DataMemory Number bytes on each ndbd(DB) node allocated for storing data 102760448 1048576 17592186044416
1539 select * from ndb$dict_obj_types where type_id < 3;
1540 type_id type_name
1541 1 System table
1542 2 User table
1543 select * from ndb$dbtc_apiconnect_state;
1544 select * from ndb$dblqh_tcconnect_state;
1545 ## Check that the new views return data and exists.
1546 select block_number from blocks where block_name = "DBTC";
1547 block_number
1548 245
1549 select param_name from config_params where param_number = 101;
1550 param_name
1551 NoOfReplicas
1552 select type_name from ndb$dict_obj_types where type_id = 3;
1553 type_name
1554 Unique hash index
1555
1556 ## All tables in ndbinfo/ should now be using engine=NDBINFO
1557 select count(*) from information_schema.tables
1558 where table_schema = 'ndbinfo' and engine != 'ndbinfo';
1559 count(*)
1560 0
1561
1562 select * from ndbinfo.config_params order by param_number;
1563 UPDATE ndb$config_params SET param_number=1 WHERE param_name = "NoOfReplicas";
1564 ERROR HY000: Table 'ndb$config_params' is read only
1565
1566 UPDATE config_params SET param_number=1 WHERE param_name = "NoOfReplicas";
1567 ERROR HY000: Table 'ndb$config_params' is read only
1568
1569 select node_id, param_name, config_param, config_value
1570 from ndbinfo.config_values cv, ndbinfo.config_params cp
1571 where cv.config_param = cp.param_number and
1572 cp.param_name = 'MaxNoOfConcurrentTransactions'
1573 order by node_id;
1574 node_id param_name config_param config_value
1575 1 MaxNoOfConcurrentTransactions 106 2048
1576 2 MaxNoOfConcurrentTransactions 106 2048
1577
1578 select node_id, param_name, config_value
1579 from ndbinfo.config_values cv, ndbinfo.config_params cp
1580 where cv.config_param = cp.param_number and
1581 cp.param_name = 'NodeId'
1582 order by node_id;
1583 node_id param_name config_value
1584 1 NodeId 1
1585 2 NodeId 2
1586
1587 select node_id, concat(config_value / 1024 / 1024, 'M') as DataMemory
1588 from ndbinfo.config_values
1589 where config_param = 112 /* DataMemory */
1590 order by node_id;
1591 node_id DataMemory
1592 1 30M
1593 2 30M
1594
1595 set @@ndbinfo_show_hidden=TRUE;
1596 desc ndb$processes;
1597 Field Type Null Key Default Extra
1598 reporting_node_id int unsigned YES NULL
1599 node_id int unsigned YES NULL
1600 node_type int unsigned YES NULL
1601 node_version varchar(512) YES NULL
1602 process_id int unsigned YES NULL
1603 angel_process_id int unsigned YES NULL
1604 process_name varchar(512) YES NULL
1605 service_URI varchar(512) YES NULL
1606 set @@ndbinfo_show_hidden=default;
1607 SELECT view_definition from information_schema.views
1608 WHERE table_schema = 'ndbinfo' and table_name = 'processes';
1609 VIEW_DEFINITION
1610 select distinct `ndbinfo`.`ndb$processes`.`node_id` AS `node_id`,(case `ndbinfo`.`ndb$processes`.`node_type` when 0 then 'NDB' when 1 then 'API' when 2 then 'MGM' else NULL end) AS `node_type`,`ndbinfo`.`ndb$processes`.`node_version` AS `node_version`,nullif(`ndbinfo`.`ndb$processes`.`process_id`,0) AS `process_id`,nullif(`ndbinfo`.`ndb$processes`.`angel_process_id`,0) AS `angel_process_id`,`ndbinfo`.`ndb$processes`.`process_name` AS `process_name`,`ndbinfo`.`ndb$processes`.`service_URI` AS `service_URI` from `ndbinfo`.`ndb$processes` order by `ndbinfo`.`ndb$processes`.`node_id`
1611
1612 SELECT reporting_node_id, node_id, node_type,
1613 replace(replace(process_name,"-debug",""),".exe","") AS process_name
1614 FROM ndb$processes
1615 ORDER BY reporting_node_id, node_id;
1616 reporting_node_id node_id node_type process_name
1617 1 1 0 ndbmtd
1618 1 3 2 ndb_mgmd
1619 1 16 1 mysqld
1620 1 32 1 mysqld
1621 1 48 1 mysqld
1622 1 49 1 mysqld
1623 1 63 1 mysqld
1624 1 127 1 mysqld
1625 2 2 0 ndbmtd
1626 2 3 2 ndb_mgmd
1627 2 16 1 mysqld
1628 2 32 1 mysqld
1629 2 48 1 mysqld
1630 2 49 1 mysqld
1631 2 63 1 mysqld
1632 2 127 1 mysqld
1633
1634 set @windows = convert(@@version_compile_os using latin1)
1635 in ('Win32', 'Win64', 'Windows');
1636
1637 SELECT node_id,
1638 node_type,
1639 replace(replace(process_name,"-debug",""),".exe","") AS process_name,
1640 if(process_id is null, "null", "not_null") as proc_id,
1641 if(@windows AND node_type = 'API',
1642 "null",
1643 if(angel_process_id is null, "null", "not_null")) as angel_proc_id,
1644 if(NOT @windows AND node_type = 'API',
1645 "not_null",
1646 if(angel_process_id is null, "null", "not_null")) as monitor_proc_id
1647 FROM processes
1648 ORDER BY node_id;
1649 node_id node_type process_name proc_id angel_proc_id monitor_proc_id
1650 1 NDB ndbmtd not_null not_null not_null
1651 2 NDB ndbmtd not_null not_null not_null
1652 3 MGM ndb_mgmd not_null null null
1653 16 API mysqld not_null null not_null
1654 32 API mysqld not_null null not_null
1655 48 API mysqld not_null null not_null
1656 49 API mysqld not_null null not_null
1657 63 API mysqld not_null null not_null
1658 127 API mysqld not_null null not_null
1659
1660 set @@ndbinfo_show_hidden=TRUE;
1661 desc ndb$config_nodes;
1662 Field Type Null Key Default Extra
1663 reporting_node_id int unsigned YES NULL
1664 node_id int unsigned YES NULL
1665 node_type int unsigned YES NULL
1666 node_hostname varchar(512) YES NULL
1667 set @@ndbinfo_show_hidden=default;
1668 select view_definition, is_updatable, security_type
1669 FROM information_schema.views
1670 WHERE table_schema = 'ndbinfo' AND table_name = 'config_nodes';
1671 VIEW_DEFINITION IS_UPDATABLE SECURITY_TYPE
1672 select distinct `ndbinfo`.`ndb$config_nodes`.`node_id` AS `node_id`,(case `ndbinfo`.`ndb$config_nodes`.`node_type` when 0 then 'NDB' when 1 then 'API' when 2 then 'MGM' else NULL end) AS `node_type`,`ndbinfo`.`ndb$config_nodes`.`node_hostname` AS `node_hostname` from `ndbinfo`.`ndb$config_nodes` order by `ndbinfo`.`ndb$config_nodes`.`node_id` NO INVOKER
1673
1674 SELECT * from ndb$config_nodes order by reporting_node_id, node_id;
1675 reporting_node_id node_id node_type node_hostname
1676 1 1 0 localhost
1677 1 2 0 localhost
1678 1 3 2 localhost
1679 1 16 1 localhost
1680 1 32 1 localhost
1681 1 48 1 localhost
1682 1 49 1 localhost
1683 1 63 1 localhost
1684 1 127 1 localhost
1685 1 192 1 localhost
1686 1 228 1 localhost
1687 1 229 1 localhost
1688 1 230 1 localhost
1689 1 231 1 localhost
1690 1 232 1 localhost
1691 1 233 1 localhost
1692 1 255 1 localhost
1693 2 1 0 localhost
1694 2 2 0 localhost
1695 2 3 2 localhost
1696 2 16 1 localhost
1697 2 32 1 localhost
1698 2 48 1 localhost
1699 2 49 1 localhost
1700 2 63 1 localhost
1701 2 127 1 localhost
1702 2 192 1 localhost
1703 2 228 1 localhost
1704 2 229 1 localhost
1705 2 230 1 localhost
1706 2 231 1 localhost
1707 2 232 1 localhost
1708 2 233 1 localhost
1709 2 255 1 localhost
1710
1711 SELECT node_type, count(*),
1712 GROUP_CONCAT(node_id order by 1 asc separator " ") ids
1713 FROM config_nodes group by node_type order by node_type;
1714 node_type count(*) ids
1715 API 14 16 32 48 49 63 127 192 228 229 230 231 232 233 255
1716 MGM 1 3
1717 NDB 2 1 2
1718
1719
1720 #
1721 # Bug#26048272 IMPLEMENT NDBINFO TABLE FOR LOOKUP OF ERROR CODES AND MESSAGES
1722 #
1723 select *
1724 from ndbinfo.error_messages
1725 where error_code = 920;
1726 error_code error_description error_status error_classification
1727 920 Row operation defined after refreshTuple() Permanent error Application error
1728
1729 select *
1730 from ndbinfo.error_messages
1731 where error_code = 2304;
1732 error_code error_description error_status error_classification
1733 2304 Array index out of range Temporary error, restart node Internal error, programming error or missing error message, please report a bug
1734
1735 select *
1736 from ndbinfo.error_messages
1737 where error_code = 5001;
1738 error_code error_description error_status error_classification
1739 5001 Usage error
1740
1741 select count(*) > 700
1742 from ndbinfo.error_messages;
1743 count(*) > 700
1744 1
1745
1746 desc hwinfo;
1747 Field Type Null Key Default Extra
1748 node_id int unsigned YES NULL
1749 cpu_cnt_max int unsigned YES NULL
1750 cpu_cnt int unsigned YES NULL
1751 num_cpu_cores int unsigned YES NULL
1752 num_cpu_sockets int unsigned YES NULL
1753 HW_memory_size bigint unsigned YES NULL
1754 model_name varchar(512) YES NULL
1755 desc cpuinfo;
1756 Field Type Null Key Default Extra
1757 node_id int unsigned YES NULL
1758 cpu_no int unsigned YES NULL
1759 cpu_online int unsigned YES NULL
1760 core_id int unsigned YES NULL
1761 socket_id int unsigned YES NULL
1762 desc cpudata;
1763 Field Type Null Key Default Extra
1764 node_id int unsigned YES NULL
1765 cpu_no int unsigned YES NULL
1766 cpu_online int unsigned YES NULL
1767 cpu_userspace_time int unsigned YES NULL
1768 cpu_idle_time int unsigned YES NULL
1769 cpu_system_time int unsigned YES NULL
1770 cpu_interrupt_time int unsigned YES NULL
1771 cpu_exec_vm_time int unsigned YES NULL
1772 desc cpudata_50ms;
1773 Field Type Null Key Default Extra
1774 node_id int unsigned YES NULL
1775 measurement_id int unsigned YES NULL
1776 cpu_no int unsigned YES NULL
1777 cpu_online int unsigned YES NULL
1778 cpu_userspace_time int unsigned YES NULL
1779 cpu_idle_time int unsigned YES NULL
1780 cpu_system_time int unsigned YES NULL
1781 cpu_interrupt_time int unsigned YES NULL
1782 cpu_exec_vm_time int unsigned YES NULL
1783 elapsed_time int unsigned YES NULL
1784 desc cpudata_1sec;
1785 Field Type Null Key Default Extra
1786 node_id int unsigned YES NULL
1787 measurement_id int unsigned YES NULL
1788 cpu_no int unsigned YES NULL
1789 cpu_online int unsigned YES NULL
1790 cpu_userspace_time int unsigned YES NULL
1791 cpu_idle_time int unsigned YES NULL
1792 cpu_system_time int unsigned YES NULL
1793 cpu_interrupt_time int unsigned YES NULL
1794 cpu_exec_vm_time int unsigned YES NULL
1795 elapsed_time int unsigned YES NULL
1796 desc cpudata_20sec;
1797 Field Type Null Key Default Extra
1798 node_id int unsigned YES NULL
1799 measurement_id int unsigned YES NULL
1800 cpu_no int unsigned YES NULL
1801 cpu_online int unsigned YES NULL
1802 cpu_userspace_time int unsigned YES NULL
1803 cpu_idle_time int unsigned YES NULL
1804 cpu_system_time int unsigned YES NULL
1805 cpu_interrupt_time int unsigned YES NULL
1806 cpu_exec_vm_time int unsigned YES NULL
1807 elapsed_time int unsigned YES NULL
1808
1809 select * from hwinfo;
1810 select * from cpuinfo;
1811 select * from cpudata;
1812 select * from cpudata_50ms;
1813 select * from cpudata_1sec;
1814 select * from cpudata_20sec;
1815 #
1816 # BUG#32073640 Add ndbinfo.backup_id
1817 # - the table have one column and one row
1818 #
1819 select * from backup_id;
1820 id
1821 <backup_id>
1822
1823 #
1824 # Bug#32198754 EXTEND MTR CHECK TESTCASES TO CHECK NDB DICTIONARY ...
1825 # - adds a new ndbinfo.dict_obj_tree view which can show
1826 # the object tree of objects in NDB dictionary
1827 #
1828 CREATE LOGFILE GROUP first_lg
1829 ADD UNDOFILE 'lg1_undofile.dat' INITIAL_SIZE 1M UNDO_BUFFER_SIZE = 1M
1830 ENGINE=NDB;
1831
1832 CREATE TABLESPACE first_ts
1833 ADD DATAFILE 'ts1_datafile.dat'
1834 USE LOGFILE GROUP first_lg INITIAL_SIZE 2M
1835 ENGINE NDB;
1836
1837 CREATE TABLE test.t1 (
1838 a int PRIMARY KEY
1839 ) ENGINE = NDB TABLESPACE first_ts STORAGE DISK;
1840
1841 CREATE TABLE test.t2 (
1842 a int PRIMARY KEY,
1843 b int,
1844 UNIQUE(b)
1845 ) engine = NDB;
1846
1847 SELECT name, parent_name, root_name, level, indented_name, path
1848 FROM ndbinfo.dict_obj_tree
1849 WHERE root_name IN ('first_lg', 'first_ts', 'test/def/t1', 'test/def/t2');
1850 name parent_name root_name level indented_name path
1851 first_lg first_lg first_lg 1 first_lg first_lg
1852 lg1_undofile.dat first_lg first_lg 2 -> lg1_undofile.dat first_lg -> lg1_undofile.dat
1853 first_ts first_ts first_ts 1 first_ts first_ts
1854 ts1_datafile.dat first_ts first_ts 2 -> ts1_datafile.dat first_ts -> ts1_datafile.dat
1855 test/def/t1 test/def/t1 test/def/t1 1 test/def/t1 test/def/t1
1856 sys/def/<id>/PRIMARY test/def/t1 test/def/t1 2 -> sys/def/<id>/PRIMARY test/def/t1 -> sys/def/<id>/PRIMARY
1857 NDB$INDEX_<id>_CUSTOM sys/def/<id>/PRIMARY test/def/t1 3 -> NDB$INDEX_<id>_CUSTOM test/def/t1 -> sys/def/<id>/PRIMARY -> NDB$INDEX_<id>_CUSTOM
1858 test/def/t2 test/def/t2 test/def/t2 1 test/def/t2 test/def/t2
1859 sys/def/<id>/b test/def/t2 test/def/t2 2 -> sys/def/<id>/b test/def/t2 -> sys/def/<id>/b
1860 NDB$INDEX_<id>_CUSTOM sys/def/<id>/b test/def/t2 3 -> NDB$INDEX_<id>_CUSTOM test/def/t2 -> sys/def/<id>/b -> NDB$INDEX_<id>_CUSTOM
1861 sys/def/<id>/b$unique test/def/t2 test/def/t2 2 -> sys/def/<id>/b$unique test/def/t2 -> sys/def/<id>/b$unique
1862 NDB$INDEX_<id>_UI sys/def/<id>/b$unique test/def/t2 3 -> NDB$INDEX_<id>_UI test/def/t2 -> sys/def/<id>/b$unique -> NDB$INDEX_<id>_UI
1863 sys/def/<id>/PRIMARY test/def/t2 test/def/t2 2 -> sys/def/<id>/PRIMARY test/def/t2 -> sys/def/<id>/PRIMARY
1864 NDB$INDEX_<id>_CUSTOM sys/def/<id>/PRIMARY test/def/t2 3 -> NDB$INDEX_<id>_CUSTOM test/def/t2 -> sys/def/<id>/PRIMARY -> NDB$INDEX_<id>_CUSTOM
1865
1866 SELECT indented_name, root_name FROM ndbinfo.dict_obj_tree
1867 WHERE root_name = 'test/def/t2';
1868 indented_name root_name
1869 test/def/t2 test/def/t2
1870 -> sys/def/<id>/b test/def/t2
1871 -> NDB$INDEX_<id>_CUSTOM test/def/t2
1872 -> sys/def/<id>/b$unique test/def/t2
1873 -> NDB$INDEX_<id>_UI test/def/t2
1874 -> sys/def/<id>/PRIMARY test/def/t2
1875 -> NDB$INDEX_<id>_CUSTOM test/def/t2
1876
1877 DROP TABLE test.t2;
1878 DROP TABLE test.t1;
1879 ALTER TABLESPACE first_ts DROP DATAFILE 'ts1_datafile.dat';
1880 DROP TABLESPACE first_ts;
1881 DROP LOGFILE GROUP first_lg ENGINE=NDB;