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.4k stars 4.86k forks source link

JDBC连接TDengine,批量插入数据报错TDengine Error: Unable to establish connection #4957

Closed aiword closed 3 years ago

aiword commented 3 years ago

Bug Description java使用jdbc连接TDengine时执行插入语句，单条插入以及10条批量插入正常执行也能正常入库。当把批量数据提升到100条以上时提交执行sql出现等待情况，等待时间结束报错TDengine Error: Unable to establish connection。

To Reproduce Steps to reproduce the behavior:

  1. 下面是我的jdbc连接方式 Class.forName("com.taosdata.jdbc.TSDBDriver"); String jdbcUrl = url; Properties connProps = new Properties(); connProps.setProperty(TSDBDriver.PROPERTY_KEY_USER, username); connProps.setProperty(TSDBDriver.PROPERTY_KEY_PASSWORD, password); connProps.setProperty(TSDBDriver.PROPERTY_KEY_CHARSET, "UTF-8"); connProps.setProperty(TSDBDriver.PROPERTY_KEY_LOCALE, "en_US.UTF-8"); connProps.setProperty(TSDBDriver.PROPERTY_KEY_TIME_ZONE, "UTC-8"); this.connection = DriverManager.getConnection(jdbcUrl, connProps);

    connection.createStatement().execute(sql);

  2. 下面是我的sql String sql = "insert into t (ts, speed, humidity) values (1611060613710,1,1.0)(1611060614710,1,1.0)(1611060615710,1,1.0)(1611060616710,1,1.0)(1611060617710,1,1.0)(1611060618710,1,1.0)(1611060619710,1,1.0)(1611060620710,1,1.0)(1611060621710,1,1.0)(1611060622710,1,1.0)(1611060623710,1,1.0)(1611060624710,1,1.0)(1611060625710,1,1.0)(1611060626710,1,1.0)(1611060627710,1,1.0)(1611060628710,1,1.0)(1611060629710,1,1.0)(1611060630710,1,1.0)(1611060631710,1,1.0)(1611060632710,1,1.0)(1611060633710,1,1.0)(1611060634710,1,1.0)(1611060635710,1,1.0)(1611060636710,1,1.0)(1611060637710,1,1.0)(1611060638710,1,1.0)(1611060639710,1,1.0)(1611060640710,1,1.0)(1611060641710,1,1.0)(1611060642710,1,1.0)(1611060643710,1,1.0)(1611060644710,1,1.0)(1611060645710,1,1.0)(1611060646710,1,1.0)(1611060647710,1,1.0)(1611060648710,1,1.0)(1611060649710,1,1.0)(1611060650710,1,1.0)(1611060651710,1,1.0)(1611060652710,1,1.0)(1611060653710,1,1.0)(1611060654710,1,1.0)(1611060655710,1,1.0)(1611060656710,1,1.0)(1611060657710,1,1.0)(1611060658710,1,1.0)(1611060659710,1,1.0)(1611060660710,1,1.0)(1611060661710,1,1.0)(1611060662710,1,1.0)(1611060663710,1,1.0)(1611060664710,1,1.0)(1611060665710,1,1.0)(1611060666710,1,1.0)(1611060667710,1,1.0)(1611060668710,1,1.0)(1611060669710,1,1.0)(1611060670710,1,1.0)(1611060671710,1,1.0)(1611060672710,1,1.0)(1611060673710,1,1.0)(1611060674710,1,1.0)(1611060675710,1,1.0)(1611060676710,1,1.0)(1611060677710,1,1.0)(1611060678710,1,1.0)(1611060679710,1,1.0)(1611060680710,1,1.0)(1611060681710,1,1.0)(1611060682710,1,1.0)(1611060683710,1,1.0)(1611060684710,1,1.0)(1611060685710,1,1.0)(1611060686710,1,1.0)(1611060687710,1,1.0)(1611060688710,1,1.0)(1611060689710,1,1.0)(1611060690710,1,1.0)(1611060691710,1,1.0)(1611060692710,1,1.0)(1611060693710,1,1.0)(1611060694710,1,1.0)(1611060695710,1,1.0)(1611060696710,1,1.0)(1611060697710,1,1.0)(1611060698710,1,1.0)(1611060699710,1,1.0)(1611060700710,1,1.0)(1611060701710,1,1.0)(1611060702710,1,1.0)(1611060703710,1,1.0)(1611060704710,1,1.0)(1611060705710,1,1.0)(1611060706710,1,1.0)(1611060707710,1,1.0)(1611060708710,1,1.0)(1611060709710,1,1.0)(1611060710710,1,1.0)(1611060711710,1,1.0)"; 建表sql create table if not exists t(ts timestamp, speed int, humidity float);

  3. See error 出现错误信息为 Exception in thread "main" java.sql.SQLException: TDengine Error: Unable to establish connection at com.taosdata.jdbc.TSDBJNIConnector.executeQuery(TSDBJNIConnector.java:135) at com.taosdata.jdbc.TSDBStatement.execute(TSDBStatement.java:178) at com.dj.flink.tools.TDengineUtil.save(TDengineUtil.java:151) at com.test.TaosTest.main(TaosTest.java:66) Expected Behavior 这是否是我配置问题？数据库安装为默认安装，修改了FQDN连接名等。在windows客户端能正常连通服务端

