Re: Mysqlconnector/J for PXC cluster

[Filipe Silva]

Hi Yasser,

You got it right. You are describing your scenarios pretty well and accurately. The only thing I want to add is that in scenario 2 there's a little more to it. I.e., existing connections to server 2 will automatically fall back to primary after 5 secs have elapsed but only in the following situations:
1. You have auto-commit set to true, which is by default, and fail back happens *after* this connection is used to execute something, which means that the next statement will be sent to server 2 but not the following ones.
2. If auto-commit is false, then the fall back happens only *after* and if you call Connection.commit() or Connection.rollback().

Unfortunately there's no out-of-the-box alternative to do exactly what you want. But there are a couple alternatives you could try:
1. Inject a statement interceptor (https://dev.mysql.com/doc/connector-j/en/connector-j-interceptors.html) into every connection connection, that you can use to force triggering the connection fall back by executing a dummy query in the case the main server is up. Perhaps you should find a way of monitoring the main server availability to avoid sending these dummy queries all times. Actually, instead of a dummy query, a Connection.ping() should do it too. But this works only if auto-commit is true. Just mind that you cannot use the Connection instance that comes in the StatementInterceptor.preProcess() arguments, you have to use the interceptedStatement.getConnection() instead as this one is the top logical load-multihost connection that controls the failover process while the other one is the physical connection to your server 2.
2. Replace your connections by load-balanced connections and implement your own LoadBalancingStrategy. Find some references here (http://mysqlblog.fivefarmers.com/2012/02/24/connectorj-extension-points-load-balancing-strategies/). Although not the easiest solution it gives you full power on what you wish do.

I hope that helps,
Filipe