Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » MYSQL database connection getting closed intermittently (MYSQL database connection getting closed intermittently during database writing)
MYSQL database connection getting closed intermittently [message #1751382] Mon, 09 January 2017 15:47 Go to next message
Vijayachandra kumar ramamurthy is currently offline Vijayachandra kumar ramamurthyFriend
Messages: 7
Registered: December 2015
Junior Member
Hi experts,
My application on Scout framework throws an processing error, since the mysql database connection thread is getting closed abruptly during writing data into the database.

My application parses around >4GB binary files and writes into the MYSQL database continuously, each record size would be 1500 bytes. But the MYSQL database connection automatically closed after writing around 130000 records. Later throws an error since trying to write data on closed connection.

I could not locate how to handle this issue, can I get your help here, please

The Console message:

Parsing Packet(Size 1456 ): 132840

!ENTRY org.eclipse.scout.rt.server 1 0 2017-01-09 19:58:09.448
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.dump(StatementProcessor.java:849)
SQL with binds:
INSERT IGNORE
INTO DDR.LR_ARRAY_ACOUSTIC_SENSOR(.................................)
VALUES (.................................................)

Parsing Packet(Size 1456 ): 132841

!ENTRY org.eclipse.scout.rt.server 1 0 2017-01-09 19:58:09.536
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.pool.ConnectionCloseThread.run(ConnectionCloseThread.java:33) close connection com.mysql.jdbc.JDBC4Connection@236b397d

!ENTRY org.eclipse.scout.rt.server 1 0 2017-01-09 19:58:09.580
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.dump(StatementProcessor.java:849)
SQL with binds:
INSERT IGNORE
INTO DDR.LR_ARRAY_ACOUSTIC_SENSOR(..................................)
VALUES (................................)

!ENTRY org.eclipse.scout.rt.shared 4 0 2017-01-09 20:01:02.095
!MESSAGE org.eclipse.scout.rt.shared.services.common.exceptionhandler.LogExceptionHandlerService.differentiatedLog(LogExceptionHandlerService.java:77) ProcessingStatus[ERROR code=0 SQL with binds:
INSERT IGNORE
INTO DDR.LR_ARRAY_ACOUSTIC_SENSOR(..................................)
VALUES (................................)
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.]
SQL with binds:
INSERT IGNORE
INSERT IGNORE
INTO DDR.LR_ARRAY_ACOUSTIC_SENSOR(..................................)
VALUES (................................)
!STACK 0
ProcessingException[ProcessingStatus[ERROR code=0 SQL with binds:
INSERT IGNORE
INTO DDR.LR_ARRAY_ACOUSTIC_SENSOR(..................................)
VALUES (................................)
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.]]
at org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.processModification(StatementProcessor.java:453)
at org.eclipse.scout.rt.server.services.common.jdbc.AbstractSqlService.insert(AbstractSqlService.java:741)
at org.eclipse.scout.rt.server.services.common.jdbc.SQL.insert(SQL.java:133)
at com.vimoon.rdatool.server.ui.forms.GenerateDatabaseService.addPacket(GenerateDatabaseService.java:109)
at com.vimoon.rdatool.shared.DDRParser.parseFile(DDRParser.java:496)
at com.vimoon.rdatool.server.ui.forms.GenerateDatabaseService.store(GenerateDatabaseService.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.scout.service.ServiceUtility.invoke(ServiceUtility.java:172)
at org.eclipse.scout.rt.server.DefaultTransactionDelegate.invokeImpl(DefaultTransactionDelegate.java:208)
at org.eclipse.scout.rt.server.DefaultTransactionDelegate.invoke(DefaultTransactionDelegate.java:97)
at org.eclipse.scout.rt.server.ServiceTunnelServlet.runServerJobTransactionWithDelegate(ServiceTunnelServlet.java:417)
at org.eclipse.scout.rt.server.ServiceTunnelServlet.runServerJobTransaction(ServiceTunnelServlet.java:404)
at org.eclipse.scout.rt.server.ServiceTunnelServlet$RemoteServiceJob.runTransaction(ServiceTunnelServlet.java:441)
at org.eclipse.scout.rt.server.ServerJob.runTransactionWrapper(ServerJob.java:223)
at org.eclipse.scout.rt.server.ServerJob$1.run(ServerJob.java:179)
at org.eclipse.scout.rt.server.ServerJob$1.run(ServerJob.java:1)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Unknown Source)
at org.eclipse.scout.rt.server.ServerJob.run(ServerJob.java:174)
at org.eclipse.scout.commons.job.JobEx.runNow(JobEx.java:51)
at org.eclipse.scout.rt.server.ServerJob.runNow(ServerJob.java:166)
at org.eclipse.scout.rt.server.ServiceTunnelServlet.doPost(ServiceTunnelServlet.java:343)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.scout.rt.server.commons.servletfilter.HttpServletEx.access$0(HttpServletEx.java:1)
at org.eclipse.scout.rt.server.commons.servletfilter.HttpServletEx$1.service(HttpServletEx.java:38)
at org.eclipse.scout.rt.server.commons.internal.FilterChainImpl.doFilter(FilterChainImpl.java:44)
at org.eclipse.scout.rt.server.SoapWsseJaasFilter$2.run(SoapWsseJaasFilter.java:198)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Unknown Source)
at org.eclipse.scout.rt.server.SoapWsseJaasFilter.continueChainWithPrincipal(SoapWsseJaasFilter.java:191)
at org.eclipse.scout.rt.server.SoapWsseJaasFilter.doFilter(SoapWsseJaasFilter.java:135)
at org.eclipse.scout.rt.server.commons.internal.FilterChainImpl.doFilter(FilterChainImpl.java:41)
at org.eclipse.scout.rt.server.commons.servletfilter.ServletFilterDelegate.delegateServiceMethod(ServletFilterDelegate.java:60)
at org.eclipse.scout.rt.server.commons.servletfilter.HttpServletEx.service(HttpServletEx.java:35)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.HttpServiceRuntimeImpl$LegacyServlet.service(HttpServiceRuntimeImpl.java:1232)
at org.eclipse.equinox.http.servlet.internal.registration.EndpointRegistration.service(EndpointRegistration.java:153)
at org.eclipse.equinox.http.servlet.internal.servlet.ResponseStateHandler.processRequest(ResponseStateHandler.java:63)
at org.eclipse.equinox.http.servlet.internal.context.DispatchTargets.doDispatch(DispatchTargets.java:98)
at org.eclipse.equinox.http.servlet.internal.HttpServiceRuntimeImpl.doDispatch(HttpServiceRuntimeImpl.java:372)
at org.eclipse.equinox.http.servlet.internal.servlet.ProxyServlet.service(ProxyServlet.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:357)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:221)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.Server.handle(Server.java:499)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1206)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1198)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4183)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4149)
at org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.PreparedStatementCache.getPreparedStatement(PreparedStatementCache.java:56)
at org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.processModification(StatementProcessor.java:435)
... 62 more

