MySQL Bugs: #104799: Support failover on readonly error

Bug #104799	Support failover on readonly error
Submitted:	2 Sep 2021 4:09	Modified:	2 Mar 2022 17:39
Reporter:	Karthik Appigatla (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As of now MySQL J Connector has a failover option which can connect to secondary if the primary is down. But this feature is not usable. Assume that there are 100 hosts and due to network glitch the primary is down for 5 secs, some hosts will failover to secondary and the other hosts which may not notice the glitch will still try to connect to the primary leading to split brain.

We want to have external mechanism for failover which sets/unsets the read-only. The driver should perform a failover when the host is not reachable or the host is on read-only mode. 

Even if there is a network glitch and few hosts detect that and failover to secondary, since secondary is not set to read-write, the driver should detect the read-only flag and perform a fallback to primary. 

How to repeat:
Feature request

Suggested fix:
Introduce a new variable failoverOnReadOnlyException to the J Connector
If failoverOnReadOnlyException that is set, it should perform failover

https://github.com/mysql/mysql-connector-j/blob/18bbd5e68195d0da083cbd5bd0d05d7632[…]ser...

Line 155
        String sqlState = null;
        if (t instanceof CommunicationsException || t instanceof CJCommunicationsException) {
            return true;
        } else if (t instanceof SQLException) {
            sqlState = ((SQLException) t).getSQLState();
        } else if (t instanceof CJException) {
            sqlState = ((CJException) t).getSQLState();
        }

        if (sqlState != null) {
            if (sqlState.startsWith("08")) {
                // connection error
                return true;
            }
        }

we should add the following lines
        if (sqlState != null) {
            if (sqlState.startsWith("1290")) && sqlState.indexOf("--read-only") != -1 {
                // connection error
                return true;
            }
        }

Hello Karthik,

Thank you for the reasonable feature request!

regards,
Umesh

Hi Karthik,

Thanks for your interest in MySQL Connector/J.

This fail over mechanism was not designed having in mind read/write traffic splitting. For that you have "replication" connections. They too have fail over support and are also highly configurable regarding host switching.

Please consult the following documentation:
- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connec...
- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-j2ee-concepts-managing...
- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-j2ee-concepts-load-bal...

The feature you want should be possible with this type of connections. Please try it out and let us know if it worked for you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We tried and tested all the configurations mentioned. However, the problem which we are trying to solve is quite different from the configurations proposed. 

The configurations mentioned in https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connec... we will need to manually set the connection to RW/ Read-only using conn.setReadOnly(true) before every transaction. The only support for failover is the function promoteReplicaToSource() which needs to called in client application on failover. 

Our intention is to have the driver perform the failover once it detects that the connection is read only. This will hold true for all the connections. 

Our proposal is that we are trying to catch the SQL state exception that occurs on read-only and failover irrespective of the transaction state. We could not find any options to do that in the Loadbalancer configuration either. 

The changes we have proposed FailoverConnectionProxy connection class.

any update?