You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomee.apache.org by Chris Christo <_c...@mail.com> on 2014/03/01 16:52:29 UTC
MySQL timeout
Hey guys,
Quick question. I have my war deployed on OpenEJB standalone 4.6.0 which has a jta managed data source connecting to a local MySQL db. If the app doesn’t get used (i.e an api call or whatever) for like 24 hours or so then the db connection times out. After making a couple of subsequent calls it ‘wakes up’ and responds normally. I’m assuming here that the db connection is reestablished and all things become normal again.
I’m not really sure what to do to stop the connection being dropped. I’m assuming I have to increase the session timeout or something? Is this done on the OpenEJB side or MySQL side? anyone had this problem and solved it?
The stack trace is as follows:
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 963,156,961 milliseconds ago. The last packet sent successfully to the server was 963,156,961 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
Re: MySQL timeout
Posted by Romain Manni-Bucau <rm...@gmail.com>.
see timeBetweenEvictionRunsMillis and numTestsPerEvictionRun
Romain Manni-Bucau
Twitter: @rmannibucau
Blog: http://rmannibucau.wordpress.com/
LinkedIn: http://fr.linkedin.com/in/rmannibucau
Github: https://github.com/rmannibucau
2014-03-02 12:57 GMT+01:00 Chris Christo <_c...@mail.com>:
> Hey Romain, thanks for the valuable resource!
>
> Just wanted to clarify with you and your big brain :P
>
> If I have understood it correctly, the reason the error was happening is because the connection from the pool that is attempted to be used has expired. Now, with a simple pre check using say (validationQuery) the connection will be thrown instead of being released. So effectively the only config I need is the validationQuery.
>
> Just one more thing though, how long do connections last before expiring? I didn't see a setting for this...?
>
> {
> "resources": {
> "MyDataSource": {
> "properties": {
> "JdbcDriver": "com.mysql.jdbc.Driver",
> "JdbcUrl": "jdbc:mysql://localhost:3306/DB",
> "UserName": "user",
> "Password": "password",
> "JtaManaged": true,
> "MaxActive": 8,
> "MaxIdle": 8,
> "validationQuery": "select id from t_user where id=1;"
> }
> }
> }
> }
>
>
> On 1 Mar 2014, at 17:16, Romain Manni-Bucau <rm...@gmail.com> wrote:
>
>> hi
>>
>> did you configure dbcp to test regularly the connection? see
>> http://commons.apache.org/proper/commons-dbcp/configuration.html see
>> validationQuery, timeBetweenEvictionRunsMillis etc...
>> Romain Manni-Bucau
>> Twitter: @rmannibucau
>> Blog: http://rmannibucau.wordpress.com/
>> LinkedIn: http://fr.linkedin.com/in/rmannibucau
>> Github: https://github.com/rmannibucau
>>
>>
>>
>> 2014-03-01 16:52 GMT+01:00 Chris Christo <_c...@mail.com>:
>>> Hey guys,
>>>
>>> Quick question. I have my war deployed on OpenEJB standalone 4.6.0 which has a jta managed data source connecting to a local MySQL db. If the app doesn't get used (i.e an api call or whatever) for like 24 hours or so then the db connection times out. After making a couple of subsequent calls it 'wakes up' and responds normally. I'm assuming here that the db connection is reestablished and all things become normal again.
>>>
>>> I'm not really sure what to do to stop the connection being dropped. I'm assuming I have to increase the session timeout or something? Is this done on the OpenEJB side or MySQL side? anyone had this problem and solved it?
>>>
>>> The stack trace is as follows:
>>>
>>> Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 963,156,961 milliseconds ago. The last packet sent successfully to the server was 963,156,961 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
>
Re: MySQL timeout
Posted by Chris Christo <_c...@mail.com>.
Hey Romain, thanks for the valuable resource!
Just wanted to clarify with you and your big brain :P
If I have understood it correctly, the reason the error was happening is because the connection from the pool that is attempted to be used has expired. Now, with a simple pre check using say (validationQuery) the connection will be thrown instead of being released. So effectively the only config I need is the validationQuery.
Just one more thing though, how long do connections last before expiring? I didn’t see a setting for this…?
{
"resources": {
"MyDataSource": {
"properties": {
"JdbcDriver": "com.mysql.jdbc.Driver",
"JdbcUrl": "jdbc:mysql://localhost:3306/DB",
"UserName": "user",
"Password": "password",
"JtaManaged": true,
"MaxActive": 8,
"MaxIdle": 8,
"validationQuery": "select id from t_user where id=1;"
}
}
}
}
On 1 Mar 2014, at 17:16, Romain Manni-Bucau <rm...@gmail.com> wrote:
> hi
>
> did you configure dbcp to test regularly the connection? see
> http://commons.apache.org/proper/commons-dbcp/configuration.html see
> validationQuery, timeBetweenEvictionRunsMillis etc...
> Romain Manni-Bucau
> Twitter: @rmannibucau
> Blog: http://rmannibucau.wordpress.com/
> LinkedIn: http://fr.linkedin.com/in/rmannibucau
> Github: https://github.com/rmannibucau
>
>
>
> 2014-03-01 16:52 GMT+01:00 Chris Christo <_c...@mail.com>:
>> Hey guys,
>>
>> Quick question. I have my war deployed on OpenEJB standalone 4.6.0 which has a jta managed data source connecting to a local MySQL db. If the app doesn't get used (i.e an api call or whatever) for like 24 hours or so then the db connection times out. After making a couple of subsequent calls it 'wakes up' and responds normally. I'm assuming here that the db connection is reestablished and all things become normal again.
>>
>> I'm not really sure what to do to stop the connection being dropped. I'm assuming I have to increase the session timeout or something? Is this done on the OpenEJB side or MySQL side? anyone had this problem and solved it?
>>
>> The stack trace is as follows:
>>
>> Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 963,156,961 milliseconds ago. The last packet sent successfully to the server was 963,156,961 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
Re: MySQL timeout
Posted by Romain Manni-Bucau <rm...@gmail.com>.
hi
did you configure dbcp to test regularly the connection? see
http://commons.apache.org/proper/commons-dbcp/configuration.html see
validationQuery, timeBetweenEvictionRunsMillis etc...
Romain Manni-Bucau
Twitter: @rmannibucau
Blog: http://rmannibucau.wordpress.com/
LinkedIn: http://fr.linkedin.com/in/rmannibucau
Github: https://github.com/rmannibucau
2014-03-01 16:52 GMT+01:00 Chris Christo <_c...@mail.com>:
> Hey guys,
>
> Quick question. I have my war deployed on OpenEJB standalone 4.6.0 which has a jta managed data source connecting to a local MySQL db. If the app doesn't get used (i.e an api call or whatever) for like 24 hours or so then the db connection times out. After making a couple of subsequent calls it 'wakes up' and responds normally. I'm assuming here that the db connection is reestablished and all things become normal again.
>
> I'm not really sure what to do to stop the connection being dropped. I'm assuming I have to increase the session timeout or something? Is this done on the OpenEJB side or MySQL side? anyone had this problem and solved it?
>
> The stack trace is as follows:
>
> Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 963,156,961 milliseconds ago. The last packet sent successfully to the server was 963,156,961 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