What SQL detail is and examples-PolarDB(PolarDB)-阿里云帮助中心

Background

When you use a database, schema changes on databases and tables — such as creating, adding, or dropping columns and indexes — or lock operations on databases and tables can affect normal business operations. Audit logs for these operations are critical to database O&M personnel, who need to know details such as the user account, client IP address, start time, and completion time of each operation.

Traditionally, audit logs use a global switch that audits all SQL statements. Although the audit records are comprehensive, this approach is relatively costly and may require additional components to store the information.

PolarDB for MySQL provides SQL detail to perform detailed auditing of schema changes and lock operations on databases and tables. This feature captures audit records as soon as the relevant statements start to execute, and stores the records in a system table of the database. You can configure the retention period of audit records based on your business needs. Audit records older than the retention period are automatically evicted. The audit cost of this feature is extremely low. For example, if each audit record consumes 1 KB of storage, 1,024 schema changes occur per day, and the retention period is 30 days, only 30 MB of storage is required.

Prerequisites

Your PolarDB cluster must meet one of the following version requirements:

PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.31 or later.
PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.12 or later.

You can Query the engine version to confirm the cluster version.

Parameters

You can configure the following parameters in the console to enable SQL detail and set the retention period of audit records. For the procedure of setting parameters, see Set cluster and node parameters.

Parameter	Level	Description
loose_awr_sqldetail_enabled	Global	Enables or disables SQL detail. Valid values: OFF (default): disables SQL detail. ON: enables SQL detail.
loose_awr_sqldetail_switch	Global	The operation types that SQL detail records. Sub-switches: ddl: records DDL operations. Valid values: ON (default): records the details of DDL operations. OFF: does not record the details of DDL operations. lock_db_table: records LOCK DB and LOCK TABLE operations. Valid values: ON (default): records the details of LOCK DB and LOCK TABLE operations. OFF: does not record the details of LOCK DB and LOCK TABLE operations.
loose_awr_sqldetail_retention	Global	The retention period of audit records. Records older than this period are automatically evicted. Valid values: 0 to 18446744073709551615. Default value: 2592000. Unit: seconds.

Table schema

PolarDB for MySQL provides a built-in system table sys.hist_sqldetail to store audit records. The table is automatically created when the system starts. You do not need to create it manually. The table schema is as follows:

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;

The following table describes the columns in the system table.

Column	Description
Id	The auto-increment ID.
State	The state of the operation when the record was written.
Thread_id	The ID of the thread that executed the SQL statement.
Host	The host from which the SQL statement was executed.
User	The username that executed the SQL statement.
Client_ip	The client IP address from which the SQL statement was executed.
Db	The name of the database on which the SQL statement was executed.
Sql_text	The SQL statement that was executed.
Server_command	The server command used to execute the SQL statement.
Sql_command	The command type of the SQL statement.
Start_time	The time when the SQL statement started to execute.
Exec_time	The execution duration. Unit: microseconds.
Wait_time	The wait time. Unit: microseconds.
Error_code	The error code.
Rows_sent	The number of rows returned.
Rows_examined	The number of rows scanned.
Rows_affected	The number of rows affected.
Logical_read	The number of logical reads.
Phy_sync_read	The number of physical synchronous reads.
Phy_async_read	The number of physical asynchronous reads.
Process_info	An extended field. Processing information.
Extra	An extended field. Other information.
Create_time	The time when the record was written.
Update_time	The time when the record was last updated.

Example

In the console, set the loose_awr_sqldetail_enabled parameter to ON, and then run the following statements on the database.

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)

Run the following statement to view the audit records in the sys.hist_sqldetail table.

select * from sys.hist_sqldetail\G

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 shows that SQL detail records audit information only for DDL, LOCK DB, and LOCK TABLE statements, and does not record audit information for DML statements. In addition, SQL detail writes a record to the system table as soon as the SQL statement starts to execute, and automatically updates fields such as the state in the record after the statement finishes.