Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Saurabh, On 4/8/14, 6:22 AM, Saurabh Saraswat wrote: Thanks for taking time to respond me. My updated Resource Tag is - Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource maxActive=100 maxIdle=30 maxWait=1 That's a lot of connections. Are you sure you need to be able to support 100 simultaneous queries? username=usrname password=password driverClassName=com.mysql.jdbc.Driver url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull validationQuery=Select 1 removeAbandoned=true You should use /* ping */ SELECT 1 as your validation query. The MySQL driver will perform a lightweight connectivity test instead of actually executing that query, which will improve performance. removeAbandonedTimeout=1000 1 second for abandoned timeout? That seems low. logAbandoned=false/ I highly recommend that you set this to true. I have also cross checked my code. I am closing the connection properly in finally block. How about Statements and ResultSets? I think the MySQL driver (and db) is tolerant of sloppy resource management, but Oracle certainly isn't. Also have set the max_connection=250 in etc/my.cnf for MySql. That just seems like a huge number. You really ought to set connection limits based upon user id. You don't want to lock-out root from your database if your web application runs away with the database... Even now i am not getting any Exception but my Application gets Hanged after a certain time (after Certain hits to the database from application). Have tested pooling with different ways like after setting - factory=org.apache.tomcat.jdbc.pool.DataSourceFactory. Conclusion is that i am not able to find satisfactory solution. I agree with Daniel: you need to take some thread dumps when the application hangs. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTR4TZAAoJEBzwKT+lPKRY3WcP/itpZZE7WZqM9B3fSsDNFXmq 7jm6Wpzujj2pLNjUmtzKaSk44e1XgQTQ07LoxS0b3SLPpRi7yz6KbzCeQCOzQ6KE OXVj7d3plRReg5P2HaSL6FYfYDDh2ql/tKaEEnXVXOvCLI83UEnTnN2ENrsbXwEF Lx+t2mEfX00GlENqasheX6/hcDlHDiSJlccRBGbyF9cXHTF1YFLVJl4vT3R35uwm myiPTepohDAMH4zZ1s2hzQynGpUb69/dnaIopM+BE86YavfKhuNntdFhMF9kaTOQ s7A8UpyGgR4qaCH8qeHDC+brIJVtoVPTnBrcVKiU8oLFY2+K0vCN6tutBQrHTcRz HmYN638X3u6OyHY6nS+N2oEDLzZ/CLV2dntAXhEwOojiePq1mVdDDU48VUAT1ghD BS26DPquhSpedq/XgIrxmaNX69qjb5IWWD9b/0LuxoXSTriuK8Gjhyq2xDYxhoFP 5MZLf5ebUofZsw2qYVijYvy1vXLw96HruCNQMQCzis4Zo+pEt1jk+JT4gzZmrq5o fH0bvAvLriPSYR6STeJAs2/eJ8cOCoi8Vq5AF5NAq5XZGhLnQHbF0WYmHk/ZGen3 WSUhlGXYgFIK3Pf2NRGhf0cnu7gWKighhwqsNB135R7HlPeAg/RwrIcxAdckDULz FYXDKJS+U8HZdiCOoiaQ =JYNR -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/8/14, 6:36 AM, Daniel Mikusa wrote: On Apr 8, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for taking time to respond me. My updated Resource Tag is - Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource maxActive=100 maxIdle=“30 As Chris mentioned, set maxActive 1 in your dev environment. That will help you find the problem more quickly. maxWait=1 username=usrname password=password driverClassName=com.mysql.jdbc.Driver url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull validationQuery=Select 1 removeAbandoned=true removeAbandonedTimeout=“1000 This is in seconds, so 1000 is 16.667 minutes, and is probably way to high. Whoops! You're right. I'm used to everything being in ms. I apologize for my post just now: it's seconds and you are correct. 4 days of conference+drinking will do this to you ;) For the purposes of debugging, try something much lower like 10 seconds. That way you’ll get feedback quick feedback. +1 I have also cross checked my code. I am closing the connection properly in finally block. If you have not done so already, run something like FindBugs on your code. Absolutely. FindBugs is a great tool, even though there are sometimes a lot of false-positives. Run it on your code (it's easy!) and start with the highest-priority bugs that it finds. They are pretty much all worth investigating. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTR4WaAAoJEBzwKT+lPKRYNskP/RQJlQONYDDNa3qokJUoicNr 3zqPd9w6SSvEvZnNfwR6G5a8EI/bDr4xQoEqOQfNBv6QSHrWaEBWmSkQwT/qcVh5 QMOCZ+QMtCCQVD9KtYEwwHJH2FSpv9jEiBVyiPV8Ik4hMOYhIMgcJd+C1qh4Hj7s fAfUsJ0aSZxQqexwHADhtn5UZP6/y+2DZGRZ1YVcCXmV7aPV4ghB1Nj7rY0EOxXq N6q1dt9SsxNXnzqx3xNyJw20qulTa+//B4DNSjpek87cCf+PGMr/M9qV3pqrDBv6 uBIUDWhZXaVa1ilGvyWilr9HnxTUh6JGGQEEgFNfafjO6DDJLteLFsp334QKDX+K QBp03mU7gOO7QwcEtQ49XBmzEgy1//f44m4q9uU3LyZYleT8YRs479us2gHXUbYB iVFOz1kqdFZOL4234zTE7MHkDTSd1o5lwfrzjghT+2On3/nEDYlrsmmcPkBSaVw9 XFYucvC8gXx0CLFlyaPoobwUbVF0qmjEH1Np31tiLCjXDdfsI/2Im1TZDPNCfFnv mj7+FA/h6PXV/kSrSdICjW2H38YeWI7CovUwNm3KnfTiLPC9clpJUUxXCrQWqo/+ z6gv7V0dMRso9lXBFKKh6tGosGC3Q4NPJeqyfDbLUd4lS4HvrwFn9GvJF7Y1xD/+ cKIkLEYn3Njl7Xz9I4Cj =W5a2 -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
Chris, Thanks for your regular guidance and valuable suggestions! On Fri, Apr 11, 2014 at 11:33 AM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/8/14, 6:36 AM, Daniel Mikusa wrote: On Apr 8, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for taking time to respond me. My updated Resource Tag is - Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource maxActive=100 maxIdle=30 As Chris mentioned, set maxActive 1 in your dev environment. That will help you find the problem more quickly. maxWait=1 username=usrname password=password driverClassName=com.mysql.jdbc.Driver url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull validationQuery=Select 1 removeAbandoned=true removeAbandonedTimeout=1000 This is in seconds, so 1000 is 16.667 minutes, and is probably way to high. Whoops! You're right. I'm used to everything being in ms. I apologize for my post just now: it's seconds and you are correct. 4 days of conference+drinking will do this to you ;) I can understand :) I have done all changes suggested by you guys and the good news is that the problem has been solved. Now my application is running fine. For the purposes of debugging, try something much lower like 10 seconds. That way you'll get feedback quick feedback. +1 I have also cross checked my code. I am closing the connection properly in finally block. If you have not done so already, run something like FindBugs on your code. Absolutely. FindBugs is a great tool, even though there are sometimes a lot of false-positives. Run it on your code (it's easy!) and start with the highest-priority bugs that it finds. They are pretty much all worth investigating. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTR4WaAAoJEBzwKT+lPKRYNskP/RQJlQONYDDNa3qokJUoicNr 3zqPd9w6SSvEvZnNfwR6G5a8EI/bDr4xQoEqOQfNBv6QSHrWaEBWmSkQwT/qcVh5 QMOCZ+QMtCCQVD9KtYEwwHJH2FSpv9jEiBVyiPV8Ik4hMOYhIMgcJd+C1qh4Hj7s fAfUsJ0aSZxQqexwHADhtn5UZP6/y+2DZGRZ1YVcCXmV7aPV4ghB1Nj7rY0EOxXq N6q1dt9SsxNXnzqx3xNyJw20qulTa+//B4DNSjpek87cCf+PGMr/M9qV3pqrDBv6 uBIUDWhZXaVa1ilGvyWilr9HnxTUh6JGGQEEgFNfafjO6DDJLteLFsp334QKDX+K QBp03mU7gOO7QwcEtQ49XBmzEgy1//f44m4q9uU3LyZYleT8YRs479us2gHXUbYB iVFOz1kqdFZOL4234zTE7MHkDTSd1o5lwfrzjghT+2On3/nEDYlrsmmcPkBSaVw9 XFYucvC8gXx0CLFlyaPoobwUbVF0qmjEH1Np31tiLCjXDdfsI/2Im1TZDPNCfFnv mj7+FA/h6PXV/kSrSdICjW2H38YeWI7CovUwNm3KnfTiLPC9clpJUUxXCrQWqo/+ z6gv7V0dMRso9lXBFKKh6tGosGC3Q4NPJeqyfDbLUd4lS4HvrwFn9GvJF7Y1xD/+ cKIkLEYn3Njl7Xz9I4Cj =W5a2 -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org regards, Saurabh
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/4/14, 8:57 AM, Daniel Mikusa wrote: On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for your response! I have cross checked the user / password configuration. All is correct. If you're getting Access Denied exceptions, there is only one cause and that's bad credentials (or host + credentials, because MySQL can limit access based on the host). If you're not seeing these any more then, disregard. I've never tried this, but it could also be due to connection-limits on the server itself. Having root limited to a certain number of connections sounds like a terrible idea, but then again, so does connecting as root in the first place. As i mentioned that initially i am getting the object of connection but after some time (After few hits to database from application) my web app goes to slow and than it stops working i i got the below exception- *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object* +1 to David's suggestion. You could be leaking connections or your queries are very slow. +1 Two suggestions: 1.) Enable the slow query log on your MySQL server and see if the queries are slow. Alternatively, login to your MySQL server and run 'show processlist. That will show you what queries are running. +1 2.) Enable DBCP's abandoned connection detection. See the removeAbandoned attribute. +10 With this (and logAbandoned), the pool will alert you when your application does not properly return connections to the pool. In development, I always recommend that you use maxActive=1 for your connection pools. This will expose any potential deadlocks you may have mistakenly coded into your application. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTPssiAAoJEBzwKT+lPKRYTx4QAK8a4WJIx1i+yWyZquSrCnad RBNB69jnPmYG0Uzc/yKyHzOXvv2wt1vE22wYyp64b4FFVqQNBmEnnm6XI20PSR2i Yt9lm5wZ5/5fsCGvj39B8E11GCao5enzkhXUpa51spLnjHfw5k3o0gGmWAqhLVza nOfbG+rTjjjXCrr1Y6tz0g+35M+w02TIh87Z5xdkvboqv/NRfxbGKRIZB2e1zT0K USY4skgug3L1TpKiXgoRNv7g7gbxHB7AXgL1po+PI1T1mNXCakUE81O26Etv/wm2 1A/d15LfCLou0uWQSfHPqaoODGFVOTsRWwn8xiJdjo2Ah/y7OqXfzMQh41UBO8H7 jNmakIHlb6NYDJK6LiRFlGw5K9AEO+dNFJ9e6Gi4kELB4Kn6CGqFRD3aqTsVerOb EhEG844nDmVRzr7gwK58aXSICy8PURDOfmZ+IaXehz0MARnKQiog3cWBT+EKIHxq RUAc0T/YEG+Qm1jiZef5h+NuMZLrzczQIOXXGYkjcMwGcUxmjzBbbvYbr56g84jL 3ukIXp6bnOvyIdB8jnibbICoR/sj0Mg4zia7vTPkqdXbU3Ng2W6/lV9K2Mnm9aDL OcLocnWnFGZycukIDYtfbtZOY7wTAqk5fJsZauDQGeeA4M9UXu4dPgpaoahuK8Dq moJwtEq/5/JNXkctdS7n =RI4v -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org All, I have tested with configurations suggested by all of you.I am not getting exceptions now but still after certain time, the server gets hanged. Any suggestion please,still not able to rid out of this issue. Saurabh
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
On Apr 8, 2014, at 5:46 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/4/14, 8:57 AM, Daniel Mikusa wrote: On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for your response! I have cross checked the user / password configuration. All is correct. If you're getting Access Denied exceptions, there is only one cause and that's bad credentials (or host + credentials, because MySQL can limit access based on the host). If you're not seeing these any more then, disregard. I've never tried this, but it could also be due to connection-limits on the server itself. Having root limited to a certain number of connections sounds like a terrible idea, but then again, so does connecting as root in the first place. As i mentioned that initially i am getting the object of connection but after some time (After few hits to database from application) my web app goes to slow and than it stops working i i got the below exception- *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object* +1 to David's suggestion. You could be leaking connections or your queries are very slow. +1 Two suggestions: 1.) Enable the slow query log on your MySQL server and see if the queries are slow. Alternatively, login to your MySQL server and run 'show processlist. That will show you what queries are running. +1 2.) Enable DBCP's abandoned connection detection. See the removeAbandoned attribute. +10 With this (and logAbandoned), the pool will alert you when your application does not properly return connections to the pool. In development, I always recommend that you use maxActive=1 for your connection pools. This will expose any potential deadlocks you may have mistakenly coded into your application. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTPssiAAoJEBzwKT+lPKRYTx4QAK8a4WJIx1i+yWyZquSrCnad RBNB69jnPmYG0Uzc/yKyHzOXvv2wt1vE22wYyp64b4FFVqQNBmEnnm6XI20PSR2i Yt9lm5wZ5/5fsCGvj39B8E11GCao5enzkhXUpa51spLnjHfw5k3o0gGmWAqhLVza nOfbG+rTjjjXCrr1Y6tz0g+35M+w02TIh87Z5xdkvboqv/NRfxbGKRIZB2e1zT0K USY4skgug3L1TpKiXgoRNv7g7gbxHB7AXgL1po+PI1T1mNXCakUE81O26Etv/wm2 1A/d15LfCLou0uWQSfHPqaoODGFVOTsRWwn8xiJdjo2Ah/y7OqXfzMQh41UBO8H7 jNmakIHlb6NYDJK6LiRFlGw5K9AEO+dNFJ9e6Gi4kELB4Kn6CGqFRD3aqTsVerOb EhEG844nDmVRzr7gwK58aXSICy8PURDOfmZ+IaXehz0MARnKQiog3cWBT+EKIHxq RUAc0T/YEG+Qm1jiZef5h+NuMZLrzczQIOXXGYkjcMwGcUxmjzBbbvYbr56g84jL 3ukIXp6bnOvyIdB8jnibbICoR/sj0Mg4zia7vTPkqdXbU3Ng2W6/lV9K2Mnm9aDL OcLocnWnFGZycukIDYtfbtZOY7wTAqk5fJsZauDQGeeA4M9UXu4dPgpaoahuK8Dq moJwtEq/5/JNXkctdS7n =RI4v -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org All, I have tested with configurations suggested by all of you.I am not getting exceptions now but still after certain time, the server gets hanged. Any suggestion please,still not able to rid out of this issue. Saurabh 1.) Include your updated Resource tag. We’ll want to see what you’re working with now. 2.) When the server ‘hangs’, take some thread dumps and see what it’s doing. That will hopefully provide more information about what the server is doing at the time. http://wiki.apache.org/tomcat/HowTo#How_do_I_obtain_a_thread_dump_of_my_running_webapp_.3F Dan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
Dear Dan, Thanks for taking time to respond me. My updated Resource Tag is - Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource maxActive=100 maxIdle=30 maxWait=1 username=usrname password=password driverClassName=com.mysql.jdbc.Driver url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull validationQuery=Select 1 removeAbandoned=true removeAbandonedTimeout=1000 logAbandoned=false/ I have also cross checked my code. I am closing the connection properly in finally block. Also have set the max_connection=250 in etc/my.cnf for MySql. Even now i am not getting any Exception but my Application gets Hanged after a certain time (after Certain hits to the database from application). Have tested pooling with different ways like after setting - factory=org.apache.tomcat.jdbc.pool.DataSourceFactory. Conclusion is that i am not able to find satisfactory solution. Thanking You! *Best Regards,* *Saurabh Sarasvat* On Tue, Apr 8, 2014 at 5:26 PM, Daniel Mikusa dmik...@gopivotal.com wrote: On Apr 8, 2014, at 5:46 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/4/14, 8:57 AM, Daniel Mikusa wrote: On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for your response! I have cross checked the user / password configuration. All is correct. If you're getting Access Denied exceptions, there is only one cause and that's bad credentials (or host + credentials, because MySQL can limit access based on the host). If you're not seeing these any more then, disregard. I've never tried this, but it could also be due to connection-limits on the server itself. Having root limited to a certain number of connections sounds like a terrible idea, but then again, so does connecting as root in the first place. As i mentioned that initially i am getting the object of connection but after some time (After few hits to database from application) my web app goes to slow and than it stops working i i got the below exception- *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object* +1 to David's suggestion. You could be leaking connections or your queries are very slow. +1 Two suggestions: 1.) Enable the slow query log on your MySQL server and see if the queries are slow. Alternatively, login to your MySQL server and run 'show processlist. That will show you what queries are running. +1 2.) Enable DBCP's abandoned connection detection. See the removeAbandoned attribute. +10 With this (and logAbandoned), the pool will alert you when your application does not properly return connections to the pool. In development, I always recommend that you use maxActive=1 for your connection pools. This will expose any potential deadlocks you may have mistakenly coded into your application. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTPssiAAoJEBzwKT+lPKRYTx4QAK8a4WJIx1i+yWyZquSrCnad RBNB69jnPmYG0Uzc/yKyHzOXvv2wt1vE22wYyp64b4FFVqQNBmEnnm6XI20PSR2i Yt9lm5wZ5/5fsCGvj39B8E11GCao5enzkhXUpa51spLnjHfw5k3o0gGmWAqhLVza nOfbG+rTjjjXCrr1Y6tz0g+35M+w02TIh87Z5xdkvboqv/NRfxbGKRIZB2e1zT0K USY4skgug3L1TpKiXgoRNv7g7gbxHB7AXgL1po+PI1T1mNXCakUE81O26Etv/wm2 1A/d15LfCLou0uWQSfHPqaoODGFVOTsRWwn8xiJdjo2Ah/y7OqXfzMQh41UBO8H7 jNmakIHlb6NYDJK6LiRFlGw5K9AEO+dNFJ9e6Gi4kELB4Kn6CGqFRD3aqTsVerOb EhEG844nDmVRzr7gwK58aXSICy8PURDOfmZ+IaXehz0MARnKQiog3cWBT+EKIHxq RUAc0T/YEG+Qm1jiZef5h+NuMZLrzczQIOXXGYkjcMwGcUxmjzBbbvYbr56g84jL 3ukIXp6bnOvyIdB8jnibbICoR/sj0Mg4zia7vTPkqdXbU3Ng2W6/lV9K2Mnm9aDL OcLocnWnFGZycukIDYtfbtZOY7wTAqk5fJsZauDQGeeA4M9UXu4dPgpaoahuK8Dq moJwtEq/5/JNXkctdS7n =RI4v -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org All, I have tested with configurations suggested by all of you.I am not getting exceptions now but still after certain time, the server gets hanged. Any suggestion please,still not able to rid out of this issue. Saurabh 1.) Include your updated Resource tag. We'll want to see what you're working with now. 2.) When the server 'hangs', take some thread dumps and see what it's doing. That will hopefully provide more information about what the server is doing at the time. http://wiki.apache.org/tomcat/HowTo#How_do_I_obtain_a_thread_dump_of_my_running_webapp_.3F Dan
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
On Apr 8, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for taking time to respond me. My updated Resource Tag is - Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource maxActive=100 maxIdle=“30 As Chris mentioned, set maxActive 1 in your dev environment. That will help you find the problem more quickly. maxWait=1 username=usrname password=password driverClassName=com.mysql.jdbc.Driver url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull validationQuery=Select 1 removeAbandoned=true removeAbandonedTimeout=“1000 This is in seconds, so 1000 is 16.667 minutes, and is probably way to high. For the purposes of debugging, try something much lower like 10 seconds. That way you’ll get feedback quick feedback. logAbandoned=false”/ Set this to true, otherwise you’re not going to know when it detects an abandoned connection. I have also cross checked my code. I am closing the connection properly in finally block. If you have not done so already, run something like FindBugs on your code. Also have set the max_connection=250 in etc/my.cnf for MySql. Even now i am not getting any Exception but my Application gets Hanged after a certain time (after Certain hits to the database from application). Have tested pooling with different ways like after setting - factory=org.apache.tomcat.jdbc.pool.DataSourceFactory. Conclusion is that i am not able to find satisfactory solution. Try the changes above and take some thread dumps. I think that will get you pointed in the right direction. Dan Thanking You! *Best Regards,* *Saurabh Sarasvat* On Tue, Apr 8, 2014 at 5:26 PM, Daniel Mikusa dmik...@gopivotal.com wrote: On Apr 8, 2014, at 5:46 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/4/14, 8:57 AM, Daniel Mikusa wrote: On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for your response! I have cross checked the user / password configuration. All is correct. If you're getting Access Denied exceptions, there is only one cause and that's bad credentials (or host + credentials, because MySQL can limit access based on the host). If you're not seeing these any more then, disregard. I've never tried this, but it could also be due to connection-limits on the server itself. Having root limited to a certain number of connections sounds like a terrible idea, but then again, so does connecting as root in the first place. As i mentioned that initially i am getting the object of connection but after some time (After few hits to database from application) my web app goes to slow and than it stops working i i got the below exception- *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object* +1 to David's suggestion. You could be leaking connections or your queries are very slow. +1 Two suggestions: 1.) Enable the slow query log on your MySQL server and see if the queries are slow. Alternatively, login to your MySQL server and run 'show processlist. That will show you what queries are running. +1 2.) Enable DBCP's abandoned connection detection. See the removeAbandoned attribute. +10 With this (and logAbandoned), the pool will alert you when your application does not properly return connections to the pool. In development, I always recommend that you use maxActive=1 for your connection pools. This will expose any potential deadlocks you may have mistakenly coded into your application. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTPssiAAoJEBzwKT+lPKRYTx4QAK8a4WJIx1i+yWyZquSrCnad RBNB69jnPmYG0Uzc/yKyHzOXvv2wt1vE22wYyp64b4FFVqQNBmEnnm6XI20PSR2i Yt9lm5wZ5/5fsCGvj39B8E11GCao5enzkhXUpa51spLnjHfw5k3o0gGmWAqhLVza nOfbG+rTjjjXCrr1Y6tz0g+35M+w02TIh87Z5xdkvboqv/NRfxbGKRIZB2e1zT0K USY4skgug3L1TpKiXgoRNv7g7gbxHB7AXgL1po+PI1T1mNXCakUE81O26Etv/wm2 1A/d15LfCLou0uWQSfHPqaoODGFVOTsRWwn8xiJdjo2Ah/y7OqXfzMQh41UBO8H7 jNmakIHlb6NYDJK6LiRFlGw5K9AEO+dNFJ9e6Gi4kELB4Kn6CGqFRD3aqTsVerOb EhEG844nDmVRzr7gwK58aXSICy8PURDOfmZ+IaXehz0MARnKQiog3cWBT+EKIHxq RUAc0T/YEG+Qm1jiZef5h+NuMZLrzczQIOXXGYkjcMwGcUxmjzBbbvYbr56g84jL 3ukIXp6bnOvyIdB8jnibbICoR/sj0Mg4zia7vTPkqdXbU3Ng2W6/lV9K2Mnm9aDL OcLocnWnFGZycukIDYtfbtZOY7wTAqk5fJsZauDQGeeA4M9UXu4dPgpaoahuK8Dq moJwtEq/5/JNXkctdS7n =RI4v -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail:
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote:
Dear All,
I am doing connection pooling with tomcat 7.0.39 and MySQL 5.5.After
searching on google and with your help i have done the below things.
Even i am able to get the connection successfully using this but getting
some trouble and exception.
I am explaining you all steps done by me-
*1. Have created a context.xml*
I have put this context.xml in the META-INF folder of my application. but
when i am deploying the web app to the server then it is not creating its
copy to ${CATALINA-BASE}/conf/Catalina/locathost. Why is that so ?
There are a couple possibilities.
1.) Look at “deployXML” attribute of your Host tag. If this is set to false,
it will disable parsing the context XML descriptor embedded inside the
application. This defaults to true, unless you are running with a security
manager, then it defaults to false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
2.) Look at the “copyXML attribute of both your Host and Context tags.
This needs to be set to true, because the default in Tomcat 7 is false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
http://tomcat.apache.org/tomcat-7.0-doc/config/context.html#Standard_Implementation
If both attributes are set properly, you should see the META-INF/context.xml
file from your application copied to “$CATALINA_BASE/conf/Catalina/localhost”.
But when i am putting this context.xml in ${CATALINA-BASE}/conf folder then
its working.
?xml version=1.0 encoding=UTF-8?
Context
Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource
maxActive=100 maxIdle=30 maxWait=-1
username=root password=root
driverClassName=com.mysql.jdbc.Driver
url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull/
/Context
*2. Mapping in web.xml*
resource-ref
descriptionMySql DataSource/description
res-ref-namejdbc/MaxDB/res-ref-name
res-typejavax.sql.DataSource/res-type
res-authContainer/res-auth
/resource-ref
*3. Then on my servlet i am getting the object of connection like this-*
private static InitialContext ic;
protected static DataSource datasource;
private static Context ctx;
public void doSomeStuff() throws DatabaseException
{
Connection conn = null;
try
{
conn= getConnection();
.
// do the required stuff
}
catch (Exception ex)
{
}
finally
{
conn.close();
}
}
*4. This is the method in my DAO Class i am using this method to get the
object of connection at all of my servlet.*
protected static Connection getConnection() throws DatabaseException
{
Connection conn = null;
try
{
ctx = new InitialContext();
datasource = (DataSource)
ctx.lookup(java:/comp/env/jdbc/MaxDB);
conn = datasource.getConnection();
}
catch (Exception ex)
{
}
return conn;
}
Using all this i am able to get connection. But if number of hits increases
to the server and Initially i got the the below exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
pool error Timeout waiting for idle object*
This means it was unable to get a connection from the pool within “maxWait” ms.
There are a few reasons this could happen, but I’d guess it’s because of the
next error that you reported.
Then i got the exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create
PoolableConnectionFactory (Access denied for user 'root'@'localhost' (using
password: YES))*
Seems like you can’t connect to the database. Have you double checked your
user / password / host configuration info?
Dan
Please assist me to know the root cause of the problem. I have searched it
on google and have read lots of forum but did not get the satisfactory
answer.
Hope that you all are expert and your suggestion will be valuable for me.
Thanking You!
*Best Regards,*
*Saurabh Sarasvat*
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
Dear Dan,
Thanks for your response!
I have cross checked the user / password configuration. All is correct. As
i mentioned that initially i am getting the object of connection but after
some time (After few hits to database from application) my web app goes to
slow and than it stops working i i got the below exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
pool error Timeout waiting for idle object*
I searched for this and find that reason is maxWait ms then i set it to
-1 i think which tends to unlimited time. Still i am facing the same
problem.
Can you please let me know what others reason can cause this exception.
*Best Regards,*
*Saurabh Sarasvat*
On Fri, Apr 4, 2014 at 5:16 PM, Daniel Mikusa dmik...@gopivotal.com wrote:
On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat ssaras...@pivotalindia.com
wrote:
Dear All,
I am doing connection pooling with tomcat 7.0.39 and MySQL 5.5.After
searching on google and with your help i have done the below things.
Even i am able to get the connection successfully using this but getting
some trouble and exception.
I am explaining you all steps done by me-
*1. Have created a context.xml*
I have put this context.xml in the META-INF folder of my application. but
when i am deploying the web app to the server then it is not creating its
copy to ${CATALINA-BASE}/conf/Catalina/locathost. Why is that so ?
There are a couple possibilities.
1.) Look at deployXML attribute of your Host tag. If this is set to
false, it will disable parsing the context XML descriptor embedded inside
the application. This defaults to true, unless you are running with a
security manager, then it defaults to false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
2.) Look at the copyXML attribute of both your Host and Context
tags. This needs to be set to true, because the default in Tomcat 7 is
false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
http://tomcat.apache.org/tomcat-7.0-doc/config/context.html#Standard_Implementation
If both attributes are set properly, you should see the
META-INF/context.xml file from your application copied to
$CATALINA_BASE/conf/Catalina/localhost.
But when i am putting this context.xml in ${CATALINA-BASE}/conf folder
then
its working.
?xml version=1.0 encoding=UTF-8?
Context
Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource
maxActive=100 maxIdle=30 maxWait=-1
username=root password=root
driverClassName=com.mysql.jdbc.Driver
url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull/
/Context
*2. Mapping in web.xml*
resource-ref
descriptionMySql DataSource/description
res-ref-namejdbc/MaxDB/res-ref-name
res-typejavax.sql.DataSource/res-type
res-authContainer/res-auth
/resource-ref
*3. Then on my servlet i am getting the object of connection like this-*
private static InitialContext ic;
protected static DataSource datasource;
private static Context ctx;
public void doSomeStuff() throws DatabaseException
{
Connection conn = null;
try
{
conn= getConnection();
.
// do the required stuff
}
catch (Exception ex)
{
}
finally
{
conn.close();
}
}
*4. This is the method in my DAO Class i am using this method to get the
object of connection at all of my servlet.*
protected static Connection getConnection() throws DatabaseException
{
Connection conn = null;
try
{
ctx = new InitialContext();
datasource = (DataSource)
ctx.lookup(java:/comp/env/jdbc/MaxDB);
conn = datasource.getConnection();
}
catch (Exception ex)
{
}
return conn;
}
Using all this i am able to get connection. But if number of hits
increases
to the server and Initially i got the the below exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
pool error Timeout waiting for idle object*
This means it was unable to get a connection from the pool within
maxWait ms. There are a few reasons this could happen, but I'd guess
it's because of the next error that you reported.
Then i got the exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create
PoolableConnectionFactory (Access denied for user 'root'@'localhost'
(using
password: YES))*
Seems like you can't connect to the database. Have you double checked
your user / password / host configuration info?
Dan
Please assist me to know the root cause of the problem. I have searched
it
on google and have read lots of forum but did not get the satisfactory
answer.
Hope that you all
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
On 4/4/2014 8:22 AM, Saurabh Saraswat wrote:
Dear Dan,
Thanks for your response!
I have cross checked the user / password configuration. All is correct. As
i mentioned that initially i am getting the object of connection but after
some time (After few hits to database from application) my web app goes to
slow and than it stops working i i got the below exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
pool error Timeout waiting for idle object*
I searched for this and find that reason is maxWait ms then i set it to
-1 i think which tends to unlimited time. Still i am facing the same
problem.
Can you please let me know what others reason can cause this exception.
Sounds to me like you're either leaking connection objects and
eventually run out of available connections in the pool, or your queries
are taking so long that you run out of connections before you get any of
them back.
*Best Regards,*
*Saurabh Sarasvat*
On Fri, Apr 4, 2014 at 5:16 PM, Daniel Mikusa dmik...@gopivotal.com wrote:
On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat ssaras...@pivotalindia.com
wrote:
Dear All,
I am doing connection pooling with tomcat 7.0.39 and MySQL 5.5.After
searching on google and with your help i have done the below things.
Even i am able to get the connection successfully using this but getting
some trouble and exception.
I am explaining you all steps done by me-
*1. Have created a context.xml*
I have put this context.xml in the META-INF folder of my application. but
when i am deploying the web app to the server then it is not creating its
copy to ${CATALINA-BASE}/conf/Catalina/locathost. Why is that so ?
There are a couple possibilities.
1.) Look at deployXML attribute of your Host tag. If this is set to
false, it will disable parsing the context XML descriptor embedded inside
the application. This defaults to true, unless you are running with a
security manager, then it defaults to false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
2.) Look at the copyXML attribute of both your Host and Context
tags. This needs to be set to true, because the default in Tomcat 7 is
false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
http://tomcat.apache.org/tomcat-7.0-doc/config/context.html#Standard_Implementation
If both attributes are set properly, you should see the
META-INF/context.xml file from your application copied to
$CATALINA_BASE/conf/Catalina/localhost.
But when i am putting this context.xml in ${CATALINA-BASE}/conf folder
then
its working.
?xml version=1.0 encoding=UTF-8?
Context
Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource
maxActive=100 maxIdle=30 maxWait=-1
username=root password=root
driverClassName=com.mysql.jdbc.Driver
url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull/
/Context
*2. Mapping in web.xml*
resource-ref
descriptionMySql DataSource/description
res-ref-namejdbc/MaxDB/res-ref-name
res-typejavax.sql.DataSource/res-type
res-authContainer/res-auth
/resource-ref
*3. Then on my servlet i am getting the object of connection like this-*
private static InitialContext ic;
protected static DataSource datasource;
private static Context ctx;
public void doSomeStuff() throws DatabaseException
{
Connection conn = null;
try
{
conn= getConnection();
.
// do the required stuff
}
catch (Exception ex)
{
}
finally
{
conn.close();
}
}
*4. This is the method in my DAO Class i am using this method to get the
object of connection at all of my servlet.*
protected static Connection getConnection() throws DatabaseException
{
Connection conn = null;
try
{
ctx = new InitialContext();
datasource = (DataSource)
ctx.lookup(java:/comp/env/jdbc/MaxDB);
conn = datasource.getConnection();
}
catch (Exception ex)
{
}
return conn;
}
Using all this i am able to get connection. But if number of hits
increases
to the server and Initially i got the the below exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
pool error Timeout waiting for idle object*
This means it was unable to get a connection from the pool within
maxWait ms. There are a few reasons this could happen, but I'd guess
it's because of the next error that you reported.
Then i got the exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create
PoolableConnectionFactory (Access denied for user 'root'@'localhost'
(using
password: YES))*
Seems like you can't connect to the database. Have you double checked
your user / password / host configuration info?
Dan
Please assist me to know the root cause of the
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote:
Dear Dan,
Thanks for your response!
I have cross checked the user / password configuration. All is correct.
If you’re getting “Access Denied” exceptions, there is only one cause and
that’s bad credentials (or host + credentials, because MySQL can limit access
based on the host). If you’re not seeing these any more then, disregard.
As i mentioned that initially i am getting the object of connection but after
some time (After few hits to database from application) my web app goes to
slow and than it stops working i i got the below exception-
*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
pool error Timeout waiting for idle object*
+1 to David’s suggestion. You could be leaking connections or your queries are
very slow.
Two suggestions:
1.) Enable the slow query log on your MySQL server and see if the queries are
slow. Alternatively, login to your MySQL server and run ‘show processlist”.
That will show you what queries are running.
2.) Enable DBCP’s abandoned connection detection. See the “removeAbandoned”
attribute.
http://commons.apache.org/proper/commons-dbcp/configuration.html
With this (and logAbandoned), the pool will alert you when your application
does not properly return connections to the pool.
Dan
I searched for this and find that reason is maxWait ms then i set it to
-1 i think which tends to unlimited time. Still i am facing the same
problem.
Can you please let me know what others reason can cause this exception.
*Best Regards,*
*Saurabh Sarasvat*
On Fri, Apr 4, 2014 at 5:16 PM, Daniel Mikusa dmik...@gopivotal.com wrote:
On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat ssaras...@pivotalindia.com
wrote:
Dear All,
I am doing connection pooling with tomcat 7.0.39 and MySQL 5.5.After
searching on google and with your help i have done the below things.
Even i am able to get the connection successfully using this but getting
some trouble and exception.
I am explaining you all steps done by me-
*1. Have created a context.xml*
I have put this context.xml in the META-INF folder of my application. but
when i am deploying the web app to the server then it is not creating its
copy to ${CATALINA-BASE}/conf/Catalina/locathost. Why is that so ?
There are a couple possibilities.
1.) Look at deployXML attribute of your Host tag. If this is set to
false, it will disable parsing the context XML descriptor embedded inside
the application. This defaults to true, unless you are running with a
security manager, then it defaults to false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
2.) Look at the copyXML attribute of both your Host and Context
tags. This needs to be set to true, because the default in Tomcat 7 is
false.
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implementation
http://tomcat.apache.org/tomcat-7.0-doc/config/context.html#Standard_Implementation
If both attributes are set properly, you should see the
META-INF/context.xml file from your application copied to
$CATALINA_BASE/conf/Catalina/localhost.
But when i am putting this context.xml in ${CATALINA-BASE}/conf folder
then
its working.
?xml version=1.0 encoding=UTF-8?
Context
Resource name=jdbc/MaxDB auth=Container type=javax.sql.DataSource
maxActive=100 maxIdle=30 maxWait=-1
username=root password=root
driverClassName=com.mysql.jdbc.Driver
url=jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=convertToNull/
/Context
*2. Mapping in web.xml*
resource-ref
descriptionMySql DataSource/description
res-ref-namejdbc/MaxDB/res-ref-name
res-typejavax.sql.DataSource/res-type
res-authContainer/res-auth
/resource-ref
*3. Then on my servlet i am getting the object of connection like this-*
private static InitialContext ic;
protected static DataSource datasource;
private static Context ctx;
public void doSomeStuff() throws DatabaseException
{
Connection conn = null;
try
{
conn= getConnection();
.
// do the required stuff
}
catch (Exception ex)
{
}
finally
{
conn.close();
}
}
*4. This is the method in my DAO Class i am using this method to get the
object of connection at all of my servlet.*
protected static Connection getConnection() throws DatabaseException
{
Connection conn = null;
try
{
ctx = new InitialContext();
datasource = (DataSource)
ctx.lookup(java:/comp/env/jdbc/MaxDB);
conn = datasource.getConnection();
}
catch (Exception ex)
{
}
return conn;
}
Using all this i am able to get connection. But if number of hits
increases
to the server and
RE: SQLNestedException in Connection Pooling With Tomcat 7.0.39
-Original Message- From: Saurabh Saraswat [mailto:ssaras...@pivotalindia.com] Sent: Friday, April 04, 2014 7:23 AM To: Tomcat Users List Subject: Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39 Dear Dan, Thanks for your response! I have cross checked the user / password configuration. All is correct. As i mentioned that initially i am getting the object of connection but after some time (After few hits to database from application) my web app goes to slow and than it stops working i i got the below exception- *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object* I searched for this and find that reason is maxWait ms then i set it to -1 i think which tends to unlimited time. Still i am facing the same problem. Can you please let me know what others reason can cause this exception. *Best Regards,* *Saurabh Sarasvat* --- FWIW, I also have these attributes in my context.xml file, below or in addition to what you have, Saurabh. maxIdle=30 maxWait=1 maxActive=10 testOnBorrow=true timeBetweenEvictionRunsMillis=-1 minEvictableIdleTimeMillis=28800 poolPreparedStatements=true removeAbandoned=true removeAbandonedTimeout=300 logAbandoned=false/ - 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: SQLNestedException in Connection Pooling With Tomcat 7.0.39
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Saurabh,
On 4/4/14, 1:19 AM, Saurabh Saraswat wrote:
I am doing connection pooling with tomcat 7.0.39 and MySQL
5.5.After searching on google and with your help i have done the
below things. Even i am able to get the connection successfully
using this but getting some trouble and exception. I am explaining
you all steps done by me-
[snip]
*3. Then on my servlet i am getting the object of connection like
this-*
private static InitialContext ic; protected static DataSource
datasource; private static Context ctx;
You shouldn't use class members for these: instead, do everything
locally in your getConnection method. It will make your servlet less
prone to error. If you want to fetch the DataSource one single time,
do it in a ServletContextListener and place the DataSource into the
application scope.
Just don't fetch it with every request and store it into a static
class member: you may run into odd thread behavior if you do this.
public void doSomeStuff() throws DatabaseException { Connection
conn = null; try { conn= getConnection();
. // do the required stuff
} catch (Exception ex) {
} finally { conn.close(); } }
Don't forget to:
1. wrap a try/catch block around conn.close: you don't want an
SQLException thrown from conn.close to mask any exception that is in
the process of being thrown. In the method above, you are catching all
exceptions (but not Throwables like NoSuchMethodError, etc.) so you
could still mask things.
2. Close all other JDBC resources you use (Statement, ResultSet).
3. Catch all exceptions/errors if rollback is a possibility.
- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJTPsqKAAoJEBzwKT+lPKRYNcQQAK3oNKSgXuCa1f3mFN/m/jPD
2YYkhmSFu7YpPdB6sZNvPPiPlce7Ddbxmqq9Rd84jhbxvn4C2cy0cNV7GAoJ7x8i
FnW+4UWGnnDKW3+9dS8D5JsdRbhplGYwdFWKI4smxXuZf+bUfvnpltQvtnVqacC8
cQu8tFy6XtlnxozlGKSurk4/6T+oqMSwneeuQxIh9bUvU3EwnX3aJGwcTvSNXJCk
vDOTQx+Z21Fv0CB0so33c/XfOKjB9r61zZs3GXahtpq3suCbi9Ch5hFv/FB9mjc9
cl4tGyzlbrV0kVpz4WSG9Q+/12bXt4W9aWamCH6ruZ1ddqeF9ONRmbb9dV1YZchK
Tf+/7WFB3o0Zn73/kYaDtnv1fOYBR0zDVIcO5zFNVvskLTMMzR55O2U+amujY/Aq
niPUPvBudf63H075DFJWj+9NeFUUduCgYUUgd+mmwj7PtN/0+Yu3RkUqUOEQbg0Y
OUqKrLw9G9EUYcNWlIrUk5U9PbN0pvNNpZ+jcKnYGQijSFkvWqX78YqZ5kP9yHVl
xJx0YwqL1UGZAjYUNZ276FVeA/UhVWInXzCRdjBmAvp8n6TrI39l0mftwtAh6AOQ
tyIPGio+Uvz9QrKl8azSRWQVIuwhEZcYluCn+M8zdc9yDq5u+bpNCoZxp7O50G9j
NMpO/UMsFes4OEOZTp+Q
=tV3f
-END PGP SIGNATURE-
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Daniel, On 4/4/14, 8:57 AM, Daniel Mikusa wrote: On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat ssaras...@pivotalindia.com wrote: Dear Dan, Thanks for your response! I have cross checked the user / password configuration. All is correct. If you’re getting “Access Denied” exceptions, there is only one cause and that’s bad credentials (or host + credentials, because MySQL can limit access based on the host). If you’re not seeing these any more then, disregard. I've never tried this, but it could also be due to connection-limits on the server itself. Having root limited to a certain number of connections sounds like a terrible idea, but then again, so does connecting as root in the first place. As i mentioned that initially i am getting the object of connection but after some time (After few hits to database from application) my web app goes to slow and than it stops working i i got the below exception- *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object* +1 to David’s suggestion. You could be leaking connections or your queries are very slow. +1 Two suggestions: 1.) Enable the slow query log on your MySQL server and see if the queries are slow. Alternatively, login to your MySQL server and run ‘show processlist”. That will show you what queries are running. +1 2.) Enable DBCP’s abandoned connection detection. See the “removeAbandoned” attribute. +10 With this (and logAbandoned), the pool will alert you when your application does not properly return connections to the pool. In development, I always recommend that you use maxActive=1 for your connection pools. This will expose any potential deadlocks you may have mistakenly coded into your application. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTPssiAAoJEBzwKT+lPKRYTx4QAK8a4WJIx1i+yWyZquSrCnad RBNB69jnPmYG0Uzc/yKyHzOXvv2wt1vE22wYyp64b4FFVqQNBmEnnm6XI20PSR2i Yt9lm5wZ5/5fsCGvj39B8E11GCao5enzkhXUpa51spLnjHfw5k3o0gGmWAqhLVza nOfbG+rTjjjXCrr1Y6tz0g+35M+w02TIh87Z5xdkvboqv/NRfxbGKRIZB2e1zT0K USY4skgug3L1TpKiXgoRNv7g7gbxHB7AXgL1po+PI1T1mNXCakUE81O26Etv/wm2 1A/d15LfCLou0uWQSfHPqaoODGFVOTsRWwn8xiJdjo2Ah/y7OqXfzMQh41UBO8H7 jNmakIHlb6NYDJK6LiRFlGw5K9AEO+dNFJ9e6Gi4kELB4Kn6CGqFRD3aqTsVerOb EhEG844nDmVRzr7gwK58aXSICy8PURDOfmZ+IaXehz0MARnKQiog3cWBT+EKIHxq RUAc0T/YEG+Qm1jiZef5h+NuMZLrzczQIOXXGYkjcMwGcUxmjzBbbvYbr56g84jL 3ukIXp6bnOvyIdB8jnibbICoR/sj0Mg4zia7vTPkqdXbU3Ng2W6/lV9K2Mnm9aDL OcLocnWnFGZycukIDYtfbtZOY7wTAqk5fJsZauDQGeeA4M9UXu4dPgpaoahuK8Dq moJwtEq/5/JNXkctdS7n =RI4v -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
