Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (athena.apache.org: domain of dmikusa@gopivotal.com
 designates 209.85.213.48 as permitted sender)
Content-Type: text/plain; charset=windows-1252
Mime-Version: 1.0 (Mac OS X Mail 7.2 \(1874\))
Subject: Re: SQLNestedException in Connection Pooling With Tomcat 7.0.39
From: Daniel Mikusa <dmikusa@gopivotal.com>
In-Reply-To: 
 <CAJABtvSbjLp=TUOe-iKEZdpPyTEX8KZN1gRyH+rrWtev2gcnkg@mail.gmail.com>
Date: Fri, 4 Apr 2014 08:57:13 -0400
Content-Transfer-Encoding: quoted-printable
Message-Id: <36E9B751-5C61-4CE3-A222-34AB48DF7693@gopivotal.com>
References: 
 <CAJABtvTQh8-FooYjJ7R0vBwWW0mh3z0-UrFrTHDSLyy8=4sWNA@mail.gmail.com>
 <4BD44A77-9A8D-4E39-9354-A8C70C844CD0@gopivotal.com>
 <CAJABtvSbjLp=TUOe-iKEZdpPyTEX8KZN1gRyH+rrWtev2gcnkg@mail.gmail.com>
To: Tomcat Users List <users@tomcat.apache.org>

On Apr 4, 2014, at 8:22 AM, Saurabh Saraswat =
<ssaraswat@pivotalindia.com> wrote:

> Dear Dan,
>=20
> Thanks for your response!
>=20
> I have cross checked the user / password configuration. All is =
correct.

If you=92re getting =93Access Denied=94 exceptions, there is only one =
cause and that=92s bad credentials (or host + credentials, because MySQL =
can limit access based on the host).  If you=92re 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=92s suggestion.  You could be leaking connections or your =
queries are very slow. =20

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 =
=91show processlist=94.  That will show you what queries are running.

2.) Enable DBCP=92s abandoned connection detection.  See the =
=93removeAbandoned=94 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

>=20
> 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.
>=20
> Can you please let me know what others reason can cause this =
exception.
>=20
>=20
> *Best Regards,    *
>=20
> *Saurabh Sarasvat*
>=20
>=20
>=20
>=20
> On Fri, Apr 4, 2014 at 5:16 PM, Daniel Mikusa <dmikusa@gopivotal.com> =
wrote:
>=20
>> On Apr 4, 2014, at 1:19 AM, Saurabh Saraswat =
<ssaraswat@pivotalindia.com>
>> wrote:
>>=20
>>> Dear All,
>>>=20
>>> 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-
>>>=20
>>> *1. Have created a context.xml*
>>>=20
>>> 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 ?
>>=20
>> There are a couple possibilities.
>>=20
>> 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.
>>=20
>>=20
>> =
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implemen=
tation
>>=20
>> 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.
>>=20
>>=20
>> =
http://tomcat.apache.org/tomcat-7.0-doc/config/host.html#Standard_Implemen=
tation
>>=20
>> =
http://tomcat.apache.org/tomcat-7.0-doc/config/context.html#Standard_Imple=
mentation
>>=20
>> 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".
>>=20
>>>=20
>>> But when i am putting this context.xml in ${CATALINA-BASE}/conf =
folder
>> then
>>> its working.
>>>=20
>>> <?xml version=3D"1.0" encoding=3D"UTF-8"?>
>>>=20
>>>=20
>>> <Context>
>>> <Resource name=3D"jdbc/MaxDB" auth=3D"Container" =
type=3D"javax.sql.DataSource"
>>>              maxActive=3D"100" maxIdle=3D"30" maxWait=3D"-1"
>>>              username=3D"root" password=3D"root"
>>> driverClassName=3D"com.mysql.jdbc.Driver"
>>>=20
>>>=20
>> =
url=3D"jdbc:MySQL://localhost:3306/MaxDB?zeroDateTimeBehavior=3DconvertToN=
ull"/>
>>>=20
>>> </Context>
>>>=20
>>> *2. Mapping in web.xml*
>>>=20
>>> <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>
>>>=20
>>> *3. Then on my servlet i am getting the object of connection like =
this-*
>>>=20
>>>   private static InitialContext ic;
>>>   protected static DataSource datasource;
>>>   private static Context ctx;
>>>=20
>>> public void doSomeStuff() throws DatabaseException
>>> {
>>>      Connection conn =3D null;
>>>      try
>>>      {
>>>         conn=3D getConnection();
>>>=20
>>>         .....................................
>>>        // do the required stuff
>>>=20
>>>      }
>>>      catch (Exception ex)
>>>       {
>>>=20
>>>       }
>>>       finally
>>>       {
>>>         conn.close();
>>>       }
>>>=20
>>> }
>>>=20
>>> *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.*
>>>=20
>>>  protected static Connection getConnection() throws =
DatabaseException
>>> {
>>>       Connection conn =3D null;
>>>       try
>>>       {
>>>           ctx =3D new InitialContext();
>>>           datasource =3D (DataSource)
>>> ctx.lookup("java:/comp/env/jdbc/MaxDB");
>>>           conn =3D datasource.getConnection();
>>>       }
>>>       catch (Exception ex)
>>>       {
>>>=20
>>>       }
>>>       return conn;
>>>   }
>>>=20
>>> 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-
>>>=20
>>> *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a =
connection,
>>> pool error Timeout waiting for idle object*
>>=20
>> 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.
>>=20
>>>=20
>>> Then i got the exception-
>>>=20
>>> *org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create
>>> PoolableConnectionFactory (Access denied for user 'root'@'localhost'
>> (using
>>> password: YES))*
>>=20
>> Seems like you can't connect to the database.  Have you double =
checked
>> your user / password / host configuration info?
>>=20
>> Dan
>>=20
>>>=20
>>> 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.
>>>=20
>>> Hope that you all are expert and your suggestion will be valuable =
for me.
>>>=20
>>> Thanking You!
>>>=20
>>> *Best Regards,    *
>>>=20
>>> *Saurabh Sarasvat*
>>=20
>>=20
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>=20
>>=20


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