Thanks to all for the assistance. I'll try adding those parameters. But I'm always a bit nervous about possibly just band-aiding a problem that I don't really understand.
Regarding the defensive coding approach, this error often occurs on the sql query by the internal TC security authentication code that looks up ids and passwords for login. I don't own that code. I would think any required defensive code would be already present in base TC code, yet it's failing there as well. Basically, if I could figure out how to defend against this in my code, there's still nothing I can do, coding-wise, to defend against it in a security auth call to sql, right? Bear with me for minute and make sure I really understand basically how the connection pooling works. Basically, TC grabs a handful of connections and opens them up. Then when an app needs one, a random connection is provided from the pool. As long as we're hitting all of the connections often enough, they never time out. But when things get dormant for a while connections time out and close. And in TC 5, if the luck of the draw gave me a closed connection, autoReconnect would fire it back up so that I (and the auth code) would always get a good connection. Apparently, from interpreting this exception, I am now getting expired connections that are not reconnecting. This is troubling. My code never did anything to start up all of those connections to begin with. But for some reason now, I've got to write code to check each connection I get back to see if it's connected. If it's not connected, am I just supposed to throw it back into the pool and try another one, hopefully finding at least one that is still connected? (seems like sooner or later, there'll be none that work) Or am I now supposed to make the connection call myself to try to reconnect it? It just fundamentally seems wrong that the burden should fall on my code to reconnect it if it timed out. This started failing regularly on every application on every one of my servers immediately after upgrading 5.0 --> 7.0 on otherwise untouched webapp code and an untouched MySql connector where everything had worked rock solid for years. I'm just very nervous about adding a bunch of parameters and changing up things even more strictly on a "you might try this' approach when nobody can really explain the problem. I'll go ahead and try the new parameters just because I've got to work around this someway. But I'm sure hoping somebody can tell me what's really changed to break this. Thx again Jerry On Thu, Jan 26, 2012 at 11:45 AM, Daniel Mikusa <dmik...@vmware.com> wrote: > On Thu, 2012-01-26 at 07:46 -0800, Jerry Malcolm wrote: > > I migrated to Tomcat 7.0 a couple of months ago on several servers. Ever > > since moving to 7, I periodically get the following exception on MySql > > calls on all of my 7.0 servers: > > > > Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The > > last packet successfully received from the server was 71,072,547 > > milliseconds ago. The last packet sent successfully to the server was > > 71,072,578 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. > > > > My data source configuration is: > > > > <Resource name="jdbc/xxxxxxxxxxxxxx.com" auth="Container" > > type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" > > removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" > > username="xxxxxxx" password="xxxxxxx" > > driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql:// > > 127.0.0.1/xxxxxxxxx?autoReconnect=true"/> > > > > I realize that, yes, the 71k seconds is greater than the 10k maxWait and > I > > could increase maxWait. But that would not be fixing the problem. The > > exception text says to use autoReconnect=true. But I already have > > autoReconnect=true, and I'm certain this was working back on 5.0 (I went > > from 5.0 to 7.0). > > I agree that it looks like you have specified autoReconnect correctly, > but I'm no expert there. > > I believe that autoReconnect is a feature of the JDBC driver, so you > might want to check with the MySQL folks to see why that is not working. > > > > > I also know I could (and probably should) add defensive code to check for > > connection validity. But again, this all worked fine for years on 5.0. > > Have you tried using the validation offered by the connection pool? > > If you set "testOnBorrow=true" and "validationQuery=SELECT 1", the pool > should validate connections without requiring any code changes to your > application. > > See commons-dbcp configuration docs. > > https://commons.apache.org/dbcp/configuration.html > > Dan > > > > > > It appears that autoReconnect is no longer working. Has the > configuration > > syntax changed for autoReconnect since 5.0? > > > > Thx > > > > Jerry >
