MatrixOne System Databases and Tables

MatrixOne system databases and tables are where MatrixOne stores system information, allowing you to access system details through them. During initialization, MatrixOne Intelligence creates 5 system databases: mo_catalog, information_schema, system_metrics, system, and mysql. System databases and tables are default internal system components, and users can only perform read operations on them.

mo_catalog Database

mo_catalog is used to store metadata for MatrixOne objects, such as databases, tables, columns, system variables, tenants, users, and roles.

mo_indexes Table

Column Attribute

Type

Description

id

BIGINT UNSIGNED(64)

Index ID

table_id

BIGINT UNSIGNED(64)

ID of the table where the index is located

database_id

BIGINT UNSIGNED(64)

ID of the database where the index is located

name

VARCHAR(64)

Name of the index

type

VARCHAR(11)

Type of index, including primary key index (PRIMARY), unique index (UNIQUE), secondary index (MULTIPLE)

algo

VARCHAR(11)

Algorithm used to create the index

algo_table_type

VARCHAR(11)

Hidden table type for multi-table indexes

algo_params

VARCHAR(2048)

Index algorithm parameters

is_visible

TINYINT(8)

Whether the index is visible, 1 for visible, 0 for invisible (currently all MatrixOne indexes are visible)

hidden

TINYINT(8)

Whether the index is hidden, 1 for hidden, 0 for not hidden

comment

VARCHAR(2048)

Comment for the index

column_name

VARCHAR(256)

Column name of the index’s constituent column

ordinal_position

INT UNSIGNED(32)

Column sequence in the index, starting from 1

options

TEXT(0)

Options information for the index

index_table_name

VARCHAR(5000)

Table name of the index table corresponding to this index, currently only unique indexes have index tables

mo_table_partitions Table

Column Attribute

Type

Description

table_id

BIGINT UNSIGNED(64)

ID of the current partitioned table

database_id

BIGINT UNSIGNED(64)

ID of the database to which the current partitioned table belongs

number

SMALLINT UNSIGNED(16)

Current partition number. All partitions are indexed in the order they are defined, where 1 is the number assigned to the first partition.

name

VARCHAR(64)

Name of the partition

partition_type

VARCHAR(50)

Stores the partition type information of the table. If it is a partitioned table, its enumerated values are “KEY”, “LINEAR_KEY”, “HASH”, “LINEAR_KEY_51”, “RANGE”, “RANGE_COLUMNS”, “LIST”, “LIST_COLUMNS”; if it is not a partitioned table, the value of partition_type is an empty string.Note: MatrixOne currently does not support RANGE and LIST partitions.

partition_expression

VARCHAR(2048)

The partition function expression used in the CREATE TABLE or ALTER TABLE statement when creating the partitioned table.

description_utf8

TEXT(0)

This column is used for RANGE and LIST partitions. For RANGE partitions, it contains the value set in the partition’s VALUES LESS THAN clause, which can be an integer or MAXVALUE. For LIST partitions, this column contains the values defined in the partition’s values in clause, which is a comma-separated list of integer values. For partitions that are not RANGE or LIST, this column is always NULL.Note: MatrixOne currently does not support RANGE and LIST partitions, so this column is NULL.

comment

VARCHAR(2048)

The text of the comment. Otherwise, this value is empty.

options

TEXT(0)

Options information for the partition, currently NULL

partition_table_name

VARCHAR(1024)

Name of the partition sub-table corresponding to the current partition

mo_user Table

Column Attribute

Type

Description

user_id

int

User ID, primary key

user_host

varchar(100)

User host address

user_name

varchar(100)

Username

authentication_string

varchar(100)

Encrypted authentication string for the password

status

varchar(8)

Enabled, locked, invalid

created_time

timestamp

User creation time

expired_time

timestamp

User expiration time

login_type

varchar(16)

ssl/password/other

creator

int

ID of the creator who created this user

owner

int

Administrator ID of this user

default_role

int

Default role ID for this user

mo_database Table

Column Attribute

Type

Description

dat_id

bigint unsigned

Primary key ID

datname

varchar(100)

Database name

dat_catalog_name

varchar(100)

Database catalog name, default def

dat_createsql

varchar(100)

SQL statement for creating the database

owner

int unsigned

Role ID

creator

int unsigned

User ID

created_time

timestamp

Creation time

account_id

int unsigned

Tenant ID

dat_type

varchar(23)

Database type, normal database or subscription database

mo_role Table

Column Attribute

Type

Description

role_id

int unsigned

Role ID, primary key

role_name

varchar(100)

Role name

creator

int unsigned

User ID

owner

int unsigned

MatrixOne Administrator/Tenant Administrator Owner ID

created_time

