Solar Framework's master-slave db adapter

SolarPHP's framework now comes with SQL adapter for MySQL replicated setups since the 1.0.0alpha2 release. The adapter automatically picks master or slave databases depending on the type of query. If its a SELECT, it will go to a slave, if its a UPDATE, DELETE, etc. it will automatically go to the master.

Let me just put it out there, I think that's a really bad idea. While this will work for certain situations, it is advertised as "The Solar_Sql_Adapter_MysqlReplicated adapter handles all the connections and switching-around for you, and you don't need change a single line of application code.".

Quite frankly, I started out with a similar approach. It worked fine for our staging environment, which didn't experience a lot of load, when it went to the live machine things went bad.

Suddenly I realized there were more than couple places in the application that had to do SELECT based on information that was just INSERT'ed. After the INSERT was done, the SELECT was performed on the slave, while the data hadn't arrived yet from the master. This caused some pretty freaky errors. Hoping to fix the problem, I decided to always execute any query on the master after an data-altering statement was done. Turned out that the master got quite a bit more load than the slaves after that change.

Things got worse when there was a replication lag of say, a second. Users would modify some data by submitting a form, and when they ended up on a resulting page the data was gone!

Some of the things we looked at was using a whitelist or blacklist for queries that are always ok/not ok to go to the slave, but considering we then ended up with decoupling a logical block of code, we went for the pragmatic approach. Always pick the master or slave depending on what is required for a specific piece of functionality. Yes, it might require a bit more thinking as you're working on your database code, but the behaviour is predictable and reliable.