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
- #7940, Data type column cast failed — committed to tuohai666/shardingsphere by tuohai666 4 years ago
- #7940, Data type column cast failed (#8234) — committed to apache/shardingsphere by tuohai666 4 years ago
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