timestamp

Creation time

comments

text

Comments

mo_user_grant Table

Column Attribute

Type

Description

role_id

int unsigned

Authorized role ID, composite primary key

user_id

int unsigned

User ID of the user granted the role, composite primary key

granted_time

timestamp

Grant time

with_grant_option

bool

Whether the authorized user is allowed to authorize other users or roles

mo_role_grant Table

Column Attribute

Type

Description

granted_id

int

ID of the role being granted, composite primary key

grantee_id

int

ID of the role to be granted to other roles, composite primary key

operation_role_id

int

Operation role ID

operation_user_id

int

Operation user ID

granted_time

timestamp

Grant time

with_grant_option

bool

Whether the authorized role is allowed to authorize other users or roles

mo_role_privs Table

Column Attribute

Type

Description

role_id

int unsigned

Role ID, composite primary key

role_name

varchar(100)

Role name: accountadmin/public

obj_type

varchar(16)

Object type: account/database/table, composite primary key

obj_id

bigint unsigned

Object ID, composite primary key

privilege_id

int

Privilege ID, composite primary key

privilege_name

varchar(100)

Privilege name: list of privileges

privilege_level

varchar(100)

Privilege level, composite primary key

operation_user_id

int unsigned

Operation user ID

granted_time

timestamp

Grant time

with_grant_option

bool

Whether authorization is allowed

mo_user_defined_function Table

Column Attribute

Type

Description

function_id

INT(32)

ID of the function, primary key

name

VARCHAR(100)

Name of the function

owner

INT UNSIGNED(32)

Role ID of the creator of the function

args

TEXT(0)

Parameter list of the function

rettype

VARCHAR(20)

Return type of the function

body

TEXT(0)

Function body of the function

language

VARCHAR(20)

Language used by the function

db

VARCHAR(100)

Database where the function is located

definer

VARCHAR(50)

Name of the user who defined the function

modified_time

TIMESTAMP(0)

Last modification time of the function

created_time

TIMESTAMP(0)

Creation time of the function

type

VARCHAR(10)

Type of the function, default FUNCTION

security_type

VARCHAR(10)

Security handling method, uniform value DEFINER

comment

VARCHAR(5000)

Comments for creating the function

character_set_client

VARCHAR(64)

Client character set: utf8mb4

collation_connection

VARCHAR(64)

Connection collation: utf8mb4_0900_ai_ci

database_collation

VARCHAR(64)

Database connection collation: utf8mb4_0900_ai_ci

mo_mysql_compatibility_mode Table

Column Attribute

Type

Description

configuration_id

INT(32)

Configuration item ID, auto-incrementing column, serving as the primary key to distinguish different configurations

account_id

INT(32)

Tenant ID where the configuration is located

account_name

VARCHAR(300)

Tenant name where the configuration is located

dat_name

VARCHAR(5000)

Database name where the configuration is located

variable_name

VARCHAR(300)

Name of the variable

variable_value

VARCHAR(5000)

Value of the variable

system_variables

BOOL(0)

Whether it is a system variable (in addition to system variables, compatibility variables are also added)

mo_pubs Table

Column Attribute

Type

Description

pub_name

VARCHAR(64)

Publication name

database_name

VARCHAR(5000)

Name of the published database

database_id

BIGINT UNSIGNED(64)

ID of the published database, corresponding to dat_id in the mo_database table

all_table

BOOL(0)

Whether the published database includes all tables within the database corresponding to database_id

table_list

TEXT(0)

When not all_table, a list of tables included in the published database, where table names correspond one-to-one with tables under the database corresponding to database_id

account_list

TEXT(0)

When not all_account, a list of accounts allowed to subscribe to this published database

created_time

TIMESTAMP(0)

Time when the published database was created

owner

INT UNSIGNED(32)

Role ID corresponding to the creation of the published database

creator

INT UNSIGNED(32)

User ID corresponding to the creation of the published database

comment

TEXT(0)

Remarks for creating the published database

mo_stages Table

Column Attribute

Type

Description

stage_id

INT UNSIGNED(32)

Data stage ID

stage_name

VARCHAR(64)

Data stage name

url

TEXT(0)

Object storage path (without authentication), file system path

stage_credentials

TEXT(0)

Authentication information, saved after encryption

stage_status

VARCHAR(64)

ENABLED/DISABLED Default: DISABLED

created_time

TIMESTAMP(0)

Creation time

comment

TEXT(0)

Comment

mo_sessions View

Column Name

Data Type

Description

node_id

VARCHAR(65535)

Unique identifier for a MatrixOne node. Cannot be changed once started.

conn_id

INT UNSIGNED

Unique number related to the client TCP connection in MatrixOne, automatically generated by Hakeeper.

