shardingSphere5.1.0读写分离+分片后报错问题

方便更快捷的说明问题,可以按需填写(可删除)

使用环境:

shardingSphere 5.1.0
以下是配置,因为测试环境没有主从环境所以数据源就配置了一个,读写也都是同一个数据源
spring:
autoconfigure:
exclude: org.springframework.cloud.gateway.config.GatewayAutoConfiguration,org.springframework.cloud.gateway.config.GatewayClassPathWarningAutoConfiguration
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
# username: ${MYSQL_USER:root}
# password: ${MYSQL_PWD:Gydev123#}
username: ${MYSQL_USER:gydev}
password: ${MYSQL_PWD:Gy123!@#}
url: jdbc:mysql://${MYSQL_HOST:gyscada-mysql}:${MYSQL_PORT:3306}/${MYSQL_DB:gyscadax_internet}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true
stat-view-servlet:
enabled: true
allow: “”
url-pattern: /druid/*
#login-username: admin
#login-password: admin
filter:
stat:
enabled: true
log-slow-sql: true
slow-sql-millis: 10000
merge-sql: false
wall:
config:
multi-statement-allow: true
#shardingjdbc主要配置
shardingsphere:
# 是否启用sharding
enabled: true
props:
# 是否显示sql
sql-show: true
sql-federation-enabled : true
datasource:
names: master
master:
# type: com.zaxxer.hikari.HikariDataSource
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${MYSQL_HOST:gyscada-mysql}:${MYSQL_PORT:3306}/${MYSQL_DB:gyscadax_internet}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true
username: ${MYSQL_USER:gydev}
password: ${MYSQL_PWD:Gy123!@#}
# slave1:
# # type: com.zaxxer.hikari.HikariDataSource
# type: com.alibaba.druid.pool.DruidDataSource
# driver-class-name: com.mysql.cj.jdbc.Driver
# url: jdbc:mysql://${MYSQL_HOST:gyscada-mysql-slave}:${MYSQL_PORT:3306}/${MYSQL_DB:gyscadax_internet}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true
# username: ${MYSQL_USER:root}
# password: ${MYSQL_PWD:Gydev123#}
rules:
readwrite-splitting:
data-sources:
ms:
# 读写分离类型,比如:Static,Dynamic,动态方式需要配合高可用功能
type: Static
loadBalancerName: round-robin
props:
# 注意,如果接口有事务,读写分离不生效,默认全部使用主库,为了保证数据一致性
write-data-source-name: master
read-data-source-names: master
load-balancers:
#名称自定义,跟上边的loadBalancerName配置的值保持一致
round-robin:
type: ROUND_ROBIN
sharding:
tables:
machine_collect_item_history: # 分表,逻辑表名
# 节点数据
actual-data-nodes: master.machine_collect_item_history
table-strategy: # 配置分表策略
standard: # 用于单分片键的标准分片场景
sharding-column: collection_time
sharding-algorithm-name: tb-score-inline
# 分片算法配置
sharding-algorithms:
tb-score-inline: # 分片算法名称 不能使用下划线
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.gyzh.gyscada.internet.algorithm.YearMonthShardingAlgorithm

场景、问题:

数据分片与读写分离单个功能是正常的,一起配置后进入策略类已经返回真实表名进行查询后报错如图


已进行操作:

现状:

请提供可以复现异常的步骤,目前看以上信息没法复现问题,例如:GatewayClassPathWarningAutoConfiguration 实现,执行什么 SQL 能够复现问题。

SELECT a.Id, a.collect_item_code, a.collect_item_name, a.collect_item_value, a.create_time, a.collection_time, a.create_by_name, a.dept_name, b.manufacturing_number, b.machine_code, b.machine_name, e.collect_item_type_name, u.unit_name FROM machine_collect_item_history a LEFT JOIN machine_machine b ON a.machine_id = b.Id AND b.del_flag = 0 LEFT JOIN collect_item_type e ON a.collect_item_type_id = e.Id AND e.del_flag = 0 LEFT JOIN machine_collect_item f ON a.machine_collect_item_id = f.id AND f.del_flag = 0 LEFT JOIN common_unit u ON u.id = f.unit_id AND u.del_flag = 0 WHERE a.del_flag = 0 AND a.collection_time >= ? AND a.collection_time <= ? AND a.machine_id = ? AND a.collect_item_type_id = ? ORDER BY a.collection_time ASC LIMIT ?

这是当时运行的sql

这个sql如果我不加读写分离的配置,是可以正常查询的,一加入就会报错 :joy:

可以用 markdown 把配置格式化下吗?现在这个格式很难阅读。。。


贴出截图可以吗,格式好后的贴进来好像就变得没有格式了

昨天出现的异常是因为我开启了sql-federation-enabled : true,关闭后此异常没有了但是又出现了这个问题,我的sql里面没有进行跨库查询
nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: java.lang.IllegalStateException: All tables must be in the same datasource. ### The error may exist in file [D:\IdeaProjects\DRWisdomPlatformAPI\gyscada-internet\gyscada-internet-biz\target\classes\mapper\MachineCollectItemHistoryMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT a.id, a.machine_id AS machineId, d.machine_code machineCode, d.machine_name machineName, d.manufacturing_number manufacturingNumber, a.machine_group_id AS machineGroupId, e.machine_group_name machineGroupName, a.collect_item_type_id AS collectItemTypeId, a.machine_collect_item_id AS machineCollectItemId, a.collect_item_code AS collectItemCode, a.collect_item_name AS collectItemName, (CASE WHEN a.collect_item_value = ‘null’ THEN 0 ELSE a.collect_item_value END) AS collectItemValue, a.collection_time AS collectionTime, date(a.collection_time) AS reCollectionTime FROM machine_collect_item_history a LEFT JOIN machine_collect_item b ON a.machine_collect_item_id = b.id AND b.del_flag = 0 LEFT JOIN collect_item c ON b.collect_item_id = c.id AND c.del_flag = 0 LEFT JOIN machine_machine d ON a.machine_id = d.id AND d.del_flag = 0 LEFT JOIN machine_group e ON a.machine_group_id = e.id AND e.del_flag = 0 LEFT JOIN machine_collect_item_point f ON b.id = f.machine_collect_item_id AND f.del_flag = 0 WHERE a.del_flag = 0 AND d.access_flag = 1 AND f.data_type_id != ‘1’ AND a.collection_time >= ? AND a.collection_time IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND d.machine_code IN (?) ORDER BY a.collection_time DESC ### Cause: java.lang.IllegalStateException: All tables must be in the same datasource.

是不是分片表和单表关联查询了?

单表指的是未做分片的表吗,在我的sql里machine_collect_item_history这张是分片表,其余都是未做分片的

是的,这种关联目前不支持

是读写分离加分表的情况下不支持这样的查询是吗

和读写分离没关系,就是分片里面单表和分片表的关联不支持

我对您刚才说的分片表和单表理解上可能有些偏差 :joy: ,再没配置读写分离配置时,我这段sql是可以正常运行的,会路由到不同分表


这个sql我用截图贴出来可能更直观一些,machine_collect_item_history这张表我是做了sharding,其他都是正常的表

兄弟,解决了吗?

嗯解决了问题出在了这段配置,我当时做了动态节点这边配置的数据源名称和我在项目里配置的数据源名称没匹配上所以报错了。

从你这个配制上,我看不明白是怎么配制才会解决。
是配制 type: Static 解决了问题
还是 type: Dynamic 解决了问题

京ICP备2021015875号