StarRocks 3.3.2 版本JDBC Catalog SQLServer外表查询报错

💬 StarRocks 用户问答 常见 FAQ
#1

【详述】StarRocks 3.3.2 版本JDBC Catalog SQLServer2012外表查询报错
【业务影响】
【是否存算分离】 是
【StarRocks版本】例如： 3.3.2
【附件】

  • 创建Catalog 语句
    CREATE EXTERNAL CATALOG jdbc0
    PROPERTIES
    (
    “type”=“jdbc”,
    “user”="",
    “password”="",
    “jdbc_uri”=“jdbc:sqlserver://10.96.7.186:1433;databaseName=IMES_Rep;trustServerCertificate=true;userSSL=false”,
    “driver_url”=“file:///opt/module/StarRocks-3.3.0/jdbc-driver/mssql-jdbc-12.4.2.jre11.jar”,
    “driver_class”=“com.microsoft.sqlserver.jdbc.SQLServerDriver”
    );

  • 查询语句
    SHOW DATABASES FROM jdbc0 —执行成功
    USE jdbc0.dbo —执行成功
    select * from PCBRepair; – 异常

  • fe.warn.log 异常信息
    2024-08-14 09:06:09.636+08:00 WARN (starrocks-mysql-nio-pool-171|6732) [DefaultCoordinator.getNext():787] get next fail, need cancel. status errorCode INTERNAL_ERROR open JDBCScanner failed, error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘501’.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:485)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at com.starrocks.jdbcbridge.JDBCScanner.open(JDBCScanner.java:95)
    , query id: 64211647-59d9-11ef-b75a-5cba2c293c74
    2024-08-14 09:06:09.636+08:00 WARN (starrocks-mysql-nio-pool-171|6732) [DefaultCoordinator.updateStatus():762] one instance report fail throw updateStatus(), need cancel. job id: -1, query id: 64211647-59d9-11ef-b75a-5cba2c293c74, instance id: NaN
    2024-08-14 09:06:09.636+08:00 WARN (starrocks-mysql-nio-pool-171|6732) [DefaultCoordinator.getNext():813] query failed: open JDBCScanner failed, error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘501’.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:485)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at com.starrocks.jdbcbridge.JDBCScanner.open(JDBCScanner.java:95)

2024-08-14 09:06:09.636+08:00 WARN (starrocks-mysql-nio-pool-171|6732) [StmtExecutor.execute():646] Query 64211647-59d9-11ef-b75a-5cba2c293c74 failed. Planner profile : Planner:

  • – Parser[1] 0
    Reason:
#2

贴一下报错的 profile 把

#3

这个就是报错就是 fe.warn.log 文件里面的所有内容，由于公司电脑限制无法上传文件只能文本粘贴抱歉

#4

贴一下 sqlserver 里面的表的 schema 把

#5

create table dbo.PCBRepair
(
ID int identity
constraint PCBRepair_PK
primary key,
PCBNo char(11) not null,
PCBModelID char(12) not null,
Type char(10) not null,
Line char(30) not null,
Station char(10) not null,
TestLogID int,
Status int not null,
Editor varchar(30) not null,
Cdt datetime not null,
Udt datetime not null,
LogID int
)

#6

explain select * from xxx 看眼正常吗，正常贴下结果。

然后再 select col from xxx，一列一列挨个查，看看是那一列处理有问题。

#7

执行 explain select * from jdbc0.dbo.PCBRepair --结果

PLAN FRAGMENT 0
OUTPUT EXPRS:ID | PCBNo | PCBModelID | Type | Line | Station | TestLogID | Status | Editor | Cdt | Udt | LogID
PARTITION: UNPARTITIONED

RESULT SINK

0:SCAN JDBC
TABLE: [dbo].[PCBRepair]
" QUERY: SELECT ID, PCBNo, PCBModelID, Type, Line, Station, TestLogID, Status, Editor, Cdt, Udt, LogID FROM [dbo].[PCBRepair]"

执行 select ID from jdbc0.dbo.PCBRepair 随便查询那一列都报上面一样错

#8

看下be上jni的log吧

#9

我是存算分离模式没有be 节点 只有cn 节点 ，全部的错就是上面提到的错误

#10

你这是fe的log，我需要看cn的log

#11

cn的log 就是这个没有其他的了一样的错误

#13

查询 select count(1) from CQ_DATACENTER_TPJ.dbo.line 正常
查询 select * from CQ_DATACENTER_TPJ.dbo.line 就报上面的错

#14

Registered driver with driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found, trying direct instantiation

#15

插眼，一样的错误

京ICP备2022026421号-1