Sybase 12 idle disconnect

rafig · September 22, 2011, 11:39am

Hi all,
Yesterday I migrated my largest customer from the old Servoy supplied Sybase to Sybase 12 as they have other processes that talk directly to the Sybase db.
In the past, sometimes these connections wouldn’t drop, so Sybase would have lots of connections left open on it.
I therefore added the “-ti 480” parameter to the sybase.config launch string, which means, drop any idle connection after 12 hours.

-gb high -ti 480 -x tcpip{dobroadcast=no} -qs -c 1500m -o "D:/CRM_12/sybase_log.txt"
"D:/CRM_12/servoy_repository_5.db"
"D:/CRM_12/crm.db"
"D:/CRM_12/log_data.db"
"D:/CRM_12/example.db"

This looks like it dropped these stray idle connections, but because they stopped work last night about 6pm, Sybase then dropped all the Servoy connections around 2am this morning, so when at 8am someone tried to log into Servoy client, it failed and servoy_log said

2011-09-22 07:58:38,893 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.server.Zc.Zo - java.sql.SQLException: JZ006: Caught IOException: com.sybase.jdbc3.jdbc.SybConnectionDeadException: JZ0C0: Connection is already closed.
2011-09-22 07:58:38,893 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.util.Debug - destroyObject: java.sql.SQLException: JZ0C0: Connection is already closed.
java.sql.SQLException: JZ0C0: Connection is already closed.
	at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.checkConnection(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.close(Unknown Source)
	at org.apache.commons.dbcp.PoolingConnection.close(PoolingConnection.java:93)
	at org.apache.commons.dbcp.DelegatingConnection.close(DelegatingConnection.java:247)
	at org.apache.commons.dbcp.PoolableConnection.reallyClose(PoolableConnection.java:122)
	at org.apache.commons.dbcp.PoolableConnectionFactory.destroyObject(PoolableConnectionFactory.java:628)
	at com.servoy.j2db.Za.Za.Zo.destroyObject(Zo.java:14)
	at org.apache.commons.pool.impl.GenericObjectPool.invalidateObject(GenericObjectPool.java:863)
	at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:106)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
	at org.apache.commons.dbcp.DelegatingConnection.close(DelegatingConnection.java:247)
	at com.servoy.j2db.Za.Za.Zn.close(Zn.java:71)
	at com.servoy.j2db.util.Utils.closeConnection(Utils.java:1468)
	at com.servoy.j2db.server.Zc.Zo.Za(Zo.java:881)
	at com.servoy.j2db.server.Zc.Zo.Zk(Zo.java:1452)
	at com.servoy.j2db.server.Zc.Zo.Zd(Zo.java:507)
	at com.servoy.j2db.server.Zc.Zo.getRepositoryTable(Zo.java:1400)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:57)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:36)
	at com.servoy.j2db.dataprocessing.Zb.Za(Zb.java:260)
	at com.servoy.j2db.dataprocessing.Za.register(Za.java:24)
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2011-09-22 07:58:38,893 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.util.Debug - Throwable
java.sql.SQLException: Already closed.
	at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
	at org.apache.commons.dbcp.DelegatingConnection.close(DelegatingConnection.java:247)
	at com.servoy.j2db.Za.Za.Zn.close(Zn.java:71)
	at com.servoy.j2db.util.Utils.closeConnection(Utils.java:1468)
	at com.servoy.j2db.server.Zc.Zo.Za(Zo.java:881)
	at com.servoy.j2db.server.Zc.Zo.Zk(Zo.java:1452)
	at com.servoy.j2db.server.Zc.Zo.Zd(Zo.java:507)
	at com.servoy.j2db.server.Zc.Zo.getRepositoryTable(Zo.java:1400)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:57)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:36)
	at com.servoy.j2db.dataprocessing.Zb.Za(Zb.java:260)
	at com.servoy.j2db.dataprocessing.Za.register(Za.java:24)
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2011-09-22 07:58:38,893 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.util.Debug - Throwable
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: JZ006: Caught IOException: com.sybase.jdbc3.jdbc.SybConnectionDeadException: JZ0C0: Connection is already closed. JZ006: Caught IOException: com.sybase.jdbc3.jdbc.SybConnectionDeadException: JZ0C0: Connection is already closed.
	at com.servoy.j2db.server.Zc.Zo.Za(Zo.java:1248)
	at com.servoy.j2db.server.Zc.Zo.Zk(Zo.java:1452)
	at com.servoy.j2db.server.Zc.Zo.Zd(Zo.java:507)
	at com.servoy.j2db.server.Zc.Zo.getRepositoryTable(Zo.java:1400)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:57)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:36)
	at com.servoy.j2db.dataprocessing.Zb.Za(Zb.java:260)
	at com.servoy.j2db.dataprocessing.Za.register(Za.java:24)
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2011-09-22 07:58:38,893 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.dataprocessing.Zb - Error updating client stats
org.apache.commons.dbcp.SQLNestedException: Borrow prepareStatement from pool failed
	at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:113)
	at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
	at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:30)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:36)
	at com.servoy.j2db.dataprocessing.Zb.Za(Zb.java:260)
	at com.servoy.j2db.dataprocessing.Za.register(Za.java:24)
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: JZ0C0: Connection is already closed.
	at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.checkConnection(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.a(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.prepareStatement(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.prepareStatement(Unknown Source)
	at com.servoy.j2db.Za.Za.Zm.prepareStatement(Zm.java:115)
	at org.apache.commons.dbcp.PoolingConnection.makeObject(PoolingConnection.java:285)
	at com.servoy.j2db.Za.Za.Zh.borrowObject(Zh.java:24)
	at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:107)
	... 20 more
2011-09-22 07:58:38,893 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.util.Debug - destroyObject: java.sql.SQLException: JZ0C0: Connection is already closed.
java.sql.SQLException: JZ0C0: Connection is already closed.
	at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.checkConnection(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybConnection.close(Unknown Source)
	at org.apache.commons.dbcp.PoolingConnection.close(PoolingConnection.java:93)
	at org.apache.commons.dbcp.DelegatingConnection.close(DelegatingConnection.java:247)
	at org.apache.commons.dbcp.PoolableConnection.reallyClose(PoolableConnection.java:122)
	at org.apache.commons.dbcp.PoolableConnectionFactory.destroyObject(PoolableConnectionFactory.java:628)
	at com.servoy.j2db.Za.Za.Zo.destroyObject(Zo.java:14)
	at org.apache.commons.pool.impl.GenericObjectPool.invalidateObject(GenericObjectPool.java:863)
	at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:106)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
	at org.apache.commons.dbcp.DelegatingConnection.close(DelegatingConnection.java:247)
	at com.servoy.j2db.Za.Za.Zn.close(Zn.java:71)
	at com.servoy.j2db.util.Utils.closeConnection(Utils.java:1468)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:62)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:36)
	at com.servoy.j2db.dataprocessing.Zb.Za(Zb.java:260)
	at com.servoy.j2db.dataprocessing.Za.register(Za.java:24)
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
2011-09-22 07:58:38,909 ERROR [RMI TCP Connection(3043)-128.1.1.18] com.servoy.j2db.util.Debug - Throwable
java.sql.SQLException: Already closed.
	at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
	at org.apache.commons.dbcp.DelegatingConnection.close(DelegatingConnection.java:247)
	at com.servoy.j2db.Za.Za.Zn.close(Zn.java:71)
	at com.servoy.j2db.util.Utils.closeConnection(Utils.java:1468)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:62)
	at com.servoy.j2db.dataprocessing.Zi.Za(Zi.java:36)
	at com.servoy.j2db.dataprocessing.Zb.Za(Zb.java:260)
	at com.servoy.j2db.dataprocessing.Za.register(Za.java:24)
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
...
2011-09-22 07:59:07,753 ERROR [RMI TCP Connection(3046)-128.1.1.18] com.servoy.j2db.util.Debug - Throwable
com.servoy.j2db.persistence.RepositoryException: Server repository_server not found
	at com.servoy.j2db.dataprocessing.Zu.Za(Zu.java:642)
	at com.servoy.j2db.dataprocessing.Zu.performQuery(Zu.java:274)
	at com.servoy.j2db.server.Za.Zc.Za(Zc.java:198)
	at com.servoy.j2db.server.Za.Zc.checkPasswordForUserName(Zc.java:177)
	at com.servoy.j2db.server.ApplicationServer.checkDefaultServoyAuthorisation(ApplicationServer.java:118)
	at com.servoy.j2db.server.Zk.login(Zk.java:60)
	at sun.reflect.GeneratedMethodAccessor294.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
	at sun.rmi.transport.Transport$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)

