<p dir="auto"><strong>The shardingsphere version is 5.0.0.beta,database is mysql.</strong></p>
<p dir="auto"><strong>shardingsphere does not report errors：</strong><br>
2021-11-03 10:45:38.836|biz-e3424a198605469bb31139f9767fe21b||SQLLogger.java|74|INFO||||||[XNIO-1 task-1] Logic SQL: insert into <code class="notranslate">t_pipeline_info</code> (<code class="notranslate">PIPELINE_ID</code>, <code class="notranslate">PROJECT_ID</code>, <code class="notranslate">PIPELINE_NAME</code>, <code class="notranslate">PIPELINE_DESC</code>) values (?, ?, ?, ?) on duplicate key update <code class="notranslate">t_pipeline_info</code>.<code class="notranslate">PIPELINE_DESC</code> = ?<br>
2021-11-03 10:45:38.837|biz-e3424a198605469bb31139f9767fe21b||SQLLogger.java|74|INFO||||||[XNIO-1 task-1] SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.OnDuplicateKeyColumnsSegment@7b11b60e])<br>
2021-11-03 10:45:38.838|biz-e3424a198605469bb31139f9767fe21b||SQLLogger.java|74|INFO||||||[XNIO-1 task-1] Actual SQL: ds_0 ::: insert into <code class="notranslate">t_pipeline_info</code> (<code class="notranslate">PIPELINE_ID</code>, <code class="notranslate">PROJECT_ID</code>, <code class="notranslate">PIPELINE_NAME</code>, <code class="notranslate">PIPELINE_DESC</code>) values (?, ?, ?, ?) on duplicate key update <code class="notranslate">t_pipeline_info</code>.<code class="notranslate">PIPELINE_DESC</code> = ? ::: [p-17bd8ac584be4d228c9ad2d15e9d169117bd8ac584be4d228c9ad2d15e9d169117bd8ac584be4d228c9ad2d15e9d1691, devops-test, bnmvvv1234, dfdf89089808, dfdf89089808]</p>
<p dir="auto"><strong>Native SQL directly inserts an error</strong><br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/5268164/140003658-61fa2dc9-9484-46b7-a9f0-d060cfff0b39.png"><img src="https://user-images.githubusercontent.com/5268164/140003658-61fa2dc9-9484-46b7-a9f0-d060cfff0b39.png" alt="企业微信截图_16359070273966" style="max-width: 100%;"></a></p>
<p dir="auto">My configuration is as follows：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="private fun dataSourceMap(config: DataSourceProperties): Map&lt;String, DataSource&gt; {
    val dataSourceMap: MutableMap&lt;String, DataSource&gt; = mutableMapOf()
    val dataSourceConfigs = config.dataSourceConfigs
    dataSourceConfigs.forEachIndexed { index, dataSourceConfig -&gt;
        val dataSourceName = &quot;$DATA_SOURCE_NAME_PREFIX$index&quot;
        dataSourceMap[dataSourceName] = createHikariDataSource(
            datasourcePoolName = dataSourceName,
            datasourceUrl = dataSourceConfig.url,
            datasourceUsername = dataSourceConfig.username,
            datasourcePassword = dataSourceConfig.password,
            datasourceInitSql = dataSourceConfig.initSql,
            datasouceLeakDetectionThreshold = dataSourceConfig.leakDetectionThreshold
        )
    }
    return dataSourceMap
}

private fun createHikariDataSource(
    datasourcePoolName: String,
    datasourceUrl: String,
    datasourceUsername: String,
    datasourcePassword: String,
    datasourceInitSql: String?,
    datasouceLeakDetectionThreshold: Long
): HikariDataSource {
    return HikariDataSource().apply {
        poolName = datasourcePoolName
        jdbcUrl = datasourceUrl
        username = datasourceUsername
        password = datasourcePassword
        driverClassName = Driver::class.java.name
        minimumIdle = 10
        maximumPoolSize = 50
        idleTimeout = 60000
        connectionInitSql = datasourceInitSql
        leakDetectionThreshold = datasouceLeakDetectionThreshold
    }
}

