Bug 50660

Summary: Improve validationQuery error handling
Product: Tomcat Modules Reporter: Daniel Mikusa <dmikusa>
Component: jdbc-poolAssignee: Tomcat Developers Mailing List <dev>
Status: RESOLVED FIXED    
Severity: minor    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: proposed interceptor

Description Daniel Mikusa 2011-01-26 10:29:22 UTC 
If an invalid validationQuery is specified, the SQL Exception is logged at the DEBUG log level.

Example:

2011-01-25 18:32:56,139 DEBUG [org.apache.tomcat.jdbc.pool.PooledConnection] Unable to validate object:
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;SELECT 1;<table_expr>

Since an invalid validationQuery can cause problems with the data source, it would be nice to see this message logged at a different log level, such as WARN or ERROR.

If that would create too much noise then I would suggest instead to add a check during the initialization of the data source that would test the validation query.  Then this single check could log at WARN or ERROR.

Thanks

Dan
Comment 1 Filip Hanik 2011-01-26 12:05:16 UTC 
hi Dan, this is a tricky one. 
Cause, we'd also be logging a lot of false positive. Connections that have gone stale and simply need to be reconnected. 
So WARN log level would not be good in that case. 
However, I agree that initially you would want to know that the query is invalid.
Comment 2 Daniel Mikusa 2011-01-26 12:48:21 UTC 
Created attachment 26553 [details]
proposed interceptor
Comment 3 Daniel Mikusa 2011-01-26 12:49:46 UTC 
What about adding an Interceptor to do the validation?  The attached interceptor would take the validation query and run it when the pool is created.  If there is a problem with the query it would report just that one time.

Dan
Comment 4 Filip Hanik 2011-01-26 13:23:24 UTC 
If the connection to the database fails here, the interceptor will print out validationQuery has failed. and that is not correct
I think the pool itself needs to do some validations upon startup

1. validation query
2. transaction isolation level
3. schema/catalog
4. default read only

this is a valid bug, just needs to encompass the correct solution

best
Filip
Comment 5 Filip Hanik 2012-03-20 13:48:03 UTC 
(In reply to comment #3)
> What about adding an Interceptor to do the validation?  The attached
> interceptor would take the validation query and run it when the pool is
> created.  If there is a problem with the query it would report just that one
> time.
> 
> Dan

I'll add in logging when the pool starts. Right now, if initializing the pool fails, it sends a JMX notification but doesn't log anything.  

looking in the code it does
            if (log.isDebugEnabled())
                log.debug("Unable to validate object:",ignore);

So that should already be taken care of