so basically, because Sybase closed the connection, Servoy Application Server (SAS) couldn’t recover.
I have SAS Database Server set to
Query validation type: Exception Validation
What can I do to get Servoy to handle the dropped connections gracefully and then re-connect?? (I just restarted SAS to fix issue quickly…)
If I change it to ‘Query Validation - SELECT 1;’, will that work??
I am at client now, but of course we won’t know until tomorrow morning again whether it has and the client won’t be happy with having to restart service each morning (the boss was in this morning and WAS NOT impressed that Servoy wasn’t running!)
I really hope someone can reply today to me as this isn’t reflecting well on me (I can’t remove timeout at the moment and restart Sybase as everyone is madly busy using Servoy and Warehouse using back-end)
Thanks
Rafi

jcompagner · September 22, 2011, 11:44am

yes use a Query validation in this end. Exception validation is not really something you should depend on.

rafig · September 22, 2011, 12:20pm

jcompagner:
yes use a Query validation in this end. Exception validation is not really something you should depend on.

Thanks Johan!
I had already put those settings in, but will have to wait until end of day to restart SAS…

Robert_Huber · September 22, 2011, 9:11pm

jcompagner:
yes use a Query validation in this end. Exception validation is not really something you should depend on.

Johan, can you explain a bit more about Query validation and which setting to use under which circumstances?

Regards, Robert

jcompagner · September 22, 2011, 9:53pm

try to always use query validation
Because with Exception validation you are always 1 to late. Because then 1 query is already failing because of an exception, so something is already going wrong in 1 client…

With query validation this is not the case, because before your real client call is done first a validation call is done, so a client will get always a good connection.
Especially when using connections that can time out.

rafig · September 22, 2011, 10:08pm

Hi Johan,
if that’s the case, why isn’t SAS set to that by default??

jcompagner · September 23, 2011, 8:09am

we don’t know what we can query does “select 1” work for all databases? i don’t know
But we could do something like that for databases we know.

rafig · September 24, 2011, 4:28pm

Just to say that changing to this setting fixed the problem, so that is great!

jcompagner:
we don’t know what we can query does “select 1” work for all databases? i don’t know
But we could do something like that for databases we know.

Thanks!

Topic		Replies	Views
JZ0C0: Connection is already closed Classic Servoy	3	3792	June 18, 2008
Switching Sybase off on closing servoy -ga Classic Servoy	1	1758	March 17, 2009
Sybase open/close? Classic Servoy	6	2178	November 27, 2004
Too many active connections Classic Servoy	14	8024	August 31, 2011
Connections idle timeout in Database Server Tab Classic Servoy	5	4421	January 16, 2014

Sybase 12 idle disconnect

Related topics