25.6.16.25 The ndbinfo dict_obj_tree Table

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 on dict_obj_types to obtain the name of the object type

• id

  Object identifier; same as the id column in dict_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 Schema FILES table; for undo log files, it also the same as the value shown for the log_id column in the ndbinfo logbuffers and logspaces tables

• name

  The fully qualified name of the object; the same as the fq_name column in dict_obj_info

  For a table, this is database_name/def/table_name (the same as its parent_name); for an index of any type, this takes the form NDB$INDEX_index_id_CUSTOM

• parent_type

  The DICT object type of this object's parent object; join on dict_obj_types to obtain the name of the object type

• parent_id

  Identifier for this object's parent object; the same as the dict_obj_info table's id column

• parent_name

  Fully qualified name of this object's parent object; the same as the dict_obj_info table's fq_name column

  For a table, this has the form database_name/def/table_name. For an index, the name is sys/def/table_id/index_name. For a primary key, it is sys/def/table_id/PRIMARY, and for a unique key it is sys/def/table_id/uk_name$unique

• root_type

  The DICT object type of the root object; join on dict_obj_types to obtain the name of the object type

• root_id

  Identifier for the root object; the same as the dict_obj_info table's id column

• root_name

  Fully qualified name of the root object; the same as the dict_obj_info table's fq_name column

• level

  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 left

• indented_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:

CREATE 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:

mysql> 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:

mysql> 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:

mysql> 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:

mysql> 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:

mysql> 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)

The dict_obj_tree table was added in NDB 8.0.24.