Re: Mysqlconnector/J for PXC cluster
Posted by: Filipe Silva
Date: March 04, 2016 10:59AM

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 ( 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 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 ( Although not the easiest solution it gives you full power on what you wish do.

I hope that helps,

Options: ReplyQuote

Written By
Re: Mysqlconnector/J for PXC cluster
March 04, 2016 10:59AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.