Screenshots

Environment (please complete the following information):

Additional Context 最初发现该问题是在使用springboot2.x mybatis连接TDengine时出现，未找到解决方案，使用JDBC手动连接测试报了同样的错误。以下为springboot方面提供的错误信息. org.springframework.jdbc.UncategorizedSQLException:

Error updating database. Cause: java.sql.SQLException: TDengine Error: Unable to establish connection

The error may exist in file [\com\dj\flink\mapper\tdengine\TaosMapper.xml]

The error may involve com.dj.flink.dao.tdengine.TaosMapper.batchInsert-Inline

The error occurred while setting parameters

SQL: insert into t (ts, speed, humidity) values (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?)

Cause: java.sql.SQLException: TDengine Error: Unable to establish connection

; uncategorized SQLException; SQL state []; error code [-2147483637]; TDengine Error: Unable to establish connection; nested exception is java.sql.SQLException: TDengine Error: 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.$Proxy136.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) at com.sun.proxy.$Proxy139.batchInsert(Unknown Source) at com.dj.flink.service.impl.TaosDataServiceImpl.save(TaosDataServiceImpl.java:48) at com.dj.flink.rest.TestRestController.taosTest100(TestRestController.java:141) 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:105) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:893) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:798) 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.servlet.resource.ResourceUrlEncodingFilter.doFilter(ResourceUrlEncodingFilter.java:64) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:215) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271) 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.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:94) 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:541) 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:367) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1598) 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: Unable to establish connection at com.taosdata.jdbc.TSDBJNIConnector.executeQuery(TSDBJNIConnector.java:135) at com.taosdata.jdbc.TSDBStatement.executeUpdate(TSDBStatement.java:100) at com.taosdata.jdbc.SavedPreparedStatement.executeSql(SavedPreparedStatement.java:450) at com.taosdata.jdbc.SavedPreparedStatement.executeBatchInternal(SavedPreparedStatement.java:259) at com.taosdata.jdbc.TSDBPreparedStatement.execute(TSDBPreparedStatement.java:304) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) 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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) at com.sun.proxy.$Proxy190.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) 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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy188.update(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) 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) ... 98 more

aiword commented 3 years ago

附上debugFlag为135之后的跟踪日志。 taoslog.0文件是客户端当次操作错误日志。 taoslog0.0、taosdlog.0文件是服务端开启跟踪后的当次操作日志。 taosdlog.0.txt taoslog.0.txt taoslog0.0.txt

sangshuduo commented 3 years ago

请在 Windows 中把 taos.cfg 中增加 debugFlag 135，再跑同样的步骤，然后把 taoslog 上传

aiword commented 3 years ago

补充文件： Windows 中把 taos.cfg 中增加 debugFlag 135后报错日志

aiword commented 3 years ago

taoslog.0.txt

sangshuduo commented 3 years ago

似乎单纯是网络传输不稳定，长一点的 SQL插入命令的重试多次后失败，能否用工具测试下网络吞吐量和稳定性？

aiword commented 3 years ago

似乎单纯是网络传输不稳定，长一点的SQL插入命令的重试多次后失败，能否用工具测试下网络的断裂和稳定性？

后续我会使用工具测试跟进一下看看，现在使用RESTful API能正常写入，测试4万一批可以写入成功。感觉应该不是网络的问题，使用的是同局域网内虚拟机。或者有什么配置项会对此有影响？我暂时没发现有相关的配置项呢

sangshuduo commented 3 years ago

你试试插更多条，说不定就可以了，因为TDengine insert在数据包小于15K的时候会用UDP，大于才用TCP，RESTful 走TCP，所以有一种可能是网络UDP不稳定

aiword commented 3 years ago

你试试插更多条，说不定就可以了，因为TDengine insert在数据包小于15K的时候会用UDP，大于才用TCP，RESTful 走TCP，所以有一种可能是网络UDP不稳定

真是个悲惨的经历...确实是UDP的问题，将每批次数据量提上来就可以了。有没有什么方式能让TDengine的数据操作请求全都走TCP呢？因为我业务的数据大小不可控，存在少数的情况呢

sangshuduo commented 3 years ago

这个确实有考虑可以用配置选项来使所有通信都用TCP，不过暂时还没有，请先看是否可以内部解决通信稳定性问题，问题我先关闭了