Sharding-proxy (5.1.2) 在进行跨库关联查询时，如果包含了 Blob 类型的字段会引发 java.lang.IllegalArgumentException 异常

Apache ShardingSphere 提问
#1

使用环境：

  • os: rocky linux 8.5
  • jdk: openJDK 1.8
  • mysql: 5.7.30
  • SQL 工具：dbeaver 21.3.2

场景、问题：

  • 有两个 MySQL 实例，各有一个数据库，详细拓扑结构如下：
10.8.2.72
     sharding_t72
         tb_user
10.8.2.73
     sharding_t73
         tb_enterprise
  • tb_user 表的结构如下
CREATE TABLE `tb_user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `birthdate` datetime NOT NULL,
  `intro` blob,
  `category` varchar(10) NOT NULL,
  `work_unit` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4;
  • tb_user 表测试数据如下
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('张大', '1990-01-01 00:00:00.0', 0xE68891E698AFE5BCA0E5A4A7EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 1);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('张二', '1990-02-02 00:00:00.0', 0xE68891E698AFE5BCA0E4BA8CEFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 1);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('张三', '1990-03-03 00:00:00.0', 0xE68891E698AFE5BCA0E4B889EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B9B2E983A8E38082, '中层', 1);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('李大', '1991-02-02 00:00:00.0', 0xE68891E698AFE69D8EE5A4A7EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 2);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('李二', '1991-03-03 00:00:00.0', 0xE68891E698AFE69D8EE4BA8CEFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 2);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('李三', '1991-04-04 00:00:00.0', 0xE68891E698AFE69D8EE4B889EFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B9B2E983A8E38082, '中层', 2);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('王大', '1995-05-05 00:00:00.0', 0xE68891E698AFE78E8BE5A4A7EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 3);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('王二', '1995-06-05 00:00:00.0', 0xE68891E698AFE78E8BE4BA8CEFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 3);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('王三', '1995-07-05 00:00:00.0', 0xE68891E698AFE78E8BE4B889EFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B9B2E983A8E38082, '中层', 3);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('蒋大', '1996-05-05 00:00:00.0', 0xE68891E698AFE8928BE5A4A7EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 4);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('蒋二', '1996-06-05 00:00:00.0', 0xE68891E698AFE8928BE4BA8CEFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 4);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('蒋三', '1996-07-05 00:00:00.0', 0xE68891E698AFE8928BE4B889EFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B9B2E983A8E38082, '中层', 4);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('赵大', '1997-05-05 00:00:00.0', 0xE68891E698AFE8B5B5E5A4A7EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 5);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('赵二', '1997-06-05 00:00:00.0', 0xE68891E698AFE8B5B5E4BA8CEFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 5);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('赵三', '1998-07-05 00:00:00.0', 0xE68891E698AFE8B5B5E4B889EFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B9B2E983A8E38082, '中层', 5);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('刘大', '1998-05-05 00:00:00.0', 0xE68891E698AFE58898E5A4A7EFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 6);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('刘二', '1998-06-05 00:00:00.0', 0xE68891E698AFE58898E4BA8CEFBC8CE680A7E588ABE794B7EFBC8CE698AFE4B8AAE5B7A5E4BABAE38082, '普工', 6);
INSERT INTO tb_user (name, birthdate, intro, category, work_unit) VALUES('刘三', '1998-07-05 00:00:00.0', 0xE68891E698AFE58898E4B889EFBC8CE680A7E588ABE5A5B3EFBC8CE698AFE4B8AAE5B9B2E983A8E38082, '中层', 6);
  • tb_enterprise 表结构如下
CREATE TABLE `tb_enterprise` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name_of_enterprise` varchar(255) NOT NULL,
  `property_of_enterprise` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
  • tb_enterprise 表测试数据如下
INSERT INTO tb_enterprise (name_of_enterprise, property_of_enterprise) VALUES('测试企业一', '国有企业');
INSERT INTO tb_enterprise (name_of_enterprise, property_of_enterprise) VALUES('测试企业二', '私营企业');
INSERT INTO tb_enterprise (name_of_enterprise, property_of_enterprise) VALUES('测试企业三', '国有企业');
INSERT INTO tb_enterprise (name_of_enterprise, property_of_enterprise) VALUES('测试企业四', '私营企业');
INSERT INTO tb_enterprise (name_of_enterprise, property_of_enterprise) VALUES('测试企业五', '国有企业');
INSERT INTO tb_enterprise (name_of_enterprise, property_of_enterprise) VALUES('测试企业六', '私营企业');
  • 在配置成单表跨库后执行以下 SQL 发生错误
select tu.name
	 , tu.intro  -- 该字段是一个 Blob
	 , 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

在 DBeaver 中报错如下

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1997] [C1997]: Runtime exception: [Cannot find JDBC type '2003' in column type]
	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: Runtime exception: [Cannot find JDBC type '2003' in column type]
	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-11 16:51:50.213 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-07-11 16:51:50.213 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds73 ::: SELECT * FROM `tb_enterprise`
[ERROR] 2022-07-11 16:51:50.237 [ShardingSphere-Command-0] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
java.lang.IllegalArgumentException: Cannot find JDBC type '2003' in column type
        at org.apache.shardingsphere.db.protocol.mysql.constant.MySQLBinaryColumnType.valueOfJDBCType(MySQLBinaryColumnType.java:148)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.builder.ResponsePacketBuilder.buildQueryResponsePackets(ResponsePacketBuilder.java:58)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.processQuery(MySQLComQueryPacketExecutor.java:107)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:99)
        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)
[INFO ] 2022-07-11 16:51:50.254 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: /* ApplicationName=DBeaver 21.1.2 - SQLEditor <Script-4.sql> */ SHOW WARNINGS

已进行操作：

  • 在 sharding-proxy 上创建了逻辑数据库 sharding_test
  • 通过 DistSQL 将上述的物理数据库进行了资源定义
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)
);

现状：

  • 单表查询（无论是否包含 Blob 字段）可正常完成
  • 跨库关联查询中不包含 Blob 字段时能正常完成
  • 跨库关联查询中包含 Blob 字段时不能进行，发生上述异常
Sharding-proxy (5.1.2) 在进行跨库关联查询时，使用中文作为查询条件时引发 java.sql.SQLException 异常
京ICP备2021015875号