Gecko [ www.jdcpatala.org ]

Name	Size	Permission
bug36547.result	455 B	-rw-r--r--
loaddata_autocom_ndb.result	740 B	-rw-r--r--
ndb_add_partition.result	13.36 KB	-rw-r--r--
ndb_addnode_restart.result	384 B	-rw-r--r--
ndb_addnode_restart2.result	454 B	-rw-r--r--
ndb_alter_table.result	19.49 KB	-rw-r--r--
ndb_alter_table2.result	685 B	-rw-r--r--
ndb_alter_table3.result	1.48 KB	-rw-r--r--
ndb_alter_table_backup.result	1.76 KB	-rw-r--r--
ndb_alter_table_dml.result	1.83 KB	-rw-r--r--
ndb_alter_table_error.result	664 B	-rw-r--r--
ndb_alter_table_online.result	29.02 KB	-rw-r--r--
ndb_alter_table_online2.result	3.21 KB	-rw-r--r--
ndb_alter_table_online_multi.r...	2 KB	-rw-r--r--
ndb_auto_increment.result	11.49 KB	-rw-r--r--
ndb_autoinc.result	2.69 KB	-rw-r--r--
ndb_backup_rate.result	912 B	-rw-r--r--
ndb_basic.result	32.68 KB	-rw-r--r--
ndb_bitfield.result	8.39 KB	-rw-r--r--
ndb_blob.result	26.47 KB	-rw-r--r--
ndb_blob_big.result	1.24 KB	-rw-r--r--
ndb_blob_partition.result	9.03 KB	-rw-r--r--
ndb_bug13563280.result	224 B	-rw-r--r--
ndb_bug14166590.result	416 B	-rw-r--r--
ndb_bug17624736.result	1.71 KB	-rw-r--r--
ndb_bug17719439.result	439 B	-rw-r--r--
ndb_bug19390895.result	1.67 KB	-rw-r--r--
ndb_bug26793.result	286 B	-rw-r--r--
ndb_bug31477.result	1.85 KB	-rw-r--r--
ndb_bulk_delete.result	1.87 KB	-rw-r--r--
ndb_bushy_joins.result	88.95 KB	-rw-r--r--
ndb_cache.result	9.35 KB	-rw-r--r--
ndb_cache2.result	13.9 KB	-rw-r--r--
ndb_cache_multi.result	2.44 KB	-rw-r--r--
ndb_cache_multi2.result	3.04 KB	-rw-r--r--
ndb_cache_trans.result	6.65 KB	-rw-r--r--
ndb_charset.result	5.77 KB	-rw-r--r--
ndb_checksum.result	38 B	-rw-r--r--
ndb_column_properties.result	16.01 KB	-rw-r--r--
ndb_condition_pushdown.result	131.71 KB	-rw-r--r--
ndb_config.result	3.36 KB	-rw-r--r--
ndb_config2.result	475 B	-rw-r--r--
ndb_create_table.result	1.02 KB	-rw-r--r--
ndb_cursor.result	792 B	-rw-r--r--
ndb_database.result	1.25 KB	-rw-r--r--
ndb_dbug_lock.result	1.64 KB	-rw-r--r--
ndb_dbug_tc_select.result	6.71 KB	-rw-r--r--
ndb_dd_alter.result	28.43 KB	-rw-r--r--
ndb_dd_basic.result	21.17 KB	-rw-r--r--
ndb_dd_bug12581213.result	334 B	-rw-r--r--
ndb_dd_ddl.result	5.94 KB	-rw-r--r--
ndb_dd_disk2memory.result	15.44 KB	-rw-r--r--
ndb_dd_dump.result	15.97 KB	-rw-r--r--
ndb_dd_initial_lg.result	941 B	-rw-r--r--
ndb_dd_restore_check_tablespac...	8.3 KB	-rw-r--r--
ndb_dd_restore_compat.result	3.73 KB	-rw-r--r--
ndb_dd_sql_features.result	22.62 KB	-rw-r--r--
ndb_ddl_open_trans.result	1.44 KB	-rw-r--r--
ndb_disconnect_ddl.result	291 B	-rw-r--r--
ndb_discover_db.result	1.92 KB	-rw-r--r--
ndb_dist_priv.result	6.36 KB	-rw-r--r--
ndb_dist_priv_bug_13340854.res...	248 B	-rw-r--r--
ndb_err4012.result	2.22 KB	-rw-r--r--
ndb_error_reporter.result	5.61 KB	-rw-r--r--
ndb_fired_trigger_shortage.res...	694 B	-rw-r--r--
ndb_fk_addnode.result	1.39 KB	-rw-r--r--
ndb_fk_alter.result	8.34 KB	-rw-r--r--
ndb_fk_bugs.result	27.58 KB	-rw-r--r--
ndb_fk_build.result	2.31 KB	-rw-r--r--
ndb_fk_cascade_delete.result	4.82 KB	-rw-r--r--
ndb_fk_cascade_delete_multi.re...	2.56 KB	-rw-r--r--
ndb_fk_cascade_update.result	1.66 KB	-rw-r--r--
ndb_fk_create_drop.result	4.8 KB	-rw-r--r--
ndb_fk_db.result	7.18 KB	-rw-r--r--
ndb_fk_deferred_cascade.result	2.82 KB	-rw-r--r--
ndb_fk_disable.result	11.64 KB	-rw-r--r--
ndb_fk_is.result	8.49 KB	-rw-r--r--
ndb_fk_locase.result	2.44 KB	-rw-r--r--
ndb_fk_locking.result	1.47 KB	-rw-r--r--
ndb_fk_multi_column.result	2.04 KB	-rw-r--r--
ndb_fk_mysqldump.result	2.93 KB	-rw-r--r--
ndb_fk_mysqldump2.result	72 B	-rw-r--r--
ndb_fk_no_fk.result	30.14 KB	-rw-r--r--
ndb_fk_no_fk_legacy.result	2.77 KB	-rw-r--r--
ndb_fk_noaction.result	5.68 KB	-rw-r--r--
ndb_fk_resources.result	2.58 KB	-rw-r--r--
ndb_fk_restore.result	14.08 KB	-rw-r--r--
ndb_fk_restrict.result	6.82 KB	-rw-r--r--
ndb_fk_self_ref.result	3.74 KB	-rw-r--r--
ndb_fk_setdefault.result	1.03 KB	-rw-r--r--
ndb_fk_setnull.result	3.23 KB	-rw-r--r--
ndb_global_schema_lock.result	3.6 KB	-rw-r--r--
ndb_global_schema_lock_error.r...	1.88 KB	-rw-r--r--
ndb_grant.result	17.65 KB	-rw-r--r--
ndb_hashmap.result	624 B	-rw-r--r--
ndb_hidden_pk.result	10.55 KB	-rw-r--r--
ndb_index.result	17.84 KB	-rw-r--r--
ndb_index_ordered.result	16.73 KB	-rw-r--r--
ndb_index_stat.result	17.74 KB	-rw-r--r--
ndb_index_stat_partitions.resu...	3.82 KB	-rw-r--r--
ndb_index_stat_restart.result	2.2 KB	-rw-r--r--
ndb_index_unique.result	20.29 KB	-rw-r--r--
ndb_insert.result	36.52 KB	-rw-r--r--
ndb_join_pushdown_bka.result	949.68 KB	-rw-r--r--
ndb_join_pushdown_default.resu...	865.61 KB	-rw-r--r--
ndb_join_pushdown_nobnl.result	864.87 KB	-rw-r--r--
ndb_join_pushdown_none.result	865.08 KB	-rw-r--r--
ndb_limit.result	1.99 KB	-rw-r--r--
ndb_load.result	2.66 KB	-rw-r--r--
ndb_loaddatalocal.result	1.57 KB	-rw-r--r--
ndb_lock.result	4.17 KB	-rw-r--r--
ndb_lock_table.result	205 B	-rw-r--r--
ndb_many_fragments.result	26.72 KB	-rw-r--r--
ndb_mgm.result	5.7 KB	-rw-r--r--
ndb_mgmd.result	198 B	-rw-r--r--
ndb_minmax.result	1.47 KB	-rw-r--r--
ndb_multi.result	8.83 KB	-rw-r--r--
ndb_multi_row.result	1.33 KB	-rw-r--r--
ndb_multi_update_delete.result	1.83 KB	-rw-r--r--
ndb_native_default_support.res...	50.29 KB	-rw-r--r--
ndb_one_fragment.result	4.05 KB	-rw-r--r--
ndb_optimize_table.result	2 KB	-rw-r--r--
ndb_optimized_node_selection.r...	1017 B	-rw-r--r--
ndb_partition_error.result	1.44 KB	-rw-r--r--
ndb_partition_error2.result	150 B	-rw-r--r--
ndb_partition_hash.result	890 B	-rw-r--r--
ndb_partition_key.result	8.82 KB	-rw-r--r--
ndb_partition_list.result	2.31 KB	-rw-r--r--
ndb_partition_range.result	7.91 KB	-rw-r--r--
ndb_print_file.result	709 B	-rw-r--r--
ndb_rbwr.result	0 B	-rw-r--r--
ndb_read_multi_range.result	15 KB	-rw-r--r--
ndb_reconnect.result	1.7 KB	-rw-r--r--
ndb_rename.result	910 B	-rw-r--r--
ndb_replace.result	3.64 KB	-rw-r--r--
ndb_restore_bug17916243.result	537 B	-rw-r--r--
ndb_restore_compat_downward.re...	132.43 KB	-rw-r--r--
ndb_restore_compat_endianness....	10.97 KB	-rw-r--r--
ndb_restore_conv_lossy_charbin...	15.46 KB	-rw-r--r--
ndb_restore_conv_lossy_integra...	20.72 KB	-rw-r--r--
ndb_restore_conv_more.result	56.57 KB	-rw-r--r--
ndb_restore_conv_padding.resul...	6.14 KB	-rw-r--r--
ndb_restore_conv_promotion.res...	23.26 KB	-rw-r--r--
ndb_restore_discover.result	540 B	-rw-r--r--
ndb_restore_misc.result	25.16 KB	-rw-r--r--
ndb_restore_print.result	9.8 KB	-rw-r--r--
ndb_restore_schema_blobs.resul...	6.44 KB	-rw-r--r--
ndb_restore_schema_partitions....	13.25 KB	-rw-r--r--
ndb_restore_schema_rewrites.re...	12.22 KB	-rw-r--r--
ndb_restore_schema_subsets.res...	95.9 KB	-rw-r--r--
ndb_restore_schema_tolerance.r...	4.53 KB	-rw-r--r--
ndb_restore_undolog.result	18.76 KB	-rw-r--r--
ndb_row_count.result	5.19 KB	-rw-r--r--
ndb_row_format.result	1.74 KB	-rw-r--r--
ndb_select_count.result	334 B	-rw-r--r--
ndb_share.result	11.29 KB	-rw-r--r--
ndb_short_sigs.result	2.54 KB	-rw-r--r--
ndb_single_user.result	4.01 KB	-rw-r--r--
ndb_sp.result	1.17 KB	-rw-r--r--
ndb_spj_dict.result	11.63 KB	-rw-r--r--
ndb_sql_allow_batching.result	918 B	-rw-r--r--
ndb_startup.result	358 B	-rw-r--r--
ndb_statistics0.result	16.23 KB	-rw-r--r--
ndb_statistics1.result	15.99 KB	-rw-r--r--
ndb_subquery.result	9.29 KB	-rw-r--r--
ndb_suma_handover.result	427 B	-rw-r--r--
ndb_temporary.result	949 B	-rw-r--r--
ndb_tmp_table_and_DDL.result	3.11 KB	-rw-r--r--
ndb_transaction.result	4.62 KB	-rw-r--r--
ndb_trigger.result	9.38 KB	-rw-r--r--
ndb_truncate.result	425 B	-rw-r--r--
ndb_types.result	4.91 KB	-rw-r--r--
ndb_update.result	2.97 KB	-rw-r--r--
ndb_update_no_read.result	11.23 KB	-rw-r--r--
ndb_view.result	597 B	-rw-r--r--
ndb_wait_until_ready.result	533 B	-rw-r--r--
ndb_wl946_main.result	19.39 KB	-rw-r--r--
ndb_wl946_post.result	3.7 KB	-rw-r--r--
ndb_wl946_pre.result	6.62 KB	-rw-r--r--
ndbapi-examples.result	3.34 KB	-rw-r--r--
ndbinfo.result	44.04 KB	-rw-r--r--
ndbinfo_cache.result	775 B	-rw-r--r--
ndbinfo_dump.result	95 B	-rw-r--r--
ndbinfo_upgrade.result	382 B	-rw-r--r--
ps_7ndb.result	103.05 KB	-rw-r--r--
strict_autoinc_5ndb.result	894 B	-rw-r--r--
wl10837.result	524 B	-rw-r--r--

