Sharding-proxy (5.1.2) 在进行跨库关联查询时，使用中文作为查询条件时引发 java.sql.SQLException 异常

Apache ShardingSphere 提问
#1

使用环境：

具体环境及基本配置请参考 https://community.sphere-ex.com/t/topic/1063

场景、问题：

  • 在执行跨库关联查询时发生错误
select tu.name
	 , tu.category
	 , te.name_of_enterprise
	 , te.property_of_enterprise
from sharding_test.tb_user as tu
join sharding_test.tb_enterprise as te 
on tu.work_unit = te.ID
where te.property_of_enterprise = '国有企业'
  • 在 DBeaver 中报错如下
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1815] [HY000]: Internal error: exception while executing query: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'u' in 'where clause'
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:513)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:444)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:431)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:816)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3280)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4686)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Internal error: exception while executing query: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'u' in 'where clause'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441)
	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
	... 12 more
  • 查看 sharding-proxy 日志，显示如下异常信息
[INFO ] 2022-07-12 13:25:26.816 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds73 ::: SELECT * FROM `tb_enterprise` WHERE `property_of_enterprise` = u&'\56fd\6709\4f01\4e1a'
[ERROR] 2022-07-12 13:25:26.819 [ShardingSphere-Command-4] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
java.sql.SQLException: exception while executing query: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'u' in 'where clause'
        at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
        at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
        at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:577)
        at org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137)
        at org.apache.shardingsphere.infra.federation.executor.original.OriginalFilterableExecutor.executeQuery(OriginalFilterableExecutor.java:82)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:156)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:132)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:73)
        at org.apache.shardingsphere.proxy.backend.text.data.impl.SchemaAssignedDatabaseBackendHandler.execute(SchemaAssignedDatabaseBackendHandler.java:61)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:97)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:107)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:750)
Caused by: org.apache.shardingsphere.infra.exception.ShardingSphereException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'u' in 'where clause'
        at org.apache.shardingsphere.infra.federation.executor.original.table.FilterableTableScanExecutor.execute(FilterableTableScanExecutor.java:162)
        at org.apache.shardingsphere.infra.federation.executor.original.table.FilterableTableScanExecutor.execute(FilterableTableScanExecutor.java:147)
        at org.apache.shardingsphere.infra.federation.executor.original.table.FilterableTable.scan(FilterableTable.java:53)
        at org.apache.calcite.interpreter.TableScanNode.createProjectableFilterable(TableScanNode.java:204)
        at org.apache.calcite.interpreter.TableScanNode.create(TableScanNode.java:84)
        at org.apache.calcite.interpreter.Nodes$CoreCompiler.visit(Nodes.java:75)
        at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:263)
        at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:220)
        at org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:469)
        at org.apache.calcite.interpreter.Interpreter$CompilerImpl.visit(Interpreter.java:358)
        at org.apache.calcite.interpreter.Nodes$CoreCompiler.visit(Nodes.java:48)
        at org.apache.calcite.interpreter.Interpreter$CompilerImpl.visitRoot(Interpreter.java:312)
        at org.apache.calcite.interpreter.Interpreter.<init>(Interpreter.java:88)
        at Baz.bind(Unknown Source)
        at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363)
        at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:333)
        at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578)
        at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569)
        at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
        at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
        at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
        at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:573)
        ... 12 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'u' in 'where clause'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
        at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:829)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:923)
        at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:103)
        at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:40)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:68)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:86)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:66)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:131)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:116)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
        at org.apache.shardingsphere.infra.federation.executor.original.table.FilterableTableScanExecutor.execute(FilterableTableScanExecutor.java:169)
        at org.apache.shardingsphere.infra.federation.executor.original.table.FilterableTableScanExecutor.execute(FilterableTableScanExecutor.java:155)
        ... 36 common frames omitted
[INFO ] 2022-07-12 13:25:26.822 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: /* ApplicationName=DBeaver 21.1.2 - SQLEditor <Script-4.sql> */ SHOW WARNINGS

现状：

  • 尝试使用 unicode 重写条件，但无法返回正确内容
select tu.name
	 , tu.category
	 , te.name_of_enterprise
	 , te.property_of_enterprise
from sharding_test.tb_user as tu
join sharding_test.tb_enterprise as te 
on tu.work_unit = te.ID
where te.property_of_enterprise = '\u56fd\u6709\u4f01\u4e1a'
#2

可尝试配置字符集，参考这个链接：

#3

我的数据源本身就设置了 UTF8 的字符集，还是会出现问题

CREATE RESOURCE ds72 (
  URL="jdbc:mysql://10.8.2.72:3306/sharding_t72?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8",
  USER=sharding_user,
  PASSWORD=123456Aa,
  PROPERTIES("connectionTimeoutMilliseconds"=30000,"idleTimeoutMilliseconds"=60000,"maxLifetimeMilliseconds"=1800000,"maxPoolSize"=50,"minPoolSize"=1)
);

CREATE RESOURCE ds73 (
  URL="jdbc:mysql://10.8.2.73:3306/sharding_t73?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8",
  USER=sharding_user,
  PASSWORD=123456Aa,
  PROPERTIES("connectionTimeoutMilliseconds"=30000,"idleTimeoutMilliseconds"=60000,"maxLifetimeMilliseconds"=1800000,"maxPoolSize"=50,"minPoolSize"=1)
);

我提的问题的原因应该跟你的不太一样，我查了一下应该是在 SQL 解析转换时将条件中的中文转换成了 u& 开头的字符串而造成的。

#4

不用 DBeaver，通过命令行执行 SQL 是否有同样的问题呢？

#5

通过 mysql.exe 连接 proxy 后，执行相同的 SQL 还是会出现同样的问题。

mysql-native
mysql-native1500×256 66.9 KB

#6

好的，我会继续调查这个问题。

#7

这个问题有结果了吗？我用5.1的时候就存在这个问题，今天下了个5.3.2，好像还是有这个问题，有解决办法嘛？

京ICP备2021015875号