zhangwangren edited a comment on issue #13067:
URL:
https://github.com/apache/shardingsphere/issues/13067#issuecomment-945407149
At first when i only set the config like this:
# the broadcast-tables
spring.shardingsphere.rules.sharding.broadcast-tables=reservoir_flood_limit,reservoir_flood_prevention,station_basic_info_data,river_flood_prevention,dict_station_belong_reservoir,dict_point_station_relation
# datasoruce sharding rule
spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.props.algorithmClassName=com.sky.dam.water.config.sharding.TimeDatabaseShardingAlgorithm
# table sharding rule
spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.props.algorithmClassName=com.sky.dam.water.config.sharding.TimeTableShardingAlgorithm
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=TM
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=db-class-based
spring.shardingsphere.rules.sharding.tables.reservoir_water_data.actual-data-nodes=ds$->{0..9}.reservoir_water_data_0$->{1..9},ds$->{0..9}.reservoir_water_data_1$->{0..2}
spring.shardingsphere.rules.sharding.tables.reservoir_water_data.table-strategy.standard.sharding-column=TM
spring.shardingsphere.rules.sharding.tables.reservoir_water_data.table-strategy.standard.sharding-algorithm-name=tb-class-based
this sql is worked:
SELECT
rwd.id,
rwd.STCD,
sbi.STNM,
rwd.TM,
rwd.RZ,
rwd.INQ,
rwd.W,
rwd.BLRZ,
rwd.OTQ,
rwd.RWCHRCD,
rwd.RWPTN,
rwd.INQDR,
rwd.MSQMT,
rwd.ADDVCD
FROM reservoir_water_data rwd
LEFT JOIN station_basic_info_data sbi ON sbi.STCD = rwd.STCD
WHERE 1=1
AND rwd.STCD = '81120905'
AND rwd.TM >= '2021-09-26 00:00:00' AND rwd.TM < '2021-09-26 23:55:00'
but this sql doesn't work, and throw a Exception like this "Caused by:
org.apache.shardingsphere.infra.exception.ShardingSphereException: Cannot find
table rule and default data source with logic tables":
SELECT
rwdl.id,
rwdl.STCD,
bid.STNM,
rwdl.TM,
rwdl.RZ,
rfl.FSLTDZ AS FRZ,
(rwdl.RZ - rfl.FSLTDZ) AS difference,
rfp.DDZ,
CAST((rfp.DDZ - rwdl.RZ) AS DECIMAL(7,3)) AS DDRZ,
rwdl.INQ,
rwdl.W,
CAST(rfp.TTCP AS DECIMAL(7,3)) AS TTCP,
CAST((rwdl.W/rfp.TTCP) AS DECIMAL(7,3)) AS full_rate,
rwdl.BLRZ,
rwdl.OTQ,
rwdl.RWCHRCD,
rwdl.RWPTN,
rwdl.ADDVCD,
bid.STLC AS address,
bid.LGTD,
bid.LTTD
FROM reservoir_water_data_latest rwdl
LEFT JOIN station_basic_info_data bid ON bid.STCD = rwdl.STCD
LEFT JOIN reservoir_flood_prevention rfp ON rfp.STCD = rwdl.STCD
LEFT JOIN reservoir_flood_limit rfl ON rfl.STCD = rwdl.STCD
the table 'reservoir_water_data_latest' doesn't use sharding rule.
When i set the 'actual-data-nodes' like this, other config have no change:
spring.shardingsphere.rules.sharding.tables.reservoir_flood_limit.actual-data-nodes=ds$->{0}.reservoir_flood_limit
spring.shardingsphere.rules.sharding.tables.reservoir_flood_prevention.actual-data-nodes=ds$->{0}.reservoir_flood_prevention
spring.shardingsphere.rules.sharding.tables.station_basic_info_data.actual-data-nodes=ds$->{0}.station_basic_info_data
spring.shardingsphere.rules.sharding.tables.river_flood_prevention.actual-data-nodes=ds$->{0}.river_flood_prevention
spring.shardingsphere.rules.sharding.tables.dict_station_belong_reservoir.actual-data-nodes=ds$->{0}.dict_station_belong_reservoir
spring.shardingsphere.rules.sharding.tables.dict_point_station_relation.actual-data-nodes=ds$->{0}.dict_point_station_relation
the first sql doesn't work and my table sharing rule doesn't too, throw a
Exception like this "Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near
'.shardingsphere.sharding.rewrite.token.pojo.TableToken@138e4187 rwd"
the second sql is run well.
Actual SQL: ds0 ::: SELECT
rwd.id,
rwd.STCD,
sbi.STNM,
rwd.TM,
rwd.RZ,
rwd.INQ,
rwd.W,
rwd.BLRZ,
rwd.OTQ,
rwd.RWCHRCD,
rwd.RWPTN,
rwd.INQDR,
rwd.MSQMT,
rwd.ADDVCD
FROM
org.apache.shardingsphere.sharding.rewrite.token.pojo.TableToken@138e4187 rwd
LEFT JOIN
org.apache.shardingsphere.sharding.rewrite.token.pojo.TableToken@33c5a40e sbi
ON sbi.STCD = rwd.STCD
WHERE 1=1
AND rwd.STCD = ?
AND rwd.TM >= ? AND rwd.TM < ?
AND rwd.id > ? LIMIT ? ::: [81120905, 2021-09-26 00:00:00.0, 2021-09-26
23:55:00.0, 0, 10]
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
