A colleague also pointed me this little tool that implements the magic
switching:
http://forge.mysql.com/wiki/MySQL_Proxy
Andrus
On Feb 10, 2009, at 9:05 AM, Andrus Adamchik wrote:
Interesting... I am using a similar setup with MySQL (a master and a
bunch of slaves), however we split the code into read-only and read/
write webapp modules and use separate URL's for each type. In
addition to proper load balancing, this adds a layer of security
(apps that are read-only are guaranteed to have no write access even
if there are programming errors that result in write attempts). Of
course this is not as generic (for instance I'd like my read-write
apps to still read from slaves).
To handle it the way MySQL describes it, the simplest way is to
create a custom "replicating adapter" that is a subclass of
MySQLAdapter, and override 'getAction'. There's a few helper classes
involved there (MySQLActionBuilder, ...), but the goal is to
override MySQLSelectAction to call "connection.setReadOnly" before
execution.
If you are using SQLTemplates or ProcedureQueries, the approach is
similar, only you'll need to analyze the contents of the query to
decide whether this is a select or update.
We may include this in Cayenne at some point, but as demonstrated
above, it should be easy to do it as a custom extension.
Andrus
On Feb 10, 2009, at 1:04 AM, Michael Shea wrote:
Hi guys,
I am currently using Cayenne 3.0M4 in a web application, using a
MySQL database.
I have been asked to modify the application so that we can perform
reads from one of N slave database servers, but writes will occur
only on one master database server.
Is it possible to do this with Cayenne? Anyone know how? =)
I've looked at this briefly:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html
... So it looks like I could configure a JNDI datasource that
represents the master + all the slaves pretty easily. I just don't
know how to get Cayenne to call "connection.setReadOnly(...)" as
necessary.
Thanks!
Mike Shea.