@Bean
fun shardingDataSource(config: DataSourceProperties): DataSource {
    val shardingRuleConfig = ShardingRuleConfiguration()
    // 设置表的路由规则
    val tableRuleConfigs = shardingRuleConfig.tables
    val dataSourceSize = config.dataSourceConfigs.size
    tableRuleConfigs.add(getTableRuleConfiguration(&quot;t_pipeline_info&quot;, dataSourceSize))
    tableRuleConfigs.add(getTableRuleConfiguration(&quot;t_pipeline_user&quot;, dataSourceSize))
    val dbShardingAlgorithmrProps = Properties()
    dbShardingAlgorithmrProps.setProperty(&quot;strategy&quot;, &quot;STANDARD&quot;)
    dbShardingAlgorithmrProps.setProperty(&quot;algorithmClassName&quot;,
        &quot;com.xxx.devops.sharding.configuration.BkDatabaseShardingAlgorithm&quot;)
    shardingRuleConfig.shardingAlgorithms[&quot;bkDatabaseShardingAlgorithm&quot;] =
        ShardingSphereAlgorithmConfiguration(&quot;CLASS_BASED&quot;, dbShardingAlgorithmrProps)

    shardingRuleConfig.defaultTableShardingStrategy = NoneShardingStrategyConfiguration()
    shardingRuleConfig.defaultDatabaseShardingStrategy =
        StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, &quot;bkProcessDatabaseShardingAlgorithm&quot;)
    val properties = Properties()
    // 是否打印SQL解析和改写日志
    properties.setProperty(&quot;sql-show&quot;, &quot;true&quot;)
    return ShardingSphereDataSourceFactory.createDataSource(
        dataSourceMap(config),
        listOf(shardingRuleConfig),
        properties
    )
}

fun getTableRuleConfiguration(
    tableName: String,
    dataSourceSize: Int,
    specifyDataSourceName: String? = null
): ShardingTableRuleConfiguration? {
    // 生成实际节点规则
    val actualDataNodes = if (specifyDataSourceName != null) {
        &quot;$specifyDataSourceName.$tableName&quot;
    } else {
        val lastIndex = dataSourceSize - 1
        &quot;$DATA_SOURCE_NAME_PREFIX\${0..$lastIndex}.$tableName&quot;
    }
    val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
    tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
    tableRuleConfig.databaseShardingStrategy =
        StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, &quot;bkDatabaseShardingAlgorithm&quot;)
    return tableRuleConfig
}"><pre class="notranslate"><code class="notranslate">private fun dataSourceMap(config: DataSourceProperties): Map&lt;String, DataSource&gt; {
    val dataSourceMap: MutableMap&lt;String, DataSource&gt; = mutableMapOf()
    val dataSourceConfigs = config.dataSourceConfigs
    dataSourceConfigs.forEachIndexed { index, dataSourceConfig -&gt;
        val dataSourceName = "$DATA_SOURCE_NAME_PREFIX$index"
        dataSourceMap[dataSourceName] = createHikariDataSource(
            datasourcePoolName = dataSourceName,
            datasourceUrl = dataSourceConfig.url,
            datasourceUsername = dataSourceConfig.username,
            datasourcePassword = dataSourceConfig.password,
            datasourceInitSql = dataSourceConfig.initSql,
            datasouceLeakDetectionThreshold = dataSourceConfig.leakDetectionThreshold
        )
    }
    return dataSourceMap
}

private fun createHikariDataSource(
    datasourcePoolName: String,
    datasourceUrl: String,
    datasourceUsername: String,
    datasourcePassword: String,
    datasourceInitSql: String?,
    datasouceLeakDetectionThreshold: Long
): HikariDataSource {
    return HikariDataSource().apply {
        poolName = datasourcePoolName
        jdbcUrl = datasourceUrl
        username = datasourceUsername
        password = datasourcePassword
        driverClassName = Driver::class.java.name
        minimumIdle = 10
        maximumPoolSize = 50
        idleTimeout = 60000
        connectionInitSql = datasourceInitSql
        leakDetectionThreshold = datasouceLeakDetectionThreshold
    }
}

