使用环境:
- os: rocky linux 8.5
- jdk: openJDK 1.8
- mysql: 5.7.30
- SQL 工具:dbeaver 21.3.2
场景、问题:
当前有两个 MySQL 实例,各有一个数据库,具体结构如下:
10.8.2.72
shardingdb_v20220307
child_table
10.8.2.73
shardingdb_v20220206
main_table
当前期望能执行关联查询:
select * from main_table m join child_table c on m.original_id = c.original_id;
但在查询时发生错误:
-
SQL 工具反馈
Table 'shardingdb_v20220206.child_table' doesn't exist
-
sharding-proxy 日志
[ERROR] 2022-03-10 14:32:16.397 [ShardingSphere-Command-0] o.a.s.p.f.c.CommandExecutorTask - Exception occur: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'shardingdb_v20220206.child_table' doesn't exist
已进行操作:
- 已配置
server.yaml
,并设置sql-federation-enabled: true
,详细内容如下:
rules:
- !AUTHORITY
users:
- root@%:123456
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
check-duplicate-table-enabled: false
check-table-metadata-enabled: false
kernel-executor-size: 4
max-connections-size-per-query: 1
proxy-backend-driver-type: JDBC
proxy-backend-executor-suitable: OLAP
proxy-backend-query-fetch-size: -1
proxy-frontend-executor-size: 0
proxy-frontend-flush-threshold: 128
proxy-frontend-max-connections: 0
proxy-hint-enabled: false
proxy-opentracing-enabled: false
show-process-list-enabled: false
sql-federation-enabled: true
sql-show: true
- 已配置分片文件
config-sharding.yaml
,具体内容如下:
schemaName: shardingdb
dataSources:
shardingdb_v20220206:
url: jdbc:mysql://10.8.2.73:3306/shardingdb_v20220206?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
username: sharding_user
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
shardingdb_v20220307:
url: jdbc:mysql://10.8.2.72:3306/shardingdb_v20220307?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
username: sharding_user
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
main_table:
actualDataNodes: shardingdb_v20220206.main_table
child_table:
actualDataNodes: shardingdb_v20220307.child_table
bindingTables:
- main_table,child_table
现状:
无法完成查询,并报表不存在错误。