grafana connects to shardingsphere, report type error when logging in

[congzhou2603]

Bug Report

Using grafana connects to shardingsphere, openGauss report :column "auth_token_seen" is of type boolean but expression is of type character varying.

Which version of ShardingSphere did you use?

shardingsphere-5.2.1， commit id：85541320133e8049027e922557636fe32c58625c

grafana-7.1.3 with opengauss-gdbc

openGauss-3.1.0，create a MySQL compatible database（create database DBCOMPATIBILITY='B';）

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

proxy

Expected behavior

log in successfully.

Actual behavior

Reason analyze (If you can)

The problem is initially positioned as that in the preparestatment of shardingsphere to openGauss, the expected bool field is actually passed to varchar.

the error sql of preparedstatement

 INSERT INTO "user_auth_token" ("user_id","auth_token","prev_auth_token","user_agent","client_ip","auth_token_seen","seen_at","rotated_at","created_at","updated_at") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING "id";

the complate sql

INSERT INTO "user_auth_token" ("user_id","auth_token","prev_auth_token","user_agent","client_ip","auth_token_seen","seen_at","rotated_at","created_at","updated_at") VALUES ('1', 'af6ae41531d013b0034c0f9b52c8c22ccd218ae1a4821f1827adce9717170061', 'af6ae41531d013b0034c0f9b52c8c22ccd218ae1a4821f1827adce9717170061', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36', '192.168.0.28', 'false', '0', '1668518543', '1668518543', '1668518543') RETURNING "id"

Before shardingsphere sends the message to openGauss, the data type of the error column is 1043 which means varchar.

When shardingsphere receives a message, the data type of the error column is POSTGRESQL_TYPE_BIT.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

the detail of table

rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: local
    providerType: Atomikos
    #providerType: Narayana
props:
  max-connections-size-per-query: 1
  executor-size: 16  # Infinite by default.
  sql-show: true
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy.transaction.type: XA
  sql-federation-type: ADVANCED

databaseName: grafana

dataSources:
  ds_0:
    url: jdbc:opengauss://192.168.0.129:56666/grafana?serverTimezone=UTC&useSSL=false&connectTimeout=10&batchMode=on&loggerLevel=OFF
    username: grafana
    password: Huawei@123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
  - !SHARDING
    defaultDatabaseStrategy:
      standard:
        shardingAlgorithmName: database_default_inline
        shardingColumn: id
    #      none: null
    defaultTableStrategy:
      none: null
    shardingAlgorithms:
      database_default_inline:
        props:
          algorithm-expression: ds_0
        type: INLINE
      ds_t1_alg:
        props:
          algorithm-expression: ds_${ds_id % 1}
        type: INLINE
      ts_t1_alg:
        props:
          algorithm-expression: ts_${ts_id % 1}
        type: INLINE
    tables:
      t1:
        actualDataNodes: ds_${0..0}.ts_${0..0}
        databaseStrategy:
          standard:
            shardingAlgorithmName: ds_t1_alg
            shardingColumn: ds_id
        tableStrategy:
          standard:
            shardingAlgorithmName: ts_t1_alg
            shardingColumn: ts_id

[TeslaCN]

There is no way to send POSTGRESQL_TYPE_BIT by JDBC driver for now. This issue could not reproduce accurately by JDBC. But we still need to consider handling BIT type.