@Bean
fun shardingDataSource(config: DataSourceProperties): DataSource {
    val shardingRuleConfig = ShardingRuleConfiguration()
    // 设置表的路由规则
    val tableRuleConfigs = shardingRuleConfig.tables
    val dataSourceSize = config.dataSourceConfigs.size
    tableRuleConfigs.add(getTableRuleConfiguration("t_pipeline_info", dataSourceSize))
    tableRuleConfigs.add(getTableRuleConfiguration("t_pipeline_user", dataSourceSize))
    val dbShardingAlgorithmrProps = Properties()
    dbShardingAlgorithmrProps.setProperty("strategy", "STANDARD")
    dbShardingAlgorithmrProps.setProperty("algorithmClassName",
        "com.xxx.devops.sharding.configuration.BkDatabaseShardingAlgorithm")
    shardingRuleConfig.shardingAlgorithms["bkDatabaseShardingAlgorithm"] =
        ShardingSphereAlgorithmConfiguration("CLASS_BASED", dbShardingAlgorithmrProps)

    shardingRuleConfig.defaultTableShardingStrategy = NoneShardingStrategyConfiguration()
    shardingRuleConfig.defaultDatabaseShardingStrategy =
        StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
    val properties = Properties()
    // 是否打印SQL解析和改写日志
    properties.setProperty("sql-show", "true")
    return ShardingSphereDataSourceFactory.createDataSource(
        dataSourceMap(config),
        listOf(shardingRuleConfig),
        properties
    )
}

fun getTableRuleConfiguration(
    tableName: String,
    dataSourceSize: Int,
    specifyDataSourceName: String? = null
): ShardingTableRuleConfiguration? {
    // 生成实际节点规则
    val actualDataNodes = if (specifyDataSourceName != null) {
        "$specifyDataSourceName.$tableName"
    } else {
        val lastIndex = dataSourceSize - 1
        "$DATA_SOURCE_NAME_PREFIX\${0..$lastIndex}.$tableName"
    }
    val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
    tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
    tableRuleConfig.databaseShardingStrategy =
        StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkDatabaseShardingAlgorithm")
    return tableRuleConfig
}
</code></pre></div>
<p dir="auto"><strong>My table structure is as follows：</strong></p>
<p dir="auto">CREATE TABLE <code class="notranslate">t_pipeline_info</code> (<br>
<code class="notranslate">PIPELINE_ID</code> varchar(34) NOT NULL DEFAULT,<br>
<code class="notranslate">PROJECT_ID</code> varchar(64) NOT NULL COMMENT,<br>
<code class="notranslate">PIPELINE_NAME</code> varchar(255) NOT NULL,<br>
<code class="notranslate">PIPELINE_DESC</code> varchar(255) DEFAULT NULL,<br>
PRIMARY KEY (<code class="notranslate">PIPELINE_ID</code>),<br>
UNIQUE KEY <code class="notranslate">T_PIPELINE_INFO_NAME_uindex</code> (<code class="notranslate">PROJECT_ID</code>,<code class="notranslate">PIPELINE_NAME</code>),<br>
KEY <code class="notranslate">PROJECT_ID</code> (<code class="notranslate">PROJECT_ID</code>,<code class="notranslate">PIPELINE_ID</code>)<br>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</p>
<h2 dir="auto">Question</h2>
<p dir="auto"><code class="notranslate">server.yaml</code></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the &quot;License&quot;); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an &quot;AS IS&quot; BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

######################################################################################################
# 
# If you want to configure governance, authorization and proxy properties, please refer to this file.
# 
######################################################################################################

#scaling:
#  blockQueueSize: 10000
#  workerThread: 40
#  clusterAutoSwitchAlgorithm:
#    type: IDLE
#    props:
#      incremental-task-idle-minute-threshold: 30
#  dataConsistencyCheckAlgorithm:
#    type: DEFAULT
#
#mode:
#  type: Cluster
#  repository:
#    type: ZooKeeper
#    props:
#      namespace: governance_ds
#      server-lists: localhost:2181
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500
#  overwrite: false
#
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@%:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos

