Mysql master/slave replication .Connect to master even for read queries? (does Driver "ping" master before going to slave?)

Question

I use mysql master/slave replication (write to master and reads to slaves) with ReplicationDriver.My connection URL is as follows :

"jdbc:mysql:replication://master:3306,slave1:3307,slave2:3308/sampledb?allowMasterDownConnections=true"  

I use Spring + Spring MyBatis modules.

I have marked my transaction as readOnly as follows :

@Override
    @Transactional(rollbackFor=Exception.class,readOnly=true)
    public Sample getSample(SampleKey sampleKey) throws SampleException {
       //Call MyBastis based DAO  with "select" queries.
    }

But when I see the transaction/db logs it shows that even for the "readOnly" transactions ReplicationDriver first hits master. Notice the lines "Acquired Connection" and "Releasing JDBC connection" lines.

Why is this happening ?

1) No matter if its a read-only query , does the JDBC driver still "ping" master to check whether its live and then goto the slave for actual querying ?

2) If the readOnly=true , doesn't Spring set the readOnly(true) of the underlying Connection object ?

2014-03-19 12:32:28,280 DEBUG [http-8080-2] [AbstractPlatformTransactionManager.java:365] - Creating new transaction with name [com.rakuten.gep.foo.businesslogic.impl.SampleBusinessLogicImpl.getSample]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly; '',-java.lang.Exception
2014-03-19 12:32:28,390 DEBUG [http-8080-2] [DataSourceTransactionManager.java:204] - Acquired Connection [jdbc:mysql://master:3306/, [email protected], MySQL Connector Java] for JDBC transaction
CACHED DAO
Trying to retrive from the Cache
2014-03-19 12:32:31,334 DEBUG [http-8080-2] [Slf4jImpl.java:47] - ooo Using Connection [jdbc:mysql://slave1:3307/, [email protected], MySQL Connector Java]
2014-03-19 12:32:31,334 DEBUG [http-8080-2] [Slf4jImpl.java:47] - ==>  Preparing: select tbl.item_id, tbl.item_name, tbl.create_time, tbl.update_time from sample_tbl tbl where tbl.item_id=? 
2014-03-19 12:32:31,335 DEBUG [http-8080-2] [Slf4jImpl.java:47] - ==> Parameters: 79bc3c80-af0a-11e3-a8e4-b8e8560f9d02(String)
Adding SampleTbl id to cache : 79bc3c80-af0a-11e3-a8e4-b8e8560f9d02
2014-03-19 12:32:31,340 DEBUG [http-8080-2] [AbstractPlatformTransactionManager.java:752] - Initiating transaction commit
2014-03-19 12:32:31,342 DEBUG [http-8080-2] [DataSourceTransactionManager.java:264] - Committing JDBC transaction on Connection [jdbc:mysql://slave1:3307/, [email protected], MySQL Connector Java]
2014-03-19 12:32:31,382 DEBUG [http-8080-2] [DataSourceTransactionManager.java:322] - Releasing JDBC Connection [jdbc:mysql://master:3306/, [email protected], MySQL Connector Java] after transaction

My connection settings are :

<Context>
  <WatchedResource>WEB-INF/web.xml</WatchedResource>
  <Resource name="jdbc/sample"
            auth="Container"
            type="javax.sql.DataSource" 
            factory="org.apache.commons.dbcp.BasicDataSourceFactory"     
            username="root"
            password="root"
            driverClassName="com.mysql.jdbc.ReplicationDriver"
            url="jdbc:mysql:replication://master:3306,slave1:3307,slave2:3308/sampledb?allowMasterDownConnections=true"       
            connectionCachingEnabled="true"
            connectionCacheProperties="{MaxStatementsLimit=10}"
            removeAbandoned="true"
            removeAbandonedTimeout="600"
            logAbandoned="true"            
            timeBetweenEvictionRunsMillis="1000"
            minEvictableIdleTimeMillis="1000"
            testOnBorrow="false"
            testOnReturn="false"
            validationQuery="select null"
            testWhileIdle="true"
            maxActive="10"
            maxIdle="3"
            maxWait="1000"
            defaultAutoCommit="false"/>

</Context>

Answer 1

What you are seeing is a side-effect of the fact that the MySQL JDBC driver is managing the connection(s) to the physical servers (whether it's master or slave). Neither the connection pool nor the Spring transaction manager know about the fact that database connection is talking to several servers. Everything looks like it's working correctly, but I'll explain why it looks like the master connection is being used.

• First, DBCP is creating a single JDBC connection through the MySQL driver. This connection will point to the master until set to read-only at which point it will switch over to the slave.
• Second, Spring is getting a connection from the pool and writing to the debug log that it has acquired a connection. Because the connection has not yet been set to read-only mode, it will route queries to the master.
• Third, Spring is changing the connection over to read-only mode at which point queries will be routed to the slave.
• Next, your application (or iBatis or w/e) is given the connection to perform some work with the database.
• After you return control to Spring, the transaction on the connection will be committed. Because the connection is in read-only mode, you can see the transaction debug message showing that queries will be routed to the slave server.
• Finally, the connection is reset before being returned to the pool. The read-only mode is cleared and the last log message once again reflects that the connection will route queries to the master server.

Hope this helps. If you need more detailed information, let me know.