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;

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
SELECT COUNT(*) FROM ndb$tables;
COUNT(*)
11
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
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(*)
121

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
10	diskpagebuffer
6	logbuffers
5	logspaces
9	nodes
3	pools
7	resources
0	tables
2	test
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 'ndb$test' is read only

FLUSH TABLES;
SELECT table_id FROM ndb$tables;
table_id
0
1
2
3
4
5
6
7
8
9
10

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:
Warning	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:
Error	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'
Warning	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:
Error	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:
Error	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'
Warning	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
Error	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
Error	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
Error	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
Error	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
Error	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

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