Switch Atomikos Connection Pool Url Naming Exception

[pgtips]

Hello,

The need: switch an Atomikos Connection pool bean's url dynamically. There is a default bean with pool settings etc in xml config file.

I tested the code using the following:

@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 worked.

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)

Any suggestions / pointers?

Thank you,
Peter

[mminella]

How is this being used within the scope of a job? My gut is to say "don't do that", but without seeing what you are trying to attempt within the scope of this job I can't be sure.

[pgtips]

Thank you for responding.

Through earlier tasklets that retrieve files, unzip if necessary, I arrive with an arraylist of file objects at the ItemReader stage. Each file may go to different databases to be inserted or it may be multiplies going to the same database. I don't know before hand what files are coming in for what databases. (New databases are created on the 1st of each month which means large numbers of dbs that I don't want to have to declare in a config file (and may not know) adding to the dynamic nature) I thought if I set up default settings for the Atomikos pool (username, password etc are the same) I could reset the pool before the actual writing by the jdbcTemplate.

I want (I think) the dynamic Dynamic DataSource Routing with no already determined database sources.

I think I will try the above approach using Atomikos and known databases and see if it works like that and then try and make it dynamic. Will post back what I found.

In the meantime still welcome input / correction.

Thank you.

[mminella]

Just to understand, the step in question is to pipe files into multiple databases, correct? If that is the case, why not define connections to all of the potential databases and use the ClassifierCompositeItemWriter? This ItemWriter allows you to delegate to the appropriate ItemWriter (in your case, each configured to point to a new database) based on a classifier of some kind.

[pgtips]

Yes - you are correct.

When you say 'define connections to all of the potential databases' are you meaning define them in a bean context file or on the fly? Currently if the database does not exist an earlier step before the reader/processor/writer step creates the db and accompanying schema, so no record of it in the context file.

If on the fly - create and store the connections and subsequent ItemWriters in the stepExecutionerListener? I think I only need to change one thing - the datasource. Say the datasource (ds1) points to ABC_Feb_2013. I have my Dao injected with (ds1) but before the dao.insert() / dao.update() I change (ds1) to point to BCD_Feb_2013. Dao is none the wiser, 'blindly' inserts and am thinking, I have been Spring like in making it very loose.

Admit to being fuzzy - as much as I have jumped into using Spring (and the bits I get - love it!) I am of the impression - you must know all beans up front e.g datasources. So with what I have seen (Dynamic DataSource Routing) in our case I have 150 databases and on the 1st of Feb 30 more were created. I now need to add 30 more entries in the datasource-context.xml. I then need to add 30 more entries to the enum type and re-deploy. Doesn't make sense and pretty sure that is my own doing...

I am reading up on ClassifierCompositeItemWriter in Spring Batch In Action - has an example.

Thank you,

[mminella]

So where does the connection information come from? Are you saying that the database connection information for all databases to be written to are not known at the start of the job? If that is the case, I would advocate for creating a job that does the db creation and another one that runs after it for the inserting into those databases so that all of the connection information is available at startup.