All Products
Search

This Product

    Hologres:SQL diagnosis

    Document Center

    Hologres:SQL diagnosis

    Last Updated:Sep 12, 2024

    Hologres provides slow query logs. By default, Hologres logs data manipulation language (DML) statements that take more than 100 ms to execute and all data definition language (DDL) statements. Hologres provides the SQL diagnosis feature based on slow query logs. The SQL diagnosis feature allows you to analyze query trends and details from different dimensions. This helps you understand the usage of instances and optimize the instances to achieve better performance. This topic describes how to use the SQL diagnosis feature.

    Diagnostic items and suggestions

    The following table describes the diagnostic items provided by the SQL diagnosis feature.

    Diagnostic item

    Description

    Total number of queries

    Displays the total number of queries that are performed in a specified time period.

    Successful Query

    Displays the total number of queries that are successfully performed in a specified time period.

    Failed Query

    Displays the total number of failed queries in a specified time period.

    Failed Query details

    Displays the error codes, number of failures for each type of error code, typical queries, and failure details to facilitate the troubleshooting of failed queries.

    You can use the Query Insight feature to view more query details. You can also identify the causes of issues based on the error codes. For more information about the Query Insight feature, see Query Insight. For more information about the error codes, see Error codes in this topic.

    Success and Failure Query Trends

    Displays the trends of the numbers of successful queries and failed queries in a specified time period. This allows you to obtain the overall running status of the queries in the current instance.

    Time consumption percentage trend of Query

    Displays the trend of execution durations of queries in a specified time period.

    By default, only the execution durations of the following statements are collected: SELECT, INSERT, UPDATE, and DELETE.

    DML trend

    Displays the trends of the numbers of times that different types of DML queries are performed in a specified time period.

    By default, the statistics on only the following types of DML statements are collected: SELECT, INSERT, UPDATE, and DELETE.

    DDL trend

    Displays the trends of the numbers of times that different types of DDL queries are performed in a specified time period.

    By default, the statistics on only the following types of DDL statements are collected: CREATE TABLE, DROP TABLE, TRUNCATE TABLE, ALTER TABLE, CALL, CREATE EXTENSION, CREATE FOREIGN TABLE, ALTER FOREIGN TABLE, IMPORT FOREIGN SCHEMA, DROP FOREIGN TABLE, CREATE SCHEMA, CREATE VIEW, DROP VIEW, GRANT, CREATE ROLE, ALTER ROLE, and COMMENT.

    The percentage of query application sources

    Displays the proportion or trend of queries based on the application_name parameter.

    You can analyze the applications that initiate the largest number of queries. To help you better observe abnormal tasks, we recommend that you specify different application_name values for different types of tasks.

    Percentages of queries in the user dimension

    Displays the proportion or trend of queries based on the username parameter.

    You can view the number of queries that are performed by using each account in a visualized manner. To facilitate troubleshooting, we recommend that you use different accounts to run different tasks.

    Query trend of the execution engine

    Displays the trends of the numbers of queries that are performed by using different engines in a specified time period.

    Hologres uses different engines for different queries based on query characteristics. The engines include Hologres Query Engine (HQE), PostgreSQL Query Engine (PQE), SDK, and FixedQE. For more information about the engines, see Architecture. We recommend that you do not perform PQE queries. You can perform HQE queries instead of PQE queries to achieve better performance.

    Usage notes

    • The SQL diagnosis feature does not collect data of system-related SQL statements. The following code shows the filter conditions:

      WHERE
