我的书签
添加书签
移除书签MatrixOne System Database and Tables
MatrixOne system database and tables are where MatrixOne stores system information. We can access the system information through them. MatrixOne creates 6 system databases at initialization: mo_catalog, information_schema, system_metrcis, system, mysql, and mo_task. mo_task is under development and have no direct impact on users. The other system databases and table functions are described in this document.
The system can only modify system databases and tables, and users can only read from them.
mo_catalog database
mo_catalog stores the metadata of MatrixOne objects: databases, tables, columns, system variables, accounts, users, and roles.
Start with MatrixOne 0.6 has introduced the concept of multi-account, the default sys account and other accounts have slightly different behaviors. The system table mo_account, which serves the multi-tenancy management, is only visible for the sys account; the other accounts don’t see this table.
mo_database table
| column | type | comments |
|---|---|---|
| dat_id | bigint unsigned | Primary key |
| datname | varchar(100) | Database name |
| dat_catalog_name | varchar(100) | Database catalog name, default as def |
| dat_createsql | varchar(100) | Database creation SQL statement |
| owner | int unsigned | Role id |
| creator | int unsigned | User id |
| created_time | timestamp | Create time |
| account_id | int unsigned | Account id |
mo_tables table
| column | type | comments |
|---|---|---|
| rel_id | bigint unsigned | Primary key |
| relname | varchar(100) | Name of the table, index, view, and so on. |
| reldatabase | varchar(100) | The database that contains this relation. reference mo_database.datname |
| reldatabase_id | bigint unsigned | The database id that contains this relation. reference mo_database.datid |
| relpersistence | varchar(100) | p = permanent table, t = temporary table |
| relkind | varchar(100) | r = ordinary table, e = external table, i = index, S = sequence, v = view, m = materialized view |
| rel_comment | varchar(100) | |
| rel_createsql | varchar(100) | Table creation SQL statement |
| created_time | timestamp | Create time |
| creator | int unsigned | Creator ID |
| owner | int unsigned | Creator’s default role id |
| account_id | int unsigned | Account id |
| partitioned | blob | Partition by statement |
| viewdef | blob | View definition statement |
mo_columns table
| column | type | comments |
|---|---|---|
| att_uniq_name | varchar(256) | Primary Key. Hidden, composite primary key, format is like “${att_relname_id}-${attname}” |
| account_id | int unsigned | accountID |
| att_database_id | bigint unsigned | databaseID |
| att_database | varchar(256) | database Name |
| att_relname_id | bigint unsigned | table id |
| att_relname | varchar(256) | The table this column belongs to.(references mo_tables.relname) |
| attname | varchar(256) | The column name |
| atttyp | varchar(256) | The data type of this column (zero for a dropped column). |
| attnum | int | The number of the column. Ordinary columns are numbered from 1 up. |
| att_length | int | bytes count for the type. |
| attnotnull | tinyint(1) | This represents a not-null constraint. |
| atthasdef | tinyint(1) | This column has a default expression or generation expression. |
| att_default | varchar(1024) | default expression |
| attisdropped | tinyint(1) | This column has been dropped and is no longer valid. A dropped column is still physically present in the table, but is ignored by the parser and so cannot be accessed via SQL. |
| att_constraint_type | char(1) | p = primary key constraint, n=no constraint |
| att_is_unsigned | tinyint(1) | unsigned or not |
| att_is_auto_increment | tinyint(1) | auto increment or not |
| att_comment | varchar(1024) | comment |
| att_is_hidden | tinyint(1) | hidden or not |
| attr_has_update | tinyint(1) | This columns has update expression |
| attr_update | varchar(1024) | update expression |
mo_account table (Only visible for sys account)
| column | type | comments |
|---|---|---|
| account_id | int unsigned | account id |
| account_name | varchar(100) | account name |
| status | varchar(100) | open/suspend |
| created_time | timestamp | create time |
| comments | varchar(256) | comment |
| suspended_time | TIMESTAMP | Time of the account’s status is changed |
mo_role table
| column | type | comments |
|---|---|---|
| role_id | int unsigned | role id |
| role_name | varchar(100) | role name |
| creator | int unsigned | user_id |
| owner | int unsigned | MOADMIN/ACCOUNTADMIN ownerid |
| created_time | timestamp | create time |
| comments | text | comment |
mo_user table
| column | type | comments |
|---|---|---|
| user_id | int | user id |
| user_host | varchar(100) | user host address |
| user_name | varchar(100) | user name |
| authentication_string | varchar(100) | authentication string encrypted with password |
| status | varchar(8) | open,locked,expired |
| created_time | timestamp | user created time |
| expired_time | timestamp | user expired time |
| login_type | varchar(16) | ssl/password/other |
| creator | int | the creator id who created this user |
| owner | int | the admin id for this user |
| default_role | int | the default role id for this user |
mo_user_grant table
| column | type | comments |
|---|---|---|
| role_id | int unsigned | role id |
| user_id | int unsigned | user id |
| granted_time | timestamp | granted time |
| with_grant_option | bool | If permission granting is permitted |
mo_role_grant table
| column | type | comments |
|---|---|---|
| granted_id | int | the role id being granted |
| grantee_id | int | the role id to grant others |
| operation_role_id | int | operation role id |
| operation_user_id | int | operation user id |
| granted_time | timestamp | granted time |
| with_grant_option | bool | If permission granting is permitted |
mo_role_privs table
| column | type | comments |
|---|---|---|
| role_id | int | role id |
| role_name | varchar(100) | role name: accountadmin/public |
| obj_type | varchar(16) | object type: account/database/table |
| obj_id | bigint unsigned | object id |
| privilege_id | int | privilege id |
| privilege_name | varchar(100) | privilege name: the list of privileges |
| privilege_level | varchar(100) | level of privileges |
| operation_user_id | int unsigned | operation user id |
| granted_time | timestamp | granted time |
| with_grant_option | bool | If permission granting is permitted |
system_metrics database
system_metrics collect the status and statistics of SQL statements, CPU & memory resource usage.
system_metrics tables have more or less same column types, fields in these tables are described as follows:
- collecttime:Collection time
value: the value of the collecting metric
node: the MatrixOne node uuid
role: the MatrixOne node role, can be CN, DN or LOG.
- account: default as “sys”, the account who fires the SQL request.
- type:SQL type, can be
select,insert,update,delete,othertypes.
metric table
| Column | Type | Comment |
|---|---|---|
| metric_name | VARCHAR(128) | metric name, like: sql_statement_total, server_connections, process_cpu_percent, sys_memory_used, .. |
| collecttime | DATETIME | metric data collect time |
| value | DOUBLE | metric value |
| node | VARCHAR(36) | MatrixOne node uuid |
| role | VARCHAR(32) | MatrixOne node role |
| account | VARCHAR(128) | account name, default “sys” |
| type | VARCHAR(32) | SQL type: like insert, select, update … |
The other tables are all views of the metric table:
process_cpu_percenttable: Process CPU busy percentage.process_open_fstable: Number of open file descriptors.process_resident_memory_bytestable: Resident memory size in bytes.server_connectiontable: Server connection numbers.sql_statement_errorstable: Counter of sql statements executed with errors.sql_statement_totaltable: Counter of executed sql statement.sql_transaction_errorstable: Counter of transactional statements executed with errors.sql_transaction_totaltable: Counter of transactional sql statement.sys_cpu_combined_percenttable: System CPU busy percentage, average among all logical cores.sys_cpu_seconds_totaltable: System CPU time spent in seconds, normalized by number of coressys_disk_read_bytestable: System disk read in bytes.sys_disk_write_bytestable: System disk write in bytes.sys_memory_availabletable: System memory available in bytes.sys_memory_usedtable: System memory used in bytes.sys_net_recv_bytestable: System net received in bytes.sys_net_sent_bytestable: System net sent in bytes.
system database
System database stores MatrixOne historical SQL statements, system logs, error information.
statement_info table
It records user and system SQL statement with detailed information.
| Column | Type | Comments |
|---|---|---|
| statement_id | VARCHAR(36) | statement unique id |
| transaction_id | VARCHAR(36) | Transaction unique id |
| session_id | VARCHAR(36) | session unique id |
| account | VARCHAR(1024) | account name |
| user | VARCHAR(1024) | user name |
| host | VARCHAR(1024) | user client ip |
| database | VARCHAR(1024) | what database current session stay in |
| statement | TEXT | sql statement |
| statement_tag | TEXT | note tag in statement(Reserved) |
| statement_fingerprint | TEXT | note tag in statement(Reserved) |
| node_uuid | VARCHAR(36) | node uuid, which node gen this data |
| node_type | VARCHAR(64) | node type in MO, val in [DN, CN, LOG] |
| request_at | DATETIME | request accept datetime |
| response_at | DATETIME | response send datetime |
| duration | BIGINT | exec time, unit: ns |
| status | VARCHAR(32) | sql statement running status, enum: Running, Success, Failed |
| err_code | VARCHAR(1024) | error code |
| error | TEXT | error message |
| exec_plan | JSON | statement execution plan |
| rows_read | BIGINT | rows read total |
| bytes_scan | BIGINT | bytes scan total |
| stats | JSON | global stats info in exec_plan |
| statement_type | VARCHAR(1024) | statement type, val in [Insert, Delete, Update, Drop Table, Drop User, …] |
| query_type | VARCHAR(1024) | query type, val in [DQL, DDL, DML, DCL, TCL] |
| role_id | BIGINT | role id |
| sql_source_type | TEXT | Type of SQL source internally generated by MatrixOne |
rawlog table
It records very detailed system logs.
| Column | Type | Comments |
|---|---|---|
| raw_item | VARCHAR(1024) | raw log item |
| node_uuid | VARCHAR(36) | node uuid, which node gen this data. |
| node_type | VARCHAR(64) | node type in MO, val in [DN, CN, LOG] |
| span_id | VARCHAR(16) | span unique id |
| statement_id | VARCHAR(36) | statement unique id |
| logger_name | VARCHAR(1024) | logger name |
| timestamp | DATETIME | timestamp of action |
| level | VARCHAR(1024) | log level, enum: debug, info, warn, error, panic, fatal |
| caller | VARCHAR(1024) | where it log, like: package/file.go:123 |
| message | TEXT | log message |
| extra | JSON | log dynamic fields |
| err_code | VARCHAR(1024) | error log |
| error | TEXT | error message |
| stack | VARCHAR(4096) | |
| span_name | VARCHAR(1024) | span name, for example: step name of execution plan, function name in code, … |
| parent_span_id | VARCHAR(16) | parent span unique id |
| start_time | DATETIME | |
| end_time | DATETIME | |
| duration | BIGINT | exec time, unit: ns |
| resource | JSON | static resource information |
The other 3 tables(log_info, span_info and error_info) are views of statement_info and rawlog table.
information_schema database
Information Schema provides an ANSI-standard way of viewing system metadata. MatrixOne also provides a number of custom information_schema tables, in addition to the tables included for MySQL compatibility.
Many INFORMATION_SCHEMA tables have a corresponding SHOW command. The benefit of querying INFORMATION_SCHEMA is that it is possible to join between tables.
Tables for MySQL compatibility
| Table Name | Description |
|---|---|
| CHARACTER_SETS | Provides a list of character sets the server supports. |
| COLUMNS | Provides a list of columns for all tables. |
| ENGINES | Provides a list of supported storage engines. |
| KEY_COLUMN_USAGE | Describes the key constraints of the columns, such as the primary key constraint. |
| PROCESSLIST | Provides similar information to the command SHOW PROCESSLIST. |
| SCHEMATA | Provides similar information to SHOW DATABASES. |
| TABLES | Provides a list of tables that the current user has visibility of. Similar to SHOW TABLES. |
| TRIGGERS | Provides similar information to SHOW TRIGGERS. |
| USER_PRIVILEGES | Summarizes the privileges associated with the current user. |
CHARACTER_SETS table
The description of columns in the CHARACTER_SETS table is as follows:
CHARACTER_SET_NAME: The name of the character set.DEFAULT_COLLATE_NAMEThe default collation name of the character set.DESCRIPTIONThe description of the character set.MAXLENThe maximum length required to store a character in this character set.
COLUMNS table
The description of columns in the COLUMNS table is as follows:
TABLE_CATALOG: The name of the catalog to which the table with the column belongs. The value is alwaysdef.TABLE_SCHEMA: The name of the schema in which the table with the column is located.TABLE_NAME: The name of the table with the column.COLUMN_NAME: The name of the column.ORDINAL_POSITION: The position of the column in the table.COLUMN_DEFAULT: The default value of the column. If the explicit default value isNULL, or if the column definition does not include thedefaultclause, this value isNULL.IS_NULLABLE: Whether the column is nullable. If the column can store null values, this value isYES; otherwise, it isNO.DATA_TYPE: The type of data 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: The numeric precision of a number-type column.NUMERIC_SCALE: The numeric scale of a number-type column.DATETIME_PRECISION: For time-type columns, the fractional seconds precision.CHARACTER_SET_NAME: The name of the character set of a string column.COLLATION_NAME: The name of the collation of a string column.COLUMN_TYPE: The column type.COLUMN_KEY: Whether this column is indexed. This field might 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 the primary key or one of multiple primary keys.UNI: This column is the first column of the unique index.MUL: The column is the first column of a non-unique index, in which a given value is allowed to occur for multiple times.EXTRA: Any additional information of the given column.PRIVILEGES: The privilege that the current user has on this column.COLUMN_COMMENT: Comments contained in the column definition.GENERATION_EXPRESSION: For generated columns, this value displays the expression used to calculate the column value. For non-generated columns, the value is empty.SRS_ID: This value applies to spatial columns. It contains the columnSRIDvalue that indicates the spatial reference system for values stored in the column.
ENGINES table
The description of columns in the ENGINES table is as follows:
ENGINES: The name of the storage engine.SUPPORT: The level of support that the server has on the storage engine.COMMENT: The brief 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 supportssavepoints.
PROCESSLIST table
Fields in the PROCESSLIST table are described as follows:
- ID: The ID of the user connection.
- USER: The name of the user who is executing
PROCESS. - HOST: The address that the user is connecting to.
- DB: The name of the currently connected default database.
- COMMAND: The command type that
PROCESSis executing. - TIME: The current execution duration of
PROCESS, in seconds. - STATE: The current connection state.
- INFO: The requested statement that is being processed.
SCHEMATA table
The SCHEMATA table provides information about databases. The table data is equivalent to the result of the SHOW DATABASES statement. Fields in the SCHEMATA table are described as follows:
CATALOG_NAME: The catalog to which the database belongs.SCHEMA_NAME: The database name.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 alwaysNULL.DEFAULT_TABLE_ENCRYPTION: defines the default encryption setting for databases and general tablespaces.
TABLES table
The description of columns in the TABLES table is as follows:
TABLE_CATALOG: The name of the catalog which the table belongs to. The value is alwaysdef.TABLE_SCHEMA: The name of the schema which the table belongs to.TABLE_NAME: The name of the table.TABLE_TYPE: The type of the table.ENGINE: The type of the storage engine.VERSION: Version. The value is10by default.ROW_FORMAT: The row format. The value is currentlyCompact.TABLE_ROWS: The number of rows in the table in statistics.AVG_ROW_LENGTH: The average row length of the table.AVG_ROW_LENGTH=DATA_LENGTH/TABLE_ROWS.DATA_LENGTH: Data length.DATA_LENGTH=TABLE_ROWS* the sum of storage lengths of the columns in the tuple. The replicas of TiKV are not taken into account.MAX_DATA_LENGTH: The maximum data length. The value is currently0, which means the data length has no upper limit.INDEX_LENGTH: The index length.INDEX_LENGTH=TABLE_ROWS* the sum of lengths of the columns in the index tuple.DATA_FREE: Data fragment. The value is currently0.AUTO_INCREMENT: The current step of the auto- increment primary key.CREATE_TIME: The time at which the table is created.UPDATE_TIME: The time at which the table is updated.CHECK_TIME: The time at which the table is checked.TABLE_COLLATION: The collation of strings in the table.CHECKSUM: Checksum.CREATE_OPTIONS: Creates options.TABLE_COMMENT: The comments and notes of the table.
USER_PRIVILEGES table
The USER_PRIVILEGES table provides information about global privileges.
Fields in the USER_PRIVILEGES table are described as follows:
GRANTEE: The name of the granted user, which is in the format of'user_name'@'host_name'.TABLE_CATALOG: The name of the catalog to which the table belongs. This value is alwaysdef.PRIVILEGE_TYPE: The privilege type to be granted. Only one privilege type is shown in each row.IS_GRANTABLE: If you have theGRANT OPTIONprivilege, the value isYES; otherwise, the value isNO.
mysql database
Grant system tables
These system tables contain grant 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: stored procedure and stored function privileges.
