MySQL Bugs: #56100: Replication driver routes DML statements to read-only slaves

Bug #56100	Replication driver routes DML statements to read-only slaves
Submitted:	18 Aug 2010 23:04	Modified:	1 Dec 2015 20:41
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
A Statement object will refer to either the master or load-balanced slave connection, depending on the state of the read-only state of the Connection when the created (Connection.createStatement()), even if that state has subsequently been changed (to route DML to the master, or to use the slaves for read-only statements).  This is true for other types of statements (PreparedStatement, CallableStatement, etc.) as well.

How to repeat:
Execute code in this order:

Connection conn = DriverManager.getConnection("jdbc:mysql:replication://localhost,slave1,slave2/test",sqlUser, sqlPasswd);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
conn.setReadOnly(false);
stmt.execute("UPDATE tbl SET a = 1");

Suggested fix:
Ensure that statements are routed to master/slaves according to the read-only state at time of execution, rather than time of Statement creation.

This should be fixed by the changes made for BUG#51643. Todd could you test this?

(and if not fixed, the fix is the same, setup the connection proxy for all created statements).

Added the following entry to the Connector/J 5.1.38 changelog:

"After the initial call of Connection.setReadOnly() after a replication-aware connection was created, subsequent calls of Connection.setReadOnly() could not change the nature of the connection. This has been fixed by putting replication connections under the multi-host connection proxy structure that Connector/J has been using for load-balanced and fail-over connections."