!ENTRY org.eclipse.scout.rt.server 4 0 2017-01-09 20:01:02.114
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.AbstractSqlService$SqlTransactionMember.rollback(AbstractSqlService.java:901) null
No operations allowed after connection closed.
!STACK 0
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1206)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1198)
at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4690)
at org.eclipse.scout.rt.server.services.common.jdbc.AbstractSqlService$SqlTransactionMember.rollback(AbstractSqlService.java:897)
at org.eclipse.scout.rt.server.transaction.BasicTransaction.rollback(BasicTransaction.java:175)
at org.eclipse.scout.rt.server.ServerJob.runTransactionWrapper(ServerJob.java:268)
at org.eclipse.scout.rt.server.ServerJob$1.run(ServerJob.java:179)
at org.eclipse.scout.rt.server.ServerJob$1.run(ServerJob.java:1)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Unknown Source)
at org.eclipse.scout.rt.server.ServerJob.run(ServerJob.java:174)
at org.eclipse.scout.commons.job.JobEx.runNow(JobEx.java:51)
at org.eclipse.scout.rt.server.ServerJob.runNow(ServerJob.java:166)
at org.eclipse.scout.rt.server.ServiceTunnelServlet.doPost(ServiceTunnelServlet.java:343)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.scout.rt.server.commons.servletfilter.HttpServletEx.access$0(HttpServletEx.java:1)
at org.eclipse.scout.rt.server.commons.servletfilter.HttpServletEx$1.service(HttpServletEx.java:38)
at org.eclipse.scout.rt.server.commons.internal.FilterChainImpl.doFilter(FilterChainImpl.java:44)
at org.eclipse.scout.rt.server.SoapWsseJaasFilter$2.run(SoapWsseJaasFilter.java:198)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Unknown Source)
at org.eclipse.scout.rt.server.SoapWsseJaasFilter.continueChainWithPrincipal(SoapWsseJaasFilter.java:191)
at org.eclipse.scout.rt.server.SoapWsseJaasFilter.doFilter(SoapWsseJaasFilter.java:135)
at org.eclipse.scout.rt.server.commons.internal.FilterChainImpl.doFilter(FilterChainImpl.java:41)
at org.eclipse.scout.rt.server.commons.servletfilter.ServletFilterDelegate.delegateServiceMethod(ServletFilterDelegate.java:60)
at org.eclipse.scout.rt.server.commons.servletfilter.HttpServletEx.service(HttpServletEx.java:35)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.HttpServiceRuntimeImpl$LegacyServlet.service(HttpServiceRuntimeImpl.java:1232)
at org.eclipse.equinox.http.servlet.internal.registration.EndpointRegistration.service(EndpointRegistration.java:153)
at org.eclipse.equinox.http.servlet.internal.servlet.ResponseStateHandler.processRequest(ResponseStateHandler.java:63)
at org.eclipse.equinox.http.servlet.internal.context.DispatchTargets.doDispatch(DispatchTargets.java:98)
at org.eclipse.equinox.http.servlet.internal.HttpServiceRuntimeImpl.doDispatch(HttpServiceRuntimeImpl.java:372)
at org.eclipse.equinox.http.servlet.internal.servlet.ProxyServlet.service(ProxyServlet.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:357)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:221)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.Server.handle(Server.java:499)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Unknown Source)

