MySQL JDBC Connection Timeout: The last packet successfully received from the server

[Mark van Wyk]

Hi guys,

I've been receiving this error intermittently and fighting with it for over a month now. I need help.

Internal Server Error: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 66,943,475 milliseconds ago. The last packet sent successfully to the server was 66,943,475 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.

Press refresh 3 or 4 times, and everything seems to wake up and the error disappears for a few days.

I've tried:

• autoReconnect = true (doesn't seem to work anyway)
• Updating to latest version of MySQL Driver
• Switching to Tomcat Container Connection Pools
• Switching to C3PO

It seemed that switching to C3PO helped for a while. I had it set to attempt to reconnect for 30 times in a row, and that seemed to work. For some reason it stopped working again.

This problem does not seem to happen on my local machine. Only on the server. I've read through 1000's of forum posts.

Tomcat 6.0.28

web.xml

<resource-ref> <description>MySQL Datasource</description> <res-ref-name>jdbc/sbiexam5-c3po</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>

context.xml

<Resource auth="Container" description="DB Connection" driverClass="com.mysql.jdbc.Driver" maxPoolSize="100" minPoolSize="8" name="jdbc/sbiexam5-c3po" user="sbiexam" password="--removed for example--" factory="org.apache.naming.factory.BeanFactory" type="com.mchange.v2.c3p0.ComboPooledDataSource" acquireRetryAttempts="30" acquireRetryDelay="1000" jdbcUrl="jdbc:mysql://localhost:3306/sbiexam5?autoReconnect=true">

</Resource>

Any ideas MUCH appreciated...

[James Mc Millan]

Hello

The MySQL server is expiring the idle connections in your pool. The connections are used when there is a usage spike, and are then left idle until the next usage spike where they have already been expired by the server. Those connections are then removed from the pool and new connections are acquired, but you still get the error.

C3P0 can test your idle connections every few seconds, or you can test connections on checkout.
Try setting idleConnectionTestPeriod for c3p0, we usually use a value of 60 (it is in seconds). Alternatively, set testConnectionOnCheckout, although that is very aggressive and resource intensive.

I'd also recommend setting preferredTestQuery to something like "SELECT 1" (just to test the connection), as the default test query is some metadata query for some reason which becomes expensive with lots of connections from lots of hosts.

I'd recommend reading through the C3P0 documentation for details:
http://www.mchange.com/projects/c3p0/index.html

Hope that helps :-)
James

--
You received this message because you are subscribed to the Google Groups "CTJUG Forum" group.
To post to this group, send email to CTJUG...@googlegroups.com
To unsubscribe from this group, send email to CTJUG-Forum...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/CTJUG-Forum
For the ctjug home page see http://www.ctjug.org.za
For jobs see http://jobs.gamatam.com/

[Sparky]

Hi James,

That help a lot. I'll try that!

Mark

> >    - autoReconnect = true (doesn't seem to work anyway)
> >    - Updating to latest version of MySQL Driver
> >    - Switching to Tomcat Container Connection Pools
> >    - Switching to C3PO

>
> > It seemed that switching to C3PO helped for a while. I had it set to
> > attempt to reconnect for 30 times in a row, and that seemed to work. For
> > some reason it stopped working again.
>
> > This problem does not seem to happen on my local machine. Only on the
> > server. I've read through 1000's of forum posts.
>
> > Tomcat 6.0.28
>

> > *web.xml*

>
> >  <resource-ref> <description>MySQL Datasource</description>
> > <res-ref-name>jdbc/sbiexam5-c3po</res-ref-name>
> > <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth>
> > </resource-ref>
>

> > *context.xml*