[Carbonado-interest] Using Carbonado with MySQL replication

[Gunnar Wagenknecht]

Hi,

Has anyone tried using Carbonado with MySQL replication? It seemst that
MySQL does the magic routing between the replica and the master but
requires the application to call Connection#setReadOnly to indicate
whether the call can go to a replica.

Can Carbonado set the read-only flag when acquiring connections?

-Gunnar

[Brian S O'Neill]

You'd have to do this using the JDBCConnectionCapability. As a
convenience, to make all connections read only, you can wrap the
DataSource to do this. Otherwise, modify Carbonado to support this feature.

Post by Gunnar Wagenknecht
Hi,
Has anyone tried using Carbonado with MySQL replication? It seemst that
MySQL does the magic routing between the replica and the master but
requires the application to call Connection#setReadOnly to indicate
whether the call can go to a replica.
Can Carbonado set the read-only flag when acquiring connections?
-Gunnar

[Gunnar Wagenknecht]

Post by Brian S O'Neill
You'd have to do this using the JDBCConnectionCapability. As a
convenience, to make all connections read only, you can wrap the
DataSource to do this. Otherwise, modify Carbonado to support this feature.

I have a wrapped DS already in order to point connections coming from a
shared pool to a MySQL server to a particular database (#setCatalog).
However, the read-only flag is a bit more complicated. When the
connection is acquired from the DS I just won't know yet if it's for
read or write purposes. Thus, I'm afraid I'll have to go with the last
option. ;)

-Gunnar

[Gunnar Wagenknecht]

Hi,

I'm interested in providing a patch for this. I'm looking for potential
guidance to this, i.e. places to modify.

There are two places were connections are fetched from the data source:

JDBCRepository.getConnection() (line 444)
JDBCRepository.getConnectionForTxn(IsolationLevel) (line 474)

I'd like to modify both places to add a call to con.setReadOnly(true)
which will mark a connection as read only.

It might be worth to set read-only to false when a specific isolation
level is requested, though. This will ensure that the master is used
even for selects (replication is asynchronous in MySQL).

Is there a single place which is called when a connection is about to be
used to write to/modify a database? I'd use such a hook to switch the
connection to be writeable for going to the master.

-Gunnar

Post by Brian S O'Neill
You'd have to do this using the JDBCConnectionCapability. As a
convenience, to make all connections read only, you can wrap the
DataSource to do this. Otherwise, modify Carbonado to support this feature.

Post by Gunnar Wagenknecht
Hi,
Has anyone tried using Carbonado with MySQL replication? It seemst that
MySQL does the magic routing between the replica and the master but
requires the application to call Connection#setReadOnly to indicate
whether the call can go to a replica.
Can Carbonado set the read-only flag when acquiring connections?
-Gunnar

[Brian S O'Neill]

Generated code calls the JDBCRepository.getConnection method for
insert/update/delete. See line 1310 in JDBCStorableGenerator. No
distinction is made for read/write operations at the moment.

Post by Gunnar Wagenknecht
Hi,
I'm interested in providing a patch for this. I'm looking for potential
guidance to this, i.e. places to modify.
JDBCRepository.getConnection() (line 444)
JDBCRepository.getConnectionForTxn(IsolationLevel) (line 474)
I'd like to modify both places to add a call to con.setReadOnly(true)
which will mark a connection as read only.
It might be worth to set read-only to false when a specific isolation
level is requested, though. This will ensure that the master is used
even for selects (replication is asynchronous in MySQL).
Is there a single place which is called when a connection is about to be
used to write to/modify a database? I'd use such a hook to switch the
connection to be writeable for going to the master.
-Gunnar

Post by Brian S O'Neill
You'd have to do this using the JDBCConnectionCapability. As a
convenience, to make all connections read only, you can wrap the
DataSource to do this. Otherwise, modify Carbonado to support this feature.

Post by Gunnar Wagenknecht
Hi,
Has anyone tried using Carbonado with MySQL replication? It seemst that
MySQL does the magic routing between the replica and the master but
requires the application to call Connection#setReadOnly to indicate
whether the call can go to a replica.
Can Carbonado set the read-only flag when acquiring connections?
-Gunnar