The dict_obj_tree
table provides a tree-based
view of table information from the
dict_obj_info
table. This is
intended primarily for use in testing, but can be useful in
visualizing hierarchies of NDB
database
objects.
The dict_obj_tree
table contains the
following columns:
type
Type of
DICT
object; join ondict_obj_types
to obtain the name of the object typeid
Object identifier; same as the
id
column indict_obj_info
For Disk Data undo log files and data files, this is the same as the value shown in the
LOGFILE_GROUP_NUMBER
column of the Information SchemaFILES
table; for undo log files, it also the same as the value shown for thelog_id
column in the ndbinfologbuffers
andlogspaces
tablesname
The fully qualified name of the object; the same as the
fq_name
column indict_obj_info
For a table, this is
(the same as itsdatabase_name
/def/table_name
parent_name
); for an index of any type, this takes the formNDB$INDEX_
index_id
_CUSTOMparent_type
The
DICT
object type of this object's parent object; join ondict_obj_types
to obtain the name of the object typeparent_id
Identifier for this object's parent object; the same as the
dict_obj_info
table'sid
columnparent_name
Fully qualified name of this object's parent object; the same as the
dict_obj_info
table'sfq_name
columnFor a table, this has the form
. For an index, the name isdatabase_name
/def/table_name
sys/def/
. For a primary key, it istable_id
/index_name
sys/def/
, and for a unique key it istable_id
/PRIMARYsys/def/
table_id
/uk_name
$uniqueroot_type
The
DICT
object type of the root object; join ondict_obj_types
to obtain the name of the object typeroot_id
Identifier for the root object; the same as the
dict_obj_info
table'sid
columnroot_name
Fully qualified name of the root object; the same as the
dict_obj_info
table'sfq_name
columnlevel
Level of the object in the hierarchy
path
Complete path to the object in the
NDB
object hierarchy; objects are separated by a right arrow (represented as->
), starting with the root object on the leftindented_name
The
name
prefixed with a right arrow (represented as->
) with a number of spaces preceding it that correspond to the object's depth in the hierarchy
The path
column is useful for obtaining a
complete path to a given NDB
database object
in a single line, whereas the indented_name
column can be used to obtain a tree-like layout of complete
hierarchy information for a desired object.
Example: Assuming the existence of a
test
database and no existing table named
t1
in this database, execute the following
SQL statement:
Press CTRL+C to copyCREATE TABLE test.t1 ( a INT PRIMARY KEY, b INT, UNIQUE KEY(b) ) ENGINE = NDB;
You can obtain the path to the table just created using the query shown here:
Press CTRL+C to copymysql> SELECT path FROM ndbinfo.dict_obj_tree -> WHERE name LIKE 'test%t1'; +-------------+ | path | +-------------+ | test/def/t1 | +-------------+ 1 row in set (0.14 sec)
You can see the paths to all dependent objects of this table using the path to the table as the root name in a query like this one:
Press CTRL+C to copymysql> SELECT path FROM ndbinfo.dict_obj_tree -> WHERE root_name = 'test/def/t1'; +----------------------------------------------------------+ | path | +----------------------------------------------------------+ | test/def/t1 | | test/def/t1 -> sys/def/13/b | | test/def/t1 -> sys/def/13/b -> NDB$INDEX_15_CUSTOM | | test/def/t1 -> sys/def/13/b$unique | | test/def/t1 -> sys/def/13/b$unique -> NDB$INDEX_16_UI | | test/def/t1 -> sys/def/13/PRIMARY | | test/def/t1 -> sys/def/13/PRIMARY -> NDB$INDEX_14_CUSTOM | +----------------------------------------------------------+ 7 rows in set (0.16 sec)
To obtain a hierarchical view of the t1
table
with all its dependent objects, execute a query similar to this
one which selects the indented name of each object having
test/def/t1
as the name of its root object:
Press CTRL+C to copymysql> SELECT indented_name FROM ndbinfo.dict_obj_tree -> WHERE root_name = 'test/def/t1'; +----------------------------+ | indented_name | +----------------------------+ | test/def/t1 | | -> sys/def/13/b | | -> NDB$INDEX_15_CUSTOM | | -> sys/def/13/b$unique | | -> NDB$INDEX_16_UI | | -> sys/def/13/PRIMARY | | -> NDB$INDEX_14_CUSTOM | +----------------------------+ 7 rows in set (0.15 sec)
When working with Disk Data tables, note that, in this context,
a tablespace or log file group is considered a root object. This
means that you must know the name of any tablespace or log file
group associated with a given table, or obtain this information
from SHOW CREATE TABLE
and then
querying INFORMATION_SCHEMA.FILES
,
or similar means as shown here:
Press CTRL+C to copymysql> SHOW CREATE TABLE test.dt_1\G *************************** 1. row *************************** Table: dt_1 Create Table: CREATE TABLE `dt_1` ( `member_id` int unsigned NOT NULL AUTO_INCREMENT, `last_name` varchar(50) NOT NULL, `first_name` varchar(50) NOT NULL, `dob` date NOT NULL, `joined` date NOT NULL, PRIMARY KEY (`member_id`), KEY `last_name` (`last_name`,`first_name`) ) /*!50100 TABLESPACE `ts_1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> SELECT DISTINCT TABLESPACE_NAME, LOGFILE_GROUP_NAME -> FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='ts_1'; +-----------------+--------------------+ | TABLESPACE_NAME | LOGFILE_GROUP_NAME | +-----------------+--------------------+ | ts_1 | lg_1 | +-----------------+--------------------+ 1 row in set (0.00 sec)
Now you can obtain hierarchical information for the table, tablespace, and log file group like this:
Press CTRL+C to copymysql> SELECT indented_name FROM ndbinfo.dict_obj_tree -> WHERE root_name = 'test/def/dt_1'; +----------------------------+ | indented_name | +----------------------------+ | test/def/dt_1 | | -> sys/def/23/last_name | | -> NDB$INDEX_25_CUSTOM | | -> sys/def/23/PRIMARY | | -> NDB$INDEX_24_CUSTOM | +----------------------------+ 5 rows in set (0.15 sec) mysql> SELECT indented_name FROM ndbinfo.dict_obj_tree -> WHERE root_name = 'ts_1'; +-----------------+ | indented_name | +-----------------+ | ts_1 | | -> data_1.dat | | -> data_2.dat | +-----------------+ 3 rows in set (0.17 sec) mysql> SELECT indented_name FROM ndbinfo.dict_obj_tree -> WHERE root_name LIKE 'lg_1'; +-----------------+ | indented_name | +-----------------+ | lg_1 | | -> undo_1.log | | -> undo_2.log | +-----------------+ 3 rows in set (0.16 sec)