BMaximus has asked for the wisdom of the Perl Monks concerning the following question:

My fellow Monks, I have a delicate problem I need to solve and I need your help.

My project at work is to enable a MySQL master, slave architecture to work as one server as a read only server where the statement is routed to it only if it does nothing to the database (meaning it's only retieving data, not changing it) and the other server is a write server and I'm sure you get it, if the statement does something to the database the changes are routed through to that server and the changes made to the read only server through replication.

My question is other than subclassing DBI is there a way to do this already? The example I saw in the DBI documentation made it clear as to how to subclass DBI when creating your own connection, do, prepare and execute methods when you're only using one connection, but I'm at a loss as to how I can use two connections and have them used intelligently. I understand I need to call the SUPER::connect after I get all the needed connection information and pass back a handle and it gets messy here. I need to create two connections and have them both available. Do I bless the new class with both connections storred in a hash or do I need to do somethig else? I have an idea on how to get the new class to use the connections intelligently but that could change depending on your answer.

I'm pretty sure this has been done before and I feel like I'm reinventing the wheel here. I hope there's a simple answer to this.

Thank you for your time.
  • Comment on Subclassing DBI and creating two connections

Replies are listed 'Best First'.
Re: Subclassing DBI and creating two connections
by perrin (Chancellor) on Dec 02, 2005 at 17:46 UTC
    I strongly recommend not subclassing DBI. You are very likely to break things and clash with other systems like Class::DBI or Apache::DBI.

    I don't actually use these myself, but DBIx::Cluster and DBIx::HA look like they may do some of what you want.

Re: Subclassing DBI and creating two connections
by renodino (Curate) on Dec 02, 2005 at 19:52 UTC
    1. I suspect that implementing this as a pure DBI subclass would prove a bit cumbersome; you'd effectively have to replicate the entire DBI i/f in order to route the API calls to either of your connections.

    2. That being said, you might look into DBD::Multiplex. I've not used it, but it has been updated quite recently and I know Mssr. Bunce had given it some attention at one time. It would appear to provide most of the functionality you seek.
Re: Subclassing DBI and creating two connections
by valdez (Monsignor) on Dec 03, 2005 at 16:29 UTC

    I have the same setup and I suggest to write a wrapper around DBI that provides an handle for reading and one for writing; then use the read handle if you need to perform only reads, or use the write handle if you need to perform both writes and reads. The problem here is the time spent to replicate data from master to slave server: there is no warranty that you will be able to read from the slave the data written to the master few moments before.

    For a similar reason don't use now() in any insert or update statement, because the slave will not use the same timestamp used on the master.

    Ciao, Valerio