Code Editor : ndbinfo.result

result_format: 2

SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE,
       PLUGIN_LIBRARY,PLUGIN_LIBRARY_VERSION,PLUGIN_AUTHOR,PLUGIN_DESCRIPTION
FROM information_schema.plugins WHERE PLUGIN_NAME = 'ndbinfo';
PLUGIN_NAME	PLUGIN_VERSION	PLUGIN_STATUS	PLUGIN_TYPE	PLUGIN_LIBRARY	PLUGIN_LIBRARY_VERSION	PLUGIN_AUTHOR	PLUGIN_DESCRIPTION
ndbinfo	0.1	ACTIVE	STORAGE ENGINE	NULL	NULL	Sun Microsystems Inc.	MySQL Cluster system information storage engine

## Creation of temporary tables should not be supported by NDBINFO engine
CREATE TEMPORARY TABLE `t1` (
  `dummy` INT UNSIGNED
) ENGINE=NDBINFO;
ERROR HY000: Table storage engine 'ndbinfo' does not support the create option 'TEMPORARY'

USE ndbinfo;

SELECT node_id, memory_type, used>0 AND used < total/20 AS used_ok, 
  used_pages>0 AND used_pages < total_pages/20 AS used_pages_ok, 
  total, total_pages 
  FROM ndbinfo.memoryusage WHERE memory_type="Long message buffer"
  ORDER BY node_id;
node_id	memory_type	used_ok	used_pages_ok	total	total_pages
1	Long message buffer	0	0	4194304	16384
2	Long message buffer	0	0	4194304	16384