#props:
#  max-connections-size-per-query: 1
#  kernel-executor-size: 16  # Infinite by default.
#  proxy-frontend-flush-threshold: 128  # The default value is 128.
#  proxy-opentracing-enabled: false
#  proxy-hint-enabled: false
#  sql-show: false
#  check-table-metadata-enabled: false
#  show-process-list-enabled: false
#    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
#    # The default value is -1, which means set the minimum value for different JDBC drivers.
#  proxy-backend-query-fetch-size: -1
#  check-duplicate-table-enabled: false
#  sql-comment-parse-enabled: false
#  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
#    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
#    # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
#  proxy-backend-executor-suitable: OLAP
#  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
#  sql-federation-enabled: false
"><pre class="notranslate"><code class="notranslate">#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

######################################################################################################
# 
# If you want to configure governance, authorization and proxy properties, please refer to this file.
# 
######################################################################################################

#scaling:
#  blockQueueSize: 10000
#  workerThread: 40
#  clusterAutoSwitchAlgorithm:
#    type: IDLE
#    props:
#      incremental-task-idle-minute-threshold: 30
#  dataConsistencyCheckAlgorithm:
#    type: DEFAULT
#
#mode:
#  type: Cluster
#  repository:
#    type: ZooKeeper
#    props:
#      namespace: governance_ds
#      server-lists: localhost:2181
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500
#  overwrite: false
#
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@%:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos

#props:
#  max-connections-size-per-query: 1
#  kernel-executor-size: 16  # Infinite by default.
#  proxy-frontend-flush-threshold: 128  # The default value is 128.
#  proxy-opentracing-enabled: false
#  proxy-hint-enabled: false
#  sql-show: false
#  check-table-metadata-enabled: false
#  show-process-list-enabled: false
#    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
#    # The default value is -1, which means set the minimum value for different JDBC drivers.
#  proxy-backend-query-fetch-size: -1
#  check-duplicate-table-enabled: false
#  sql-comment-parse-enabled: false
#  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
#    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
#    # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
#  proxy-backend-executor-suitable: OLAP
#  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
#  sql-federation-enabled: false

</code></pre></div>
<p dir="auto"><code class="notranslate">config-ds.yaml</code></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="databaseName: sharding_db
#
dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/_ds_0?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/ds_1?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..63}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..63}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
#  defaultAuditStrategy:
#    auditorNames:
#      - sharding_key_required_auditor
#    allowHintDisable: true

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 64}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 64}

  keyGenerators:
    snowflake:
      type: SNOWFLAKE
#
#  auditors:
#    sharding_key_required_auditor:
#      type: DML_SHARDING_CONDITIONS
#
#  scalingName: default_scaling
#  scaling:
#    default_scaling:
#      input:
#        workerThread: 40
#        batchSize: 1000
#      output:
#        workerThread: 40
#        batchSize: 1000
#      streamChannel:
#        type: MEMORY
#        props:
#          block-queue-size: 10000
#      completionDetector:
#        type: IDLE
#        props:
#          incremental-task-idle-seconds-threshold: 1800
#      dataConsistencyChecker:
#        type: DATA_MATCH
#        props:
#          chunk-size: 1000"><pre class="notranslate"><code class="notranslate">databaseName: sharding_db
#
dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/_ds_0?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/ds_1?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..63}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..63}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
#  defaultAuditStrategy:
#    auditorNames:
#      - sharding_key_required_auditor
#    allowHintDisable: true

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 64}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 64}

  keyGenerators:
    snowflake:
      type: SNOWFLAKE
#
#  auditors:
#    sharding_key_required_auditor:
#      type: DML_SHARDING_CONDITIONS
#
#  scalingName: default_scaling
#  scaling:
#    default_scaling:
#      input:
#        workerThread: 40
#        batchSize: 1000
#      output:
#        workerThread: 40
#        batchSize: 1000
#      streamChannel:
#        type: MEMORY
#        props:
#          block-queue-size: 10000
#      completionDetector:
#        type: IDLE
#        props:
#          incremental-task-idle-seconds-threshold: 1800
#      dataConsistencyChecker:
#        type: DATA_MATCH
#        props:
#          chunk-size: 1000
</code></pre></div>