RFE: Add signal handling for restarting DB connections

[jamoore]

Some portions of the server like DB connections it would be good to have "refreshable" (possibly re-reading the configuration). For example, database connections can hang causing serious issues.

[jmoore]

Moving to current sprint to evaluate feasibility.

[jmoore]

By adding a call to datasource.close() in the SelfCorrectingDataSource I'm able to get this phenotype:

# Start server and login normally
~/git/dist $ bin/omero -s root@localhost -C login
Password:
Created session 74745ed9-6938-48e6-8244-1b27302f75ca (root@localhost:4064). Idle timeout: 10.0 min. Current group: system


# Lock the session table in another console via "lock table session in access exclusive mode;"
# Console hangs as "Password:" until the signal is sent, which appears as an InternalException
~/git/dist $ bin/omero -s root@localhost -C login
Password:
InternalException: Failed to connect: exception ::Glacier2::CannotCreateSessionException
{
    reason =  Wrapped Exception: (org.springframework.transaction.TransactionSystemException):
Could not roll back JDBC transaction; nested exception is java.sql.SQLException: connection is closed
}

# __without restarting the server__, user tries to login again
# which succeeds.
~/git/dist $ bin/omero -s root@localhost -C login
Password:
Created session 4032371b-597a-4fa6-a1c5-f529883f1439 (root@localhost:4064). Idle timeout: 10.0 min. Current group: system
~/git/dist $ 

Acceptions seen in the log include:

Caused by: org.hibernate.exception.JDBCConnectionException: could not load an entity: [ome.model.meta.Session#7443]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.loader.Loader.loadEntity(Loader.java:1957)
        at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86)
        at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:76)
        at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3270)
        at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:496)
        at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:477)
        at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:227)
        at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:147)
        at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:1082)
        at org.hibernate.impl.SessionImpl.immediateLoad(SessionImpl.java:1020)
        at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:176)
        at org.hibernate.Hibernate.initialize(Hibernate.java:428)
        at ome.logic.QueryImpl$1.doInHibernate(QueryImpl.java:174)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
        ... 27 more
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:220)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
        at sun.reflect.GeneratedMethodAccessor16.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:63)
        at $Proxy56.executeQuery(Unknown Source)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
        at org.hibernate.loader.Loader.doQuery(Loader.java:718)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
        at org.hibernate.loader.Loader.loadEntity(Loader.java:1953)
        ... 40 more
Caused by: java.net.SocketException: Socket closed
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
        at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1182)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
        ... 53 more

and

2011-05-10 17:12:58,803 WARN  [        ome.services.util.ServiceHandler] (l.Server-0) Unknown exception thrown.
org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is java.sql.SQLException: connection is closed
        at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:286)        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)        at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:430)        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:112)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)        at ome.tools.hibernate.ProxyCleanupFilter$Interceptor.invoke(ProxyCleanupFilter.java:231)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)        at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:111)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy64.doWork(Unknown Source)
        at ome.services.util.Executor$Impl.execute(Executor.java:369)
        at ome.services.sessions.SessionManagerImpl.createSession(SessionManagerImpl.java:296)
        at ome.services.sessions.SessionManagerImpl.createWithAgent(SessionManagerImpl.java:248)        at ome.services.blitz.fire.SessionManagerI.create(SessionManagerI.java:173)
        at Glacier2._SessionManagerDisp.___create(_SessionManagerDisp.java:92)        at Glacier2._SessionManagerDisp.__dispatch(_SessionManagerDisp.java:125)
        at IceInternal.Incoming.invoke(Incoming.java:159)
        at Ice.ConnectionI.invokeAll(ConnectionI.java:2037)        at Ice.ConnectionI.message(ConnectionI.java:972)
        at IceInternal.ThreadPool.run(ThreadPool.java:577)        at IceInternal.ThreadPool.access$100(ThreadPool.java:12)        at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:971)
Caused by: java.sql.SQLException: connection is closed        at bitronix.tm.resource.jdbc.lrc.LrcConnectionHandle.getDelegate(LrcConnectionHandle.java:48)
        at bitronix.tm.resource.jdbc.lrc.LrcConnectionHandle.rollback(LrcConnectionHandle.java:77)        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

and

2011-05-10 17:12:58,805 ERROR [ ome.services.blitz.fire.SessionManagerI] (l.Server-4) Error while creating ServiceFactoryIome.conditions.InternalException:  Wrapped Exception: (org.springframework.transaction.TransactionSystemException):
Could not roll back JDBC transaction; nested exception is java.sql.SQLException: connection is closed        at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:286)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)        at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:430)        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:112)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)        at ome.tools.hibernate.ProxyCleanupFilter$Interceptor.invoke(ProxyCleanupFilter.java:231)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:111)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)        at $Proxy64.doWork(Unknown Source)
        at ome.services.util.Executor$Impl.execute(Executor.java:369)        at ome.services.sessions.SessionManagerImpl.createSession(SessionManagerImpl.java:296)
        at ome.services.sessions.SessionManagerImpl.createWithAgent(SessionManagerImpl.java:248)
        at ome.services.blitz.fire.SessionManagerI.create(SessionManagerI.java:173)

[jburel]

Moved from sprint 2011-05-19 (12)

[jburel]

Moved from sprint 2011-06-02 (13)

[jburel]

Moved from sprint 2011-06-16 (14)

[jmoore]

This is done and in a branch. Moving to 4.4, so after sprint6 it can get evaluated: ​https://github.com/joshmoore/openmicroscopy/tree/4210-USR1

[jmoore]

This work is actually done and on a branch of mine. Considering adding it.

[jmoore]

In PR-206, closing this for discussion there:

commit a5815fd8f1adb0db89c522144bfec1663e1c491b
Author: jmoore <josh@glencoesoftware.com>
Date:   Tue May 10 17:23:53 2011

    Initial USR1 handler for closing DB handles (See #4210)

[jmoore <josh@…>]

(In [a5815fd8f1adb0db89c522144bfec1663e1c491b/ome.git] on branch develop) Initial USR1 handler for closing DB handles (See #4210)

[mtbcarroll]

If we could poke the process by some means other than USR1, USR2 then for the rest we could drop the sun.* imports in favor of a normal runtime shutdown hook.

[jamoore]

If we could poke the process by some means other than USR1, USR2 then for the rest we could drop the sun.* imports

Adding a service method which would do this is straight-forward enough, but in the case that the DB is having issues (which is the reason for the signal handler) then you likely won't be able to authenticate to call the method. Other options I can think of:

• an Ice service at a very external (i.e. non-Hibernate related) layer which only listens on localhost
• a JMX invocation

in favor of a normal runtime shutdown hook.

How is this related?

[mtbcarroll]

in favor of a normal runtime shutdown hook

We don't need the sun.* stuff to simply handle the JVM being shut down, but I think we do need it to notice USR1, USR2 so while we're handling specific signal types anyway I figure we may as well leave the shutdown code as it is.