[javamelody] JDBC Monitoring is always "0" (Zero)

[kapilok]

Hi Community,

I am using Tomcat 5.5 with multiple c3p0 connection pools configured
as below.
As per the javamelody documentation any JNDI datasources starting with
"jdbc/" should be discovered and their metrics captured.

But, I always get "0" for JDBC active connections and Used JDBC
connections.

Please advise.

Connection pool config in server.xml :
<Resource auth="Container"
driverClassName="net.sourceforge.jtds.jdbc.Driver" logAbandoned="true"
maxActive="5" maxIdle="5" maxWait="50000" name="jdbc/iyogi_datasource"
password="Global@123" removeAbandoned="true"
type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://172.16.20.107/
iyogi18feb" username="sa"/>

<Resource auth="Container"
driverClassName="net.sourceforge.jtds.jdbc.Driver" logAbandoned="true"
maxActive="5" maxIdle="2" maxWait="50000" name="jdbc/pms_datasource"
password="Global@123" removeAbandoned="true"
type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://172.16.20.107/
pms18feb" username="sa"/>

<Resource auth="Container"
driverClassName="net.sourceforge.jtds.jdbc.Driver" logAbandoned="true"
maxActive="5" maxIdle="2" maxWait="50000" name="jdbc/audit_datasource"
password="Global@123" removeAbandoned="true"
type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://172.16.20.107/
iyogi18feb" username="sa"/>

<Resource auth="Container"
driverClassName="net.sourceforge.jtds.jdbc.Driver" logAbandoned="true"
maxActive="5" maxIdle="5" maxWait="50000" name="jdbc/
idmaster_datasource" password="Global@123" removeAbandoned="true"
type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://172.16.20.107/
iyogi18feb" username="sa"/>

<Resource auth="Container"
driverClassName="net.sourceforge.jtds.jdbc.Driver" logAbandoned="true"
maxActive="300" maxIdle="25" maxWait="50000" name="jdbc/
cti_pms_datasource" password="Global@123" removeAbandoned="true"
type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://172.16.20.107/
pms18feb" username="sa"/>


--
Subscription settings: http://groups.google.com/group/javamelody/subscribe?hl=en

[Emeric Vernat]

Hi,

I have just tested again with tomcat 5.5.27 (and tomcat 6) and I do not
reproduce your issue with Tomcat 5.5.
JDBC datasources defined in xml context file (in
$TOMCAT_HOME/conf/Catalina/localhost/ directory) or in server.xml
"GlobalNamingResources" with "ResourceLink" in context file are both
monitored.
The names of my jdbc datasources start with "jdbc/" in both cases and
these datasources are looked up with: new
InitialContext().lookup("java:comp/env/jdbc/TestDB").

I tested that the parameter "datasources" is not needed in this case and
that the "resource-ref" part in web.xml is not needed either to have
working and monitored datasources.
Note that the "resource-ref" part in web.xml is documented to be needed
in tomcat docs, so I would recommend to include it:
Tomcat 5.5:
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#MySQL%20DBCP%20Example
Tomcat 6:
http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html#MySQL%20DBCP%20Example


But something is a little strange in your post. The jdbc datasources
configurations you have given are perfect tomcat datasources. And tomcat
uses DBCP connection pools for these datasources.
Why do you say "c3p0 connection pools"? Are you sure that you use the
datasources?

Another question: When do you look up the datasources? Every time you
need it or just once before or after javamelody's filter and listener
init? (If it is only once and if it is before javamelody's filter and
listener init, then it could be a problem).

bye, Emeric
Post Scriptum: you should not use MS sql server, because it could be
that an opensource library like javamelody has an unconscious
incompatibility with MS products...
Of course I'm just kidding, since the jdbc api is the same for all
databases ;-)

[Emeric Vernat]

Hi,

I have created an issue for this:
http://code.google.com/p/javamelody/issues/detail?id=20

As said in the issue, I have committed a fix in trunk of svn to fix this.
And I have built from current trunk a jar with the fix which is available here:
http://javamelody.googlecode.com/files/javamelody-20100422.jar

With this fix, datasources are now automatically monitored when defined with
JndiObjectFactoryBean in your Spring context file, at the condition that the Spring
context file of javamelody (net/bull/javamelody/monitoring-spring.xml, included in
the provided jar) is loaded as one of the first configuration files
( as said in http://code.google.com/p/javamelody/wiki/UserGuide#7._JDBC ).

The fix is in fact a change in the post-processor used in monitoring-spring.xml,
which adds a proxy to JndiObjectFactoryBean instance(s) in order to monitor the
datasource(s).


I am very interested to know if this new jar fix the issue for you.

So you can:
- try this new jar when you wish
- or just wait about 2 weeks to use the next release (1.15)
- or try lookupOnStartup/proxyInterface and cache properties in
JndiObjectFactoryBean, while waiting for the next release
- or everything else suitable to you

bye, Emeric


Le 21/04/2010 07:35, kapilok a �crit :
> 1. Ignore the "c3p0 connection pools". I was using the default tomcat
> datasources.
>
> 2. The datasources are looked-up using spring, so I guess the lookup
> is just once. Please advise what I should do in this case.
> <!-- Datasource definition -->
>
> <bean id="dataSource"
> class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName"
> value="java:comp/env/jdbc/iyogi_datasource" />
> </bean>
>
> <bean id="dataSourcePMS"
> class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName"
> value="java:comp/env/jdbc/pms_datasource" />
> </bean>
>
> <bean id="dataSourceIdMaster"
> class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName"
> value="java:comp/env/jdbc/idmaster_datasource" />
> </bean>

>
> On Apr 20, 8:40 pm, Emeric Vernat<ever...@free.fr> wrote:
>
>> Hi,
>>
>> I have just tested again with tomcat 5.5.27 (and tomcat 6) and I do not

>> reproduce your issue with Tomcat 5.5.JDBCdatasources defined in xml context file (in

>> $TOMCAT_HOME/conf/Catalina/localhost/ directory) or in server.xml
>> "GlobalNamingResources" with "ResourceLink" in context file are both
>> monitored.

>> The names of myjdbcdatasources start with "jdbc/" in both cases and

>> these datasources are looked up with: new
>> InitialContext().lookup("java:comp/env/jdbc/TestDB").
>>
>> I tested that the parameter "datasources" is not needed in this case and
>> that the "resource-ref" part in web.xml is not needed either to have
>> working and monitored datasources.
>> Note that the "resource-ref" part in web.xml is documented to be needed
>> in tomcat docs, so I would recommend to include it:

>> Tomcat 5.5:http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howt...
>> Tomcat 6:http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howt...

>>
>> But something is a little strange in your post. Thejdbcdatasources
>> configurations you have given are perfect tomcat datasources. And tomcat
>> uses DBCP connection pools for these datasources.
>> Why do you say "c3p0 connection pools"? Are you sure that you use the
>> datasources?
>>
>> Another question: When do you look up the datasources? Every time you
>> need it or just once before or after javamelody's filter and listener
>> init? (If it is only once and if it is before javamelody's filter and
>> listener init, then it could be a problem).
>>
>> bye, Emeric
>> Post Scriptum: you should not use MS sql server, because it could be
>> that an opensource library like javamelody has an unconscious
>> incompatibility with MS products...

>> Of course I'm just kidding, since thejdbcapi is the same for all
>> databases ;-)
>>
>> Le 20/04/2010 07:57, kapilok a �crit :

[kapilok]

Hi,

I tried the new jar.
I can now see the Database and JDBC Datasource under "System
Information".
But the JDBC connection graphs are still always showing "0".
I think its now recognizing the datasources from spring-config, but
still unable to log the connection data that is used for graphing.

Did you test that the graphs work?

Thanks anyways.
Kapil

On Apr 22, 6:00 pm, Emeric Vernat <ever...@free.fr> wrote:
> Hi,
>

[Emeric Vernat]

Hi Kapil,

Yes, I tested and the graphs work, using only a datasource from
JndiObjectFactoryBean.

May I ask if you used the file javamelody-20100422.jar and if you used
net/bull/javamelody/monitoring-spring.xml (included in the provided jar)
as said in http://code.google.com/p/javamelody/wiki/UserGuide#7._JDBC ?

And if you can debug with javamelody's sources, is the proxy of
JndiObjectFactoryBean correctly created in
SpringDataSourceBeanPostProcessor?

bye, Emeric


Le 23/04/2010 13:22, kapilok a �crit :