sr 3.0.0版本，创建mysql jdbc catalog，创建成功，但是查询报错

💬 StarRocks 用户问答
#1

【详述】sr 3.0.0版本，创建mysql jdbc catalog，创建成功，但是查询报错
【背景】

  1. 创建mysql jdbc catalog语句：
    CREATE EXTERNAL CATALOG mysql_catalog_205
    PROPERTIES
    (
    “type”=“jdbc”,
    “user”=“xxxx”,
    “password”=“xxxx”,
    “jdbc_uri”=“jdbc:mysql://xxx.xxx.xxx.xxx:3306”,
    “driver_url”=“https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar”,
    “driver_class”=“com.mysql.cj.jdbc.Driver”
    );

  2. 所有be的 start_be.sh 都增加了java_home
    image

  3. 查询sql报错：
    show databases from mysql_catalog_201;

报错信息如下：

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1064] [42000]: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

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:510)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:441)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:169)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:428)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:813)

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:169)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4589)

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.SQLSyntaxErrorException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)

at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)

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

【StarRocks版本】3.0.0-48f4d81
【集群规模】例如：3fe + 3be（fe与be混部）

#2

fe.log 的报错信息

2023-06-02 11:30:21,108 INFO (starrocks-mysql-nio-pool-16|4461) [MetadataMgr$QueryMetadatas.getConnectorMetadata():244] Succeed to register query level connector metadata [catalog:mysql_catalog_201, queryId: cd93d866-00f5-11ee-905d-1866dafb1278]
2023-06-02 11:30:21,114 ERROR (starrocks-mysql-nio-pool-16|4461) [MetadataMgr.listDbNames():107] Failed to listDbNames on catalog mysql_catalog_201
com.starrocks.connector.exception.StarRocksConnectorException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.starrocks.connector.jdbc.JDBCMetadata.listDbNames(JDBCMetadata.java:75) ~[starrocks-fe.jar:?]
at com.starrocks.server.MetadataMgr.listDbNames(MetadataMgr.java:105) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ShowExecutor.handleShowDb(ShowExecutor.java:765) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:259) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1124) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:514) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:348) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:462) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:728) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_181]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_181]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_181]
2023-06-02 11:30:21,115 WARN (starrocks-mysql-nio-pool-16|4461) [StmtExecutor.execute():558] execute Exception, sql /* ApplicationName=DBeaver Enterprise 21.1.0 - SQLEditor <Script-79.sql> */ show databases from mysql_catalog_201
com.starrocks.connector.exception.StarRocksConnectorException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.starrocks.connector.jdbc.JDBCMetadata.listDbNames(JDBCMetadata.java:75) ~[starrocks-fe.jar:?]
at com.starrocks.server.MetadataMgr.listDbNames(MetadataMgr.java:105) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ShowExecutor.handleShowDb(ShowExecutor.java:765) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:259) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1124) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:514) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:348) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:462) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:728) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_181]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_181]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_181]

#3

这个看着怀疑是驱动的问题 方便把 JDBC 驱动程序部署在 FE 或 BE 所在节点上任意相同路径下，然后把 driver_url 设置为该路径，格式为 file:///<path>/to/the/driver 试一下吗 ？ 然后创建成功后 您
SHOW CREATE CATALOG ****; 看下这个 Catalog 发下截图我看下

#4

  1. 我把mysql jar包放到相同路径下，
    image
    image
    image

  2. 创建catalog的语句：
    CREATE EXTERNAL CATALOG mysql_catalog_201
    PROPERTIES
    (
    “type”=“jdbc”,
    “user”=“xxx”,
    “password”=“xxx”,
    “jdbc_uri”=“jdbc:mysql://xxx.xxx.xxx.xxx:3306”,
    “driver_url”=“file:///home/anweikai/mysql_catalog”,
    “driver_class”=“com.mysql.cj.jdbc.Driver”
    );

  3. show create catalog mysql_catalog_201;

    image
    image1183×321 18.8 KB

  4. 执行 show databases from mysql_catalog_201;还是报错

#5

麻烦大佬看看，是我设置的有问题吗？

#6

driver_url应该写到jar文件级别

image
image1532×390 76.2 KB

#7

image
image1030×429 20.7 KB

driver_url设置到jar包也不行，还是报错

image
image939×586 34 KB

#8

大佬，还是不行，麻烦在看看，多谢

#9

大佬，我在 jdbc_uri 后增加?useSSL=false，查询 show databases from mysql_catalog_201; 可以正常执行，但是查询某个表的时候报错，报错sql： select * from mysql_catalog_201.example_cdc_db.my_test; 麻烦大佬看下

image
image1006×501 21.4 KB

报错信息如下：

image
image1584×648 54.5 KB

be.out的日志信息如下：
报错信息be.out (9.8 KB)

#10

你怎么连接的集群，走的负载均衡器还是？

#11

连接什么集群？mysql就只是单独的一个节点，也没有走负载均衡什么的。测试使用 ?useSSL=false，我是看doris文档里有，就尝试了一下。

CREATE EXTERNAL CATALOG mysql_catalog_201
PROPERTIES
(
“type”=“jdbc”,
“user”=“xxx”,
“password”=“xxx”,
“jdbc_uri”=“jdbc:mysql://xxx.xxx.xxx.xxx:3306”,
“driver_url”=“file:///home/anweikai/mysql_catalog/mysql-connector-java-8.0.28.jar”,
“driver_class”=“com.mysql.cj.jdbc.Driver”
);

#12

应该看starrocks文档呀，https://docs.starrocks.io/zh-cn/latest/data_source/catalog/jdbc_catalog
我的意思，你连接starrocks直连的fe ip还是前面有负载均衡器，确认下starrocks和mysql中的wait_timeout分别设置的多少

#13

starrocks是直连的，没有负载均衡。

sr的 wait_timeout
image

mysql 的 wait_timeout
image

#14

sr的集群机器到mysql的机器网络层面有防火墙吗？
jdbc 8.0试试这个
jdbc:mysql://xxx:3306/example_cdc_db?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&zeroDateTimeBehavior=convertToNull

#15

这个报错看起来是网络层面的问题，客户端（sr）发起了请求，但是服务端（mysql）把连接已经释放了

#16

jdbc_uri设置成"jdbc_uri"=“jdbc:mysql://10.40.1.201:3306/example_cdc_db?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&zeroDateTimeBehavior=convertToNull”，查询show databases from mysql_catalog_201;还是报错

CREATE EXTERNAL CATALOG mysql_catalog_201
PROPERTIES
(
“type”=“jdbc”,
“user”=“xxx”,
“password”=“xxx”,
“jdbc_uri”=“jdbc:mysql://xxx.xxx.xxx.xxx:3306/example_cdc_db?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&zeroDateTimeBehavior=convertToNull”,
“driver_url”=“file:///home/anweikai/mysql_catalog/mysql-connector-java-8.0.28.jar”,
“driver_class”=“com.mysql.cj.jdbc.Driver”
);

报错信息如下：

image
image1096×448 29.7 KB

我在sr 的fe节点可以直接连接mysql，截图如下：

image
image653×599 12.5 KB

#17

你们mysql是哪个版本

#18

防火墙的问题确认了吗？在sr的机器（fe和be节点）直接用mysql客户端连接mysql可以正常查询吗

#19

mysql 版本是 5.7.16

目前我们是3台（fe+be 混步）

只有1台fe可以直接用mysql客户端连接mysql，并正常查询，是需要所有节点都需要安装mysql？

#20

在所有节点上telnet下mysql的3306端口看下有问题吗

京ICP备2022026421号-1