Developer looses database connection overnight?

[Neale]

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.

[Jan Blok]

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

[Neale]

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.

[Jan Blok]

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.

[Neale]

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.

[Jan Blok]

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.

[sebster]

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.