Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.2Kb
Man Pages (Zip) - 402.4Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

25.6.16.25 The ndbinfo dict_obj_tree Table

Thedict_obj_tree table provides a tree-based view of table information from thedict_obj_info table. This is intended primarily for use in testing, but can be useful in visualizing hierarchies ofNDB database objects.

Thedict_obj_tree table contains the following columns:

  • type

    Type ofDICT object; join ondict_obj_types to obtain the name of the object type

  • id

    Object identifier; same as theid column indict_obj_info

    For Disk Data undo log files and data files, this is the same as the value shown in theLOGFILE_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 tables

  • name

    The fully qualified name of the object; the same as thefq_name column indict_obj_info

    For a table, this isdatabase_name/def/table_name (the same as itsparent_name); for an index of any type, this takes the formNDB$INDEX_index_id_CUSTOM

  • parent_type

    TheDICT object type of this object's parent object; join ondict_obj_types to obtain the name of the object type

  • parent_id

    Identifier for this object's parent object; the same as thedict_obj_info table'sid column

  • parent_name

    Fully qualified name of this object's parent object; the same as thedict_obj_info table'sfq_name column

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

  • root_type

    TheDICT object type of the root object; join ondict_obj_types to obtain the name of the object type

  • root_id

    Identifier for the root object; the same as thedict_obj_info table'sid column

  • root_name

    Fully qualified name of the root object; the same as thedict_obj_info table'sfq_name column

  • level

    Level of the object in the hierarchy

  • path

    Complete path to the object in theNDB object hierarchy; objects are separated by a right arrow (represented as->), starting with the root object on the left

  • indented_name

    Thename prefixed with a right arrow (represented as->) with a number of spaces preceding it that correspond to the object's depth in the hierarchy

Thepath column is useful for obtaining a complete path to a givenNDB database object in a single line, whereas theindented_name column can be used to obtain a tree-like layout of complete hierarchy information for a desired object.

Example: Assuming the existence of atest database and no existing table namedt1 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 thet1 table with all its dependent objects, execute a query similar to this one which selects the indented name of each object havingtest/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 fromSHOW CREATE TABLE and then queryingINFORMATION_SCHEMA.FILES, or similar means as shown here:

mysql> SHOW CREATE TABLE test.dt_1\G*************************** 1. row ***************************       Table: dt_1Create 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_ci1 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)

Thedict_obj_tree table was added in NDB 8.0.24.