Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的

问题描述

Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的。

以下例子以 OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本和 OceanBase Connector/J V2.4.1 版本为例进行说明。

1. 创建测试方法 testPs()。

   @Test
    public void testPs(){
        Connection conn;
        Connection conn1;
        Statement stmt;
   
        try {
            // 打开链接
            conn = DriverManager.getConnection("jdbc:oceanbase://xx.xx.xx.xx:xxxx/test?useServerPrepStmts=true", "test@xxxx", "test");
            conn1 = DriverManager.getConnection("jdbc:oceanbase://xx.xx.xx.xx:xxxx/oceanbase?useServerPrepStmts=true", "root@sys", "");
            stmt = conn.createStatement();
            String r = "";
            RowId i = null;
   
            // 创建测试表
            System.out.println("创建测试表");
            stmt.executeUpdate("set autocommit=1;");
            stmt.executeUpdate("create table test1(c1 int, c2 int)partition by hash(c1) partitions 3;");
            conn1.createStatement().executeUpdate("alter proxyconfig set enable_cached_server='false';");
            stmt.executeUpdate("insert into test1 values(911);");
            stmt.executeUpdate("insert into test1 values(918);");
            ResultSet rs = stmt.executeQuery("select * from test1 where 0;");
            while(rs.next()){
                r = rs.getString(1);
                i = rs.getRowId(1);
            }
            PreparedStatement ps = conn.prepareStatement("select * from test1 where a = :1;");
            ps.setString(1, r);
            ResultSet rs1 = ps.executeQuery();
            while(rs1.next()){
                Assert.assertEquals(1, rs1.getInt(1));
            }
            ResultSet rs2 = conn1.createStatement().executeQuery("select query_sql,plan_type,PARAMS_VALUE from oceanbase.GV$SQL_AUDIT where query_sql like 'select * from test1 where%' order by request_time desc limit 1;");
            while(rs2.next()){
           // Assert.assertEquals(1, rs2.getInt(2));
                System.out.println(rs2.getString(1)+" "+rs2.getInt(2)+" "+rs2.getString(3));
            }
            ps.setRowId(1, i);
            ResultSet rs3 = ps.executeQuery();
            while(rs3.next()){
                Assert.assertEquals(1, rs3.getInt(1));
            }
            ResultSet rs4 = conn1.createStatement().executeQuery("select query_sql,plan_type,PARAMS_VALUE from oceanbase.GV$SQL_AUDIT where query_sql like 'select * from test1 where%' order by request_time desc limit 1;");
            while(rs4.next()){
            // Assert.assertEquals(1, rs4.getInt(2));
                System.out.println(rs4.getString(1)+" "+rs4.getInt(2)+" "+rs4.getString(3));
            }
   
            stmt.executeUpdate("drop table test1;");
            stmt.close();
            conn.close();
            conn1.close();
        } catch (Exception se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        }  // 处理 Class.forName 错误
        System.out.println("Goodbye!");
    }
   

2. 每次 SQL 语句请求的 PS 协议绑定变量的值。

   obclient > select tenant_id,query_sql , trace_id , REQUEST_TYPE,ps_stmt_id,PARAMS_VALUE from oceanbase.gv$sql_audit where query_sql like 'select * from test1 where%';
   +-----------+----------------------------------+-----------------------------------+--------------+------------+--------------+
   | tenant_id | query_sql                        | trace_id                          | REQUEST_TYPE | ps_stmt_id | PARAMS_VALUE |
   +-----------+----------------------------------+-----------------------------------+--------------+------------+--------------+
   |      1002 | select * from test1 where 0      | YB420BA65670-00060057FDE01DFC-0-0 |            2 |          0 |              |
   |         1 | select * from test1 where a = ?; | YB420BA6566F-0006004751CE7B96-0-0 |            5 |          1 |              |
   |         1 | select * from test1 where a = ?; | YB420BA6566F-0006004751CE7B97-0-0 |            6 |          1 | 1            |
   |      1002 | select * from test1 where 0      | YB420BA6566F-000600474E4BF895-0-0 |            2 |          0 |              |
   |      1002 | select * from test1 where a = ?; | YB420BA6566F-000600474E4BF8A3-0-0 |            5 |          1 |              |
   |      1002 | select * from test1 where a = ?; | YB420BA6566F-000600474E4BF8A4-0-0 |            6 |          1 | 1            |
   |      1003 | select * from test1 where a = ?; | YB420BA6566F-00060047500BF7CD-0-0 |            5 |          1 |              |
   |      1003 | select * from test1 where a = ?; | YB420BA6566F-00060047500BF7CE-0-0 |            1 |          1 |              |
   +-----------+----------------------------------+-----------------------------------+--------------+------------+--------------+
   8 rows in set (0.27 sec)
   

3. 集群中所有的租户以及租户的分布信息。

   obclient > select * from oceanbase.__all_tenant;
   +----------------------------+----------------------------+-----------+-------------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+
   | gmt_create                 | gmt_modified               | tenant_id | tenant_name       | replica_num | zone_list         | primary_zone      | locked | collation_type | info          | read_only | rewrite_merge_version | locality                                    | logonly_replica_num | previous_locality | storage_format_version | storage_format_work_version | default_tablegroup_id | compatibility_mode | drop_tenant_time | status               | in_recyclebin |
   +----------------------------+----------------------------+-----------+-------------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+
   | xxxx-xx-xx xx:xx:xx.xxxxx  | xxxx-xx-xx xx:xx:xx.xxxxx  |         1 | sys               |          -1 | zone1;zone2;zone3 | zone1;zone2,zone3 |      0 |              0 | system tenant |         0 |                     0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |                   0 |                   |                      0 |                           0 |                    -1 |                  0 |               -1 | TENANT_STATUS_NORMAL |             0 |
   | xxxx-xx-xx xx:xx:xx.xxxxx  | xxxx-xx-xx xx:xx:xx.xxxxx  |      1001 | chongzheng_oracle |          -1 | zone1;zone2;zone3 | zone1,zone2,zone3 |      0 |              0 |               |         0 |                     0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |                   0 |                   |                      0 |                           0 |                    -1 |                  1 |               -1 | TENANT_STATUS_NORMAL |             0 |
   | xxxx-xx-xx xx:xx:xx.xxxxx  | xxxx-xx-xx xx:xx:xx.xxxxx  |      1002 | L3                |          -1 | zone1;zone2;zone3 | RANDOM            |      0 |              0 |               |         0 |                     0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |                   0 |                   |                      0 |                           0 |                    -1 |                  0 |               -1 | TENANT_STATUS_NORMAL |             0 |
   | xxxx-xx-xx xx:xx:xx.xxxxx  | xxxx-xx-xx xx:xx:xx.xxxxx  |      1003 | L3_oracle         |          -1 | zone1;zone2;zone3 | zone1;zone2,zone3 |      0 |              0 |               |         0 |                     0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |                   0 |                   |                      0 |                           0 |                    -1 |                  1 |               -1 | TENANT_STATUS_NORMAL |             0 |
   +----------------------------+----------------------------+-----------+-------------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+
   4 rows in set (0.00 sec)
   

适用版本

OceanBase 数据库 V3.x 版本。

问题原因

设置了 is_execute_ps_cursor 参数导致走 PL 路径，而 PL 路径走的是 inner sql 执行。由于记录进 SQL Audit 时记录的类型是 inner sql，所以导致了 request type 错误。

解决方法

升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.4 BP4 (oceanbase-3.2.4.4-104000052023062021) 及之后版本。