System Catalogs and System Views

Updated on 2024-12-06 GMT+08:00

View PDF

**Table 1** Differences between M-compatible databases and GaussDB in terms of system catalogs or views
No.	System Catalog or System View	Column	Difference
1	information_schema.columns	generation_expression	The output of this column varies due to different string concatenation logics of expressions in M-compatible mode and MySQL.
2	information_schema.columns	data_type	The output result of this column in M-compatible mode, having not been modified due to the data type format_type involved, is different from that in MySQL.
3	information_schema.columns	column_type	The output result of this column in M-compatible mode, having not been modified due to the data type format_type involved, is different from that in MySQL.
4	information_schema.tables	engine	In M-compatible mode: ENGINE is aligned with data of information_schema.engines. In some system catalogs, ENGINE is left empty. If the default table is an ASTORE table and STORAGE_TYPE is not specified, ENGINE is empty.
5	information_schema.tables	version	This column is not supported in M-compatible mode.
6	information_schema.tables	row_format	This column is not supported in M-compatible mode.
7	information_schema.tables	avg_row_length	In M-compatible mode, the result of dividing the size of the data files by the number of all tuples (including live tuples and dead tuples) is used. If there is no tuple in the table, the value is null.
8	information_schema.tables	max_data_length	This column is not supported in M-compatible mode.
9	information_schema.tables	data_free	In M-compatible mode, it indicates the result of (Number of dead tuples/Total number of tuples) x Data file size. If there is no tuple in the table, the value is null.
10	information_schema.tables	check_time	This column is not supported in M-compatible mode.
11	information_schema.tables	create_time	In M-compatible mode, this behavior of column is different from that in MySQL. When a view is created in MySQL, this column is set to null. In M-compatible mode, the actual table creation time is displayed. The value is null if it is a table or view provided by the database.
12	information_schema.tables	update_time	The value is null if it is a table or view provided by the M-compatible database.
13	information_schema.statistics	collation	The value can only be A or D but not NULL in M-compatible mode.
14	information_schema.statistics	packed	This column is not supported in M-compatible mode.
15	information_schema.statistics	sub_part	This column is not supported in M-compatible mode.
16	information_schema.statistics	comment	This column is not supported in M-compatible mode.
17	information_schema.partitions	subpartition_name	In M-compatible mode, if a partition is not a level-2 partition, the value is null.
18	information_schema.partitions	subpartition_ordinal_position	In M-compatible mode, if a partition is not a level-2 partition, the value is null.
19	information_schema.partitions	partition_method	In M-compatible mode: Partitioning policy. If the partition is not a level-1 partition, the value is null. 'r': range partition. 'i': interval partition. 'l': list partition. 'h': hash partition
20	information_schema.partitions	subpartition_method	In M-compatible mode: Level-2 partitioning policy. If a partition is not a level-2 partition, the value is null. 'r': range partition. 'i': interval partition. 'l': list partition. 'h': hash partition
21	information_schema.partitions	partition_description	In M-compatible mode, level-1 partitions and level-2 partitions are distinguished.
22	information_schema.partitions	partition_expression	This column is not supported in M-compatible mode.
23	information_schema.partitions	subpartition_expression	This column is not supported in M-compatible mode.
24	information_schema.partitions	data_length	This column is not supported in M-compatible mode.
25	information_schema.partitions	max_data_length	This column is not supported in M-compatible mode.
26	information_schema.partitions	index_length	This column is not supported in M-compatible mode.
27	information_schema.partitions	data_free	This column is not supported in M-compatible mode.
28	information_schema.partitions	create_time	This column is not supported in M-compatible mode.
29	information_schema.partitions	update_time	This column is not supported in M-compatible mode.
30	information_schema.partitions	check_time	This column is not supported in M-compatible mode.
31	information_schema.partitions	checksum	This column is not supported in M-compatible mode.
32	information_schema.partitions	partition_comment	This column is not supported in M-compatible mode.
33	information_schema.partitions	nodegroup	This column is not supported in M-compatible mode.

NOTE:

The precision range cannot be specified for the command output of the integer type in a view. For example, the bigint(1) type in MySQL corresponds to the bigint type in M-compatible mode, and the bigint(21) unsigned type in MySQL corresponds to the bigint unsigned type in M-compatible mode.
The int type in MySQL corresponds to the integer type in M-compatible mode.
M-compatible mode does not support columns of the set and enum types that are supported in MySQL. This version does not support or display Column_priv column in the m_schema.columns_priv view, Table_priv,Column_priv column in the m_schema.tables_priv view, Routine_type,Proc_priv column in the m_schema.procs_priv view, the type,language,sql_data_access,is_deterministic,security_type,sql_mode column in the m_schema.proc view, or the type column in the m_schema.func view.
table_rows, avg_row_length, data_length, data_free, index_length, and cardinality in information_schema.tables and cardinality in information_schema.statistics are obtained based on statistics. Therefore, run ANALYZE to update statistics before viewing them. (If data is updated in the database, you are advised to delay running ANALYZE.)
The index columns contained in information_schema.statistics must be complete table columns in the created indexes. If the index columns are expressions, they are not in this view.
table_row in information_schema.partitions is obtained based on statistics. Before viewing the value, run ANALYZE to update the statistics. (If data is updated in the database, you are advised to delay running ANALYZE.)
The format of the grantee column supported in MySQL is ' user_name '@' host_name '. In the M-compatible database, it is the name of the user or role to which the permission is granted.
For the host column supported in the M-compatible database, the hostname of the current node is returned.
In MySQL, you need the permission before viewing m_schema.tables_priv, information_schema.user_privileges, information_schema.schema_privileges, information_schema.table_privileges, information_schema.column_privileges, m_schema.columns_priv, m_schema.func, and m_schema.procs_priv. In the M-compatible database, you can view them with the default permission. For example, for table t1, you need the corresponding permission in MySQL so that you can view the corresponding permission information in the permission view. In the M-compatible database, you can view the permission information related to table t1 in the view.
A system view in m_schema is a system catalog in MySQL.
The collations of VIEW_DEFINITION in information_schema.views and ROUTINE_DEFINITION in information_schema.routines are not controlled.
For the view fields of the character type listed in "Schemas" in M-Compatibility Developer Guide, the character set is utf8mb4, and the collation is utf8mb4_bin or utf8mb4_general_ci, and the collation priority is the priority of columns of data types that support collation described in "SQL Reference > Character Set and Collations > Rules for Combining Character Sets and Collations" in M-Compatibility Developer Guide. These features are different from those in MySQL.

Parent topic: SQL

Previous topic: Users and Permissions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot