Developer looses database connection overnight?

Discuss all problems you have with Servoy here. It might help to mention the Servoy version and Operating System version you are using

Developer looses database connection overnight?

Postby Neale » Sun Feb 01, 2004 11:14 pm

Hi,

I've setup a proof-of-concept/solution-beta initially utilsing Servoy Developer(planning to go to Server RSN) ver 1.2final with Java 1.4.1 on Linux (Debian Sarge snapshot) and with repository and data both in Mysql (same host as developer). Client is on Mac OSX.

All goes well till I leave it, at least overnight. I came back after a couple of days and the Client wasn't working (sorry, details not available) and neither was developer.

Here's the error:

Code: Select all
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Communication link failure: java.io.IOException, underlying cause: Unexpected end of input stream

** BEGIN NESTED EXCEPTION **

java.io.IOException
MESSAGE: Unexpected end of input stream

STACKTRACE:

java.io.IOException: Unexpected end of input stream
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1405)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1775)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2030)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1563)
        at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at com.servoy.j2db.persistence.datasource.n.invoke(Unknown Source)
        at $Proxy0.executeQuery(Unknown Source)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
        at com.servoy.j2db.persistence.Repository.for(Unknown Source)
        at com.servoy.j2db.persistence.l.do(Unknown Source)
        at com.servoy.j2db.persistence.l.if(Unknown Source)
        at com.servoy.j2db.persistence.Repository.getSolutionDefinitions(Unknown Source)
        at com.servoy.j2db.p.a(Unknown Source)
        at com.servoy.j2db.J2DBClient.openSolution(Unknown Source)
        at com.servoy.j2db.cmd.s.doIt(Unknown Source)
        at com.servoy.j2db.cmd.b$2.run(Unknown Source)
        at java.awt.event.InvocationEvent.dispatch(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)


My only theory so far is that this is ticlked by the daily run of "mysqladmin flush-logs" (hopefully I can test this on Tuesday morning).

Any other ideas?

Thanks,
Neale.
Neale
 
Posts: 230
Joined: Thu May 15, 2003 6:29 am
Location: Australia

Postby Jan Blok » Mon Feb 02, 2004 11:03 am

You also might want to check if the connection idle timeout of your database is disabled (set to zero for most dbs)
For example sybase default closes a db connection after one hour idle, something you don't want in server enviorement
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby Neale » Mon Feb 02, 2004 11:46 am

Bingo! That'll be it:

http://www.mysql.com/documentation/mysq ... #Gone_away

That says "By default, the server closes the connection after 8 hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.".

I'll try that tomorrow and see how it goes.

Meanwhile, could Servoy handle this a little more graciously/constructively? Could Servoy be configured to "ping" the DB server (say every hour, but make the minutes configurable)?

Many thanks,
Neale.
Neale
 
Posts: 230
Joined: Thu May 15, 2003 6:29 am
Location: Australia

Postby Jan Blok » Mon Feb 02, 2004 11:51 am

Neale wrote:Meanwhile, could Servoy handle this a little more graciously/constructively? Could Servoy be configured to "ping" the DB server (say every hour, but make the minutes configurable)?

Hardly, we consider setting the timeout setting on the database to be the most effective solution.
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby Neale » Wed Feb 04, 2004 8:06 am

I was having problems disabling MySQLs timeout, so took that issue to a mysql list.

The response there (from, AFAIK, the MySQL Connector/J author) was that the correct solution is for Servoy to handle the timeout (or any other interuption to the JDBC connection) gracefully:

JDBC connections aren't guaranteed to last forever (I even checked with
the JDBC spec lead on this).

You need to make sure your application has the 'smarts' to handle
connection failure, and re-connect and retry the transaction (if it makes
sense), or mark the current connection as bad, pass the exception up the
stack, and re-establish the connection later.

Connections don't go away just because of wait_timeout, someone might
unplug a switch, restart the database server, ifconfig an interface down,
router flakes out...There are many potential failure scenarios for a
network connection, and only your application will know what the _prudent_
action to take (retry immediately, throw error retry later, shutdown, etc0
is.
[...]

Archived at: http://marc.theaimsgroup.com/?l=mysql&m ... 912005&w=2

A bit more rummaging around turned up this in the Connector/J README file:

Issue:

"I have a servlet/application that works fine for a day, and then stops
working overnight".

Resolution:

MySQL closes connections after 8 hours of inactivity. You either
need to use a connection pool that handles stale connections or use the
"autoReconnect" parameter (see "USAGE AND INSTALLATION"). Also, you should
be catching SQLExceptions in your application and dealing with them,
[...]


Obviously there's a difference of opinion here :-(

Any chance you could take this up with Mark Matthews <mark@mysql.com> ?

Thanks,
Neale.
Neale
 
Posts: 230
Joined: Thu May 15, 2003 6:29 am
Location: Australia

Postby Jan Blok » Wed Feb 04, 2004 11:09 am

Well we have connection pool which drops connections on which a sqlexception occurs, but you will recieve indeed an error, after which you can retry (on a different connection)
In a application server environment (3-tier model) you just cannot set the connection to timeout, timeouts are mend for (2-tier, client which connects directly to database instead of via server)
So far I don't know any dbvendor which does not allow you to disable the timeout.
I will ask the dev-team to have a look at this issue.
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby sebster » Wed Feb 11, 2004 8:12 pm

I have looked at this issue and added a connection validation method to the pool which will allow you to execute a query over the connection every time it is taken from the pool. If the query fails the connection is thrown out. This is then repeated until a working connection is found. If no working connections are found, a new connection is made and returned.

This feature wil be in 2.0 RC5 and should solve your problems if you do not wish to turn off the connection timeout.
.
Check the servoy administration servlet (usually at http://localhost:8080/servoy-admin when you are running the developer), under DB Servers.

Hope this helps.
Sebastiaan van Erk
Servoy
sebster
 
Posts: 251
Joined: Thu Apr 24, 2003 10:03 am
Location: Utrecht, The Netherlands


Return to Discuss possible Issues and Bugs

Who is online

Users browsing this forum: No registered users and 3 guests

cron