-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Arnaud,
On 11/20/12 4:23 AM, Arnaud wrote: > We have been struggling with this MySQL last packet message for > some times as well. And we are still not sure if we have solved it > yet. You can get the "last packet" message for a number of reasons. Typically, it's because the connection has gone stale due to firewall or other connection timeout and the connection has been closed but it is still in the connection pool. > However we did discover quite a few things. > > We minimized our DBCP configuration to only a few parameters > <Context path=""> <Resource auth="Container" > driverClassName="com.mysql.jdbc.Driver" name="jdbc/retailUser" > type="javax.sql.DataSource" username="xxxxxx" password="xxxxx" > url="jdbc:mysql://server/db" validationQuery="SELECT 1" > testOnBorrow="true"/> </Context> > > That's it. All the other parameters have acceptable default values. > The problem, we think, is actually that with the default > configuration you will open to many connections and not use them > within the wait_timeout of MySQL. Sounds to me that the default values aren't acceptable, then. > So, now and then you will retrieve a connection that has timed out. > The validation query solves this. Correct. For MySQL, you'll want to use "/* ping */ SELECT 1" for your query: it will be fasted with any reasonably-recent version of Connector/J which does a lightweight ping to the database instead of actually issuing the query, fetching the results, etc. > As far as we know the autoReconnect is a jConnector parameter and > only applies to MySQL. That is correct. The product is Connector/J. > MySQL says in their documentation that it's the applications job to > handle failed connections. For the step of just getting the > connection DBCP is solving this with the validationQuery="SELECT > 1" testOnBorrow="true" settings. But if it happens in the midst of > a transaction, your application should handle it. While true, I can't imagine any database that would commit-on-connection-failure. Most applications are written to simply throw a SQLException and either abandon the transaction or re-try it. In either case, the intended behavior is achieved: no half-performed transactions are committed. > If you do not set validationQuery than testOnBorrow will simply not > run. This is clear from the DBCP documentation. > removeAbandoned only removes connections that you forget to close > in your application. This isn't strictly true: the abandonedTimeout specifies a window of opportunity that the client has to return a particular resource. If the transaction runs long, the connection can be considered "abandoned" even if the application eventually closes it. > You can use this setting for a while together with logAbandoned to > find your application leaks where you forget to close the > connection after use, but when done you should remove logAbandoned > because the logging adds overhead from every connection borrowed. This is also clear from the documentation. > Most of this we found at > tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html > <http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html> and at > dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html > > <http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html> DBCP != Tomcat-pool They can be configured in very similar ways, but tomcat-pool is a lot more flexible and was designed to be higher-performing than DBCP -- though I haven't independently verified that it is actually faster. - -chris -----BEGIN PGP SIGNATURE----- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with undefined - http://www.enigmail.net/ iEYEARECAAYFAlCr+28ACgkQ9CaO5/Lv0PAG4gCfS225GnljSNwwAaOuVMuRqWD1 umEAnAuYOb5YML3B4Vbxvcj4cjm/Wfu9 =Y/Ij -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