usename != 'system'
AND client_addr != '127.0.0.1' 
AND (application_name IS NULL 
OR application_name NOT IN ('AutoPartition', 'holoweb_system', 'HgGenInQuery'))

    • By default, the hologres.hg_query_log table logs DML statements that take more than 1s to execute and all DDL statements, and the SQL diagnosis feature collects data of all DML and DDL statements that take more than 100 ms to execute. In this case, the amount of data in the hologres.hg_query_log table may be less than the amount of data collected by the SQL diagnosis feature. To resolve this issue, you can change the value of the log_min_duration_statement parameter to 100 ms. For more information, see the "log_min_duration_statement" section in Query and analyze slow query logs.

    • Both the SQL diagnosis feature and slow query logs allow you to query data only in the previous month.

    • Data is updated on the next day. Therefore, you can only query data in the previous month starting from the previous day. You can filter data by time based on your business requirements. By default, the data of the previous day is displayed. You can filter data by time based on your business requirements.

    • The permission policy for SQL diagnosis data is the same as the permission policy for slow query logs. For more information, see the "Grant query permissions to users" section in Query and analyze slow query logs.

    Procedure

    This section describes how to use HoloWeb to view SQL diagnosis data in a visualized manner.

    1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb.

    2. In the top navigation bar, click Diagnostics and Optimization.

    3. In the left-side navigation pane, choose Instance diagnosis > SQL diagnosis.

    4. In the upper part of the SQL diagnosis page, configure the query conditions.

      The following table describes the query condition parameters.

      Parameter

      Required

      Description

      Instance Name

      Yes

      The name of the instance whose slow queries you want to query. By default, the connected instance is selected.

      Time

      Yes

      The time range of slow queries. By default, Yesterday is selected. The longest time range is the previous month.

    5. Click Submission. The query result is displayed.

    Intelligent SQL diagnosis

    The SQL Editor tab in the HoloWeb console displays SQL diagnosis information if a query fails. The Query Insight page also provides the intelligent SQL diagnosis feature, which allows the system to automatically return the cause and solution of a failed query to the SQL Editor tab. This way, you can handle failed queries in a more convenient manner.

    Error codes

    Error code

    Description

    Common error message

    Solution

    HG_ERRCODE_FDW_ERROR

    An error message is reported when metadata of the MaxCompute table is imported to Hologres. In most cases, this issue occurs because the MaxCompute table of the type is not supported by Hologres.

    failed to import foreign schema from odps: Can't find file system factory

    For more information, see HG_ERRCODE_FDW_ERROR.

    ERRCODE_FDW_ERROR

    An error message is reported during a query by using a foreign table.

    • failed to import foreign schema from odps: Authorization Failed: xxx

    • failed to import foreign schema from odps:Table not found -xxx

    For more information, see ERRCODE_FDW_ERROR.

    • ERRCODE_UNIQUE_VIOLATION

    • pk violates

    A uniqueness constraint violation exists. In most cases, this error code is returned when duplicate primary key values exist during data writes.

    duplicate key value violates unique constraint DETAIL: xxx already exists.

    • Handle the data that contains duplicate primary key values.

    • If this error code is returned for the INSERT statement, rewrite the statement based on the insert into xx on conflict syntax to deduplicate primary key values. For more information, see INSERT ON CONFLICT(UPSERT).

    • If this error code is returned for the insert into xx on conflict syntax, the source data contains duplicate primary key values. In this case, troubleshoot the error by following the instructions in INSERT ON CONFLICT(UPSERT).

    • ERRCODE_CHECK_VIOLATION

    • partition constraint

    A check constraint violation exists. In most cases, this error code is returned when you write a data record whose partition key value is different from the partition key value specified in the statement. For example, you write data whose partition key value is 0240229 to the 20240301 partition.

    new row for relation xx violates partition constraint DETAIL: Failing row contains (column1)=(xxxx).

    Make sure that you write data whose partition key value is the same as the partition key value specified in the statement.

    • ERRCODE_NOT_NULL_VIOLATION

    • not-null constraint

    • UsageProblem

    A NOT NULL constraint violation exists. In most cases, this error code is returned when you write NULL data to NOT NULL columns.

    null value in column xxx violates not-null constraint DETAIL: Failing row contains (null).

    Process dirty data.

    ERRCODE_UNDEFINED_TABLE

    The table does not exist. In most cases, this error code is returned when the table is created but the metadata is not updated or the TRUNCATE or DROP operation is performed on the table during the query.

    Dispatch query failed: Table not found

    Use the Query Insight feature to check whether the TRUNCATE or DROP operation is performed on the table during the query. Then, retry the task. For more information, see Query Insight.

    • ERRCODE_INTERNAL_ERROR

    • ERPC_ERROR_CONNECTION_CLOSED

    An unexpected internal error occurs. For example, the instance breaks down or the query is unexpectedly interrupted.

    • Transaction xx is not found or it was expired and cancelled.

    • Query is cancelled

    • ERPC_ERROR_CONNECTION_CLOSED

    None.

    • ERRCODE_QUERY_CANCELED

    • User canceled

    • CANCELLED

    • Query Is Cancelled

    • InternalQueryIsClosed

    The query is canceled. In most cases, this error code is returned when the client times out or the TRUNCATE or DROP operation is performed on the table.

    • ERROR: canceling statement due to statement timeout

    • canceling statement due to user request

    For more information, see Manage queries.

    • ERRCODE_FEATURE_NOT_SUPPORTED

    • Unsupported Feature

    A specific feature is not supported.

    • Dynamic partition selector is not supported

    • ALTER TABLE CHANGE OWNER is not supported in SPM (Simple Permission Mode)

    • Feature not supported: insert into parent table

    For more information, see FAQ about SQL statements.

    ERRCODE_UNDEFINED_OBJECT

    An undefined object is specified in the SQL statement. In most cases, this error code is returned when a column or table group specified in the SQL statement does not exist.

    • column xxx does not exist

    • Table group xxx does not exist.

    • Create the missing object.

    • Check whether the name of the missing object in the SQL statement is valid.

    • ERRCODE_INSUFFICIENT_PRIVILEGE

    • permission denied

    Required permissions are not granted to the account. Authorization is required.

    • ERROR: permission denied for schema xxx

    • ERROR: permission denied for foreign table table_info

    For more information, see Permissions on Hologres instances.

    • ERRCODE_OUT_OF_MEMORY

    • OOM

    An out of memory (OOM) error occurs due to insufficient memory for queries.

    Total memory used by all existing queries exceeded memory limitation

    For more information, see FAQ about OOM.

    • ERRCODE_DATATYPE_MISMATCH

    • Unmatched Data Row Schema Number

    • Dataset Schema Not Match

    Data types mismatch. In most cases, this error code is returned when the data type of a field specified in the SQL statement does not match the actual data type of the field.

    • unmatched data row schema number

    • Datasets has different schema

    Make sure that the data type specified in the SQL statement matches the actual data type of the field.

    • ERRCODE_DIVISION_BY_ZERO

    • division by zero

    The SQL statement specifies division by 0.

    division by zero

    Process dirty data, or configure Grand Unified Configuration (GUC) parameters to allow division by 0. For more information, see the "Functions" section in Migrate data from MySQL to Hologres.

    ERRCODE_STRING_DATA_RIGHT_TRUNCATION

    A string is truncated from the right. In most cases, this error code is returned when the length of a field value of the VARCHAR type exceeds the upper limit that is specified when the table is created.

    value too long for type character varying(xx)

    Create another table and modify the upper limit on the length of fields of the VARCHAR type. You can also set the data type to TEXT.

    • ERRCODE_PROGRAM_LIMIT_EXCEEDED

    • Exceed Odps Scan Limit

    An upper limit specified in Hologres is exceeded. For example, the number of partitions that you want to scan, the number of data rows that you want to read, or the number of bytes that you want to read in a foreign table exceeds the upper limit.

    • number of read rows (xxxxx) exceeds limit (xxxxxxx)

    • number of partitions (xxx) scanned for "xxxx" exceeds the maximum allowed (xxx)

    • scan (xxx GB) for "xxxxx" exceeds the maximum allowed (xxx GB)

    For more information about how to fix the issue that an upper limit is exceeded when you query data by using foreign tables, see FAQ about integration with MaxCompute.

    ERRCODE_SYNTAX_ERROR

    The SQL syntax is invalid.

    syntax error at or near "xxxxx"

    Check the syntax of the SQL statement.

    ERRCODE_UNDEFINED_FUNCTION

    The function syntax is invalid, no extension is created, or the function is not supported.

    • function xxxxx does not exist

    • operator does not exist: xxxxxx

    Use functions based on function syntaxes to prevent issues such as invalid syntax or no extension. For more information, see Functions.

    ERRCODE_E_R_E_READING_SQL_DATA_NOT_PERMITTED

    You are not granted the read permissions on foreign tables.

    check permission for foreign table scan failed: failed to check permission:MaxCompute error,Authorization Failed [4019], You have NO privilege 'odps:Select' on {xxxxxxxxxx}

    For more information, see Permissions on MaxCompute.

    • ERRCODE_DUPLICATE_OBJECT

    • already exist

    Duplicate objects exist. In most cases, this error code is returned when you create duplicate extensions, publications, or roles.

    • publication "xxxxx" already exists

    • extension "xxxxx" already exists

    • role "xxxxxxxx" already exists

    Do not create duplicate objects.

    • ERRCODE_INVALID_TEXT_REPRESENTATION

    • invalid input

    The text expression is invalid. In most cases, this error code is returned when you convert an invalid string into another data type. For example, you convert an empty string ("") into the INT data type.

    invalid input syntax for integer: xxx

    Process dirty data.

    ERRCODE_BAD_COPY_FILE_FORMAT

    The format of files or data on which the COPY operation is performed is invalid. In most cases, this error code is returned when the source data contains a delimiter, such as a space, that is specified in the COPY statement. As a result, the number of columns in the source table is different from the number of columns in the destination table.

    • extra data after last expected column. failed to query next

    • missing data for column "xxx". failed to query next

    Process dirty data.

    ERRCODE_UNDEFINED_COLUMN

    The query statement defines a column that does not exist.

    column xxxxx does not exist

    Check the syntax of the SQL statement.

    ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE

    Data values are beyond the specified range.

    • Data values of a numeric type are beyond the specified range. For example, DECIMAL(4,2) and NUMERIC(4,2) specify that up to 2 digits are allowed in the integer part. The value 100 is beyond the range.

    • Data values of the INT or BIGINT type are beyond the specified range.

    • value "xxxxx" is out of range for type bigint

    • numeric field overflow

    • bigint out of range

    • integer out of range

    Check for dirty data or type definition errors and modify data types of columns.

    ERRCODE_DATETIME_FIELD_OVERFLOW

    A value overflow occurs in columns of the TIMESTAMP, TIMESTAMPZ, DATE, TIME, or TIMETZ data type. For example, the value "877411-01-01 00:00:00+08" is written to a column of the TIMESTAMPTZ data type.

    • date/time field value out of range: "xxxxxx"

    • date out of range: "xxxxxx"

    Process dirty data.

    ERRCODE_INVALID_PARAMETER_VALUE

    The parameter values are invalid. You can troubleshoot the error based on the error message. For example, the error message column "col" with type "float4" cannot be set as "bitmap_columns" indicates that bitmap indexes cannot be configured for columns of the FLOAT4 data type.

    • mismatched properties: table orientation is "column" but storage format is "sst"

    • resharding insert select table data failed : Dispatch query failed: internal error: Failed to get available shards for query

    • InsertOverwrite insert select table data failed : column a.unsign_type does not exist

    Check the syntax of the SQL statement.

    ERRCODE_INVALID_DATETIME_FORMAT

    The date format is invalid. For example, the format of the date in the SELECT TO_DATE('aa', 'YYYY-MM-DD'); statement is invalid.

    • invalid input syntax for type timestamp: ""

    • invalid input syntax for type date: ""

    • invalid value "" for "yyyy",Value must be an integer.

    Process dirty data.

    ERRCODE_CHARACTER_NOT_IN_REPERTOIRE

    Some characters are not supported by the encoding format. In most cases, this error code is returned when a character is not supported by UTF-8.

    invalid byte sequence for encoding "UTF8": 0xe9 0x80

    Process dirty data.

    ERRCODE_DUPLICATE_TABLE

    Duplicate tables exist. In most cases, this error code is returned when you create a table that has the same name as an existing table.

    relation "xxxx" already exists

    Do not create a table that has the same name as an existing table.

    ERRCODE_UNTRANSLATABLE_CHARACTER

    Some characters cannot be converted into a specified format.

    character with byte sequence 0xe4 0x9e 0xab in encoding "UTF8" has no equivalent in encoding "GBK"

    Note

    Characters encoded in UTF-8 have no equivalent characters in GBK.

    Process dirty data.

    ERRCODE_GROUPING_ERROR

    The GROUP BY clause is invalid.

    column "xxx" must appear in the GROUP BY clause or be used in an aggregate function

    Check the syntax of the SQL statement. Make sure that the fields of aggregate functions are included in the GROUP BY clause.

    • ERRCODE_INVALID_TRANSACTION_STATE

    • Usage Problem

    The transaction status is invalid. This error code is returned when transaction-related operations are invalid. For example, the CALL SET_TABLE_PROPERTY statement that is used to create a distribution key for a table and the statement that is used to create the table are not included in the same transaction.

    SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction

    Execute the CREATE TABLE and CALL SET_TABLE_PROPERTY statements in the same transaction by using the begin; and commit; keywords.

    ERRCODE_AMBIGUOUS_COLUMN

    A column name in the statement is ambiguous. In most cases, this error code is returned when a column name in the SQL statement may indicate columns in different tables. For example, in the select id from t1 inner join t2 on t1.id = t2.id statement, whether id indicates the id column in table t1 or table t2 is not clarified.

    column reference "xxx" is ambiguous

    Check the syntax of the SQL statement.

    ERRCODE_DUPLICATE_COLUMN

    Duplicate columns exist. In most cases, this error code is returned when a field is declared multiple times during table creation.

    column "xxx" specified more than once

    Check the syntax of the SQL statement.

    ERRCODE_AMBIGUOUS_FUNCTION

    The function is invalid. A function supports input parameters of various data types. In most cases, this error code is returned when the data type of input parameters is not supported.

    For example, the to_char function supports to_char(timestamp/timestamptz, text),

    to_char(double precision, text), and to_char(int, text). '2024-02-22' is not supported by the function. If you use to_char('2024-02-22', 'YYYY-MM-DD'), the error code is returned. In this case, you can use to_char('2024-02-22'::timestamptz, 'YYYY-MM-DD') to explicitly convert the data type.

    Check the syntax of the SQL statement.

    ERRCODE_INVALID_COLUMN_DEFINITION

    The column definition is invalid. In most cases, this error code is returned when no precision is specified for columns of the NUMERIC or DECIMAL data type in Hologres tables.

    invalid definition of a numeric type

    Process dirty data.

    ERRCODE_INVALID_CATALOG_NAME

    ERRCODE_UNDEFINED_DATABASE

    The specified database does not exist.

    None.

    Check whether the database exists.

    ERRCODE_CANNOT_COERCE

    Conversion between two data types is not supported.

    cannot cast type date to integer

    Check the syntax of the SQL statement.

    ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST

    In most cases, this error code is returned when you try to delete an object on which one or more other existing objects depend. For example, when you delete a schema, the tables in the schema still exist.

    None.

    Process the dependencies. For more information, see Delete an account.

    ERRCODE_UNDEFINED_SCHEMA or

    ERRCODE_INVALID_SCHEMA_NAME

    The specified schema does not exist.

    schema "xxxx" does not exist

    Check whether the schema exists. If the schema does not exist, create a schema.

    ERRCODE_DUPLICATE_DATABASE

    Duplicate databases exist. This error code is returned when you create a duplicate database.

    None.

    Do not create duplicate databases.

    AutoAnalyze-Failed

    The auto-analyze feature fails.

    • query row count from analyze table

    • query from analyze table

    In most cases, this error code is caused by backend issues. Submit a ticket for troubleshooting.

    Import Foreign Table Not Found

    The foreign table cannot be found.

    • failed to get foregin table split:Table not found

    • Failed to get odps table:Not enable acid table

    • failed to get foregin table split:% not found

    Check whether the foreign table exists.

    Cannot Acquire Lock In Time

    In most cases, this error code is returned due to failures in acquiring locks. For example, if you perform high-concurrency queries and drop operations on the same table, a deadlock occurs on the backend node. As a result, all operations on the table are stuck and the error code is returned.

    internal error: Cannot acquire lock in time, current owners

    For more information, see Locks and lock troubleshooting.

    • OTHER

    • QueryNextFTEFailed

    • QueryNextPQEFailed

    • ForeignSplitOrSchemaConnectionClosed

    • ConnectionRefused

    • ERPC_ERROR_TIMEOUT

    • ERPC_ERROR_CONNECTION_CLOSED

    Unexpected errors occur.

    • kConnectError: channel is empty

    • ERPC_ERROR_CONNECTION_CLOSED

    • internal error: Connect timeout, err: std_exception: Connection refused

    Submit a ticket for troubleshooting.