Re: Should validationQuery commit ?
Hi Chris, Le 18/04/2014 20:44, Christopher Schultz a écrit : Every database I've ever used accepts SELECT 1 FROM DUAL because Oracle did it first. You could try that. Well, Postgresql does not (although you can of course create table dual(id integer primary key); insert 1 into dual(id);). That said, I was hit by another (related) problem this week-end, with Oracle. The validation query was configured as 'select 1 from dual;'. Notice the semi-column: this broke everything with intermittent ORA-12516 errors. Removing the semi-column fixes my app with Oracle. Has anyone an idea of what is happening with this ; ? Franck - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Should validationQuery commit ?
On Tue, Apr 22, 2014 at 01:58:56PM +0200, Franck Routier wrote: [snip] That said, I was hit by another (related) problem this week-end, with Oracle. The validation query was configured as 'select 1 from dual;'. Notice the semi-column: this broke everything with intermittent ORA-12516 errors. Removing the semi-column fixes my app with Oracle. Has anyone an idea of what is happening with this ; ? I believe that the semicolon is a delimiter used by SQL*Plus (and many other interactive tools for DBMS access). It seems to be used by SQL itself only in the module language and embedded SQL, where a statement delimiter is needed. It's not part of an SQL statement, so it probably doesn't belong in a query string. Commandline tools need a statement delimiter, but statements fed to the DBMS programmatically are delimited by end-of-string. -- Mark H. Wood, Lead System Programmer mw...@iupui.edu Machines should not be friendly. Machines should be obedient. signature.asc Description: Digital signature
Re: Should validationQuery commit ?
Hi, Le 18/04/2014 20:44, Christopher Schultz a écrit : Every database I've ever used accepts SELECT 1 FROM DUAL because Oracle did it first. You could try that. Well, Postgresql does not.But you can omit the from if you want, as in 'select 1' That said, using rollbackOnReturn=true seemed to work, but with Oracle I'm getting quite freuent ORA-12516 (TNS:listener could not find available handler with matching protocol stack) errors since I activated this setting. Is there a known gotcha with rollbackOnReturn=true and Oracle ? Maybe I should give a higher maxWait ? (right now, I left default setting). Thanks for any hint, Franck - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Should validationQuery commit ?
Hi, we recently switched to tomcat-jdbc-pool as our connection pool, and have since encountered performance problems with Postgresql. The problem is due to the fact that postgres is having hard time vacuuming, due to always ongoing idle transactions. See http://permalink.gmane.org/gmane.comp.db.postgresql.performance/37893 for the details. It turns out that those idle transactions were coming from tomcat pool validation queries... here is our configuration: property name=defaultAutoCommit value=false/ property name=maxActive value=30/ property name=maxIdle value=30/ property name=initialSize value=10/ property name=minIdle value=10/ property name=testOnReturn value=true/ property name=testOnBorrow value=true/ property name=validationQuery value=select 1 from rtelre/ property name=validationInterval value=0/ rtelre is a dummy table with 1 column and one row. We use it for compatibility reasons, since we deploy in several environments, and on oracle the from clause is mandatory (so select 1 does not fit). So the problem is that the select 1 from rtelre is never commited, nor rolled back. Adding a commit in the query seems to work : select 1 from rtelre; commit; does not leave idle transactions. That said, we never encountered such config example in the docs, nor on web. So here is my question(s): - is it expected that the validationQuery transaction are not terminated ? - is adding a commit (or rollback, for that matters) in the query a valid workaround, or even the only sensible configuration ? - if so, shouldn't it be mentionned somewhere in the docs ? Thanks, Franck - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Should validationQuery commit ?
On Apr 18, 2014, at 5:05 AM, Franck Routier franck.rout...@axege.com wrote: Hi, we recently switched to tomcat-jdbc-pool as our connection pool, and have since encountered performance problems with Postgresql. The problem is due to the fact that postgres is having hard time vacuuming, due to always ongoing idle transactions. See http://permalink.gmane.org/gmane.comp.db.postgresql.performance/37893 for the details. It turns out that those idle transactions were coming from tomcat pool validation queries... here is our configuration: property name=defaultAutoCommit value=false/ property name=maxActive value=30/ property name=maxIdle value=30/ property name=initialSize value=10/ property name=minIdle value=10/ property name=testOnReturn value=true/ property name=testOnBorrow value=true/ property name=validationQuery value=select 1 from rtelre/ property name=validationInterval value=0/ rtelre is a dummy table with 1 column and one row. We use it for compatibility reasons, since we deploy in several environments, and on oracle the from clause is mandatory (so select 1 does not fit). So the problem is that the select 1 from rtelre is never commited, nor rolled back. Adding a commit in the query seems to work : select 1 from rtelre; commit; does not leave idle transactions. That said, we never encountered such config example in the docs, nor on web. So here is my question(s): - is it expected that the validationQuery transaction are not terminated ? I don’t know what the intended behavior is here since I didn’t write the code, but given that you have set defaultAutoCommit to false, I could see this happening. - is adding a commit (or rollback, for that matters) in the query a valid workaround, or even the only sensible configuration ? Another option you might try would be to set “rollbackOnReturn to “true”. That is supposed to instruct the pool to rollback an open transactions when the connection is returned to the pool. I haven’t tried it, but it seems like it would work here. Dan - if so, shouldn't it be mentionned somewhere in the docs ? Thanks, Franck - 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: Should validationQuery commit ?
Hi, Le 18/04/2014 13:53, Daniel Mikusa a écrit : Another option you might try would be to set “rollbackOnReturn to “true”. That is supposed to instruct the pool to rollback an open transactions when the connection is returned to the pool. I haven’t tried it, but it seems like it would work here. Dan Hummm, not sure this is really what we want. The problem in not with the application returning unclosed transactions, it's rather than when the connection is returned to the pool, the pool itself will open a transaction and not close it... But I'll try it anyway. Thanks, Franck - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Should validationQuery commit ?
On Fri, 2014-04-18 at 11:05 +0200, Franck Routier wrote: property name=validationQuery value=select 1 from rtelre/ Is is possible the select just needs to be terminated with a semi-colon? - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Should validationQuery commit ?
Le 18/04/2014 13:53, Daniel Mikusa a écrit : Another option you might try would be to set “rollbackOnReturn to “true”. That is supposed to instruct the pool to rollback an open transactions when the connection is returned to the pool. I haven’t tried it, but it seems like it would work here. Ok, I tested, and you were absolutely right: rollbackOnReturn does the trick. Moreover, notice that have 'select 1 from rtelre; commit;' as a validationQuery works on Postgresql, but is categorically refused by Oracle... So using rollbackOnReturn=true works and is more portable. Thanks. nb: adding a ; as suggest by Tim does not change anything. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Should validationQuery commit ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Dan, On 4/18/14, 7:53 AM, Daniel Mikusa wrote: On Apr 18, 2014, at 5:05 AM, Franck Routier franck.rout...@axege.com wrote: Hi, we recently switched to tomcat-jdbc-pool as our connection pool, and have since encountered performance problems with Postgresql. The problem is due to the fact that postgres is having hard time vacuuming, due to always ongoing idle transactions. See http://permalink.gmane.org/gmane.comp.db.postgresql.performance/37893 for the details. It turns out that those idle transactions were coming from tomcat pool validation queries... here is our configuration: property name=defaultAutoCommit value=false/ property name=maxActive value=30/ property name=maxIdle value=30/ property name=initialSize value=10/ property name=minIdle value=10/ property name=testOnReturn value=true/ property name=testOnBorrow value=true/ property name=validationQuery value=select 1 from rtelre/ property name=validationInterval value=0/ rtelre is a dummy table with 1 column and one row. We use it for compatibility reasons, since we deploy in several environments, and on oracle the from clause is mandatory (so select 1 does not fit). So the problem is that the select 1 from rtelre is never commited, nor rolled back. Adding a commit in the query seems to work : select 1 from rtelre; commit; does not leave idle transactions. That said, we never encountered such config example in the docs, nor on web. So here is my question(s): - is it expected that the validationQuery transaction are not terminated ? I don’t know what the intended behavior is here since I didn’t write the code, but given that you have set defaultAutoCommit to false, I could see this happening. +1 Also note that setting defaultAutoCommit=false changes the driver to be different than the JDBC spec default. That may confuse some Java programmers. - is adding a commit (or rollback, for that matters) in the query a valid workaround, or even the only sensible configuration ? Another option you might try would be to set “rollbackOnReturn to “true”. That is supposed to instruct the pool to rollback an open transactions when the connection is returned to the pool. I haven’t tried it, but it seems like it would work here. It might not, if the connection isn't being returned to the pool, since it's just being checked. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTUXJhAAoJEBzwKT+lPKRYvNQP/Ak2BPOwiW+xdCv99KKObYs7 232psJaox7GZg08Rn28Omyoqjzp+OmAUfT0iAy5cGKfRpX2+4SAQ4uG7qYyLEOkn jMy7tTk8B/V2ezuhLpxprXGOW6PT35xfEmakrDGMm6VrRwynZTUwVHDO9wijye8P 2kBETWUUwP1ML5u5bZzJ+yvFBBcpQZsMoo40jLvC0txTo0QzaJDfglLaZKa0+Xlv DYnseBcSvsuRnmJiLRSrVo3OcToTPzSERP8Rnn4adgSTISrbuKNmLrpIdtsCF+aO vxhTlJlfcK/ndl1+Kwin+kuMMbOAezzHpLcJnlmeZ+56MXsgjSF1W1ZBVsfosPZ2 gwVwmQCpQpRP3bNZPUXNtHCvXbIVKqF2PN6ExD4x34rtn+BE7St3Cv+Hyg36mrER 8O62t1y0fkk92S9vkGREVdtCw6XzwD6gApBunDtKqlilomjbwnf4FICfm+Oe4vgl EaTHLnKMlhzVhwWcXuHHyihGQysFi42HcTDbEcmx7M4btfYlGFoKqN2PSFsr1KfI 3T6C7mi8c24REXCXe9HzRp+UZ8HHVVqxCXZyNf04Zj42pmUnlPxR8R5Orp72TuIf 2wES9mW37tEZ0LkrfRWr6X2d4unmw7anaqZkUlIt12kD5h3XmqB7PXojYgchKF2V JWsVzpDt3HPGbQhBBxtr =2stW -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Should validationQuery commit ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Franck, On 4/18/14, 5:05 AM, Franck Routier wrote: we recently switched to tomcat-jdbc-pool as our connection pool, and have since encountered performance problems with Postgresql. The problem is due to the fact that postgres is having hard time vacuuming, due to always ongoing idle transactions. See http://permalink.gmane.org/gmane.comp.db.postgresql.performance/37893 for the details. It turns out that those idle transactions were coming from tomcat pool validation queries... here is our configuration: property name=defaultAutoCommit value=false/ property name=maxActive value=30/ property name=maxIdle value=30/ property name=initialSize value=10/ property name=minIdle value=10/ property name=testOnReturn value=true/ property name=testOnBorrow value=true/ property name=validationQuery value=select 1 from rtelre/ property name=validationInterval value=0/ rtelre is a dummy table with 1 column and one row. We use it for compatibility reasons, since we deploy in several environments, and on oracle the from clause is mandatory (so select 1 does not fit). Every database I've ever used accepts SELECT 1 FROM DUAL because Oracle did it first. You could try that. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTUXKgAAoJEBzwKT+lPKRYs7oQAIo212h6oQgQ4tlTLWnWt/AJ Oc9kD0uCSZrsDZaABHkILPOGyqcKv+2uYHJ0+tMvuZw0rsYybVCSsxn3a/Kmqaqj wlm+md0ru0MA9/lbMwIPe1w8k8ppMk/fALBBooBu6M0RXs1oGnzAGfPoQ+RNA0Iu JeSOWKKRybQvHH+dv8dlJZJLPBCUx5KzLpuPtl3btDXt7Oa3X5qt+T8ya8W/yi8j QN171iNNZMANFAv32KTPIwIwvg5gup3ptFcHsqcY8prTee1qW25dShMAad4QEKRl eQqMhaI1ETzQaXofNt4WgCP32XPKpfqY0TEBpBBB+3VMLYm9hhEgM359FYZPonAJ EXTo7oGth/8HpO7ZwL7OGV73gf4iondPjPiG8+ks9OI4aWhBN717OLQGwkn5JbaP DYFQJiQ57zcWKcWWUhk6BC4Rh1K61Z69V/D5V2iAtjClvJNGgdIBsyG1DEGD1u+z RkoR3LIQi+n6qPld+XzWqP2nNc5YckbCSR6bL0pza/sraGJXymCwXdbJtlVL3sJT YGF10pfApEt4Imoi6+WjiLX8G3bd0/FNfp02rxJtsQL+eyDXUUICz8dOoJhBkIp2 vgr/u8TM0O+vu/pt4v/aovJO1Rqggsr76Rp5xUqY0Amzrn1wg7b9f8uDr8EhZNFv d3R4rDxto3/8b9upDJUz =p05t -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
