Hi,
Is there anybody who's tried the read/write splitting proxy with java
frameworks "hibernate" and "spring" ?
I have two mysql servers on two different servers. The 'test' user has
been granted "all" permissions on one of them and only "select" on the
other one. Both servers run with autocommit set to false.
The mysql proxy running locally on my machine is set to run by the
following script:
============
#!/bin/bash
export LUA_PATH="./share/doc/mysql-proxy/?.lua"
./bin/mysql-proxy --log-level=debug \
--proxy-lua-script=./share/doc/mysql-proxy/rw-splitting.lua \
--proxy-read-only-backend-addresses=192.168.11.108:3306 \
--proxy-backend-addresses=192.168.11.107:3306 \
--admin-username=root --admin-password= \
--admin-lua-script=./lib/mysql-proxy/lua/admin.lua
===========
When I open a mysql client on port 4040 and run commands with and
without transactions, the read and writes are splitted correctly
between
the two servers. In other words the statements between "begin" and
"commit" goes to the "master" (addressed by
--proxy-backend-addresses=192.168.11.107:3306) and other queries go to
the "slave" (addressed by
--proxy-read-only-backend-addresses=192.168.11.108:3306).
Now, I have a simple java program using spring and hibernate, with the
mysql proxy as my datasource. Some methods in my program are tagged as
being transactional, and some are not tagged as being transactional.
My
understanding is that all sql-statements which are fired in the
transactional methods would be redirected to the "master" database and
those sql-queries fired in the non-transactional methods to the
read-only database. But it is not the case. It seems that mysql-proxy
does not recognize or does not respect the transaction flags coming
from
spring/hibernate.
===========
Other useful data:
OS platform:
Mysql server : 5.1.52 - Running on Linux 2.6.32-71.18.2.el6.x86_64
#1
x86_64 GNU/Linux
Mysql proxy : 0.8.1 - Running on Linux 2.6.35-28-generic #50-Ubuntu
x86_64 GNU/Linux
Java : 1.6
hibernate : 3.5.6
spring : 3.0.5
===========
What happens in the following example is that in most cases, the
transactional method gets executed on the read-only server.
Example:
==============================================
Here is my test java methods:
@Transactional(propagation = Propagation.REQUIRED) // This
tells the hibernate entity manager to tag this method as being
transactioanl
public merchant selectUpdateSelectMerchant(long merchantId) {
Merchant merchant = merchantDao.findById(merchantId); //
merchantDao is a simple class calling to the hibernate entity-
manager in
order to exec. the query
merchant.setSomeProperty("xxx");
merchantDao.merge(merchant); // merge-method makes
the hibernate's entity-manager to do the update
merchant = merchantDao.findById(merchant.getId());
return merchant;
}
// This method is non-transactional
public Merchant selectNonTransactionalMerchant(Long merchantId) {
Merchant merchant = merchantDao.findById(merchantId); //
merchantDao is a simple class calling to the hibernate entity-
manager in
order to exec. the query
assert merchant != null;
return merchant;
}
Spring's context file is:
....
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-
method="close" >
<property name="driverClassName"
value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:4040/satellite" />
<property name="username" value="test" />
<property name="password" value="" />
</bean>
<tx:annotation-driven mode="aspectj"
transaction-manager="transactionManager" />
<bean id="transactionManager"
class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory"
ref="entityManagerFactory" />
</bean>
<bean id="entityManagerFactory"
class
="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="persistenceUnitName" value="persistenceUnit" />
<property name="persistenceXmlLocation"
value="classpath*:META-INF/persistence.xml" />
<property name="jpaProperties">
<props>
<prop
key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</
prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>
....
=====================================================
_______________________________________________
Mailing list: https://launchpad.net/~mysql-proxy-discuss
Post to : mysql-proxy-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~mysql-proxy-discuss
More help : https://help.launchpad.net/ListHelp
