search

taosdata / TDengine

High-performance, scalable time-series database designed for Industrial IoT (IIoT) scenarios
https://tdengine.com
GNU Affero General Public License v3.0
23.39k stars 4.86k forks source link

使用官方提供的springbootdemo连接服务端报ERROR (8000000b): Unable to establish connection #7970

Closed Memorry0 closed 3 years ago

Memorry0 commented 3 years ago

基本描述： 服务端：2.2.0.0（centOS虚拟机上的单机版） maven：2.0.34 连接方式：JDBC-JNI

使用windows客户端Taos Shell输入taos能直接连上并执行语句，使用TDengineGUI也可以正常使用，但使用springboot+mybatis的demo，也就是那个weather表的CURD（本地有改过库、表名）在IDE运行时，能正常执行建表建库语句，但只要涉及到查询、插入等语句，则会报TDengine ERROR (8000000b): Unable to establish connection。 SQL语句能正常在shell中执行。 来寻求解决方案。

错误日志日下： Whitelabel Error Page This application has no explicit mapping for /error, so you are seeing this as a fallback.

Thu Sep 16 18:50:29 CST 2021 There was an unexpected error (type=Internal Server Error, status=500).

Error querying database. Cause: java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection ### The error may exist in file [F:\ChromeDownloads\springbootdemo\target\classes\mapper\tdengine\WeatherMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: select collect_time, insert_time, x_axis,y_axis,z_axis, temperature,singal_strength,device_code from animal.tag100000100018935 order by collect_time desc ### Cause: java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection ; uncategorized SQLException; SQL state []; error code [-2147483637]; TDengine ERROR (8000000b): Unable to establish connection; nested exception is java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection

org.springframework.jdbc.UncategorizedSQLException:

Error querying database. Cause: java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection

The error may exist in file [F:\ChromeDownloads\springbootdemo\target\classes\mapper\tdengine\WeatherMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: select collect_time, insert_time, x_axis,y_axis,z_axis, temperature,singal_strength,device_code from animal.tag100000100018935 order by collect_time desc

Cause: java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection

; uncategorized SQLException; SQL state []; error code [-2147483637]; TDengine ERROR (8000000b): Unable to establish connection; nested exception is java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) at com.sun.proxy.$Proxy71.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93) at com.sun.proxy.$Proxy75.select(Unknown Source) at com.taosdata.jdbc.springbootdemo.service.WeatherService.query(WeatherService.java:26) at com.taosdata.jdbc.springbootdemo.controller.WeatherController.queryWeather(WeatherController.java:34) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:888) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1579) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLException: TDengine ERROR (8000000b): Unable to establish connection at com.taosdata.jdbc.TSDBError.createSQLException(TSDBError.java:75) at com.taosdata.jdbc.TSDBJNIConnector.executeQuery(TSDBJNIConnector.java:123) at com.taosdata.jdbc.TSDBStatement.execute(TSDBStatement.java:85) at com.taosdata.jdbc.TSDBPreparedStatement.execute(TSDBPreparedStatement.java:266) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ... 58 more

Memorry0 commented 3 years ago

image

Memorry0 commented 3 years ago

我又尝试了完全使用原版的demo（仅修改yml的连接地址）后，问题依旧，建库建表可以，插入查询等CURD不行。

sangshuduo commented 3 years ago

通常是 TCP 没开放导致的

Memorry0 commented 3 years ago

通常是 TCP 没开放导致的 我是在centOS7上跑的单机server，防火墙已经关了的。

image

sangshuduo commented 3 years ago

Windows 上用 IP 地址吗？用 FQDN ，在 Windows/system32/drivers/etc/hosts 里加上 FQDN 和 IP 地址的映射试试

sangshuduo commented 3 years ago

修改了 taos.cfg 里的 FQDN 需要修改 dnodeEps.json 或者删除旧数据重启 taosd

Memorry0 commented 3 years ago

服务端：直接卸载了，然后删掉了usr/local/taos目录和var/lib的目录，然后重装了。 客户端配置如下： image

image

配好后，启动springboot服务执行写入方法，报 JNI connection is NULL了。

sangshuduo commented 3 years ago

firstEp vm.centos.org:6030

Memorry0 commented 3 years ago

image 配置好后，windows shell无法使用了…… 你如果方便，能否在物联网大数据13群里直接沟通下，这样互相回复效率不高。

Memorry0 commented 3 years ago

问题已经解决，我把详情过程描述一下，供后来人参考。 我是在centos上安装的单机版的2.2.0.0，windows客户端也是一样的版本，maven是2.0.32（官网有写版本匹配），这个自己注意下。 防火墙关掉或开放6030-6042的端口是前提，如果都还没到建库建表就连不上，建议看下这个：https://www.taosdata.com/cn/documentation/faq 如果出现能正常建库建表但无法写入查询数据的情况，建议逐个做下列这些操作： 服务端： 1、停掉服务：systemctl stop taosd； 2、删掉/var/lib/taos下的所有文件夹（这里存的数据库文件，如果你是生产环境，可以不做这步，但后面可能会无法启动taosd）； 2、修改host，新增"192.168.1.225 vm.centos.org"，前面的IP是我的服务器本机地址，后面的域名你自己任意写，只是为了做转发用； 3、修改/usr/local/taos/cfg/taos.cfg，将firstEp配置为"vm.centos.org:6030"，fqdn配置为"vm.centos.org"，注意这两个都不能注释，域名就是上一步配置的； 4、启动服务：systemctl start taosd

客户端(windows客户端或springboot服务)： 1、修改host，新增"192.168.1.225 vm.centos.org" 2、修改tao.cfg，只需要改firstEp为vm.centos.org:6030就行了。 3、 springboot的tdengine的连接地址也需要换成vm.centos.org:6030。

我看了issue历史，遇到无法连接问题基本就三类： 1、客户端和服务端之间网络不通； 2、客户端、服务端和jdbc驱动之间的版本不匹配； 3、客户端和服务端之间cfg配置和hosts配置。

hosts配置一定不能忘，这块我觉得合理性有待商榷。

dxy9 commented 3 years ago

可以切换到restfullDriver ,瞬间解决

driver-class-name: com.taosdata.jdbc.rs.RestfulDriver
url: jdbc:TAOS-RS://ip:6041/mp_test?timezone=UTC-8&charset=UTF-8&locale=en_US.UTF-8
Jackhzf commented 2 years ago

jdbc:TAOS-RS

使用此链接也连不上，我查看了一下6041没有被tdengine占用