SELECT node_id, block_number, block_instance, pool_name, 
  used>0 AND used < total/20 AS used_ok, 
  total, high>=used AND high < total/20 AS high_ok, entry_size, config_param1, 
  config_param2, config_param3, config_param4  
  FROM ndbinfo.ndb$pools WHERE pool_name="Long message buffer"
  ORDER BY node_id;
node_id	block_number	block_instance	pool_name	used_ok	total	high_ok	entry_size	config_param1	config_param2	config_param3	config_param4
1	254	0	Long message buffer	0	16384	0	256	157	0	0	0
2	254	0	Long message buffer	0	16384	0	256	157	0	0	0

SHOW CREATE TABLE ndb$tables;
Table	Create Table
ndb$tables	CREATE TABLE `ndb$tables` (
  `table_id` int(10) unsigned DEFAULT NULL,
  `table_name` varchar(512) DEFAULT NULL,
  `comment` varchar(512) DEFAULT NULL
) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='metadata for tables available through ndbinfo'

SELECT * FROM ndb$tables;
table_id	table_name	comment
0	tables	metadata for tables available through ndbinfo
1	columns	metadata for columns available through ndbinfo 
2	test	for testing
3	pools	pool usage
4	transporters	transporter status
5	logspaces	logspace usage
6	logbuffers	logbuffer usage
7	resources	resources usage (a.k.a superpool)
8	counters	monotonic counters
9	nodes	node status
10	diskpagebuffer	disk page buffer info
11	threadblocks	which blocks are run in which threads
12	threadstat	Statistics on execution threads
13	transactions	transactions
14	operations	operations
15	membership	membership
16	dict_obj_info	Dictionary object info
17	frag_mem_use	Per fragment space information
18	disk_write_speed_base	Actual speed of disk writes per LDM thread, base data
19	disk_write_speed_aggregate	Actual speed of disk writes per LDM thread, aggregate data
20	frag_operations	Per fragment operational information
21	restart_info	Times of restart phases in seconds and current state
SELECT COUNT(*) FROM ndb$tables;
COUNT(*)
22
SELECT * FROM ndb$tables WHERE table_id = 2;
table_id	table_name	comment
2	test	for testing
SELECT * FROM ndb$tables WHERE table_id > 5;
table_id	table_name	comment
6	logbuffers	logbuffer usage
7	resources	resources usage (a.k.a superpool)
8	counters	monotonic counters
9	nodes	node status
10	diskpagebuffer	disk page buffer info
11	threadblocks	which blocks are run in which threads
12	threadstat	Statistics on execution threads
13	transactions	transactions
14	operations	operations
15	membership	membership
16	dict_obj_info	Dictionary object info
17	frag_mem_use	Per fragment space information
18	disk_write_speed_base	Actual speed of disk writes per LDM thread, base data
19	disk_write_speed_aggregate	Actual speed of disk writes per LDM thread, aggregate data
20	frag_operations	Per fragment operational information
21	restart_info	Times of restart phases in seconds and current state
SELECT * FROM ndb$tables WHERE table_name = 'LOGDESTINATION';
table_id	table_name	comment
SELECT COUNT(*) FROM ndb$tables t1, ndb$tables t2 WHERE t1.table_id = t1.table_id;
COUNT(*)
484

SELECT table_id, table_name, comment from ndb$tables
  WHERE table_id > 2 AND table_id <= 5 ORDER BY table_id;
table_id	table_name	comment
3	pools	pool usage
4	transporters	transporter status
5	logspaces	logspace usage
SELECT table_id FROM ndb$tables  WHERE table_id = 2 ORDER BY table_name;
table_id
2
SELECT table_id, table_name FROM ndb$tables ORDER BY table_name;
table_id	table_name
1	columns
8	counters
16	dict_obj_info
10	diskpagebuffer
19	disk_write_speed_aggregate
18	disk_write_speed_base
17	frag_mem_use
20	frag_operations
6	logbuffers
5	logspaces
15	membership
9	nodes
14	operations
3	pools
7	resources
21	restart_info
0	tables
2	test
11	threadblocks
12	threadstat
13	transactions
4	transporters

SELECT table_id, column_id, column_name FROM ndb$columns LIMIT 7;
table_id	column_id	column_name
0	0	table_id
0	1	table_name
0	2	comment
1	0	table_id
1	1	column_id
1	2	column_name
1	3	column_type

UPDATE ndb$tables SET table_id=2 WHERE table_id=3;
ERROR HY000: Table 'ndb$tables' is read only

UPDATE ndb$tables SET table_id=9 WHERE 1=0;
ERROR HY000: Table 'ndb$tables' is read only

UPDATE ndb$tables SET table_id=9 WHERE table_id > 1;
ERROR HY000: Table 'ndb$tables' is read only

DELETE FROM ndb$tables WHERE table_id=3;
ERROR HY000: Table 'ndb$tables' is read only

DELETE FROM ndb$tables WHERE 1=0;
ERROR HY000: Table 'ndb$tables' is read only

DELETE FROM ndb$tables WHERE table_id > 1;
ERROR HY000: Table 'ndb$tables' is read only

ALTER TABLE ndb$test ADD COLUMN another_col varchar(255);
ERROR HY000: Table storage engine for 'ndb$test' doesn't have this option

FLUSH TABLES;
SELECT table_id FROM ndb$tables;
table_id
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

TRUNCATE ndb$tables;
ERROR HY000: Table 'ndb$tables' is read only

## Variables and status
SHOW GLOBAL STATUS LIKE 'ndbinfo\_%';
Variable_name	Value
SHOW GLOBAL VARIABLES LIKE 'ndbinfo\_%';
Variable_name	Value
ndbinfo_database	ndbinfo
ndbinfo_max_bytes	0
ndbinfo_max_rows	10
ndbinfo_offline	OFF
ndbinfo_show_hidden	OFF
ndbinfo_table_prefix	ndb$
ndbinfo_version	NDB_VERSION_D

SELECT counter, HEX(counter2) FROM ndb$test LIMIT 10;
counter	HEX(counter2)
0	0
1	100000000
2	200000000
3	300000000
4	400000000
5	500000000
6	600000000
7	700000000
8	800000000
9	900000000

SHOW TABLES LIKE 'ndb$te%';
Tables_in_ndbinfo (ndb$te%)
set @@ndbinfo_show_hidden=TRUE;
SHOW TABLES LIKE 'ndb$te%';
Tables_in_ndbinfo (ndb$te%)
ndb$test
set @@ndbinfo_show_hidden=default;

set @@ndbinfo_table_prefix="somethingelse";
ERROR HY000: Variable 'ndbinfo_table_prefix' is a read only variable

set @@ndbinfo_database="somethingelse";
ERROR HY000: Variable 'ndbinfo_database' is a read only variable

SELECT count(*) >= 20 FROM blocks;
count(*) >= 20
1

## 1) More columns in NDB -> allowed, with warning
DROP TABLE ndb$test;
CREATE TABLE ndb$test (node_id int unsigned) ENGINE = ndbinfo;
SELECT node_id != 0 FROM ndb$test LIMIT 1;
node_id != 0
1
Warnings:
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'
DROP TABLE ndb$test;