session_id

VARCHAR(65535)

Unique UUID to identify the session. A new UUID is generated for each new session.

account

VARCHAR(65535)

Name of the tenant.

user

VARCHAR(65535)

Name of the user.

host

VARCHAR(65535)

IP address and port of the CN node receiving client requests.

db

VARCHAR(65535)

Name of the database used when executing SQL.

session_start

VARCHAR(65535)

Timestamp when the session was created.

command

VARCHAR(65535)

Type of MySQL command, such as COM_QUERY, COM_STMT_PREPARE, COM_STMT_EXECUTE, etc.

info

VARCHAR(65535)

SQL statement being executed. An SQL statement may contain multiple statements.

txn_id

VARCHAR(65535)

Unique identifier of the related transaction.

statement_id

VARCHAR(65535)

Unique identifier (UUID) of the SQL statement.

statement_type

VARCHAR(65535)

Type of the SQL statement, such as SELECT, INSERT, UPDATE, etc.

query_type

VARCHAR(65535)

Kind of SQL statement, such as DQL (Data Query Language), TCL (Transaction Control Language), etc.

sql_source_type

VARCHAR(65535)

Source of the SQL statement, such as external or internal.

query_start

VARCHAR(65535)

Timestamp when the SQL statement started execution.

client_host

VARCHAR(65535)

IP address and port number of the client.

role

VARCHAR(65535)

Role name of the user.

mo_configurations View

Column Name

Data Type

Description

node_type

VARCHAR(65535)

Type of the node: cn (compute node), tn (transaction node), log (log node), proxy (proxy).

node_id

VARCHAR(65535)

Unique identifier of the node.

name

VARCHAR(65535)

Name of the configuration item, possibly with a nested structure prefix.

current_value

VARCHAR(65535)

Current value of the configuration item.

default_value

VARCHAR(65535)

Default value of the configuration item.

internal

VARCHAR(65535)

Indicates whether the configuration parameter is an internal parameter.

mo_locks View

Column Name

Data Type

Description

cn_id

VARCHAR(65535)

UUID of the CN node.

txn_id

VARCHAR(65535)

Transaction holding the lock.

table_id

VARCHAR(65535)

Table being locked.

lock_key

VARCHAR(65535)

Lock type. Can be point or range.

lock_content

VARCHAR(65535)

Content of the lock, represented in hexadecimal. For range locks, it represents an interval; for point locks, it represents a single value.

lock_mode

VARCHAR(65535)

Lock mode. Can be shared or exclusive.

lock_status

VARCHAR(65535)

Lock status, can be wait, acquired, or none.<br>wait: No transaction holds the lock, but there are transactions waiting on the lock.<br>acquired: A transaction holds the lock.<br>none: No transaction holds the lock, and no transactions are waiting on the lock.

lock_wait

VARCHAR(65535)

Transactions waiting on this lock.

mo_variables View

Column Name

Data Type

Description

configuration_id

INT(32)

Auto-incrementing column, used to uniquely identify each configuration item.

account_id

INT(32)

Unique identifier of the tenant.

account_name

VARCHAR(300)

Name of the tenant.

dat_name

VARCHAR(5000)

Name of the database.

variable_name

VARCHAR(300)

Name of the configuration variable.

variable_value

VARCHAR(5000)

Value of the configuration variable.

system_variables

BOOL(0)

Indicates whether the configuration variable is a system-level variable.

mo_transactions View

Column Name

Data Type

Description

cn_id

VARCHAR(65535)

Unique ID of the CN (Compute Node).

txn_id

VARCHAR(65535)

Unique ID of the transaction.

create_ts

VARCHAR(65535)

Timestamp of transaction creation, following RFC3339Nano format (“2006-01-02T15:04:05.999999999Z07:00”).

snapshot_ts

VARCHAR(65535)

Represents the transaction’s snapshot timestamp, expressed as physical and logical time.

prepared_ts

VARCHAR(65535)

Represents the transaction’s prepared timestamp, expressed as physical and logical time.

commit_ts

VARCHAR(65535)

Represents the transaction’s commit timestamp, expressed as physical and logical time.

txn_mode

VARCHAR(65535)

Identifies the transaction mode, can be pessimistic or optimistic.

isolation

VARCHAR(65535)

Represents the transaction’s isolation level, can be SI (Snapshot Isolation) or RC (Read Committed).

user_txn

VARCHAR(65535)

Indicates a user transaction, i.e., a transaction created by a user connecting to MatrixOne via a client and performing SQL operations.

txn_status

VARCHAR(65535)

Represents the current status of the transaction. Possible values include active, committed, aborting, aborted. In distributed transaction 2PC mode, it will also include prepared and committing.

