Using MySQL replication driver with JBoss Transactions

[paradigmza]

Hi,

I am trying to use the MySQL replication driver. In order to tell MySQL to use the "read only" servers, I need to set the connection to Read Only. This is quite easy to do as I am using hibernate.

So, I thought for my Select queires I would set the connection to readonly. Do the select, then set it back to normal.

((Session)em.getDelegate()).connection().setReadOnly(true);
resultList = em.createQuery("SELECT a From EntityObject").getResultList();
((Session)em.getDelegate()).connection().setReadOnly(false);

This does not work because JBoss then throws a SQL exception that I cannot set ReadOnly on a managed connection.

My Question is then.

What is the best way to use the replication driver of MySql? (Or any Database where there is a master(write)/slaves(read) setup with JBoss)

Thanks.

[jaikiran]

Try configuring this on the datasource from which you obtain the connection:

<connection-property name="readOnly">true</connection-property>

I haven't tried it myself so not sure whether it's going to work. See this doc for the details http://community.jboss.org/wiki/configdatasources

[paradigmza]

That would work if I had 2 data sources (and 2 entity managers). The mysql replication driver works by using a master/slave setup in the same datasource.

<connection-url>jdbc:mysql://master,slave1,slave2,slave3/dbname</connection-url>

Setting this to read-only would then tell the driver to only use the slaves (and never use the master), and hence defeats the purpose of using the replication driver.