Announcement Announcement Module
Collapse
No announcement yet.
jdbcTemplate always query from replication master Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • jdbcTemplate always query from replication master

    Hello,
    searched but could not find an answer. wondering if anyone has experience of using jdbcTemplate with mysql replication. thanks in advance.


    I have two server, one as master and one as slave. I use jdbcTemplate, mysql 5.

    my datasource configuration:


    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="initialSize" value="3"/>
    </bean>

    jdbc.driverClassName=com.mysql.jdbc.ReplicationDri ver
    jdbc.url=jdbc:mysql:replication://192.168.1.2:3306,localhost:3306/foton

    In the code, I try to force the readOnly connection (for testing purpose):

    this.jdbcTemplate.getDataSource().getConnection(). setReadOnly(true);
    return this.jdbcTemplate.queryForObject(query, new UserMapper(), login);

    But it seems that it always go to the master (192.168.1.2) to get the object (the program throw exception when I disconnect the master network).

    are there any other configurations/coding I miss out?

    many thanks
    canal

    ps. replication works. I can see the data replication from the master. on the client (Windows Vista), have set readonly in the my.ini:

    server_id=5
    log_slave_updates=1
    read_only=1

  • #2
    Please use [ code][/code ] tags when posting code, that way it remains readable.

    The problem is your code. You are getting a connection but that isn't the connection the query is using. The code you have is also dangerous as it can deplete your connection pool (you get a connection but don't return it).

    Use a ConnectionCallback to set it to readonly. Also that code is still dangerous as you are using a connection pool, connections are reused and as such it might be dangerous. The easiest way would be to start a readOnly transaction for that specific method.

    Comment


    • #3
      Thank you. Have tried to add transaction, but still failed:

      Code:
      	@Transactional (readOnly=true)
      	public User getUserByLogin(String login) {
              }
      debug message seems to indicate that transaction is indeed used:


      DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Creating new transaction with name [getUserByLogin]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly; ''

      but error (when disconnect the network to the master):
      Code:
      org.springframework.security.authentication.AuthenticationServiceException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure
      context is configured:
      Code:
        	<tx:annotation-driven transaction-manager="txManager"/>
        	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        		<property name="dataSource" ref="dataSource"/>
        	</bean>

      Comment


      • #4
        I was wrong. It is a different issue: when connection to master is down, query throw exception, although the slave is still working.

        with the transaction added, read-only query indeed goes to the slave I think:

        Code:
        DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager  - Creating new transaction with name [getUserByLogin]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly; ''
        DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager  - Acquired Connection [jdbc:mysql://192.168.1.2/foton, UserName=[email protected], MySQL-AB JDBC Driver] for JDBC transaction
        DEBUG org.springframework.jdbc.datasource.DataSourceUtils  - Setting JDBC Connection [jdbc:mysql://192.168.1.2/foton, UserName=[email protected], MySQL-AB JDBC Driver] read-only
        DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager  - Switching JDBC Connection [jdbc:mysql://localhost:3306/, UserName=root@localhost, MySQL-AB JDBC Driver] to manual commit
        DEBUG org.springframework.jdbc.core.JdbcTemplate  - Executing prepared SQL query
        DEBUG org.springframework.jdbc.core.JdbcTemplate  - Executing prepared SQL statement [select userId, roleId, loginId, password, firstName, lastName, email from users where loginId = ?]
        DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager  - Initiating transaction commit
        DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager  - Committing JDBC transaction on Connection [jdbc:mysql://localhost:3306/, UserName=root@localhost, MySQL-AB JDBC Driver]
        DEBUG org.springframework.jdbc.datasource.DataSourceUtils  - Resetting read-only flag of JDBC Connection [jdbc:mysql://localhost:3306/, UserName=root@localhost, MySQL-AB JDBC Driver]
        DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager  - Releasing JDBC Connection [jdbc:mysql://192.168.1.2/foton, UserName=[email protected], MySQL-AB JDBC Driver] after transaction
        DEBUG org.springframework.jdbc.datasource.DataSourceUtils  - Returning JDBC Connection to DataSource
        so how to make it work when master is down?

        Comment


        • #5
          I have no experience with a master/slave setup (at least with MySQL). So not sure how forgiving a connection is once setup. What you could try is instead of a connection pool use the SimpleDriverDataSource (although not recommended for production as that creates and closes connections when needed instead of pooling them).

          Comment


          • #6
            More likely a MySQL question, I will check around. thanks.

            Comment

            Working...
            X