All Products
Search

This Product

    PolarDB:SQL Detail

    Document Center

    PolarDB:SQL Detail

    Last Updated:Mar 30, 2026

    SQL detail audits DDL and lock operations on PolarDB for MySQL databases and tables. It captures execution context for each statement and automatically removes records older than the configured retention period. Unlike the full audit log feature — which audits every SQL statement at significant overhead — SQL detail focuses exclusively on schema-change and lock events, making it suitable for O&M (operations and maintenance) teams that need lightweight, targeted audit trails.

    How it works

    When a DDL or lock statement begins execution, SQL detail writes a record to the sys.hist_sqldetail system table. After the statement finishes, PolarDB updates the record with the final state and performance metrics. Records older than the configured retention period are evicted automatically.

    What SQL detail captures:

    • DDL operations: schema changes such as creating tables, adding columns, and modifying indexes

    • Lock operations: LOCK TABLE and LOCK DB statements

    What SQL detail does not capture:

    DML statements (INSERT, UPDATE, DELETE, SELECT) are intentionally excluded. SQL detail is designed for low-overhead schema-change auditing, not general query logging. To audit DML statements, enable the full audit log feature.

    Storage

    Each audit record consumes 1 KB of storage. For example, 1,024 DDL or lock operations per day with a 30-day retention period consumes approximately 30 MB.

    Prerequisites

    Before you begin, confirm that your cluster meets one of the following version requirements:

    • PolarDB for MySQL 8.0.1, revision version 8.0.1.1.31 or later

    • PolarDB for MySQL 8.0.2, revision version 8.0.2.2.12 or later

    To check your cluster's revision version, see Query the engine version.

    Enable SQL detail

    Configure the following parameters in the console. For instructions, see Specify cluster and node parameters.

    Parameter Level Default Valid values Description
    loose_awr_sqldetail_enabled Global OFF ON, OFF Enables or disables SQL detail
    loose_awr_sqldetail_switch Global ddl: ON; lock_db_table: ON ddl: ON/OFF; lock_db_table: ON/OFF Controls which operation types are audited. ddl covers DDL statements; lock_db_table covers LOCK TABLE and LOCK DB statements
    loose_awr_sqldetail_retention Global 2592000 0–18446744073709551615 Retention period for audit records, in seconds. Records older than this value are evicted automatically

    sys.hist_sqldetail table

    PolarDB for MySQL automatically creates the sys.hist_sqldetail system table at startup. No manual creation is needed.

    CREATE TABLE `hist_sqldetail` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `State` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `Thread_id` bigint(20) unsigned DEFAULT NULL,
  `Host` varchar(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `User` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `Client_ip` varchar(60) COLLATE utf8mb4_bin DEFAULT NULL,
  `Db` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `Sql_text` mediumtext COLLATE utf8mb4_bin NOT NULL,
  `Server_command` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `Sql_command` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `Start_time` timestamp(6) NULL DEFAULT NULL,
  `Exec_time` bigint(20) DEFAULT NULL,
  `Wait_time` bigint(20) DEFAULT NULL,
  `Error_code` int(11) DEFAULT NULL,
  `Rows_sent` bigint(20) DEFAULT NULL,
  `Rows_examined` bigint(20) DEFAULT NULL,
  `Rows_affected` bigint(20) DEFAULT NULL,
  `Logical_read` bigint(20) DEFAULT NULL,
  `Phy_sync_read` bigint(20) DEFAULT NULL,
  `Phy_async_read` bigint(20) DEFAULT NULL,
  `Process_info` text COLLATE utf8mb4_bin,
  `Extra` text COLLATE utf8mb4_bin,
  `Create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`Id`),
  KEY `i_start_time` (`Start_time`),
  KEY `i_update_time` (`Update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

    Column reference

    Column Description
    Id Auto-increment record ID
    State State of the operation when the record was written
    Thread_id ID of the thread that executed the statement
    Host Host associated with the executing user
    User Username used to execute the statement
    Client_ip Client IP address
    Db Database on which the statement was executed
    Sql_text Full text of the SQL statement
    Server_command Server command type (for example, Query)
    Sql_command Statement type (for example, create_table, alter_table, lock_tables)
    Start_time Timestamp when execution began (microsecond precision)
    Exec_time Execution duration, in microseconds. Use this to identify unusually slow DDL operations
    Wait_time Time the statement waited before execution began, in microseconds. High values may indicate lock contention
    Error_code Error code. A non-zero value means the statement failed; use this to trace failed schema changes
    Rows_sent Number of rows returned
    Rows_examined Number of rows scanned
    Rows_affected Number of rows affected
    Logical_read Number of logical reads
    Phy_sync_read Number of physical synchronous reads
    Phy_async_read Number of physical asynchronous reads
    Process_info Extended processing information
    Extra Additional extended information
    Create_time Timestamp when the record was created
    Update_time Timestamp when the record was last updated

    Example

    This example shows how SQL detail captures DDL and lock operations while ignoring DML.

    Step 1: Set loose_awr_sqldetail_enabled to ON in the console, then run the following statements:

    create table t(c1 int);
-- Query OK, 0 rows affected (0.02 sec)

create table t(c1 int);
-- ERROR 1050 (42S01): Table 't' already exists

alter table t add column c2 int;
-- Query OK, 0 rows affected (0.02 sec)
-- Records: 0  Duplicates: 0  Warnings: 0

lock tables t read;
-- Query OK, 0 rows affected (0.00 sec)

unlock tables;
-- Query OK, 0 rows affected (0.00 sec)

insert into t values(1, 2);
-- Query OK, 1 row affected (0.00 sec)

    Step 2: Query the audit records:

    select * from sys.hist_sqldetail\G

    Expected output:

    *************************** 1. row ***************************
            Id: 1
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: create table t(c1 int)
Server_command: Query
   Sql_command: create_table
    Start_time: 2023-01-13 16:18:21.840435
     Exec_time: 17390
     Wait_time: 318
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 420
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:22.391407
   Update_time: 2023-01-13 16:18:22.391407
*************************** 2. row ***************************
            Id: 2
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: create table t(c1 int)
Server_command: Query
   Sql_command: create_table
    Start_time: 2023-01-13 16:18:22.416321
     Exec_time: 822
     Wait_time: 229
    Error_code: 1050
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 55
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:23.393071
   Update_time: 2023-01-13 16:18:23.393071
*************************** 3. row ***************************
            Id: 3
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: alter table t add column c2 int
Server_command: Query
   Sql_command: alter_table
    Start_time: 2023-01-13 16:18:34.123947
     Exec_time: 16420
     Wait_time: 245
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 778
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:34.394067
   Update_time: 2023-01-13 16:18:34.394067
*************************** 4. row ***************************
            Id: 4
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: lock tables t read
Server_command: Query
   Sql_command: lock_tables
    Start_time: 2023-01-13 16:19:49.891559
     Exec_time: 145
     Wait_time: 129
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 0
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:19:50.399585
   Update_time: 2023-01-13 16:19:50.399585
*************************** 5. row ***************************
            Id: 5
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: unlock tables
Server_command: Query
   Sql_command: unlock_tables
    Start_time: 2023-01-13 16:19:56.924648
     Exec_time: 98
     Wait_time: 0
    Error_code: 0
     Rows_sent: 0
 Rows_examined: 0
 Rows_affected: 0
  Logical_read: 0
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:19:57.400294
   Update_time: 2023-01-13 16:19:57.400294

    The output contains five records — one for each DDL and lock statement. The insert into t values(1, 2) statement is not recorded because SQL detail does not capture DML operations.

    Row 2 shows Error_code: 1050 for the duplicate CREATE TABLE attempt. SQL detail records both successful and failed statements, giving O&M personnel a complete history of schema-change attempts, including those that failed.