table_id

VARCHAR(65535)

Represents the ID of the table involved in the transaction.

lock_key

VARCHAR(65535)

Represents the type of lock, can be range or point.

lock_content

VARCHAR(65535)

For point locks, it represents a single value; for range locks, it represents a range, usually in the “low - high” format. Note that a transaction may involve multiple locks, but only the first lock is displayed here.

lock_mode

VARCHAR(65535)

Represents the mode of the lock, can be exclusive or shared.

mo_columns Table

Column Attribute

Type

Description

att_uniq_name

varchar(256)

Primary key. Hidden composite primary key, format similar to “${att_relname_id}-${attname}”

account_id

int unsigned

Tenant ID

att_database_id

bigint unsigned

Database ID

att_database

varchar(256)

Database Name

att_relname_id

bigint unsigned

Table ID

att_relname

varchar(256)

The table to which this column belongs. (Refer to mo_tables.relname)

attname

varchar(256)

Column name

atttyp

varchar(256)

Data type of this column (deleted columns are 0).

attnum

int

Column number. Regular columns are numbered starting from 1.

att_length

int

Byte size of the type

attnotnull

tinyint(1)

Indicates a not-null constraint.

atthasdef

tinyint(1)

This column has a default expression or generated expression.

att_default

varchar(1024)

Default expression

attisdropped

tinyint(1)

This column has been dropped and is no longer valid. Dropped columns physically still exist in the table, but the parser ignores them, so they cannot be accessed via SQL.

att_constraint_type

char(1)

p = Primary key constraint<br>n = No constraint

att_is_unsigned

tinyint(1)

Whether it is unsigned

att_is_auto_increment

tinyint(1)

Whether it is auto-increment

att_comment

varchar(1024)

Comment

att_is_hidden

tinyint(1)

Whether it is hidden

attr_has_update

tinyint(1)

This column contains an update expression

attr_update

varchar(1024)

Update expression

attr_is_clusterby

tinyint(1)

Whether this column is used as a cluster by keyword to create the table

attr_seqnum

SMALLINT UNSIGNED(0)

Sequence number for each column

attr_enum

varchar(65535)

If the type of this column is ENUM, it represents the ENUM type value represented by this column, otherwise it is empty.

mo_tables Table

Column Attribute

Type

Description

rel_id

bigint unsigned

Primary key, Table ID

relname

varchar(100)

Name of table, index, view, etc.

reldatabase

varchar(100)

Database containing this relation, refer to mo_database.datname

reldatabase_id

bigint unsigned

ID of the database containing this relation, refer to mo_database.datid

relpersistence

varchar(100)

p = Permanent table<br> t = Temporary table

relkind

varchar(100)

r = Ordinary table<br> e = External table<br> i = Index<br> S = Sequence<br> v = View<br> m = Materialized view

rel_comment

varchar(100)

Table comment

rel_createsql

varchar(100)

SQL statement for creating the table

created_time

timestamp

Creation time

creator

int unsigned

Creator ID

owner

int unsigned

Creator’s default role ID

account_id

int unsigned

Tenant ID

partitioned

blob

Partitioned by statement

partition_info

blob

Partition information

viewdef

blob

View definition statement

constraint

varchar(5000)

Constraints associated with the table

rel_version

INT UNSIGNED(0)

Primary key, table version number

catalog_version

INT UNSIGNED(0)

System table version number

system_metrics Database

system_metrics collects status and statistical information on SQL statements, CPU, and memory resource usage.

Some common column types in metrics tables, with descriptions as follows:

-collecttime: Collection time. -value: Collected metrics value. -node: UUID of the MatrixOne node. -role: MatrixOne node role, including CN, TN, and Log. -account: Defaults to the “sys” tenant, which is the account that triggered the SQL request. -type: SQL type, can be select, insert, update, delete, or other type.

metric Table

Column Attribute

Type

Description

metric_name

VARCHAR(128)

Metric name, e.g., sql_statement_total, server_connections, process_cpu_percent, sys_memory_used, etc.

collecttime

DATETIME

Metric data collection time

value

DOUBLE

Metric value

node

VARCHAR(36)

MatrixOne node UUID

role

VARCHAR(32)

MatrixOne node role

account

VARCHAR(128)

Tenant name, default sys

type

VARCHAR(32)

SQL type, e.g., INSERT, SELECT, UPDATE

