shardingsphere: 【sharding-proxy】Actual SQL execution results are incorrect (data should have been returned, but no data was returned),

I has tried all shardingproxy versions,the result is same: The ShardingProxy log shows that the Actual SQL for the query is ok, but the SQL execution results are incorrect (data should have been returned, but no data was returned)

When directly using the interface to query the database, you can find the data. With the addition of shardingProxy proxy, the query interface does not return the data.

The logging and configuration are shown below:

一、configuration

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://19.111.4.11:3306/db1?serverTimezone=UTC&useSSL=false
    username: sera
    password: szua  
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://19.111.4.11:3306/db2?serverTimezone=UTC&useSSL=false
    username: sera
    password: szua
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_2:
    url: jdbc:mysql://19.111.4.11:3306/db3?serverTimezone=UTC&useSSL=false
    username: sera
    password: szua
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    
shardingRule:
  tables:
    pscst_prdt_cvr:
      actualDataNodes: ds_${0..1}.pscst_prdt_cvr${0..1}
      databaseStrategy:
        inline:
          shardingColumn: ecif_cust_no
          algorithmExpression: ds_${ecif_cust_no % 2}
      tableStrategy: 
        inline:
          shardingColumn: ecif_cust_no
          algorithmExpression: pscst_prdt_cvr${ecif_cust_no % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: ecif_cust_no
    bcpur_epcst_aslb_stats:
      actualDataNodes: ds_2.bcpur_epcst_aslb_stats
      databaseStrategy:
        inline:
          shardingColumn: ecif_cust_no
          algorithmExpression: ds_2
      tableStrategy: 
        inline:
          shardingColumn: ecif_cust_no
          algorithmExpression: bcpur_epcst_aslb_stats
      keyGenerator:
        type: SNOWFLAKE
        column: ecif_cust_no          
  bindingTables:
    - pscst_prdt_cvr,bcpur_epcst_aslb_stats
  defaultDataSourceName: ds_2
  defaultTableStrategy:
    none: 

二、logs(Logic SQL and Actual SQL logs) log(Logic SQL and Actual SQL logs)

[INFO ] 11:05:16.105 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL:
 SELECT BIZ_DT, ECIF_CUST_NO, DEP_FLG, DBTCD_FLG, PLM_PY_FLG, PY_GTWY_FLG, 
 SFSRV_EQP_SGNG_FLG, ATMBL_MRTG_FLG, HS_MRTG_FLG, HLPFM_ETMN_FLG, PSNL_MANG_LN_FLG, 
 PSNL_CNSM_LN_FLG, ETC_FLG, CSCRD_FLG, MBLBNK_FLG, EBP_SGNG_FLG FROM pscst_prdt_cvr 
 WHERE ECIF_CUST_NO = ?


[INFO ] 11:05:16.109 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: 
SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere
.sql.parser.sql.statement.dml.SelectStatement@6d74eaeb, 
tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@23f8fdb9), 
tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@23f8fdb9, 
projectionsContext=ProjectionsContext(startIndex=7, stopIndex=218, distinctRow=false,
 projections=[ColumnProjection(owner=null, name=BIZ_DT, alias=Optional.empty), 
 ColumnProjection(owner=null, name=ECIF_CUST_NO, alias=Optional.empty), 
 ColumnProjection(owner=null, name=DEP_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=DBTCD_FLG, alias=Optional.empty),
 ColumnProjection(owner=null, name=PLM_PY_FLG, alias=Optional.empty),
 ColumnProjection(owner=null, name=PY_GTWY_FLG, alias=Optional.empty),
 ColumnProjection(owner=null, name=SFSRV_EQP_SGNG_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=ATMBL_MRTG_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=HS_MRTG_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=HLPFM_ETMN_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=PSNL_MANG_LN_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=PSNL_CNSM_LN_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=ETC_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=CSCRD_FLG, alias=Optional.empty),
 ColumnProjection(owner=null, name=MBLBNK_FLG, alias=Optional.empty), 
 ColumnProjection(owner=null, name=EBP_SGNG_FLG, alias=Optional.empty)]), 
 groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@6386efb2, 
 orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@23183b2c,
 paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@64ef8bcc, 
 containsSubquery=false)
[INFO ] 11:05:16.113 [ShardingSphere-Command-2] ShardingSphere-SQL - 
Actual SQL: ds_0 ::: SELECT BIZ_DT, ECIF_CUST_NO, DEP_FLG, DBTCD_FLG, PLM_PY_FLG, 
PY_GTWY_FLG, SFSRV_EQP_SGNG_FLG, ATMBL_MRTG_FLG, HS_MRTG_FLG, HLPFM_ETMN_FLG, 
PSNL_MANG_LN_FLG, PSNL_CNSM_LN_FLG, ETC_FLG, CSCRD_FLG, MBLBNK_FLG, 
EBP_SGNG_FLG FROM pscst_prdt_cvr0 WHERE ECIF_CUST_NO = ? ::: [10000]

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 46 (18 by maintainers)

Commits related to this issue

Most upvoted comments

I don’t think the root cause is relative with the useServerPrepStmts=true&cachePrepStmts=true parameters, because we have did a lot of tests for the prepared statement.

I believe you can get the right result if you change the CP to Hikari. As to why druid didn’t work, I think a tcpdump package will help. @duzhanfei