mysql-proxy-discuss team mailing list archive

[Farhad Dehghani <farhad@xxxxxxxxxxxxxxxxx>]

Thanks Mark for your reply.
To do the splitting in the java layer is absolutely an alternative which
we also have been looking at; and spring-framework provides some basic
routines and classes for that as well. Personally I prefer to do the
splitting transparent to our applications in a proxy or the like. For
now, I guess we go for doing the r/w splitting in the application layer.

Thanks,
Farhad

On 2011-05-31 15:25, Mark Leith wrote:
> Hi,
>
> I believe that the rw-splitting example at the moment would need to
> catch a BEGIN statement to do this, and C/J doesn't really do that.
>
> There are native ways to perform read/write splitting with Java, when
> you have control of the code yourself, take a look at the following
> presentation from a couple of my colleagues:
>
> http://www.youtube.com/watch?v=ZQjo3W1nw_U
>
> Using the native methods would be my recommendation if possible,
> rather than trying to make the proxy rw-splitting.lua work with this.
>
> Regards
>
> Mark
>
> On 25 May 2011, at 11:57, Farhad Dehghani wrote:
>
>> 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
>
> Best regards
>
> Mark
>