Re: [jdbc-pool] Validation query leaves transaction open
I don't have one. I'm creating the pool in code. Basically like this: PoolConfiguration p = new PoolProperties(); p.setUrl(url); p.setDriverClassName(driverClass); p.setUsername(username); p.setPassword(password); p.setJmxEnabled(true); p.setTestWhileIdle(true); p.setTestOnBorrow(true); p.setTestOnReturn(false); p.setValidationQuery("SELECT 1"); p.setValidationInterval(3); p.setValidationQueryTimeout(15000); p.setLogValidationErrors(true); p.setTimeBetweenEvictionRunsMillis(6); p.setMaxActive(maxConnections); p.setMaxIdle(Math.max(maxConnections / 2, minConnections)); p.setMinIdle(minConnections); p.setInitialSize(minConnections); p.setMaxWait(maxWait); p.setRemoveAbandoned(true); p.setRemoveAbandonedTimeout(60*30); // 30 minutes (in seconds) p.setLogAbandoned(true); p.setMinEvictableIdleTimeMillis(5*60*1000); // 5 minutes p.setFairQueue(true); p.setJdbcInterceptors( "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState" +";org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" ); org.apache.tomcat.jdbc.pool.DataSource result = new org.apache.tomcat.jdbc.pool.DataSource(p); On Mon, Aug 15, 2016 at 4:44 PM Christopher Schultz < ch...@christopherschultz.net> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA256 > > John, > > On 8/11/16 3:33 PM, John Huss wrote: > > I have verified that in version 8.0.36 of tomcat jdbc the > > validation query functionality does not commit the transaction it > > starts in order to run the validation query, which can cause an > > open transaction for a very long time (possibly until the database > > crashes). This seems to be especially problematic if you have a > > large pool where the connections are idle for a large period of > > time. > > > > I also tested against version 9.0.0M9, and in that version the > > problem is fixed. However, nothing about this appears in the > > release notes for any version (9.0, 8.5, or 8.0). If this bug has > > indeed been fixed it should be documented in the release notes. > > Also, back-porting the fix to the 8.0 branch would be very > > helpful. > > > > For the time being I am working around the problem by changing my > > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using > > postgresql). This prevents the issue. > > > > This was reported previously (three years ago) here: > > https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3C > CAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3 > E > > Can > > > you post your whole (sanitized) definition? > > Thanks, > - -chris > -BEGIN PGP SIGNATURE- > Comment: GPGTools - http://gpgtools.org > Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ > > iQIcBAEBCAAGBQJXsjewAAoJEBzwKT+lPKRYqacP/j/IUunibNUhUMx+FzyfBVZo > wK2WcQdM/gdjo6VvhT30A5ndf0HgD8PxHt+7ZaQ8LPQ3cqcLem2KhZvaMD0Kaepw > k2+HCCayv89KhDXBlvLdKBKJ9mNT1s7cgmNLsF0xdvEioTi8KAbJtG0JOndYwQFb > MUuYBifOoW4ir47tQmETnu0z6rGvezTRu7eycbiaUZw0jNoQgy0WojJQqm2mmQou > uLkQghDHivqeauCLf/SbnHnJR1RuXTxef6v25M3a0rbC1XpLE9mrv/CExI92Vm9R > 94iR7olGErxJbjZywSBM4sbdlhT91IFbSeIAh/Q6FlnC2MbJxEH5naUHVIX9q36R > p94412emJ2tBWxLB8VY5E9hJVeTNzOe2+bhjBypj9z9aewuCi9oDfVs5pTTmKedu > LypcLCWsXPg4/VQ4DQ6VL81igAnLnNZbcWNueBTQiqEg2F5c3pjEwfc/G4YqTfQ0 > byTRBraKBR3FLU8n9kkwpNpO4MMVX/keUTD1NEhP/NL8ldh+nGroekFx1JBmzIk4 > 5ID8gvtelcS9Mwp+nxz4uUuXYy0iDe4pMX50QRG2AY+Hxye4rfX9NmCG/BYhwNAQ > x0X/16fZ4usyKOyj75Lya6IfbMTq2ufv8rVciKaIJJXybQNAhgWvO2F1VZHTgdIG > HT8rSho2EhsqjyNlR2s4 > =28er > -END PGP SIGNATURE- > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > >
Re: [jdbc-pool] Validation query leaves transaction open
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 John, On 8/11/16 3:33 PM, John Huss wrote: > I have verified that in version 8.0.36 of tomcat jdbc the > validation query functionality does not commit the transaction it > starts in order to run the validation query, which can cause an > open transaction for a very long time (possibly until the database > crashes). This seems to be especially problematic if you have a > large pool where the connections are idle for a large period of > time. > > I also tested against version 9.0.0M9, and in that version the > problem is fixed. However, nothing about this appears in the > release notes for any version (9.0, 8.5, or 8.0). If this bug has > indeed been fixed it should be documented in the release notes. > Also, back-porting the fix to the 8.0 branch would be very > helpful. > > For the time being I am working around the problem by changing my > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using > postgresql). This prevents the issue. > > This was reported previously (three years ago) here: > https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3C CAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3 E Can > you post your whole (sanitized) definition? Thanks, - -chris -BEGIN PGP SIGNATURE- Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJXsjewAAoJEBzwKT+lPKRYqacP/j/IUunibNUhUMx+FzyfBVZo wK2WcQdM/gdjo6VvhT30A5ndf0HgD8PxHt+7ZaQ8LPQ3cqcLem2KhZvaMD0Kaepw k2+HCCayv89KhDXBlvLdKBKJ9mNT1s7cgmNLsF0xdvEioTi8KAbJtG0JOndYwQFb MUuYBifOoW4ir47tQmETnu0z6rGvezTRu7eycbiaUZw0jNoQgy0WojJQqm2mmQou uLkQghDHivqeauCLf/SbnHnJR1RuXTxef6v25M3a0rbC1XpLE9mrv/CExI92Vm9R 94iR7olGErxJbjZywSBM4sbdlhT91IFbSeIAh/Q6FlnC2MbJxEH5naUHVIX9q36R p94412emJ2tBWxLB8VY5E9hJVeTNzOe2+bhjBypj9z9aewuCi9oDfVs5pTTmKedu LypcLCWsXPg4/VQ4DQ6VL81igAnLnNZbcWNueBTQiqEg2F5c3pjEwfc/G4YqTfQ0 byTRBraKBR3FLU8n9kkwpNpO4MMVX/keUTD1NEhP/NL8ldh+nGroekFx1JBmzIk4 5ID8gvtelcS9Mwp+nxz4uUuXYy0iDe4pMX50QRG2AY+Hxye4rfX9NmCG/BYhwNAQ x0X/16fZ4usyKOyj75Lya6IfbMTq2ufv8rVciKaIJJXybQNAhgWvO2F1VZHTgdIG HT8rSho2EhsqjyNlR2s4 =28er -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: [jdbc-pool] Validation query leaves transaction open
2016-08-12 13:46 GMT+02:00 John Huss : > In every database I have used every query requires a transaction. Often > they will start them automatically if you don't do it explicitly. Right , but I guess that Tomcat uses JDBC connections and I wonder why for executing validationQuery sentence, Tomcat doesn't use autocommit = true setting > On Fri, Aug 12, 2016 at 4:13 AM Jose María Zaragoza > wrote: > >> 2016-08-11 21:33 GMT+02:00 John Huss : >> > I have verified that in version 8.0.36 of tomcat jdbc the validation >> query >> > functionality does not commit the transaction it starts in order to run >> the >> > validation query, which can cause an open transaction for a very long >> time >> > (possibly until the database crashes). This seems to be especially >> > problematic if you have a large pool where the connections are idle for a >> > large period of time. >> > >> > I also tested against version 9.0.0M9, and in that version the problem is >> > fixed. However, nothing about this appears in the release notes for any >> > version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should >> be >> > documented in the release notes. Also, back-porting the fix to the 8.0 >> > branch would be very helpful. >> > >> > For the time being I am working around the problem by changing my >> > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using >> > postgresql). This prevents the issue. >> >> >> I wonder why a validationQuery needs to open a new transaction >> >> >> > >> > This was reported previously (three years ago) here: >> > >> https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E >> > >> > Thanks, >> > John >> >> - >> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org >> For additional commands, e-mail: users-h...@tomcat.apache.org >> >> - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: [jdbc-pool] Validation query leaves transaction open
In every database I have used every query requires a transaction. Often they will start them automatically if you don't do it explicitly. On Fri, Aug 12, 2016 at 4:13 AM Jose María Zaragoza wrote: > 2016-08-11 21:33 GMT+02:00 John Huss : > > I have verified that in version 8.0.36 of tomcat jdbc the validation > query > > functionality does not commit the transaction it starts in order to run > the > > validation query, which can cause an open transaction for a very long > time > > (possibly until the database crashes). This seems to be especially > > problematic if you have a large pool where the connections are idle for a > > large period of time. > > > > I also tested against version 9.0.0M9, and in that version the problem is > > fixed. However, nothing about this appears in the release notes for any > > version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should > be > > documented in the release notes. Also, back-porting the fix to the 8.0 > > branch would be very helpful. > > > > For the time being I am working around the problem by changing my > > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using > > postgresql). This prevents the issue. > > > I wonder why a validationQuery needs to open a new transaction > > > > > > This was reported previously (three years ago) here: > > > https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E > > > > Thanks, > > John > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > >
Re: [jdbc-pool] Validation query leaves transaction open
2016-08-11 21:33 GMT+02:00 John Huss : > I have verified that in version 8.0.36 of tomcat jdbc the validation query > functionality does not commit the transaction it starts in order to run the > validation query, which can cause an open transaction for a very long time > (possibly until the database crashes). This seems to be especially > problematic if you have a large pool where the connections are idle for a > large period of time. > > I also tested against version 9.0.0M9, and in that version the problem is > fixed. However, nothing about this appears in the release notes for any > version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should be > documented in the release notes. Also, back-porting the fix to the 8.0 > branch would be very helpful. > > For the time being I am working around the problem by changing my > validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using > postgresql). This prevents the issue. I wonder why a validationQuery needs to open a new transaction > > This was reported previously (three years ago) here: > https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E > > Thanks, > John - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
[jdbc-pool] Validation query leaves transaction open
I have verified that in version 8.0.36 of tomcat jdbc the validation query functionality does not commit the transaction it starts in order to run the validation query, which can cause an open transaction for a very long time (possibly until the database crashes). This seems to be especially problematic if you have a large pool where the connections are idle for a large period of time. I also tested against version 9.0.0M9, and in that version the problem is fixed. However, nothing about this appears in the release notes for any version (9.0, 8.5, or 8.0). If this bug has indeed been fixed it should be documented in the release notes. Also, back-porting the fix to the 8.0 branch would be very helpful. For the time being I am working around the problem by changing my validation query from "SELECT 1" to be "SELECT 1; COMMIT" (using postgresql). This prevents the issue. This was reported previously (three years ago) here: https://mail-archives.apache.org/mod_mbox/tomcat-users/201301.mbox/%3CCAPw873vy%2B9kWMQiFPbnTX6g1FSQ8oTVhDJ3Zagp9UkUZ3d5qjw%40mail.gmail.com%3E Thanks, John