You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Saurabh Saraswat <ss...@pivotalindia.com> on 2014/04/04 07:19:46 UTC

SQLNestedException in Connection Pooling With Tomcat 7.0.39

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 ?

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>
 <description>MySql DataSource</description>
 <res-ref-name>jdbc/MaxDB</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</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*

Then i got the exception-

*org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create
PoolableConnectionFactory (Access denied for user 'root'@'localhost' (using
password: YES))*



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*

Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by David kerber <dc...@verizon.net>.
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 <dm...@gopivotal.com> wrote:
>
>> On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat <ss...@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>
>>> <description>MySql DataSource</description>
>>> <res-ref-name>jdbc/MaxDB</res-ref-name>
>>> <res-type>javax.sql.DataSource</res-type>
>>> <res-auth>Container</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-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


RE: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by "Propes, Barry L " <ba...@citi.com>.

-----Original Message-----
From: Saurabh Saraswat [mailto:ssaraswat@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="10000"
    maxActive="10"
    testOnBorrow="true"
    timeBetweenEvictionRunsMillis="-1"
    minEvictableIdleTimeMillis="28800"
    poolPreparedStatements="true"
    removeAbandoned="true"
    removeAbandonedTimeout="300"
    logAbandoned="false"/>


>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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="10000"

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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Saurabh Saraswat <ss...@pivotalindia.com>.
Chris,

Thanks for your regular guidance and valuable suggestions!

On Fri, Apr 11, 2014 at 11:33 AM, Christopher Schultz <
chris@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
> > <ss...@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="10000" 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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>

regards,
Saurabh

Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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
> <ss...@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="10000" 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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Daniel Mikusa <dm...@gopivotal.com>.
On Apr 8, 2014, at 8:22 AM, Saurabh Saraswat <ss...@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="10000"
>               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 <dm...@gopivotal.com> wrote:
> 
>> On Apr 8, 2014, at 5:46 AM, Saurabh Saraswat <ss...@pivotalindia.com>
>> wrote:
>> 
>>> On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz <
>>> chris@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
>>>>> <ss...@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-unsubscribe@tomcat.apache.org
>>>> For additional commands, e-mail: users-help@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-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>> 
>> 


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Saurabh Saraswat <ss...@pivotalindia.com>.
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="10000"
               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 <dm...@gopivotal.com> wrote:

> On Apr 8, 2014, at 5:46 AM, Saurabh Saraswat <ss...@pivotalindia.com>
> wrote:
>
> > On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz <
> > chris@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
> >>> <ss...@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-unsubscribe@tomcat.apache.org
> >> For additional commands, e-mail: users-help@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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>

Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Daniel Mikusa <dm...@gopivotal.com>.
On Apr 8, 2014, at 5:46 AM, Saurabh Saraswat <ss...@pivotalindia.com> wrote:

> On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz <
> chris@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
>>> <ss...@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-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Saurabh Saraswat <ss...@pivotalindia.com>.
On Fri, Apr 4, 2014 at 8:39 PM, Christopher Schultz <
chris@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
> > <ss...@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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@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

Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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
> <ss...@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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Daniel Mikusa <dm...@gopivotal.com>.
On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat <ss...@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 <dm...@gopivotal.com> wrote:
> 
>> On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat <ss...@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>
>>> <description>MySql DataSource</description>
>>> <res-ref-name>jdbc/MaxDB</res-ref-name>
>>> <res-type>javax.sql.DataSource</res-type>
>>> <res-auth>Container</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-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>> 
>> 


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Saurabh Saraswat <ss...@pivotalindia.com>.
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 <dm...@gopivotal.com> wrote:

> On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat <ss...@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>
> > <description>MySql DataSource</description>
> > <res-ref-name>jdbc/MaxDB</res-ref-name>
> > <res-type>javax.sql.DataSource</res-type>
> > <res-auth>Container</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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>

Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Daniel Mikusa <dm...@gopivotal.com>.
On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat <ss...@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>
> <description>MySql DataSource</description>
> <res-ref-name>jdbc/MaxDB</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39

Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org