How to mark db pool connection as faulty?

[asmec]

GF has a way to mark connection faulty so that pool will reconnect.

For e.g.

com.sun.appserv.jdbc.DataSource ds=

  (com.sun.appserv.jdbc.DataSource)context.lookup("dataSource");

Connection con = ds.getConnection();

Statement stmt = null;

try{

  stmt = con.createStatement();

  stmt.executeUpdate("Update");

}

catch (BadConnectionException e){

  ds.markConnectionAsBad(con) //marking it as bad for removal

}

finally{

  stmt.close(); 

  con.close(); //Connection will be destroyed during close.

}

how to do the same with wildfly ?

[jesper.pedersen]

Just use an <exception-sorter> which marks the SQLException you want as fatal. Then choose the <flush-strategy> that you are looking for.

[asmec]

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

[jesper.pedersen]

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"

[asmec]

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