-
1. Re: How to mark db pool connection as faulty?
jesper.pedersen Apr 8, 2014 10:46 AM (in response to asmec)1 of 1 people found this helpfulJust use an <exception-sorter> which marks the SQLException you want as fatal. Then choose the <flush-strategy> that you are looking for.
-
2. Re: How to mark db pool connection as faulty?
asmec Apr 8, 2014 3:31 PM (in response to jesper.pedersen)Thanks!
Sorry I'm super new with WF, so need bit more details
Tthe flush-strategy I select to be for eg IdleConnections (this will reconnect all idle and all faulty ones right)
The exception-sorter in wf console I put a name of a class that will handle exceptions and decide what to do like
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter”/>
I'm not sure how this works then. I get connection from the pool (that's invalid) and try to use it, I get exception, I handle that exception and then what? In GF I'd markConnectionAsBad but what should I do here? will the
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter
somehow know the exception happened and reconnect or should I throw some exception here or should I not handle exception ?!thanks in advance
-
3. Re: How to mark db pool connection as faulty?
jesper.pedersen Apr 8, 2014 3:45 PM (in response to asmec)Connection validation would be <valid-connection-checker> with <validate-on-match> or <background-validation>.
<exception-sorter> and <flush-strategy> is when a SQLException is thrown, and the sorter detects it is fatal through its SQL error code. IdleConnections will kill the faulty connection, and all connections currently idle in the pool.
You will likely find that most of this is "by magic"
-
4. Re: Re: How to mark db pool connection as faulty?
asmec Apr 8, 2014 6:13 PM (in response to jesper.pedersen)I have very bad experience with "by magic" so I try to figure out how stuff works from start to end before I trust it
I seen pools reset hundreds of connections without need, I seen pools reset working connections mid query, I seen pools checking if connections is valid every time connection is taken from the pool .. all that was described as "works by magic" but was actually only giving appearance of working while creating many serious issues on loaded system.
If I setup exception-sorter class name to "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"
and flush-strategy to "failingconn only" and have a code doing:
try{
stmt = con.createStatement();
stmt.executeUpdate("Update");
}
catch (Exception e){
// What should I do here
}
finally{
stmt.close();
con.close(); //Connection will be destroyed during close.
}
Where did this exception sorter pick up the exception? What should I do in my exception handling? Will I get exception at all? What is actually happening? Is exception-sorter inserting itself as connection/datasource so it internally handles exception and then throws it back to me to handle it too while it marks connection bad or ?
Initial test I have made is configure pool with 10 connections with these handlers and then
for (i=0;i<10;i++) con[i]=ds.getConnection();
sleep(100000);
con[1].prepare...
now I expect first time I call this to work ok, then next time I call it during sleep I kill all connections directly on mysql server, now I expect prepare to fail, that's ok but next time I call same script it fails again, and again, and again, and I don't see pool creating new connections on mysql server ? so the "automagical" solution well .. I definitely miss-configured something since I'm not getting how this thing works
thanks for bearing with me, too much time spent learning GF tweaks so I think in "how it works in GF" not how it "should" work