Database Url assigned at Step AtomikosDataSourceBean url reset to this url - no change to url.

[Peter Brown]

Hello,

This is a continuation of an earlier resolved thread - Datasource url database name not known until after a Batch step. I moved over to using the Atomikos connection pool and the url switch is not sticking. I can't get the AtomikosDataSourceBean to refresh or recognize the url change.

The xml config for the datasource bean is:

	<util:properties id="uymDbProperties"
		location="classpath:/META-INF/uymdb.properties" />
	<bean id="uymDS" class="com.atomikos.jdbc.AtomikosDataSourceBean"
		init-method="init" destroy-method="close">
		<property name="uniqueResourceName" value="uymDS" />
		<property name="xaDataSourceClassName"
			value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
		<property name="xaProperties" ref="uymDbProperties" />
		<property name="minPoolSize" value="10" />
		<property name="maxPoolSize" value="20" />
		<property name="borrowConnectionTimeout" value="30" />
		<property name="testQuery" value="select 1" />
		<property name="maintenanceInterval" value="60" />
	</bean>

	<bean id="uymFileJdbcDao" class="com.xxx.domain.uym.UymFileJdbcDao">
		<property name="dataSource" ref="uymDS" />
	</bean>

The class follows the standard:

public class UymFileJdbcDao extends JdbcDaoSupport implements UymFileDao { }

Doing:

try {
	AtomikosDataSourceBean ds = (AtomikosDataSourceBean) getDataSource().getConnection();
} catch (SQLException e) {
	e.printStackTrace();
}

gives the bean - I make the changes and try

ds.close();
ds.init();

- log shows it initializing and I output the new url to make sure and the first insert for the UymFileJdbcDao inserts in the default url found in the uymdb.properties file.

I don't think my issue is the assigning of the url but how to force the AtomikosDataSourceBean to reset or the JtaTransactionManager to refresh?

I have looked at Spring AOP Hot Swappable Target Sources in my wanders but not sure.

Any pointers, again much appreciated.

Thanks,
Peter

[Peter Brown]

This is as far as I got if anyone needs it, now re-assessing global transactions:

@Test
public void testJob() throws Exception {
	AtomikosDataSourceBean dsb = (AtomikosDataSourceBean) context.getBean("uymDS");
	SwitchAtomikosDataSource uymSwitch = (SwitchAtomikosDataSource) context.getBean("switchAtomikosDataSource");

	// Default url
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		conn = dsb.getConnection();
		stmt = conn.createStatement();
		System.out.println("before 1st db:  " + dsb.getXaProperties().getProperty("url"));
		rs = stmt.executeQuery("SELECT id FROM file");
		while (rs.next()) {
			System.out.println("file id " + rs.getInt("id"));
		}
		stmt.close();
	} finally {
		conn.close();
	}
		
	String oldUrl = dsb.getXaProperties().getProperty("url");
	String newUrl = "JTS";
		
	// Check if no need to switch
	if (uymSwitch.needToSwitchDataSource(oldUrl, newUrl)) {
		dsb = uymSwitch.getNewUymDataSource(dsb, newUrl);
	}

	try {
		conn = dsb.getConnection();
		stmt = conn.createStatement();
		System.out.println("before 2nd db:  " + dsb.getXaProperties().getProperty("url"));
		rs = stmt.executeQuery("SELECT id FROM file");
		while (rs.next()) {
			System.out.println("file id " + rs.getInt("id"));
		}
		stmt.close();
	} finally {
		conn.close();
	}
}

and it works.

I then setup an aop implementation that gets the needed oldurl from the datasource and newurl from a stepExecutionListener. The idea being that I get the first index of the stepExecutionListener array object in the aop proxy - close (if needed) and initialize with new url and the jdbcTemplate is none the wiser...

I then run the Spring Batch job and get the following:

INFO : com.atomikos.icatch.imp.BaseTransactionManager - createCompositeTransaction ( 300000 ): created new ROOT transaction with id tmdev0005400236
INFO : com.atomikos.jdbc.AbstractDataSourceBean - AtomikosDataSoureBean 'uymDS': close...
dbName is: MFS_2013_Feb
url is: jdbc:mysql://xxx.xxx.xxx.xxx:xxxx/MFS_2013_Feb?zeroDateTimeBehavior=convertToNull
INFO : com.atomikos.jdbc.AbstractDataSourceBean - AtomikosDataSoureBean 'uymDS': init...
INFO : com.atomikos.jdbc.AtomikosDataSourceBean - AtomikosDataSoureBean 'uymDS': initializing with [ xaDataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource, uniqueResourceName=uymDS, maxPoolSize=2, minPoolSize=2, borrowConnectionTimeout=30, maxIdleTime=60, reapTimeout=0, maintenanceInterval=60, testQuery=select 1, xaProperties=[autoReconnect=true,user=xxx,url=jdbc:mysql://xxx.xxx.xxx.xxx:xxxx/MFS_2013_Feb?zeroDateTimeBehavior=convertToNull,password=xxx,autoReconnectForPools=true,autoReconnectForConnectionPools=true,pinGlobalTxToPhysicalConnection=true] loginTimeout=0]
INFO : com.atomikos.icatch.imp.TransactionServiceImp - Coordinator not recoverable: tmdev0005400219
INFO : com.atomikos.datasource.xa.XATransactionalResource - uymDS: refreshed XAResource
INFO : com.k12knowledge.aop.SwitchDataSourceImpl - datasource switched from jdbc:mysql://xxx.xxx.xxx.xxx:xxxx/TEST_2012_Nov?zeroDateTimeBehavior=convertToNull to : jdbc:mysql://xxx.xxx.xxx.xxx:xxxx/MFS_2013_Feb?zeroDateTimeBehavior=convertToNull
INFO : com.atomikos.jdbc.AbstractDataSourceBean - AtomikosDataSoureBean 'uymDS': getConnection ( null )...
INFO : com.atomikos.jdbc.AbstractDataSourceBean - AtomikosDataSoureBean 'uymDS': init...
WARN : com.atomikos.jdbc.AtomikosSQLException - Cannot initialize AtomikosDataSourceBean
javax.naming.NamingException: Another resource already exists with name uymDS - pick a different name

[The 'createCompositeTransaction' and 'Coordinator not recoverable' are two lines I don't see on my test run.]

I have dug around in the Atomikos code and confirmed that Atomikos is removing the named resource on close.
I have tried setting lazy-init="false" with no change.
I have tried altering the properties file url on close thinking the init will re-read the properties file on init - no change.

Kinda want this Switching Datasources dynamically at runtime (I understand boneCP is not XA Transaction compliant)

Cheers