Eclipse Community Forums: Eclipse Scout » MYSQL database connection getting closed intermittently

Home » Eclipse Projects » Eclipse Scout » MYSQL database connection getting closed intermittently (MYSQL database connection getting closed intermittently during database writing)

Show: Today's Messages :: Show Polls :: Message Navigator

MYSQL database connection getting closed intermittently [message #1751382]

Mon, 09 January 2017 15:47

Vijayachandra kumar ramamurthy

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

Ivan Motsch

Messages: 154
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?

Report message to a moderator

Re: MYSQL database connection getting closed intermittently [message #1751410 is a reply to message #1751393]

Tue, 10 January 2017 04:33

Vijayachandra kumar ramamurthy

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

Urs Beeli

Messages: 573
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?

Report message to a moderator

Re: MYSQL database connection getting closed intermittently [message #1751425 is a reply to message #1751413]

Tue, 10 January 2017 08:46

Ivan Motsch

Messages: 154
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.

Report message to a moderator

Re: MYSQL database connection getting closed intermittently [message #1751448 is a reply to message #1751425]

Tue, 10 January 2017 13:20

Vijayachandra kumar ramamurthy

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

Report message to a moderator

Re: MYSQL database connection getting closed intermittently [message #1752802 is a reply to message #1751448]

Fri, 27 January 2017 13:48

Vijayachandra kumar ramamurthy

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?

Report message to a moderator

Previous Topic:	How to connect two Scout Projects?
Next Topic:	[mars] RAP + DataSourceSecurityFilter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 05 19:02:40 GMT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter