logging database queries 1

[developerinlondon]

i want to be able to log the database queries that are made onto a file. Presently I have log4j installed and logging my classes.
I am using apache dbcp to make a database connection pool by configuring the context.xml file and calling it from the classes to run queries. would i need to subclass a dbcp class? (if so any examples?) or can i configure dbcp/jdbc driver to log the queries that are sent to it?

 

[sedj]

I know some pools certainly do optionally log SQL queries, but I'm not sure if DBCP does (I *think* it doesn't).

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

so is there any way I can extend DBCP so that it logs queries?

 

[sedj]

Of course ! It is open source, so just download it and read the source code until you find the bit you need to change !

Or ... you could use some pooling that does have the features you require.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

... does yours by any chance, sedj?

Tim

 

[sedj]

LOL, now seeing as you asked ... why yes, it does

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

sedj,
after your recommendation on tek-tips earlier on i decided to go ahead with primrose and give it a try. but i seem to be getting an error coming up:
WARNING: Unexpected exception resolving reference
java.lang.NullPointerException
at uk.org.primrose.pool.datasource.MasterPoolDataSourceFactory.getObjectInstance(MasterPoolDataSourceFactory.java:49)
at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:129)
at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
at org.apache.naming.NamingContext.lookup(NamingContext.java:792)
at org.apache.naming.NamingContext.lookup(NamingContext.java:139)
at org.apache.naming.NamingContext.lookup(NamingContext.java:780)
at org.apache.naming.NamingContext.lookup(NamingContext.java:139)
at org.apache.naming.NamingContext.lookup(NamingContext.java:780)
at org.apache.naming.NamingContext.lookup(NamingContext.java:139)
at org.apache.naming.NamingContext.lookup(NamingContext.java:780)
at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
at org.apache.naming.SelectorContext.lookup(SelectorContext.java:136)
at javax.naming.InitialContext.lookup(InitialContext.java:351)
at main.MultiFetcher.getMyDatabase(MultiFetcher.java:185)
at main.MultiFetcher.processRequest(MultiFetcher.java:95)
at main.MultiFetcher.doGet(MultiFetcher.java:164)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)

this comes up in catalina.out, whether i put it in server.xml and do a ResourceLink or put a resource directly in my apps context.xml I alwasy get this error.

the resources looks like below:
<Resource name="masterPool" auth="Container"
type="java.util.ArrayList"
factory="uk.org.primrose.pool.datasource.MasterPoolDataSourceFactory"
configFile="/opt/tomcat/conf/poolConfig.properties"
description="Holds all the pools"/>

<Resource name="jdbc/devDatabase" auth="Container"
type="uk.org.primrose.pool.datasource.PoolDataSource"
factory="uk.org.primrose.pool.datasource.MasterPoolDataSourceFactory"
description="Dev MySQL Database pool"
poolName="devDatabase" />

<Resource name="jdbc/liveDatabase" auth="Container"
type="uk.org.primrose.pool.datasource.PoolDataSource"
factory="uk.org.primrose.pool.datasource.MasterPoolDataSourceFactory"
description="Live MySQL Database pool"
poolName="liveDatabase" />

and poolConfig.properties has the following -
##### webmap pool instance #####
poolName=devDatabase
base=5
overflow=1
log=../logs/dbpools_${yyyy-MM-dd}.log
idleTime=120000
messageLogging=true
sizeLogging=true
driverClass=org.gjt.mm.mysql.Driver
driverURL=jdbc:mysql://xxx:3306/xml_availability_dev?autoReconnect=true
user=xxx
password=xxx
killActiveConnectionsOverAge=120000
cycleConnections=1000

##### webmap pool instance #####
poolName=liveDatabase
base=5
overflow=1
log=../logs/dbpools_${yyyy-MM-dd}.log
idleTime=120000
messageLogging=true
sizeLogging=true
driverClass=org.gjt.mm.mysql.Driver
driverURL=jdbc:mysql://xxx:3306/xml_availability?autoReconnect=true
user=xxx
password=xxx
killActiveConnectionsOverAge=120000
cycleConnections=1000

any ideas where I went wrong?

thanks in advance!

nayeem
[ps i couldnt post on your forum, is firefox a problem?]

 

[developerinlondon]