## 2) Column does not exist in NDB -> allowed, with warning, non existing
##    column(s) return NULL
## 2a) Extra column at end
CREATE TABLE ndb$test (node_id int, non_existing int) ENGINE = ndbinfo;
SELECT DISTINCT node_id, non_existing FROM ndb$test;
node_id	non_existing
1	NULL
2	NULL
Warnings:
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'
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'
DROP TABLE ndb$test;

## 2b) Extra column(s) in middle
CREATE TABLE ndb$test (
  node_id int unsigned,
  non_existing int unsigned,
  block_number int unsigned,
  block_instance int unsigned,
  counter int unsigned,
  counter2 bigint unsigned
) ENGINE = ndbinfo;
SELECT DISTINCT node_id, non_existing, block_number FROM ndb$test;
node_id	non_existing	block_number
1	NULL	249
2	NULL	249
Warnings:
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'
DROP TABLE ndb$test;

## 2c) Extra column first
CREATE TABLE ndb$test (non_existing int, node_id int) ENGINE = ndbinfo;
SELECT DISTINCT node_id, non_existing FROM ndb$test;
node_id	non_existing
1	NULL
2	NULL
Warnings:
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'
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'
SELECT DISTINCT non_existing, node_id FROM ndb$test;
non_existing	node_id
NULL	1
NULL	2
DROP TABLE ndb$test;

## 3) Incompatible column type -> error, with warning
## 3a) int instead of bigint
CREATE TABLE ndb$test (counter2 int) ENGINE = ndbinfo;
SELECT * FROM ndb$test;
ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
SHOW WARNINGS;
Level	Code	Message
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'
Error	1296	Got error 40001 'Incompatible table definitions' from NDBINFO
## 3b) bigint instead of int
DROP TABLE ndb$test;
CREATE TABLE ndb$test (node_id bigint) ENGINE = ndbinfo;
SELECT * FROM ndb$test;
ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
SHOW WARNINGS;
Level	Code	Message
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'
Error	1296	Got error 40001 'Incompatible table definitions' from NDBINFO
## 3c) varchar instead of int
DROP TABLE ndb$test;
CREATE TABLE ndb$test (node_id varchar(255)) ENGINE = ndbinfo;
SELECT * FROM ndb$test;
ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
SHOW WARNINGS;
Level	Code	Message
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'
Error	1296	Got error 40001 'Incompatible table definitions' from NDBINFO
DROP TABLE ndb$test;
## 3d) column which is NOT NULL
CREATE TABLE ndb$test (node_id int unsigned NOT NULL) ENGINE = ndbinfo;
SELECT * FROM ndb$test;
ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
SHOW WARNINGS;
Level	Code	Message
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'
Error	1296	Got error 40001 'Incompatible table definitions' from NDBINFO
DROP TABLE ndb$test;
## 3e) non existing column which is NOT NULL
CREATE TABLE ndb$test (
  block_number int unsigned,
  non_existing int NOT NULL) ENGINE = ndbinfo;
SELECT * FROM ndb$test;
ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO
SHOW WARNINGS;
Level	Code	Message
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'
Error	1296	Got error 40001 'Incompatible table definitions' from NDBINFO
DROP TABLE ndb$test;

## 4) Table with primary key/indexes not supported
CREATE TABLE ndb$test (node_id int, block_number int PRIMARY KEY) ENGINE = ndbinfo;
ERROR 42000: Too many keys specified; max 0 keys allowed

## 5) Table with blobs not supported
CREATE TABLE ndb$test (node_id int, block_number blob) ENGINE = ndbinfo;
ERROR 42000: The used table type doesn't support BLOB/TEXT columns

## 6) Table with autoincrement not supported
CREATE TABLE ndb$test (node_id int AUTO_INCREMENT) ENGINE = ndbinfo;
ERROR 42000: The used table type doesn't support AUTO_INCREMENT columns

select distinct node_id
from ndbinfo.diskpagebuffer;
node_id
1
2

set @@ndbinfo_offline=1;
ERROR HY000: Variable 'ndbinfo_offline' is a GLOBAL variable and should be set with SET GLOBAL

SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id;
node_id
1
2

desc threadblocks;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
thr_no	int(10) unsigned	YES		NULL	
block_name	varchar(512)	YES		NULL	
block_instance	int(10) unsigned	YES		NULL	
select distinct block_name from threadblocks order by 1;
block_name
BACKUP
CMVMI
DBACC
DBDICT
DBDIH
DBINFO
DBLQH
DBSPJ
DBTC
DBTUP
DBTUX
DBUTIL
LGMAN
NDBCNTR
NDBFS
PGMAN
QMGR
RESTORE
SUMA
THRMAN
TRIX
TRPMAN
TSMAN
desc threadstat;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
thr_no	int(10) unsigned	YES		NULL	
thr_nm	varchar(512)	YES		NULL	
c_loop	bigint(20) unsigned	YES		NULL	
c_exec	bigint(20) unsigned	YES		NULL	
c_wait	bigint(20) unsigned	YES		NULL	
c_l_sent_prioa	bigint(20) unsigned	YES		NULL	
c_l_sent_priob	bigint(20) unsigned	YES		NULL	
c_r_sent_prioa	bigint(20) unsigned	YES		NULL	
c_r_sent_priob	bigint(20) unsigned	YES		NULL	
os_tid	bigint(20) unsigned	YES		NULL	
os_now	bigint(20) unsigned	YES		NULL	
os_ru_utime	bigint(20) unsigned	YES		NULL	
os_ru_stime	bigint(20) unsigned	YES		NULL	
os_ru_minflt	bigint(20) unsigned	YES		NULL	
os_ru_majflt	bigint(20) unsigned	YES		NULL	
os_ru_nvcsw	bigint(20) unsigned	YES		NULL	
os_ru_nivcsw	bigint(20) unsigned	YES		NULL	
select count(*) > 0 block_name from threadstat;
block_name
1

desc disk_write_speed_aggregate;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
thr_no	int(10) unsigned	YES		NULL	
backup_lcp_speed_last_sec	bigint(20) unsigned	YES		NULL	
redo_speed_last_sec	bigint(20) unsigned	YES		NULL	
backup_lcp_speed_last_10sec	bigint(20) unsigned	YES		NULL	
redo_speed_last_10sec	bigint(20) unsigned	YES		NULL	
std_dev_backup_lcp_speed_last_10sec	bigint(20) unsigned	YES		NULL	
std_dev_redo_speed_last_10sec	bigint(20) unsigned	YES		NULL	
backup_lcp_speed_last_60sec	bigint(20) unsigned	YES		NULL	
redo_speed_last_60sec	bigint(20) unsigned	YES		NULL	
std_dev_backup_lcp_speed_last_60sec	bigint(20) unsigned	YES		NULL	
std_dev_redo_speed_last_60sec	bigint(20) unsigned	YES		NULL	
slowdowns_due_to_io_lag	bigint(20) unsigned	YES		NULL	
slowdowns_due_to_high_cpu	bigint(20) unsigned	YES		NULL	
disk_write_speed_set_to_min	bigint(20) unsigned	YES		NULL	
current_target_disk_write_speed	bigint(20) unsigned	YES		NULL	
select count(*) from disk_write_speed_aggregate;
count(*)
8