The following tables are views of the metric table:

  • sql_statement_total table: Counter for executed SQL statements.

  • sql_statement_errors table: Counter for SQL statements that encountered errors.

  • sql_transaction_total table: Counter for transactional SQL statements.

  • sql_transaction_errors table: Counter for transaction statements that executed with errors.

  • server_connections table: Number of server connections.

  • server_storage_usage: Server storage usage.

  • process_cpu_percent table: Percentage of CPU process busyness.

  • process_resident_memory_bytes table: Amount of resident memory, in bytes.

  • process_open_fds table: Number of open file descriptors.

  • sys_cpu_seconds_total table: Total system CPU time, in seconds, normalized by core count.

  • sys_cpu_combined_percent table: System CPU busyness percentage, average of all logical cores.

  • sys_memory_used table: Used system memory in bytes.

  • sys_memory_available table: Available system memory in bytes.

  • sys_disk_read_bytes table: System disk read in bytes.

  • sys_disk_write_bytes table: System disk write in bytes.

  • sys_net_recv_bytes table: System network received in bytes.

  • sys_net_sent_bytes table: System network sent in bytes.

system Database

The System database stores MatrixOne historical SQL statements, system logs, and error information.

statement_info Table

The statement_info table records user and system SQL statements and their detailed information.

Column Attribute

Type

Description

statement_id

VARCHAR(36)

Unique ID of the statement

transaction_id

VARCHAR(36)

Unique ID of the transaction

session_id

VARCHAR(36)

Unique ID of the account

account

VARCHAR(1024)

Tenant name

user

VARCHAR(1024)

User name

host

VARCHAR(1024)

User client IP

database

VARCHAR(1024)

Current database where the session is active

statement

TEXT

SQL statement

statement_tag

TEXT

Comment tag in the statement (reserved)

statement_fingerprint

TEXT

Comment tag in the statement (reserved)

node_uuid

VARCHAR(36)

Node UUID, i.e., the node that generated the data

node_type

VARCHAR(64)

Node type within MatrixOne, belonging to TN/CN/Log

request_at

DATETIME

Datetime when the request was accepted

response_at

DATETIME

Datetime when the response was sent

duration

BIGINT

Execution time, unit: ns

status

VARCHAR(32)

SQL statement execution status: Running, Success, Failed

err_code

VARCHAR(1024)

Error code

error

TEXT

Error message

exec_plan

JSON

Statement execution plan

rows_read

BIGINT

Total rows read

bytes_scan

BIGINT

Total bytes scanned

stats

JSON

Global statistics from exec_plan

statement_type

VARCHAR(1024)

Statement type: [Insert, Delete, Update, Drop Table, Drop User, …]

query_type

VARCHAR(1024)

Query type: [DQL, DDL, DML, DCL, TCL]

role_id

BIGINT

Role ID

sql_source_type

TEXT

SQL statement source type: internal_sql, external_sql, cloud_nonuser_sql

result_count

BIGINT(64)

Number of rows in SQL execution result

aggr_count

BIGINT(64)

Number of aggregations

rawlog Table

The rawlog table records very detailed system logs.

Column Attribute

Type

Description

raw_item

VARCHAR(1024)

Original log item

node_uuid

VARCHAR(36)

Node UUID, i.e., the node that generated the data

node_type

VARCHAR(64)

Node type within MatrixOne, belonging to TN/CN/Log

span_id

VARCHAR(16)

Unique ID of the span

trace_id

VARCHAR(36)

Unique trace UUID

logger_name

VARCHAR(1024)

Name of the logger

timestamp

DATETIME

Timestamp of the action

level

VARCHAR(1024)

Log level, e.g., debug, info, warn, error, panic, fatal

caller

VARCHAR(1024)

Location where the log was generated: package/file.go:123

message

TEXT

Log message

extra

JSON

Dynamic fields of the log

err_code

VARCHAR(1024)

Error log

error

TEXT

Error message

stack

VARCHAR(4096)

Stack information for log_info and error_info

span_name

VARCHAR(1024)

Span name, e.g., step name of execution plan, function name in code, …

parent_span_id

VARCHAR(16)

Unique ID of the parent span

start_time

DATETIME

Start time of the span

end_time

DATETIME

End time of the span

duration

BIGINT

Execution time, unit: ns

resource

JSON

Static resource information

span_kind

VARCHAR(1024)

Span type. internal: MO internal generated trace (default); statement: trace_id==statement_id; remote: communication via morpc

statement_id

VARCHAR(36)

ID of the statement

session_id

VARCHAR(36)

ID of the session

The other 3 tables (log_info, span_info, and error_info) are views of the statement_info and rawlog tables.

information_schema Database

Information Schema provides an ANSI standard way to view system metadata. In addition to tables included for MySQL compatibility, MatrixOne also provides many custom information_schema tables.

Many INFORMATION_SCHEMA tables have corresponding SHOW commands. Querying INFORMATION_SCHEMA allows joins between tables.

MySQL Compatibility Tables

Table Name

