Re: [CDBI] using different DSNs for reads/writes

Aaron Wolfe wrote:
> Hi,
> 
> I am converting some messy scripts I wrote to use the very nice Class
> DBI interface.  I have run into a wall that I can't seem to figure out
> (please forgive me if this is easy and I am just ignorant).
> 
> I have multiple sites running mysql using master -> slave replication.
>  This means all changes to the data must happen at the master.
> However, lookups should always use the local copy.  The master db is
> slow to query, but writes are very infrequent compared to reads so it
> works ok.  In my current code, I just have two db handles and pick the
> correct one to use by hand.

For normal DBI code, DBD::Multiplex would be the default solution.

It breaks down when used with Class::DBI, mostly because an insert() or 
update() call triggers a select. This can fail or return stale data if your 
replication isn't immediate. This is also a big problem for transactions.

As far as I know, there are two attempts on CPAN to solve this for CDBI: 
Class::DBI::Replicated and Class::DBI::Replication. Both look unfinished, and 
I have tried neither.

I'd love to get this stuff solved properly too, but for the time being I've 
managed to get by using Memcached instead of replicated databases. Next year, 
I anticipate our company to grow sufficiently to start needing multiple 
database servers, so I may revisit this problem later.

Rhesa

Side note: even the DBIx::Class solution is marked Experimental...