An I/O error occured while sending to the backend
Hi,
I have developed an application using the jdbc driver and
connecting to a postgresql database .
The application mainly does the following two things：
① read data from the CSV file and insert it into the database
② perform a database query
In my application, the above ① and ② are executed asynchronously.
Everything was working fine until I increase the amount of CSV data to more than 1000.
Randomly they receive an error, the stack trace of which is below.
com.nec.neosarf.exception.NeoSarfException: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
at com.nec.neosarf.services.core.controller.DutiesDao.DutiesDaoError(DutiesDao.java:2037)
at com.nec.neosarf.services.core.controller.DutiesDao.SelectData(DutiesDao.java:459)
at com.nec.neosarf.services.core.controller.DutiesDao.SelectData(DutiesDao.java:335)
at com.nec.neosarf.services.core.controller.CoreConductor.ConductorLv3_DataAccess(CoreConductor.java:2062)
at com.nec.neosarf.services.core.controller.CoreConductor.ConductorCore(CoreConductor.java:794)
at com.nec.neosarf.services.core.controller.CoreConductor.ConductorRun(CoreConductor.java:623)
at com.nec.neosarf.services.core.controller.CoreConductor.Conductor(CoreConductor.java:356)
at com.nec.neosarf.services.core.controller.CoreBody.ConductorCreation(CoreBody.java:4817)
at com.nec.neosarf.services.core.controller.CoreBody.ActionDirectorInteface(CoreBody.java:2783)
at com.nec.neosarf.services.core.controller.CoreBody.AjaxCheck(CoreBody.java:2465)
at com.nec.neosarf.services.core.controller.CoreBody.ExecutionPageRun(CoreBody.java:1693)
at com.nec.neosarf.services.core.servlet.NeoSarf.coreServlet(NeoSarf.java:386)
at com.nec.neosarf.services.core.servlet.NeoSarf.service(NeoSarf.java:139)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:711)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:459)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:353)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:313)
at com.nec.neosarf.services.core.filter.dynamic.NeoSarfFilterChain.doFilter(NeoSarfFilterChain.java:36)
at com.nec.neosarf.services.debugger.filter.DebugSynchronizedFilter.doFilter(DebugSynchronizedFilter.java:72)
at com.nec.neosarf.services.core.filter.dynamic.NeoSarfFilterChain.doFilter(NeoSarfFilterChain.java:34)
at com.nec.neosarf.services.core.filter.dynamic.DynamicRequestFilter.executeServlet(DynamicRequestFilter.java:93)
at com.nec.neosarf.services.core.filter.RequestFilter.doFilter(RequestFilter.java:450)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:359)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1735)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:382)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:119)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:121)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:121)
at com.nec.neosarf.db.DaoBase.selectReal(DaoBase.java:393)
at com.nec.neosarf.db.DaoBase.selectDuties(DaoBase.java:328)
at com.nec.neosarf.services.core.controller.DutiesDao.SelectData(DutiesDao.java:372)
... 42 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/java.net.SocketInputStream.socketRead0(Native Method)
at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
at java.base/sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:478)
at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:472)
at java.base/sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:70)
at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1454)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1065)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.receiveChar(PGStream.java:453)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2119)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
... 51 more
Thanks for any help.
On Wed, 2022-11-16 at 13:04 +0800, gzh wrote:
I have developed an application using the jdbc driver and
connecting to a postgresql database .
The application mainly does the following two things：
① read data from the CSV file and insert it into the database
② perform a database query
In my application, the above ① and ② are executed asynchronously.
Everything was working fine until I increase the amount of CSV data to more than 1000.
Randomly they receive an error, the stack trace of which is below.
Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/java.net.SocketInputStream.socketRead0(Native Method)
at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
at java.base/sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:478)
at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:472)
at java.base/sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:70)
at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1454)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1065)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
Well, set a longer socket timeout if you need to run long SQL statements, or tune
those statements to be faster.
https://jdbc.postgresql.org/documentation/use/#connection-parameters has more
information about "socketTimeout".
Yours,
Laurenz Albe
Thank you very much for your advice.
What I don't understand is that there is no problem when executing ① alone or ② alone,
the error occurs when ① and ② are executed together .
It works well when i let the application sleep after ① for 10 seconds before executing ② .
Is ② affected by ① ? Could you tell me why?
At 2022-11-16 14:57:41, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
Show quoted text
On Wed, 2022-11-16 at 13:04 +0800, gzh wrote:
I have developed an application using the jdbc driver and
connecting to a postgresql database .
The application mainly does the following two things：
① read data from the CSV file and insert it into the database
② perform a database query
In my application, the above ① and ② are executed asynchronously.
Everything was working fine until I increase the amount of CSV data to more than 1000.
Randomly they receive an error, the stack trace of which is below.Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/java.net.SocketInputStream.socketRead0(Native Method)
at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
at java.base/sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:478)
at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:472)
at java.base/sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:70)
at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1454)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1065)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)Well, set a longer socket timeout if you need to run long SQL statements, or tune
those statements to be faster.https://jdbc.postgresql.org/documentation/use/#connection-parameters has more
information about "socketTimeout".Yours,
Laurenz Albe
On 11/16/22 00:37, gzh wrote:
Thank you very much for your advice.
What I don't understand is that there is no problem when executing
① alone or ② alone,
the error occurs when ① and ② are executed together .
It works well when i let the application sleep after ① for 10 seconds
before executing ② .
Is this true for large numbers of lines in you csv? Originally you said
something like 'adding 1000 lines' broke your app. Are you using
CopyManager?
Hi, Rob
Is this true for large numbers of lines in you csv? Originally you said
something like 'adding 1000 lines' broke your app. Are you using
CopyManager?
Thank you for your reply. I didn't use CopyManager.
②'s query statement query the data inserted by ①, and a large amount of data inserted will cause performance problem.
I misunderstood, it's not ① that affects ②, it should be that ②'s query statement has a performance problem.
The Encrypt function is used in ②'s query statement to encrypt the query result, and improper use of this function leads to poor performance.
Sometimes 2~4 seconds to return the result, sometimes 7~19 seconds to return the result.
We optimized the query statement to reduce the query time to less than 1 second and now the application works well.
At 2022-11-16 14:57:41, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
Show quoted text
On Wed, 2022-11-16 at 13:04 +0800, gzh wrote:
I have developed an application using the jdbc driver and
connecting to a postgresql database .
The application mainly does the following two things：
① read data from the CSV file and insert it into the database
② perform a database query
In my application, the above ① and ② are executed asynchronously.
Everything was working fine until I increase the amount of CSV data to more than 1000.
Randomly they receive an error, the stack trace of which is below.Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/java.net.SocketInputStream.socketRead0(Native Method)
at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
at java.base/sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:478)
at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:472)
at java.base/sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:70)
at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1454)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1065)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)Well, set a longer socket timeout if you need to run long SQL statements, or tune
those statements to be faster.https://jdbc.postgresql.org/documentation/use/#connection-parameters has more
information about "socketTimeout".Yours,
Laurenz Albe