this is the error I get (catalina.out) if I put them in the server.xml as the documentation -
!!!!!!!!!!!!!
!!!!!!!!!!!!!
27-May-2005 11:47:55 org.apache.naming.NamingContext lookup
WARNING: Unexpected exception resolving reference
java.lang.NullPointerException
at uk.org.primrose.pool.datasource.MasterPoolDataSourceFactory.getObjectInstance(MasterPoolDataSourceFactory.java:49)
at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:129)
at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
at org.apache.naming.NamingContext.lookup(NamingContext.java:792)
at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:138)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:143)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:108)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.lifecycleEvent(GlobalResourcesLifecycleListener.java:80)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:676)
at org.apache.catalina.startup.Catalina.start(Catalina.java:537)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:271)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:409)
27-May-2005 11:47:55 org.apache.catalina.mbeans.GlobalResourcesLifecycleListener createMBeans
SEVERE: Exception processing Global JNDI Resources
javax.naming.NamingException
at org.apache.naming.NamingContext.lookup(NamingContext.java:804)
at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:138)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:143)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:108)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.lifecycleEvent(GlobalResourcesLifecycleListener.java:80)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:676)
at org.apache.catalina.startup.Catalina.start(Catalina.java:537)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:271)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:409)

 

[developerinlondon]

ok got past that. I was setting the wrong factory for the pools. but now i am getting a new problem:
[MasterPoolDataSourceFactory] Loading pools from /opt/tomcat/conf/poolConfig.properties.
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:271)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:409)
Caused by: java.lang.NoClassDefFoundError: javax/mail/MessagingException
at uk.org.primrose.pool.jmx.PoolControllerListener.doSend(PoolControllerListener.java:165)
at uk.org.primrose.pool.jmx.PoolControllerListener.handleNotification(PoolControllerListener.java:135)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor$ListenerWrapper.handleNotification(DefaultMBeanServerInterceptor.jav
a:1652)
at javax.management.NotificationBroadcasterSupport.handleNotification(NotificationBroadcasterSupport.java:221)
at javax.management.NotificationBroadcasterSupport.sendNotification(NotificationBroadcasterSupport.java:184)
at uk.org.primrose.pool.jmx.PoolController.createNewPoolQueue(PoolController.java:179)
at uk.org.primrose.pool.jmx.PoolController.loadPoolsFromConfigFile(PoolController.java:555)
at uk.org.primrose.pool.jmx.PoolController.loadPoolsFromConfigFile(PoolController.java:532)
at uk.org.primrose.pool.jmx.PoolController.loadPoolsFromConfigFile(PoolController.java:512)
at uk.org.primrose.pool.datasource.MasterPoolDataSourceFactory.getObjectInstance(MasterPoolDataSourceFactory.java:63)
at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:129)
at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
at org.apache.naming.NamingContext.lookup(NamingContext.java:792)
at org.apache.naming.NamingContext.lookup(NamingContext.java:152)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:138)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.createMBeans(GlobalResourcesLifecycleListener.java:108)
at org.apache.catalina.mbeans.GlobalResourcesLifecycleListener.lifecycleEvent(GlobalResourcesLifecycleListener.java:80)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:676)
at org.apache.catalina.startup.Catalina.start(Catalina.java:537)
... 6 more

 

[sedj]

Which Tomcat version you using ?

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

ok so if i am correct, the javax.mail package is not available, and i checked theres no sign of mail.jar anywhere... i know it sounds a bit naive but any ideas where I can get it from?

 

[sedj]

You need activation.jar from here :

http://java.sun.com/products/javabeans/glasgow/jaf.html

and mail.jar from here :

http://java.sun.com/products/javamail/downloads/index.html

(first section named "JavaMail 1.3.2 Release")

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

great its working now thanks!
Would it make sense to include these two jars (activation.jar and mail.jar) with the distribution? It doesnt seem to come standard with either tomcat or jdk. or at least that step needs to be added on the documentation.

in any case, I really like the detailed loggings in it! Very professional!!

Lets see if I can debug my database related problems with it now!

cheers.

 

[developerinlondon]

do you know how I can stop it from logging the queries only? (I saw messageLog and sizeLog options in the web control panel but would like to keep all logging except the queryloggings).

thanks

 

[sedj]

Yes, I think I will add them. Several people now have asked me for copies of them !

BTW, in your post above I noticed there were some "!!!!!!!!!!!!!" outputted - is this coming from primrose ? I thought I had taken them out in version 2.4.5 (I supidly left them in one day after a debugging session). Is that the version you are using ?

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

yes the ! signs are from primrose. I downloaded the only one I could download from the site, it doesnt say any version number but on the web control panel on the right I see JDMK5.1_r01, so its 5.1? Or am I looking at the wrong place...

 

[sedj]

Currently, you can only turn on/off the size of the pools, or on/off for all other messages ...

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[developerinlondon]

so if I turn messages and size off would I still get notified of any critical problems? (eg running out of connection, cant connect to database etc.)

 

[sedj]

Yes, you would.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk