mysql timeout after 8 hours of inactivity

Classic Servoy
1

My client gets an error each morning “Cannot load form data”. I looked in the server log and there is an error that the database has timed out.

I see a variety of discussions (autoreconnect, disable timeouts, etc), but I’m not sure of best practices on this. How do I configure Servoy/Mysql so I can leave the server running indefinitely and not get timeouts overnight when the server sits idle?

Thanks.

2

I have a running a solution on Win2000/Java6, against MySQL 5.0, with the latest JDBC drivers from MySQL.

I’ve experimented with a few different configurations over the last year. For a while a used “query validation” with a “SELECT 1” query. This worked reliably, but was slow. Currently, I have the MySQL dataproviders configured with “exception validation” and added the following to my “my.ini” file:

wait_timeout = 129600
interactive_timeout = 129600

See references:
wait_timeout: http://dev.mysql.com/doc/refman/5.1/en/ … it_timeout
interactive_timeout: http://dev.mysql.com/doc/refman/5.1/en/ … ve_timeout

That extends the time MySQL will let an idle connection rest to 36 hours. 8 hours is the default.

greg.

3

Thanks Greg. I’ll try the latter, as you’re currently using. I set my timeouts to 300000. Hopefully that won’t be an issue. Sure would be nice to find a long term solution that doesn’t require these timeouts.

4

A question that relates to this thread:

WinXP
Servoy 5.0.1
MySQL 5.1

I get this message when I start a client from developer after being idle for a long time:

The last packet successfully received from the server was 42,596,750 milliseconds ago. The last packet sent successfully to the server was 42,596,750 milliseconds ago. is longer than the server configured value of ‘wait_timeout’. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true’ to avoid this problem.

I like the sound of ‘autoReconnect = true’. Where do I change this setting? Can anyone confirm that this solves the problem or do I have to play with timeouts in my.ini?

Thanks

5

Hi Adrian,

amcgilly:
I like the sound of ‘autoReconnect = true’. Where do I change this setting? Can anyone confirm that this solves the problem or do I have to play with timeouts in my.ini?

You add it in the connection URL:

jdbc:mysql://serverIP/databaseName?autoReconnect=true

In my experience it doesn’t get rid of the errors in the server log though. But at least the users won’t get an error.

6

ROCLASI:
In my experience it doesn’t get rid of the errors in the server log though. But at least the users won’t get an error.

Same here ;-)

7

Jason,

You can also set the validation type of the server definition in Servoy to ‘query validation’.
Then add a simple query in the Validation Query field (something like ‘select 1’).

This should allow better reconnect when a connection is retrieved from the pool after 8 hours of inactivity.

Rob