ReplicationDriver [Possible Bug?] Internal MySQLConnection object always connects to the same host?
Greetings ,
We use MySQL master-slave setup to store data used in our REST APIs.
We are planing to direct to master db-host or to slave db-host depend the a REST request parameter.
To achieve this , we use MySQL ReplicationDriver. We use MyBatis as a layer between DB and our API.Spring is being used as the IOC framework.
We have defined two methods as follows , which the same logic to retrieve data from the DB. Only difference between these two methods is, one method has "readOnly=true" in @Transactional annotation, which will direct to "slave", and the other has "readOnly=false" which will direct to "master".
@Override
@Transactional(value = "itemTrxManager", readOnly = true, rollbackFor = Exception.class)
public Item getShopItemFromSlave(ItemGetKey itemKey, Options... opts) throws ItemException {
..
}
@Override
@Transactional(value = "itemTrxManager", readOnly = false, rollbackFor = Exception.class)
public Item getShopItemFromMaster(ItemGetKey itemKey, Options... opts) throws ItemException {
..
}
But we noticed that internally ReplicationDriver (MySQLConnection object) always direct to the first host it connects to , and doesn't change even after the change of "readOnly" flag.
ie : after starting our API , if we send request to use "slave" , ReplicationDriver will use "slave host" and even when we change to use "master", it still direct to "slave host". Otherway around is the same : after starting our API , if we send request to use "master" , ReplicationDriver will use "master host" and then if change to use "slave", it still direct to "master host".
We noticed that all the logs (Apache DBCP,Spring DAO , MyBatis logs) shows it connects to the correct host , but after debugging we figured out the issues is in MySQL driver classes (MySQLConnection objects shows incorrect host names).ie, Connection object retrieved from Apache DBCP DataSource and the internal MySQLConnection object shows different host info.
For example ,following shows a snippet from our logs .Even though it says it connects to "slave" , when debugging, the MySQL Drivers internal ReplicationDriver objects has information about "master".
DEBUG [http-8080-2] [AbstractPlatformTransactionManager.java:365] - Creating new transaction with name [com.foo.bar.item.core.businesslogic.impl.MerchantCategoryBusinessLogicImpl.listMerchantCategory]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; 'itemTrxManager' DEBUG [http-8080-2] [DataSourceTransactionManager.java:204] - Acquired Connection [jdbc:mysql://master-host:3301/, UserName=item_user@10.9.203.50, MySQL Connector Java] for JDBC transaction DEBUG [http-8080-2] [DataSourceUtils.java:153] - Setting JDBC Connection [jdbc:mysql://master-host:3301/, UserName=item_user@10.9.203.50, MySQL Connector Java] read-only
DEBUG [http-8080-2] [Slf4jImpl.java:47] - Creating a new SqlSession DEBUG [http-8080-2] [Slf4jImpl.java:47] - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@329203a8] DEBUG [http-8080-2] [Slf4jImpl.java:47] - JDBC Connection [jdbc:mysql://slave-host:3306/, UserName=item_user@10.9.203.50, MySQL Connector Java] will be managed by Spring DEBUG [http-8080-2] [Slf4jImpl.java:47] - ooo Using Connection [jdbc:mysql://slave-host:3306/, UserName=item_user@10.9.203.50, MySQL Connector Java] DEBUG [http-8080-2] [Slf4jImpl.java:47] - ==> Preparing: select merchant_category_id, custom_category_id, merchant_id, parent_id, sibling_position, image_url, create_time, update_time from glb_merchant_cat_tbl where merchant_id=?
. . .
DEBUG [http-8080-2] [Slf4jImpl.java:47] - Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@329203a8] DEBUG [http-8080-2] [Slf4jImpl.java:47] - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@329203a8] DEBUG [http-8080-2] [DataSourceUtils.java:222] - Resetting read-only flag of JDBC Connection [jdbc:mysql://slave-host:3306/, UserName=item_user@10.9.203.50, MySQL Connector Java] DEBUG [http-8080-2] [DataSourceTransactionManager.java:322] - Releasing JDBC Connection [jdbc:mysql://master-host:3301/, UserName=item_user@10.9.203.50, MySQL Connector Java] after transaction DEBUG [http-8080-2] [DataSourceUtils.java:332] - Returning JDBC Connection to DataSource
How so solve this issue ? This might be a bug in ReplicationDriver ?