!ENTRY org.eclipse.scout.rt.server 2 0 2017-01-09 20:01:02.125
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.pool.SqlConnectionPool.releaseConnection(SqlConnectionPool.java:203) closing dirty connection: com.mysql.jdbc.JDBC4Connection@236b397d

[Updated on: Mon, 09 January 2017 15:58]

Report message to a moderator

Re: MYSQL database connection getting closed intermittently [message #1751393 is a reply to message #1751382] Mon, 09 January 2017 16:59 Go to previous messageGo to next message
Ivan Motsch is currently offline Ivan MotschFriend
Messages: 123
Registered: March 2010
Senior Member
are you using auto-commit?

What is the code you are trying to execute. Is it a batch insert or manually written sql statements?
Re: MYSQL database connection getting closed intermittently [message #1751410 is a reply to message #1751393] Tue, 10 January 2017 04:33 Go to previous messageGo to next message
Vijayachandra kumar ramamurthy is currently offline Vijayachandra kumar ramamurthyFriend
Messages: 7
Registered: December 2015
Junior Member
Thanks for your response Ivan.

>>>>are you using auto-commit?
I hope so; am just using the scout framework and its classes(com.bsiag.scout.rt.server.jdbc.AbstractMySqlSqlService)/functions to configure the MYSQL/JDBC connection and passing SQL Queries to MYSQL server. I am neither explicitly starting/closing JDBC connection nor calling 'commit' manually.

>>>>What is the code you are trying to execute. Is it a batch insert or manually written sql statements?
I am passing SQL queries one by one manually through the functions from the class com.bsiag.scout.rt.server.jdbc.AbstractMySqlSqlService/AbstractSqlService: int insert(String s, Object... bindBases) throws ProcessingException; the other functions used are Object[][] select(String s, Object... bindBases) throws ProcessingException, public void selectInto(String s, Object... bindBases) throws ProcessingException, int update(String s, Object... bindBases) throws ProcessingException, int delete(String s, Object... bindBases) throws ProcessingException, etc

what are the possible situations/conditions the JDBC connection thread would be closed automatically?
How to manually invoke/close the JDBC connection, commit/rollback in scout( / disable the auto-commit) to MYSQL server?
How to capture the jdbc connection/thread close event and restart from scout?
How to configure for batch mode of data passing to MYSQL server?
Is it possible to write/read high volume of data(>4GB) at a instant to/from MYSQL from scout?

[Updated on: Tue, 10 January 2017 04:48]

Report message to a moderator

Re: MYSQL database connection getting closed intermittently [message #1751413 is a reply to message #1751410] Tue, 10 January 2017 07:12 Go to previous messageGo to next message
Urs Beeli is currently offline Urs BeeliFriend
Messages: 566
Registered: October 2012
Location: Bern, Switzerland
Senior Member
I'm not familiar with MySql and not sure how the Scout SQL Service handles transaction boundaries (we're using Spring/Hibernate/JPA in our backend).

However, the DB we are using has a transaction timeout of 120 seconds. If anything we do in a single transaction takes longer than 2 minutes, the transaction rolls back and an exception is thrown.

Since you are writing a lot of data, is it possible that you're doing it all in a single transaction and run into a timeout?
Re: MYSQL database connection getting closed intermittently [message #1751425 is a reply to message #1751413] Tue, 10 January 2017 08:46 Go to previous messageGo to next message
Ivan Motsch is currently offline Ivan MotschFriend
Messages: 123
Registered: March 2010
Senior Member
commit: you can call SQL.commit(). SQL.java is a convenience wrapper calling the SQLService.

running into timeout: yes, this is possible.
One way is to choose a longer timeout another is doing multiple inserts using split blocks.

in your insert batch make a loop and call commit for example after every 1000 inserts.

Re: MYSQL database connection getting closed intermittently [message #1751448 is a reply to message #1751425] Tue, 10 January 2017 13:20 Go to previous messageGo to next message
Vijayachandra kumar ramamurthy is currently offline Vijayachandra kumar ramamurthyFriend
Messages: 7
Registered: December 2015
Junior Member
Thanks to both for your reply/suggestions.

Though it is high volume of data, am passing only 1500 bytes in single query, hence am not sure about expiry of timeout, may be the connection timeout!!!

I am looking for the flexibility using scout wrapper for SQL service, like increasing the timeout, increasing the data cache size, manually starting/closing/commiting the jdbc connection.
Let me explore further about the scout wrapper: com.bsiag.scout.rt.server.jdbc.AbstractMySqlSqlService and org.eclipse.scout.rt.server.services.common.jdbc.AbstractSqlService

Thanks Lot
Re: MYSQL database connection getting closed intermittently [message #1752802 is a reply to message #1751448] Fri, 27 January 2017 13:48 Go to previous message
Vijayachandra kumar ramamurthy is currently offline Vijayachandra kumar ramamurthyFriend
Messages: 7
Registered: December 2015
Junior Member
The parameter "JdbcPoolConnectionBusyTimeout" is defined with the value 21600000ms in AbstractSQLService; the timer expiry causes the closing of JDBCPool connection thread, and as well the auto-commit is disabled here.

I am searching for the solution to generate the event on "JdbcPoolConnectionBusyTimeout" timer expiry and re-lease/getting-back the connection from the jdbc pool in scout. can anyone help me plz?
Previous Topic:[neon] No bean returned
Next Topic:[NEON] Demo project
Goto Forum:
  


Current Time: Thu Sep 21 12:06:37 GMT 2017

Powered by FUDForum. Page generated in 0.03269 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software