desc disk_write_speed_base;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
thr_no	int(10) unsigned	YES		NULL	
millis_ago	bigint(20) unsigned	YES		NULL	
millis_passed	bigint(20) unsigned	YES		NULL	
backup_lcp_bytes_written	bigint(20) unsigned	YES		NULL	
redo_bytes_written	bigint(20) unsigned	YES		NULL	
target_disk_write_speed	bigint(20) unsigned	YES		NULL	
select node_id from disk_write_speed_base group by node_id, thr_no;
node_id
1
1
1
1
2
2
2
2

select count(*) from disk_write_speed_aggregate_node;
count(*)
2

desc restart_info;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
node_restart_status	varchar(256)	YES		NULL	
node_restart_status_int	int(10) unsigned	YES		NULL	
secs_to_complete_node_failure	int(10) unsigned	YES		NULL	
secs_to_allocate_node_id	int(10) unsigned	YES		NULL	
secs_to_include_in_heartbeat_protocol	int(10) unsigned	YES		NULL	
secs_until_wait_for_ndbcntr_master	int(10) unsigned	YES		NULL	
secs_wait_for_ndbcntr_master	int(10) unsigned	YES		NULL	
secs_to_get_start_permitted	int(10) unsigned	YES		NULL	
secs_to_wait_for_lcp_for_copy_meta_data	int(10) unsigned	YES		NULL	
secs_to_copy_meta_data	int(10) unsigned	YES		NULL	
secs_to_include_node	int(10) unsigned	YES		NULL	
secs_starting_node_to_request_local_recovery	int(10) unsigned	YES		NULL	
secs_for_local_recovery	int(10) unsigned	YES		NULL	
secs_restore_fragments	int(10) unsigned	YES		NULL	
secs_undo_disk_data	int(10) unsigned	YES		NULL	
secs_exec_redo_log	int(10) unsigned	YES		NULL	
secs_index_rebuild	int(10) unsigned	YES		NULL	
secs_to_synchronize_starting_node	int(10) unsigned	YES		NULL	
secs_wait_lcp_for_restart	int(10) unsigned	YES		NULL	
secs_wait_subscription_handover	int(10) unsigned	YES		NULL	
total_restart_secs	int(10) unsigned	YES		NULL	
select * from restart_info;
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

desc cluster_transactions;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
block_instance	int(10) unsigned	YES		NULL	
transid	bigint(22) unsigned	YES		NULL	
state	varchar(256)	YES		NULL	
count_operations	int(10) unsigned	YES		NULL	
outstanding_operations	int(10) unsigned	YES		NULL	
inactive_seconds	int(10) unsigned	YES		NULL	
client_node_id	bigint(21) unsigned	YES		NULL	
client_block_ref	bigint(21) unsigned	YES		NULL	
desc server_transactions;
Field	Type	Null	Key	Default	Extra
mysql_connection_id	bigint(21) unsigned	NO		0	
node_id	int(10) unsigned	YES		NULL	
block_instance	int(10) unsigned	YES		NULL	
transid	bigint(22) unsigned	YES		NULL	
state	varchar(256)	YES		NULL	
count_operations	int(10) unsigned	YES		NULL	
outstanding_operations	int(10) unsigned	YES		NULL	
inactive_seconds	int(10) unsigned	YES		NULL	
client_node_id	bigint(21) unsigned	YES		NULL	
client_block_ref	bigint(21) unsigned	YES		NULL	
desc cluster_operations;
Field	Type	Null	Key	Default	Extra
node_id	int(10) unsigned	YES		NULL	
block_instance	int(10) unsigned	YES		NULL	
transid	bigint(22) unsigned	YES		NULL	
operation_type	varchar(9)	YES		NULL	
state	varchar(256)	YES		NULL	
tableid	int(10) unsigned	YES		NULL	
fragmentid	int(10) unsigned	YES		NULL	
client_node_id	bigint(21) unsigned	YES		NULL	
client_block_ref	bigint(21) unsigned	YES		NULL	
tc_node_id	bigint(21) unsigned	YES		NULL	
tc_block_no	bigint(21) unsigned	YES		NULL	
tc_block_instance	bigint(21) unsigned	YES		NULL	
desc server_operations;
Field	Type	Null	Key	Default	Extra
mysql_connection_id	bigint(21) unsigned	NO		0	
node_id	int(10) unsigned	YES		NULL	
block_instance	int(10) unsigned	YES		NULL	
transid	bigint(22) unsigned	YES		NULL	
operation_type	varchar(9)	YES		NULL	
state	varchar(256)	YES		NULL	
tableid	int(10) unsigned	YES		NULL	
fragmentid	int(10) unsigned	YES		NULL	
client_node_id	bigint(21) unsigned	YES		NULL	
client_block_ref	bigint(21) unsigned	YES		NULL	
tc_node_id	bigint(21) unsigned	YES		NULL	
tc_block_no	bigint(21) unsigned	YES		NULL	
tc_block_instance	bigint(21) unsigned	YES		NULL

create table t1 (a int primary key) engine = ndb;
begin;
insert into t1 values (1);
select state, count_operations, outstanding_operations,
IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>") 
  client_node_id
from server_transactions;
state	count_operations	outstanding_operations	client_node_id
Started	1	0	<client_node_id>
select node_id, operation_type, state,
IF(tc_node_id <= 48, "<tc_node_id>", "<incorrect nodeid>") tc_node_id,
IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>") 
  client_node_id
from server_operations
order by 1;
node_id	operation_type	state	tc_node_id	client_node_id
1	INSERT	Prepared	<tc_node_id>	<client_node_id>
2	INSERT	Prepared	<tc_node_id>	<client_node_id>

select st.state, st.count_operations, st.outstanding_operations,
       so.node_id, so.state, so.operation_type
from server_transactions st,
     server_operations so
where st.transid = so.transid
  and so.tc_node_id = st.node_id
  and so.tc_block_instance = st.block_instance
  and so.client_node_id = st.client_node_id
  and so.client_block_ref = st.client_block_ref;
state	count_operations	outstanding_operations	node_id	state	operation_type
Started	1	0	1	Prepared	INSERT
Started	1	0	2	Prepared	INSERT
rollback;
drop table t1;

select distinct resource_name
from resources
order by 1;
resource_name
DATA_MEMORY
DISK_OPERATIONS
DISK_PAGE_BUFFER
DISK_RECORDS
FILE_BUFFERS
JOBBUFFER
QUERY_MEMORY
RESERVED
SCHEMA_TRANS_MEMORY
TRANSPORTER_BUFFERS

select distinct counter_name
from counters
order by 1;
counter_name
ABORTS
ATTRINFO
COMMITS
CONST_PRUNED_RANGE_SCANS_RECEIVED
LOCAL_RANGE_SCANS_SENT
LOCAL_READS
LOCAL_READS_SENT
LOCAL_TABLE_SCANS_SENT
LOCAL_WRITES
LQHKEY_OVERLOAD
LQHKEY_OVERLOAD_NODE_PEER
LQHKEY_OVERLOAD_READER
LQHKEY_OVERLOAD_SUBSCRIBER
LQHKEY_OVERLOAD_TC
LQHSCAN_SLOWDOWNS
OPERATIONS
PRUNED_RANGE_SCANS_RECEIVED
RANGE_SCANS
RANGE_SCANS_RECEIVED
READS
READS_NOT_FOUND
READS_RECEIVED
REMOTE_RANGE_SCANS_SENT
REMOTE_READS_SENT
SCAN_BATCHES_RETURNED
SCAN_ROWS_RETURNED
SIMPLE_READS
TABLE_SCANS
TABLE_SCANS_RECEIVED
TRANSACTIONS
WRITES