Description

KEY_COLUMN_USAGE

Describes key constraints on columns, such as primary key constraints.

COLUMNS

Provides a list of columns for all tables.

PROFILING

Provides some analysis information when SQL statements are executed.

PROCESSLIST

Provides information similar to executing the SHOW PROCESSLIST command.

USER_PRIVILEGES

Lists privileges associated with the current user.

SCHEMATA

Provides information similar to executing SHOW DATABASES.

CHARACTER_SETS

Provides a list of character sets supported by the server.

TRIGGERS

Provides information similar to executing SHOW TRIGGERS.

TABLES

Provides a list of tables visible to the current user. Similar to executing SHOW TABLES.

PARTITIONS

Provides partition information for tables.

VIEWS

Provides information about views in the database.

ENGINES

Provides a list of supported storage engines.

ROUTINES

Provides some information about stored procedures.

PARAMETERS

Provides information about parameters and return values of stored procedures.

KEYWORDS

Provides information about keywords in the database. See Keywords for details.

CHARACTER_SETS Table

The columns in the CHARACTER_SETS table are described as follows:

  • CHARACTER_SET_NAME: The name of the character set.

  • DEFAULT_COLLATE_NAME: The default collation name for the character set.

  • DESCRIPTION: A description of the character set.

  • MAXLEN: The maximum length required to store a character in this character set.

COLUMNS View

The columns in the COLUMNS view are described as follows:

  • TABLE_CATALOG: The name of the catalog to which the table containing this column belongs. This value is always def.

  • TABLE_SCHEMA: The name of the schema where the table containing the column is located.

  • TABLE_NAME: The name of the table containing the column.

  • COLUMN_NAME: The name of the column.

  • ORDINAL_POSITION: The position of the column in the table.

  • COLUMN_DEFAULT: The default value for the column. This value is NULL if the explicit default value is NULL, or if the column definition does not contain a default clause.

  • IS_NULLABLE: Whether the column can be nullable. The value is YES if the column can store null values; otherwise, it is NO.

  • DATA_TYPE: The data type in the column.

  • CHARACTER_MAXIMUM_LENGTH: For string columns, the maximum length in characters.

  • CHARACTER_OCTET_LENGTH: For string columns, the maximum length in bytes.

  • NUMERIC_PRECISION: Numeric precision for numeric type columns.

  • NUMERIC_SCALE: Numeric scale for numeric type columns.

  • DATETIME_PRECISION: For time type columns, fractional seconds precision.

  • CHARACTER_SET_NAME: Character set name for string columns.

  • COLLATION_NAME: Name of the collation for string columns.

  • COLUMN_TYPE: Column type.

  • COLUMN_KEY: Whether this column is indexed. This field can have the following values:

    • Empty: This column is not indexed, or this column is indexed and is the second column in a multi-column non-unique index.

    • PRI: This column is a primary key or one of multiple primary keys.

    • UNI: This column is the first column of a unique index.

    • MUL: This column is the first column of a non-unique index, where a given value is allowed to appear multiple times.

  • EXTRA: Any additional information for the given column.

  • PRIVILEGES: Privileges that the current user has on this column.

  • COLUMN_COMMENT: Description included in the column definition.

  • GENERATION_EXPRESSION: For generated columns, this value shows the expression used to calculate the column value. For non-generated columns, this value is empty.

  • SRS_ID: This value applies to spatial columns. It contains the column’s SRID value, which indicates a spatial reference system for values stored in that column.

ENGINES Table

The columns in the ENGINES table are described as follows:

  • ENGINE: The name of the storage engine.

  • SUPPORT: The server’s level of support for the storage engine.

  • COMMENT: A short comment on the storage engine.

  • TRANSACTIONS: Whether the storage engine supports transactions.

  • XA: Whether the storage engine supports XA transactions.

  • SAVEPOINTS: Whether the storage engine supports savepoints.

PARTITIONS View

