MySQL Bugs: #34946: failoverReadOnly=false with multimaster does not work

Bug #34946	failoverReadOnly=false with multimaster does not work
Submitted:	28 Feb 2008 23:42	Modified:	9 Apr 2009 15:42
Reporter:	Clint Goudie-Nice	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	5.1.5 or 5.0.8	OS:	Linux (Centos 5.1)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
In a multimaster environment, if the first server in the jdbc connect string is unavailable, the secondary server cannot be written to.

Any attempt to write to the server will respond with:

An SQL error ocurred: Connection is read-only. Queries leading to data modification are not allowed

Additionally, since the fix of bug 27985 I cannot force the connection into a writable mode with Connection.setReadOnly(false);

If I revert to a version of Connector/j from before this fix (5.0.4) I can force the connection to be writable in this way, but if I do not do so, it still reports as read only.

This is against mysql 5.0.22-log on Centos 5.1

How to repeat:
Using this datasource attempt to perform any update:

MysqlConnectionPoolDataSource mscpds = new MysqlConnectionPoolDataSource();
mscpds.setUrl("jdbc:mysql://serverThatIsDown.somedomain.com,serverThatIsUp.somedomain.com/fs?user=root&password=somepass&autoReconnect=true&roundRobinLoadBalance=true&failOverReadOnly=false&autoReconnectForPools=true&queriesBeforeRetryMaster=99999999999999999999&secondsBeforeRetryMaster=99999999999999999999&max-connections=30&min-connections=10&inactivity-timeout=60");

Hi Clint and thanks for your report. I apologize for the delay in processing.

I would like to make one thing clear, this happens only if you *start* test with first server already down?

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".

I think the real issue at hand here is that the Connector/J doesn't support actually using multimaster as other than "Here is my master, and the rest are slaves" in which case, not being able to change them from read only to a writeable connection is a good solution. 

It's especially problematic in an environment where you have 2 masters each with two slaves attached. (so 6 db machines total). There's no way to differentiate in the connect string between which are the masters and which are the slaves. All you really have in the connect string is master1, slave1(really master 2), slave2, slave3, slave4, slave5. In this scenario, failOverReadOnly=false could be very bad, as you wouldn't want to write to slave 2,3,4, or 5 as that information would never be replicated to the master or the other slaves.

I'm saying I don't think it's a bug, you just can't failover as anything other than read only. IMO the doc should probably say the flag "failoverReadOnly=false" doesn't work and is deprecated.

In our environment we went with a different solution entirely. In our case, we've got a pool of connections to each master and each slave and we swap and round-robin between them depending on which is the most appropriate server (based on load, connections, and a slew of other factors) to be using. We don't use multiple servers in the jdbc connect string at all.

Clint, I can verify this but only as a feature request (S4). Do you agree on changing the severity?

Sure. 

I'd say the feature request is then to allow master<->master failover specified by specifying which servers are masters and which are slaves in the connect string. Similarly, the round robin and other connect options need to observe that change.

Hope this helps!

Clint

I apologize for not setting this to "Verified" sooner... must have slipped my mind. Triage is intentionally left blank.

Jess?