set @@global.ndbinfo_offline=TRUE;
select @@ndbinfo_offline;
@@ndbinfo_offline
1

CREATE TABLE ndb$does_not_exist_in_ndb(
  node_id int,
  message varchar(255)
) ENGINE = ndbinfo;

CREATE VIEW view_on_table_which_does_not_exist_in_ndb AS
  SELECT node_id, message
  FROM ndbinfo.ndb$does_not_exist_in_ndb;

SHOW CREATE TABLE ndb$does_not_exist_in_ndb;
Table	Create Table
ndb$does_not_exist_in_ndb	CREATE TABLE `ndb$does_not_exist_in_ndb` (
  `node_id` int(11) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL
) ENGINE=NDBINFO DEFAULT CHARSET=latin1

SELECT * FROM view_on_table_which_does_not_exist_in_ndb;
node_id	message
Warnings:
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
SELECT * FROM ndb$does_not_exist_in_ndb;
node_id	message
Warnings:
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
SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id;
node_id
Warnings:
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

DROP VIEW view_on_table_which_does_not_exist_in_ndb;
DROP TABLE ndb$does_not_exist_in_ndb;

set @@global.ndbinfo_offline = FALSE;

SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id;
node_id
1
2

Verify there are no ndbinfo views without 'root@localhost' as definer
select table_schema, table_name, definer from information_schema.views
  where table_schema = 'ndbinfo' and
        definer != 'root@localhost';
table_schema	table_name	definer

create table numbers(
  i int
);

insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1(
  a int not null primary key,
  b int not null,
  c1 char(255),
  vc1 varchar(1024),
  tx1 text
)engine=ndbcluster;

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;

drop table numbers;

create index ix1 on t1(c1);
create unique index ix2 on t1(b) using hash;

set @t1_id = (select cast(id as signed integer) from ndbinfo.ndb$dict_obj_info where fq_name REGEXP "def/t1$");

select type, state, parent_obj_type, replace(fq_name, cast(@t1_id as char(10)), "<t1 id>") as fq_name, if(parent_obj_id=@t1_id, "child_of_t1", if(parent_obj_id=0, "root", "error!")) as  nesting from ndbinfo.ndb$dict_obj_info where id >= @t1_id and type <= 6 order by type, id;
type	state	parent_obj_type	fq_name	nesting
2	4	0	ndbinfo/def/t1	root
2	4	2	ndbinfo/def/NDB$BLOB_<t1 id>_4	child_of_t1
3	4	2	sys/def/<t1 id>/ix2$unique	child_of_t1
6	4	2	sys/def/<t1 id>/PRIMARY	child_of_t1
6	4	2	sys/def/<t1 id>/ix1	child_of_t1

create view frag_sum as 
select 
sum(fixed_elem_alloc_bytes) as fixed_elem_alloc_bytes,
sum(var_elem_alloc_bytes) as var_elem_alloc_bytes,
sum(hash_index_alloc_bytes) as hash_index_alloc_bytes
from ndbinfo.memory_per_fragment;

create view pool_sum as
select pool_name, block_number, max(entry_size) * sum(used) as used_bytes 
from  ndbinfo.ndb$pools where pool_name <> "Data memory" 
or block_number=249 group by pool_name, block_number;

create view check_l2pmap_pools as 
select min(floor(1000*nodes.total*nodes.entry_size/
           (pages.used*pages.entry_size))) as l2pmap_ratio 
from ndbinfo.ndb$pools as pages join ndbinfo.ndb$pools as nodes 
where pages.node_id=nodes.node_id 
and pages.block_instance=nodes.block_instance 
and pages.block_number=nodes.block_number
and pages.pool_name="L2PMap pages" 
and nodes.pool_name="L2PMap nodes";

create view l2p_frag_sum as
select sum(tuple_l2pmap_alloc_bytes) as l2p_sum, 'TUP' as block 
from ndbinfo.ndb$frag_mem_use 
union 
select sum(hash_index_l2pmap_alloc_bytes) as l2p_sum, 'ACC' as block 
from ndbinfo.ndb$frag_mem_use;

select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum 
where pool_name='Index memory';
used_bytes - hash_index_alloc_bytes
0

select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
  from pool_sum, frag_sum 
  where pool_name='Data memory';
used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
0

select l2p_sum-used_bytes from l2p_frag_sum, pool_sum 
  where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or 
                                      (block='TUP' and block_number=249));
l2p_sum-used_bytes
0
0

select * from check_l2pmap_pools;
l2pmap_ratio
996

select * from sum_per_tab;
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
ndbinfo/def/t1	NULL	User table	2000	292	1.0	1	2000	1
ndbinfo/def/NDB$BLOB_<t1 id>_4	ndbinfo/def/t1	User table	1400	36	1.0	1	1400	1
sys/def/<t1 id>/PRIMARY	ndbinfo/def/t1	Ordered index	-1	272	1.0	1	0	1
sys/def/<t1 id>/ix1	ndbinfo/def/t1	Ordered index	-1	272	1.0	1	0	1
sys/def/<t1 id>/ix2$unique	ndbinfo/def/t1	Unique hash index	2000	28	1.0	1	0	1

delete from t1 where a%10=0;

select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum 
where pool_name='Index memory';
used_bytes - hash_index_alloc_bytes
0

select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
  from pool_sum, frag_sum 
  where pool_name='Data memory';
used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
0

select * from check_l2pmap_pools;
l2pmap_ratio
996

select * from sum_per_tab;
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
ndbinfo/def/t1	NULL	User table	1800	292	1.0	1	1800	1
ndbinfo/def/NDB$BLOB_<t1 id>_4	ndbinfo/def/t1	User table	1400	36	1.0	1	1400	1
sys/def/<t1 id>/PRIMARY	ndbinfo/def/t1	Ordered index	-1	272	1.0	1	0	1
sys/def/<t1 id>/ix1	ndbinfo/def/t1	Ordered index	-1	272	1.0	1	0	1
sys/def/<t1 id>/ix2$unique	ndbinfo/def/t1	Unique hash index	1800	28	1.0	1	0	1

update t1 set vc1=repeat('x', a%300), tx1=repeat('y', a*2);

select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum 
where pool_name='Index memory';
used_bytes - hash_index_alloc_bytes
0

select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
  from pool_sum, frag_sum 
  where pool_name='Data memory';
used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes
0

select * from check_l2pmap_pools;
l2pmap_ratio
996

select * from sum_per_tab;
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
ndbinfo/def/t1	NULL	User table	1800	292	1.0	1	1800	1
ndbinfo/def/NDB$BLOB_<t1 id>_4	ndbinfo/def/t1	User table	1568	36	1.0	1	1568	1
sys/def/<t1 id>/PRIMARY	ndbinfo/def/t1	Ordered index	-1	272	1.0	1	0	1
sys/def/<t1 id>/ix1	ndbinfo/def/t1	Ordered index	-1	272	1.0	1	0	1
sys/def/<t1 id>/ix2$unique	ndbinfo/def/t1	Unique hash index	1800	28	1.0	1	0	1