The columns in the PARTITIONS view are described as follows:

  • TABLE_CATALOG: The name of the catalog to which the table containing this column belongs. This value is always def.

  • TABLE_SCHEMA: The name of the schema where the table containing the column is located.

  • TABLE_NAME: The name of the table containing the column.

  • PARTITION_NAME: The name of the partition.

  • SUBPARTITION_NAME: If the row in the PARTITIONS table represents a subpartition, this is the name of that subpartition; otherwise, it is empty.

  • PARTITION_ORDINAL_POSITION: All partitions are indexed in the order they are defined, where 1 is the number assigned to the first partition. As partitions are added, deleted, and reorganized, the index may change; the number displayed in this column reflects the current order, taking into account any index changes.

  • SUBPARTITION_ORDINAL_POSITION: Within a given partition, subpartitions are indexed and re-indexed in the same way as partitions within a table.

  • PARTITION_METHOD: One of RANGE, LIST, HASH, LINEAR HASH, KEY, or LINEAR KEY.Note: MatrixOne currently does not support RANGE and LIST partitions.

  • SUBPARTITION_METHOD: One of HASH, LINEAR HASH, KEY, or LINEAR KEY.

  • PARTITION_EXPRESSION: The partition function expression used in the CREATE TABLE or ALTER TABLE statement when creating the table’s current partitioning scheme.

  • SUBPARTITION_EXPRESSION: This is similar to PARTITION_EXPRESSION, used to define how the table is subpartitioned. If the table has no subpartitions, this column is empty.

  • PARTITION_DESCRIPTION: This column applies to RANGE and LIST partitions. For RANGE partitions, it contains the value set in the partition’s VALUES LESS THAN clause, which can be an integer or MAXVALUE. For LIST partitions, this column contains the values defined in the partition’s VALUES IN clause, which is a comma-separated set of integer values. For partitions whose PARTITION_METHOD is not RANGE or LIST, this column is always empty.Note: MatrixOne currently does not support RANGE and LIST partitions.

  • TABLE_ROWS: The number of table rows in the partition.

  • AVG_ROW_LENGTH: The average length of rows stored in this partition or subpartition, in bytes. This is the same as the result of DATA_LENGTH divided by TABLE_ROWS.

  • DATA_LENGTH: The total length of all rows stored in this partition or subpartition, in bytes; i.e., the total number of bytes stored in the partition or subpartition.

  • MAX_DATA_LENGTH: Maximum data length of the partition.

  • INDEX_LENGTH: The length of the index file for this partition or subpartition, in bytes.

  • DATA_FREE: The number of bytes allocated to the partition or subpartition but not used.

  • CREATE_TIME: The time the partition or subpartition was created.

  • UPDATE_TIME: The time the partition or subpartition was last modified.

  • CHECK_TIME: The time the table belonging to this partition or subpartition was last checked.

  • CHECKSUM: The checksum value, if any; otherwise, it is empty.

  • PARTITION_COMMENT: If the partition has a comment, this is the text of the comment. If not, the value is empty. The maximum length for partition comments is defined as 1024 characters, and the display width of the PARTITION_COMMENT column is also 1024 characters to match this limit.

  • NODEGROUP: The node group to which this partition belongs.

  • TABLESPACE_NAME: The name of the tablespace to which this partition belongs. This value is always DEFAULT.

PROCESSLIST View

The fields in the PROCESSLIST view are described as follows:

  • NODE_ID: CN node UUID.

  • CONN_ID: User connection ID.

  • SESSION_ID: Session ID.

  • ACCOUNT: Tenant name.

  • USER: User name.

  • HOST: CN node listening address.

  • DB: Current connected database.

  • SESSION_START: Session creation time.

  • COMMAND: MySQL protocol Command of the statement.

  • INFO: SQL statement being processed.

  • TXN_ID: Transaction ID.

  • STATEMENT_ID: Statement ID.

  • STATEMENT_TYPE: Statement type, e.g., Select/Update/Delete.

  • QUERY_TYPE: Query type, e.g., DQL/DDL/DML.

  • SQL_SOURCE_TYPE: SQL statement source type, external or internal SQL: external_sql/internal_sql.

  • QUERY_START: Query start time.

  • CLIENT_HOST: Client address.

SCHEMATA View

The SCHEMATA view provides information about databases, with data equivalent to the result of the SHOW DATABASES statement. The fields in the SCHEMATA view are described as follows:

  • CATALOG_NAME: The catalog to which the database belongs.

  • SCHEMA_NAME: The name of the database.

  • DEFAULT_CHARACTER_SET_NAME: The default character set of the database.

  • DEFAULT_COLLATION_NAME: The default collation of the database.

  • SQL_PATH: The value of this item is always NULL.

  • DEFAULT_TABLE_ENCRYPTION: Defines the default encryption setting for the database and general tablespaces.

TABLES View

