PolarDB:SQL detail

Background information

Updates (such as creating columns and indexes, adding columns and indexes, or deleting columns and indexes) and lock operations on databases and tables affect your business. The audit logs of relevant operations are critical to O&M personnel, who require usernames, client IP addresses, start time, and end time of such operations.

The audit log feature can be globally enabled and all SQL statements are audited. The audit overheads are very high. Sometimes even additional components are required to store records.

PolarDB for MySQL provides the SQL detail feature to perform detailed audit on the update and lock operations for databases and tables. This feature captures relevant records when a SQL statement starts to be executed and stores audit records in the system table. You can specify the retention period of audit records based on your business requirements. Records that exceed this retention period are automatically evicted. This feature incurs very low audit overheads. The storage capacity of a single audit record is 1 KB. For example, if the database table is updated 1,024 times in a single day and the retention period of audit records is 30 days, only 30 MB of storage space is consumed.

Prerequisites

Parameters

Table format

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;

Examples

1. Set the loose_awr_sqldetail_enabled parameter to ON in the console and execute the following statement 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)

2. Execute the following statement to view the audit records in the sys.hist_sqldetail table:

   select * from sys.hist_sqldetail\G

   Sample result:

   *************************** 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 preceding result shows that, the SQL detail feature only stores the audit records for DDL, LOCK DB, and LOCK TABLE operations, but not for DML operations. In addition, the SQL detail function records all captured information to the system table when the SQL statement starts to be executed, and automatically updates information such as the state after the SQL statement is executed.