drop table t1;
drop view l2p_frag_sum;
drop view check_l2pmap_pools;
drop view sum_per_tab;
drop view pool_sum;
drop view frag_sum;

create view frag_ops as select 
  if(type in ('Ordered index', 'Unique hash index'), 
    substring(fq_name from 1+locate('/', fq_name, 9)), 
    if(fq_name like '%BLOB%', left(fq_name, locate('_', fq_name)), fq_name)) 
    as fq_name,
  parent_fq_name,
  type,
  count(fragment_num) as frag_count,
  sum(tot_key_reads) as tot_key_reads, 
  sum(tot_key_inserts) as tot_key_inserts, 
  sum(tot_key_updates) as tot_key_updates, 
  sum(tot_key_writes) as tot_key_writes, 
  sum(tot_key_deletes) as tot_key_deletes, 
  sum(tot_key_refs) as tot_key_refs, 
  # Attrinfo for read operations is a bit longer when running with embedded 
  # mysqld, which is why we do the rounding below. 
  round(log2(1+sum(tot_key_attrinfo_bytes))) as log_tot_key_attrinfo_bytes,
  sum(tot_key_keyinfo_bytes) as tot_key_keyinfo_bytes,
  sum(tot_key_prog_bytes) as tot_key_prog_bytes,
  sum(tot_key_inst_exec) as tot_key_inst_exec,
  sum(tot_key_bytes_returned) as tot_key_bytes_returned,
  sum(tot_frag_scans) as tot_frag_scans, 
  sum(tot_scan_rows_examined) as tot_scan_rows_examined, 
  sum(tot_scan_rows_returned) as tot_scan_rows_returned, 
  sum(tot_scan_bytes_returned) as tot_scan_bytes_returned, 
  sum(tot_scan_prog_bytes) as tot_scan_prog_bytes,
  sum(tot_scan_bound_bytes) as tot_scan_bound_bytes,
  sum(tot_scan_inst_exec) as tot_scan_inst_exec, 
  sum(conc_frag_scans) as conc_frag_scans,
  sum(conc_qd_frag_scans) as conc_qd_frag_scans, 
  sum(tot_commits) as tot_commits
  from ndbinfo.operations_per_fragment
  where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%'
  group by table_id, type, operations_per_fragment.fq_name, parent_fq_name
  order by table_id;

create table t1 (
  a int not null,
  b int not null,
  c int not null,
  d int not null,
  t text not null,
  primary key (b,a)
) engine=ndbcluster
partition by key(b,a) partitions 8;

create unique index ix1 on t1(d) using hash;

create table numbers(
  i int
);

insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

insert into t1 select x1.i+x2.i*10, 1, 1+x1.i+x2.i*10, 1+x1.i+x2.i*10,
  repeat('x', 512)
  from numbers as x1, numbers as x2;

drop table numbers;

create table t2 (
  a int, 
  b int, 
  primary key(a) using hash)
engine = ndb 
partition by key(a) partitions 8;

insert into t2 values (1, 2);
insert into t2 values (2, 3);
insert into t2 values (3, 1);

select * from frag_ops;;
fq_name	ndbinfo/def/t1
parent_fq_name	NULL
type	User table
frag_count	16
tot_key_reads	0
tot_key_inserts	200
tot_key_updates	200
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	16
tot_key_keyinfo_bytes	3200
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	24
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	64
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	400
fq_name	ndbinfo/def/NDB$BLOB_
parent_fq_name	ndbinfo/def/t1
type	User table
frag_count	16
tot_key_reads	0
tot_key_inserts	200
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	16
tot_key_keyinfo_bytes	2400
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	0
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	0
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	200
fq_name	PRIMARY
parent_fq_name	ndbinfo/def/t1
type	Ordered index
frag_count	16
tot_key_reads	0
tot_key_inserts	0
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	0
tot_key_keyinfo_bytes	0
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	0
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	0
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	0
fq_name	ix1$unique
parent_fq_name	ndbinfo/def/t1
type	Unique hash index
frag_count	16
tot_key_reads	0
tot_key_inserts	200
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	12
tot_key_keyinfo_bytes	800
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	0
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	0
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	200
fq_name	ndbinfo/def/t2
parent_fq_name	NULL
type	User table
frag_count	16
tot_key_reads	0
tot_key_inserts	6
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	7
tot_key_keyinfo_bytes	24
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	8
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	32
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	6
explain select count(*) from t1 where d<10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	ALL	ix1	NULL	NULL	NULL	100	33.33	Using where with pushed condition (`ndbinfo`.`t1`.`d` < 10)
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` where (`ndbinfo`.`t1`.`d` < 10)
select count(*) from t1 where d<10;
count(*)
9

explain select count(*) from t1 where b=1 and a<10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	range	PRIMARY	PRIMARY	8	NULL	3	100.00	Using where with pushed condition ((`ndbinfo`.`t1`.`b` = 1) and (`ndbinfo`.`t1`.`a` < 10)); Using MRR
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` where ((`ndbinfo`.`t1`.`b` = 1) and (`ndbinfo`.`t1`.`a` < 10))
select count(*) from t1 where b=1 and a<10;
count(*)
10

explain select * from t1 where b=1 and a = -5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p4	eq_ref	PRIMARY	PRIMARY	8	const,const	1	100.00	NULL
Warnings:
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))
select * from t1 where b=1 and a = -5;
a	b	c	d	t

insert into t1 values (1,1,1,1,'');
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

replace t1 values (-1,-1,-1,-1,'');
replace t1 values (1,1,5,500,'');

update t1 set d = -d, t = repeat('a', 300) where b=1 and a > 90;

delete from t1 where a%30=0;