The columns in the TABLES view are described as follows:

  • TABLE_CATALOG: The name of the catalog to which the table belongs. This value is always def.

  • TABLE_SCHEMA: The name of the schema to which the table belongs.

  • TABLE_NAME: The name of the table.

  • TABLE_TYPE: The type of the table. Basic table type is BASE TABLE, view table type is VIEW, and INFORMATION_SCHEMA table type is SYSTEM VIEW.

  • ENGINE: The type of storage engine.

  • VERSION: Version. Default value is 10.

  • ROW_FORMAT: Row storage format. Values are Fixed, Dynamic, Compressed, Redundant, Compact.

  • TABLE_ROWS: Number of rows in the table. For INFORMATION_SCHEMA tables, TABLE_ROWS is NULL.

  • AVG_ROW_LENGTH: Average row length of the table. AVG_ROW_LENGTH = DATA_LENGTH / TABLE_ROWS.

  • DATA_LENGTH: Data length. DATA_LENGTH = TABLE_ROWS * sum of stored lengths of columns in tuples.

  • MAX_DATA_LENGTH: Maximum data length. This value is currently 0, indicating no upper limit on data length.

  • INDEX_LENGTH: Index length. INDEX_LENGTH = TABLE_ROWS * total length of columns in index tuples.

  • DATA_FREE: Data fragment. This value is currently 0.

  • AUTO_INCREMENT: Current step of the auto-increment primary key.

  • CREATE_TIME: Time when the table was created.

  • UPDATE_TIME: Time when the table was updated.

  • CHECK_TIME: Time when the table was checked.

  • TABLE_COLLATION: Collation of strings in the table.

  • CHECKSUM: Checksum.

  • CREATE_OPTIONS: Creation options.

  • TABLE_COMMENT: Table comments and remarks.

USER_PRIVILEGES Table

The USER_PRIVILEGES table provides information about global privileges.

The fields in the USER_PRIVILEGES table are described as follows:

  • GRANTEE: The authorized username, in the format 'user_name'@'host_name'.

  • TABLE_CATALOG: The name of the catalog to which the table belongs. The value is def.

  • PRIVILEGE_TYPE: The type of privilege to be granted. Each row displays only one privilege type.

  • IS_GRANTABLE: If you have the GRANT OPTION privilege, the value is YES; otherwise, the value is NO.

VIEWS View

  • TABLE_CATALOG: The name of the catalog to which the view belongs. The value is def.

  • TABLE_SCHEMA: The name of the database to which the view belongs.

  • TABLE_NAME: The name of the view.

  • VIEW_DEFINITION: Provides the SELECT statement that defines the view. This includes most of what is seen in theCreate Table column generated by SHOW Create VIEW.

  • CHECK_OPTION: The value of the CHECK_OPTION attribute. Values are NONE, CASCADE, or LOCAL.

  • IS_UPDATABLE: When CREATE VIEW is set, a flag named view updatability flag is set. If UPDATE and DELETE (and similar operations) are legal for the view, the flag is set to YES (true). Otherwise, the flag is set to NO (false).

  • DEFINER: The account of the user who created the view, in the format username@hostname.

  • SECURITY_TYPE: The SQL SECURITY characteristic of the view. Values are DEFINER or INVOKER.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the view was created.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the view was created.

  • INDEX_COMMENT: Any comments provided for indexes with the comment attribute when the index was created.

  • IS_VISIBLE: Indicates whether the index is visible to the optimizer. Currently, this field has no meaning in MO.

  • EXPRESSION: Represents an expression or function in the index. Currently, this field has no meaning in MO.

STATISTICS View

Get detailed information about database table indexes and statistics. For example, you can check if an index is unique, understand the column order in an index, and estimate the number of unique values in an index.

  • TABLE_CATALOG: The catalog name of the table (always ‘def’).

  • TABLE_SCHEMA: The database name to which the table belongs.

  • TABLE_NAME: The name of the table.

  • NON_UNIQUE: Indicates whether the index allows duplicate values. If 0, the index is unique.

  • INDEX_SCHEMA: The database name to which the index belongs.

  • INDEX_NAME: The name of the index.

  • SEQ_IN_INDEX: The position of the column in the index.

  • COLUMN_NAME: The name of the column.

  • COLLATION: The collation of the column.

  • CARDINALITY: An estimate of the number of unique values in the index.

  • SUB_PART: The length of the index part. For the entire column, this value is NULL.

  • PACKED: Indicates whether compressed values are used for storage.

  • NULLABLE: Indicates whether the column allows NULL values.

  • INDEX_TYPE: The type of index (e.g., BTREE, HASH, etc.).

  • COMMENT: Comment information for the index.

  • INDEX_COMMENT: Any comments provided for indexes with the comment attribute when the index was created.

  • IS_VISIBLE: Indicates whether the index is visible to the optimizer. Currently, this field has no meaning in MO.

  • EXPRESSION: Represents an expression or function in the index. Currently, this field has no meaning in MO.

mysql Database

Authorization System Tables

The authorization system tables contain information about user accounts and their privileges:

  • user: User accounts, global privileges, and other non-privilege columns.

  • db: Database-level privileges.

  • tables_priv: Table-level privileges.

  • columns_priv: Column-level privileges.

  • procs_priv: Privileges for stored procedures and stored functions.