方便更快捷的说明问题,可以按需填写(可删除)
单节点分库多表join,能查询数据,但是proxy后台有报错,请问报错的原因,是否有其他影响?如何避免错误?
使用环境:
系统:CENTOS 7.9
ShardingSphere-Proxy:5.3.2
Mysql GPL:8.0.31
Navicat: 16.1.16
场景、问题:
cat server.yaml |egrep -v ‘^#’
authority:
users:
- user: root@%
password: root
- user: cisco
password: cisco
privilege:
type: DATABASE_PERMITTED
props:
user-database-mappings: root@%=*, cisco@%=wayne*
props:
system-log-level: INFO
sql-show: true
sql-federation-type: ADVANCED
proxy-mysql-default-version: 8.0.31
cat config-fenku-business.yaml |egrep -v '^#'
databaseName: business
dataSources:
write_ds_1:
url: jdbc:mysql://192.168.101.101:3306/business?serverTimezone=UTC&useSSL=false
username: root
password: cisco
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
write_ds_2:
url: jdbc:mysql://192.168.101.101:3306/business1?serverTimezone=UTC&useSSL=false
username: root
password: cisco
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
已进行操作:
SELECT id,login_name,user_name,phone_number,ext,password from sys_user a left join ext_user b on a.id=b.user_id WHERE b.user_id=2;
现状:
错误信息:
[INFO ] 2023-04-22 16:42:51.566 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: SHOW STATUS
[INFO ] 2023-04-22 16:42:51.566 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: write_ds_1 ::: SHOW STATUS
[INFO ] 2023-04-22 16:42:51.723 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: SHOW STATUS
[INFO ] 2023-04-22 16:42:51.723 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: write_ds_1 ::: SHOW STATUS
[INFO ] 2023-04-22 16:42:53.800 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: SELECT * FROM `business`.`sys_user`
[INFO ] 2023-04-22 16:42:53.800 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: write_ds_1 ::: SELECT * FROM `sys_user`
[INFO ] 2023-04-22 16:42:53.816 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: SELECT * FROM `business`.`ext_user`
[INFO ] 2023-04-22 16:42:53.816 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: write_ds_2 ::: SELECT * FROM `ext_user`
[INFO ] 2023-04-22 16:42:53.864 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: SHOW STATUS
[INFO ] 2023-04-22 16:42:53.864 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: write_ds_1 ::: SHOW STATUS
[ERROR] 2023-04-22 16:42:54.065 [ShardingSphere-Command-0] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: Object 'PROFILING' not found within 'INFORMATION_SCHEMA'
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5362)
at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:138)
at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:188)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1107)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1078)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3381)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3360)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3697)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1107)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1078)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:248)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1053)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:759)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:592)
at org.apache.shardingsphere.sqlfederation.optimizer.SQLOptimizeEngine.optimize(SQLOptimizeEngine.java:50)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.execute(AdvancedSQLFederationExecutor.java:146)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.executeQuery(AdvancedSQLFederationExecutor.java:114)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecuteFederation(DatabaseConnector.java:288)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.execute(DatabaseConnector.java:205)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:114)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:109)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Object 'PROFILING' not found within 'INFORMATION_SCHEMA'
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)
... 32 common frames omitted预先格式化的文本