explain select count(*) from t1 as x1 join t1 as x2 on x1.c=x2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	x1	p0,p1,p2,p3,p4,p5,p6,p7	ALL	NULL	NULL	NULL	NULL	97	100.00	Parent of 2 pushed join@1
1	SIMPLE	x2	p0,p1,p2,p3,p4,p5,p6,p7	ref	PRIMARY	PRIMARY	4	ndbinfo.x1.c	1	100.00	Child of 'x1' in pushed join@1
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` `x1` join `ndbinfo`.`t1` `x2` where (`ndbinfo`.`x2`.`b` = `ndbinfo`.`x1`.`c`)
select count(*) from t1 as x1 join t1 as x2 on x1.c=x2.b;
count(*)
1

select 
  if ((select version() like '%debug'),
      sum(tot_qd_frag_scans),
      4) as tot_qd_frag_scans
  from ndbinfo.operations_per_fragment
  where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%';
tot_qd_frag_scans
4

select * from frag_ops;;
fq_name	ndbinfo/def/t1
parent_fq_name	NULL
type	User table
frag_count	16
tot_key_reads	19
tot_key_inserts	205
tot_key_updates	236
tot_key_writes	0
tot_key_deletes	10
tot_key_refs	4
log_tot_key_attrinfo_bytes	16
tot_key_keyinfo_bytes	3760
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	4220
tot_frag_scans	56
tot_scan_rows_examined	306
tot_scan_rows_returned	215
tot_scan_bytes_returned	7116
tot_scan_prog_bytes	288
tot_scan_bound_bytes	0
tot_scan_inst_exec	305
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	450
fq_name	ndbinfo/def/NDB$BLOB_
parent_fq_name	ndbinfo/def/t1
type	User table
frag_count	16
tot_key_reads	1
tot_key_inserts	218
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	28
tot_key_refs	0
log_tot_key_attrinfo_bytes	16
tot_key_keyinfo_bytes	2964
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	268
tot_frag_scans	0
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	0
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	246
fq_name	PRIMARY
parent_fq_name	ndbinfo/def/t1
type	Ordered index
frag_count	16
tot_key_reads	0
tot_key_inserts	0
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	0
tot_key_keyinfo_bytes	0
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	80
tot_scan_rows_examined	20
tot_scan_rows_returned	20
tot_scan_bytes_returned	588
tot_scan_prog_bytes	768
tot_scan_bound_bytes	9888
tot_scan_inst_exec	58
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	0
fq_name	ix1$unique
parent_fq_name	ndbinfo/def/t1
type	Unique hash index
frag_count	16
tot_key_reads	3
tot_key_inserts	222
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	28
tot_key_refs	3
log_tot_key_attrinfo_bytes	12
tot_key_keyinfo_bytes	1012
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	0
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	0
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	250
fq_name	ndbinfo/def/t2
parent_fq_name	NULL
type	User table
frag_count	16
tot_key_reads	0
tot_key_inserts	6
tot_key_updates	0
tot_key_writes	0
tot_key_deletes	0
tot_key_refs	0
log_tot_key_attrinfo_bytes	7
tot_key_keyinfo_bytes	24
tot_key_prog_bytes	0
tot_key_inst_exec	0
tot_key_bytes_returned	0
tot_frag_scans	8
tot_scan_rows_examined	0
tot_scan_rows_returned	0
tot_scan_bytes_returned	0
tot_scan_prog_bytes	32
tot_scan_bound_bytes	0
tot_scan_inst_exec	0
conc_frag_scans	0
conc_qd_frag_scans	0
tot_commits	6
explain select count(*)
from t2 as tx, t2 as ty
where tx.a in (1,3,5)
and ty.a = tx.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tx	p2,p3,p7	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Parent of 2 pushed join@1; Using where with pushed condition ((`ndbinfo`.`tx`.`a` in (1,3,5)) and (`ndbinfo`.`tx`.`b` is not null)); Using MRR
1	SIMPLE	ty	p0,p1,p2,p3,p4,p5,p6,p7	eq_ref	PRIMARY	PRIMARY	4	ndbinfo.tx.b	1	100.00	Child of 'tx' in pushed join@1
Warnings:
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)))

create table save_counters as select * from frag_ops;

select count(*)
from t2 as tx, t2 as ty
where tx.a in (1,3,5)
and ty.a = tx.b;
count(*)
2

create view changes as select 
new.fq_name,
new.parent_fq_name,
new.type,
new.frag_count,
new.tot_key_reads - old.tot_key_reads,
new.tot_key_inserts - old.tot_key_inserts,
new.tot_key_updates - old.tot_key_updates,
new.tot_key_writes - old.tot_key_writes,
new.tot_key_deletes - old.tot_key_deletes,
new.tot_key_refs - old.tot_key_refs,
new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes,
new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes,
new.tot_key_prog_bytes - old.tot_key_prog_bytes,
new.tot_key_inst_exec - old.tot_key_inst_exec,
new.tot_key_bytes_returned - old.tot_key_bytes_returned,
new.tot_frag_scans - old.tot_frag_scans,
new.tot_scan_rows_examined - old.tot_scan_rows_examined,
new.tot_scan_rows_returned - old.tot_scan_rows_returned,
new.tot_scan_bytes_returned - old.tot_scan_bytes_returned,
new.tot_scan_prog_bytes - old.tot_scan_prog_bytes,
new.tot_scan_bound_bytes - old.tot_scan_bound_bytes,
new.tot_scan_inst_exec - old.tot_scan_inst_exec,
new.tot_commits  - old.tot_commits
from frag_ops as new, save_counters as old
where new.fq_name=old.fq_name and new.fq_name like '%t2';

select * from changes;
fq_name	ndbinfo/def/t2
parent_fq_name	NULL
type	User table
frag_count	16
new.tot_key_reads - old.tot_key_reads	5
new.tot_key_inserts - old.tot_key_inserts	0
new.tot_key_updates - old.tot_key_updates	0
new.tot_key_writes - old.tot_key_writes	0
new.tot_key_deletes - old.tot_key_deletes	0
new.tot_key_refs - old.tot_key_refs	1
new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes	2
new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes	20
new.tot_key_prog_bytes - old.tot_key_prog_bytes	168
new.tot_key_inst_exec - old.tot_key_inst_exec	7
new.tot_key_bytes_returned - old.tot_key_bytes_returned	88
new.tot_frag_scans - old.tot_frag_scans	0
new.tot_scan_rows_examined - old.tot_scan_rows_examined	0
new.tot_scan_rows_returned - old.tot_scan_rows_returned	0
new.tot_scan_bytes_returned - old.tot_scan_bytes_returned	0
new.tot_scan_prog_bytes - old.tot_scan_prog_bytes	0
new.tot_scan_bound_bytes - old.tot_scan_bound_bytes	0
new.tot_scan_inst_exec - old.tot_scan_inst_exec	0
new.tot_commits  - old.tot_commits	0

drop view changes;
drop table save_counters;
drop view frag_ops;
drop table t1;
drop table t2;

## Check that the new tables return data and exists.
select count(*) > 10 from ndb$blocks;
count(*) > 10
1
select param_number, param_name from ndb$config_params
  where param_name = "DataMemory";
param_number	param_name
112	DataMemory
select * from ndb$dict_obj_types where type_id < 3;
type_id	type_name
1	System table
2	User table
select * from ndb$dbtc_apiconnect_state;
select * from ndb$dblqh_tcconnect_state;
## Check that the new views return data and exists.
select block_number from blocks where block_name = "DBTC";
block_number
245
select param_name from config_params where param_number = 101;
param_name
NoOfReplicas
select type_name from ndb$dict_obj_types where type_id = 3;
type_name
Unique hash index

## All tables in ndbinfo/ should now be using engine=NDBINFO
select count(*) from information_schema.tables
  where table_schema = 'ndbinfo' and engine != 'ndbinfo';
count(*)
0

set @@optimizer_switch='block_nested_loop=off';
create temporary table old_count
       select counter_name, sum(val) as val
       from ndbinfo.counters
       where block_name='DBSPJ'
       group by counter_name;
drop table old_count;
set @@optimizer_switch=default;

select * from ndbinfo.config_params order by param_number;
UPDATE ndb$config_params SET param_number=1 WHERE param_name = "NoOfReplicas";
ERROR HY000: Table 'ndb$config_params' is read only

UPDATE config_params SET param_number=1 WHERE param_name = "NoOfReplicas";
ERROR HY000: Table 'ndb$config_params' is read only

${this.title}

Code Editor : ndbinfo.result