I have a master/slave MySQL replication setup. I want all writes to be sent to the master, and all reads (or at least most) to the slave.

If I change the jdbc driver to com.mysql.jdbc.ReplicationDriver with the url set to jdbc:mysql://master,slave/db reading works fine, but hibernate also sends all updates to the slave (which fail since I have set it the --read-only option to ensure consistency).

If I take the slave off-line, everything works fine (with all traffic going to the master, obviously).

The mysql docs for ReplicationDriver say that as soon as setReadOnly(false) is called, all further queries are passed to the master. I can only assume hibernate isn't calling setReadOnly(false) before updates. Why is this, and is there a simple way to enable this?

If this isn't possible, are there any other wrappers which do the same job which will work? I don't fancy writing my own JDBC wrapper!

Thanks

Ok, I have patched the mysql ReplicationDriver to automatically switch between the master and slave connections, instead of relying on setReadOnly(). This is obviously a better solution, but I still think hibernate should call setReadOnly(), since it knows what type of query it's about to execute. I did have a quick look through the hibernate source, but there's no obvious way to do this - The connections seem too far isolated from the sql execution itself. Ideally the call would go into
CollectionRemoveAction.java, EntityIdentityInsertAction.java, EntityUpdateAction.java, CollectionRecreateAction.java, CollectionUpdateAction.java, EntityDeleteAction.java, and EntityInsertAction.java

but I can't see how to get to the connection from here.

draemon,

Is there any way I could grab your patch to the MySQL driver for master/slave replication? This is exactly the situation I'm faced with.

Tom
tom@tharrisx.homedns.org

I was under the impression this bug was fixed. Are you using the most recent version of the driver?

My bug report, complete with my original patch is here:

http://bugs.mysql.com/bug.php?id=15570

If the bug isn't fixed, you might want to re-open the bug report.

I think I misunderstood your patch. I thought it might make setReadOnly redundant, since the query or proc call itself makes the consequences obvious, ie. Discrete SELECT calls are always read-only, whereas UPDATE, INSERT, etc. are plainly write statements.

My Model API wraps Hibernate and our model up in the system I'm working on certainly knows, and I've been setting setReadOnly appropriately. However, setting readOnly on the raw JDBC Connection through the "deprecated" session.connection() call (see http://forum.hibernate.org/viewtopic.php?t=974518) appears in my tests to often get lost before the SQL is actually executed. I call setReadOnly(false) on insert/update calls, and get the unhappy exceptions of connection is read only, update failed, etc.

Our operations group was asked by my manager to spend time puttting together a MySQL master/slave "cluster". Now, I'm stuck with the task of configuring our already completed system to work with the cluster, and only roadblock in my path.

Has anyone successfully integrated a MySQL master/slave cluster with Hibernate? If so, what changes to your code or config did you need to make? I'd like to know. :)

Thanks,
Tom
thomas (dot) harris (at) sourcemedia (dot) com