Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: local policy)
Message-Id: <F7C5E94E-2CFE-474F-8E4B-CECAB2AE5451@objectstyle.org>
From: Andrus Adamchik <andrus@objectstyle.org>
To: user@cayenne.apache.org
In-Reply-To: <4990B690.1020309@nitido.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v930.3)
Subject: Re: Cayenne + master/slave mysql database replication
Date: Tue, 10 Feb 2009 09:05:03 +0200
References: <49908F7B.6010201@tamu.edu> <4990B690.1020309@nitido.com>

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