Cache Database failure
rshanker May 21, 2013 11:45 AMHi all,
Im running cache Database configuration. Here is the issue being observed:
1. If is run Default cache server and cluster (seperate JVM) im observing that the storing to DB is failing with Connection Exception
2. where-as if run only the cluster program alone without the Cache Server im not facing any issues, the data is getting store properly, Unsure what is going wrong here ?
Here is the configuration snippets:
cache-config.xml
<?xml version="1.0"?>
<cache-config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/coherence/coherence-cache-config"
xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-cache-config coherence-cache-config.xsd">
<caching-scheme-mapping>
<cache-mapping>
<cache-name>dbexample</cache-name>
<scheme-name>distributed</scheme-name>
</cache-mapping>
</caching-scheme-mapping>
<caching-schemes>
<distributed-scheme>
<scheme-name>distributed</scheme-name>
<service-name>DistributedCache</service-name>
<thread-count>4</thread-count>
<request-timeout>60s</request-timeout>
<backing-map-scheme>
<read-write-backing-map-scheme>
<internal-cache-scheme>
<local-scheme>
<scheme-name>SampleMemoryScheme</scheme-name>
</local-scheme>
</internal-cache-scheme>
<cachestore-scheme>
<class-scheme>
<class-name>com.coherence.KnDBCacheStore</class-name>
</class-scheme>
</cachestore-scheme>
</read-write-backing-map-scheme>
</backing-map-scheme>
<autostart>true</autostart>
</distributed-scheme>
<local-scheme>
<scheme-name>LocalSizeLimited</scheme-name>
<eviction-policy>LRU</eviction-policy>
<high-units>1000</high-units>
<expiry-delay>1h</expiry-delay>
</local-scheme>
</caching-schemes>
</cache-config>
==========================
tangosol-coherence-override.xml
<?xml version='1.0'?>
<coherence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/coherence/coherence-operational-config"
xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-operational-config coherence-operational-config.xsd">
<cluster-config>
<member-identity>
<cluster-name system-property="tangosol.coherence.cluster">kn_test</cluster-name>
</member-identity>
<unicast-listener>
<well-known-addresses>
<socket-address id="1">
<address>192.168.7.3</address>
<port>8088</port>
</socket-address>
<socket-address id="2">
<address>192.168.7.4</address>
<port>8088</port>
</socket-address>
</well-known-addresses>
</unicast-listener>
</cluster-config>
<configurable-cache-factory-config>
<init-params>
<init-param>
<param-type>java.lang.String</param-type>
<param-value system-property="tangosol.coherence.cacheconfig">cache-config.xml</param-value>
</init-param>
</init-params>
</configurable-cache-factory-config>
</coherence>
package com.coherence;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import com.tangosol.net.cache.CacheStore;
import com.tangosol.util.Base;
public class KnDBCacheStore extends Base implements CacheStore {
protected Connection conn;
protected String tableName = "DG.SUBSCRIBERINFO";
String localIPAddr = "192.168.7.3";
String localDBDSN = "DG_010031";
String localDBUid = "dbuser";
String localDBPwd = "dbuser";
String localDBPort = "53389";
private static final String DB_DRIVER = "com.timesten.jdbc.TimesTenDriver";
String localConnURL = "jdbc:timesten:client:TTC_Server=" + localIPAddr
+ ";TTC_Server_DSN=" + localDBDSN + ";UID=" + localDBUid + ";PWD="
+ localDBPwd + ";TCP_PORT=" + localDBPort + ";TTC_Timeout=180";
protected void configureConn() {
try {
Class.forName(DB_DRIVER);
System.out.println("DB Connection URL :" + localConnURL);
conn = DriverManager.getConnection(localConnURL);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Obtain the name of the table this CacheStore is persisting to.
*
* @return the name of the table this CacheStore is persisting to
*/
public String getTableName() {
return tableName;
}
/**
* Obtain the connection being used to connect to the database.
*
* @return the connection used to connect to the database
*/
public Connection getConnection() {
return conn;
}
@Override
public Object load(Object key) {
Object value = null;
Connection conn = getConnection();
String sqlQry = "SELECT SUBSCRNAME FROM " + getTableName()
+ " WHERE MDN=?";
try {
PreparedStatement pStmt = conn.prepareStatement(sqlQry);
pStmt.setString(1, String.valueOf(key));
ResultSet rs = pStmt.executeQuery();
if (rs.next()) {
value = rs.getString("SUBSCRNAME");
}
} catch (Exception e) {
e.printStackTrace();
}
return value;
}
@Override
public Map loadAll(Collection arg0) {
throw new UnsupportedOperationException();
}
@Override
public void erase(Object key) {
Connection con = getConnection();
String sSQL = "DELETE FROM " + getTableName() + " WHERE MDN=?";
try {
PreparedStatement stmt = con.prepareStatement(sSQL);
stmt.setString(1, String.valueOf(key));
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
throw ensureRuntimeException(e, "Erase failed: key=" + key);
}
}
@Override
public void eraseAll(Collection arg0) {
throw new UnsupportedOperationException();
}
@Override
public void store(Object key, Object value) {
Connection con = getConnection();
String sTable = getTableName();
String sSQL;
// the following is very inefficient; it is recommended to use DB
// specific functionality that is, REPLACE for MySQL or MERGE for Oracle
if (load(key) != null) {
// key exists - update
sSQL = "UPDATE " + sTable + " SET SUBSCRNAME = ? where MDN = ?";
} else {
// new key - insert
sSQL = "INSERT INTO " + sTable + " (SUBSCRNAME, MDN) VALUES (?,?)";
}
try {
PreparedStatement stmt = con.prepareStatement(sSQL);
int i = 0;
stmt.setString(++i, String.valueOf(value));
stmt.setString(++i, String.valueOf(key));
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
throw ensureRuntimeException(e, "Store failed: key=" + key);
}
}
@Override
public void storeAll(Map arg0) {
throw new UnsupportedOperationException();
}
/**
* Iterate all keys in the underlying store.
*
* @return a read-only iterator of the keys in the underlying store
*/
public Iterator<Object> keys() {
Connection con = getConnection();
String sSQL = "SELECT MDN FROM " + getTableName();
List<Object> list = new LinkedList<Object>();
try {
PreparedStatement stmt = con.prepareStatement(sSQL);
ResultSet rslt = stmt.executeQuery();
while (rslt.next()) {
Object oKey = rslt.getString(1);
list.add(oKey);
}
stmt.close();
} catch (SQLException e) {
throw ensureRuntimeException(e, "Iterator failed");
}
return list.iterator();
}
}
Im running cache Database configuration. Here is the issue being observed:
1. If is run Default cache server and cluster (seperate JVM) im observing that the storing to DB is failing with Connection Exception
2. where-as if run only the cluster program alone without the Cache Server im not facing any issues, the data is getting store properly, Unsure what is going wrong here ?
Here is the configuration snippets:
cache-config.xml
<?xml version="1.0"?>
<cache-config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/coherence/coherence-cache-config"
xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-cache-config coherence-cache-config.xsd">
<caching-scheme-mapping>
<cache-mapping>
<cache-name>dbexample</cache-name>
<scheme-name>distributed</scheme-name>
</cache-mapping>
</caching-scheme-mapping>
<caching-schemes>
<distributed-scheme>
<scheme-name>distributed</scheme-name>
<service-name>DistributedCache</service-name>
<thread-count>4</thread-count>
<request-timeout>60s</request-timeout>
<backing-map-scheme>
<read-write-backing-map-scheme>
<internal-cache-scheme>
<local-scheme>
<scheme-name>SampleMemoryScheme</scheme-name>
</local-scheme>
</internal-cache-scheme>
<cachestore-scheme>
<class-scheme>
<class-name>com.coherence.KnDBCacheStore</class-name>
</class-scheme>
</cachestore-scheme>
</read-write-backing-map-scheme>
</backing-map-scheme>
<autostart>true</autostart>
</distributed-scheme>
<local-scheme>
<scheme-name>LocalSizeLimited</scheme-name>
<eviction-policy>LRU</eviction-policy>
<high-units>1000</high-units>
<expiry-delay>1h</expiry-delay>
</local-scheme>
</caching-schemes>
</cache-config>
==========================
tangosol-coherence-override.xml
<?xml version='1.0'?>
<coherence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/coherence/coherence-operational-config"
xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-operational-config coherence-operational-config.xsd">
<cluster-config>
<member-identity>
<cluster-name system-property="tangosol.coherence.cluster">kn_test</cluster-name>
</member-identity>
<unicast-listener>
<well-known-addresses>
<socket-address id="1">
<address>192.168.7.3</address>
<port>8088</port>
</socket-address>
<socket-address id="2">
<address>192.168.7.4</address>
<port>8088</port>
</socket-address>
</well-known-addresses>
</unicast-listener>
</cluster-config>
<configurable-cache-factory-config>
<init-params>
<init-param>
<param-type>java.lang.String</param-type>
<param-value system-property="tangosol.coherence.cacheconfig">cache-config.xml</param-value>
</init-param>
</init-params>
</configurable-cache-factory-config>
</coherence>
package com.coherence;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import com.tangosol.net.cache.CacheStore;
import com.tangosol.util.Base;
public class KnDBCacheStore extends Base implements CacheStore {
protected Connection conn;
protected String tableName = "DG.SUBSCRIBERINFO";
String localIPAddr = "192.168.7.3";
String localDBDSN = "DG_010031";
String localDBUid = "dbuser";
String localDBPwd = "dbuser";
String localDBPort = "53389";
private static final String DB_DRIVER = "com.timesten.jdbc.TimesTenDriver";
String localConnURL = "jdbc:timesten:client:TTC_Server=" + localIPAddr
+ ";TTC_Server_DSN=" + localDBDSN + ";UID=" + localDBUid + ";PWD="
+ localDBPwd + ";TCP_PORT=" + localDBPort + ";TTC_Timeout=180";
protected void configureConn() {
try {
Class.forName(DB_DRIVER);
System.out.println("DB Connection URL :" + localConnURL);
conn = DriverManager.getConnection(localConnURL);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Obtain the name of the table this CacheStore is persisting to.
*
* @return the name of the table this CacheStore is persisting to
*/
public String getTableName() {
return tableName;
}
/**
* Obtain the connection being used to connect to the database.
*
* @return the connection used to connect to the database
*/
public Connection getConnection() {
return conn;
}
@Override
public Object load(Object key) {
Object value = null;
Connection conn = getConnection();
String sqlQry = "SELECT SUBSCRNAME FROM " + getTableName()
+ " WHERE MDN=?";
try {
PreparedStatement pStmt = conn.prepareStatement(sqlQry);
pStmt.setString(1, String.valueOf(key));
ResultSet rs = pStmt.executeQuery();
if (rs.next()) {
value = rs.getString("SUBSCRNAME");
}
} catch (Exception e) {
e.printStackTrace();
}
return value;
}
@Override
public Map loadAll(Collection arg0) {
throw new UnsupportedOperationException();
}
@Override
public void erase(Object key) {
Connection con = getConnection();
String sSQL = "DELETE FROM " + getTableName() + " WHERE MDN=?";
try {
PreparedStatement stmt = con.prepareStatement(sSQL);
stmt.setString(1, String.valueOf(key));
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
throw ensureRuntimeException(e, "Erase failed: key=" + key);
}
}
@Override
public void eraseAll(Collection arg0) {
throw new UnsupportedOperationException();
}
@Override
public void store(Object key, Object value) {
Connection con = getConnection();
String sTable = getTableName();
String sSQL;
// the following is very inefficient; it is recommended to use DB
// specific functionality that is, REPLACE for MySQL or MERGE for Oracle
if (load(key) != null) {
// key exists - update
sSQL = "UPDATE " + sTable + " SET SUBSCRNAME = ? where MDN = ?";
} else {
// new key - insert
sSQL = "INSERT INTO " + sTable + " (SUBSCRNAME, MDN) VALUES (?,?)";
}
try {
PreparedStatement stmt = con.prepareStatement(sSQL);
int i = 0;
stmt.setString(++i, String.valueOf(value));
stmt.setString(++i, String.valueOf(key));
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
throw ensureRuntimeException(e, "Store failed: key=" + key);
}
}
@Override
public void storeAll(Map arg0) {
throw new UnsupportedOperationException();
}
/**
* Iterate all keys in the underlying store.
*
* @return a read-only iterator of the keys in the underlying store
*/
public Iterator<Object> keys() {
Connection con = getConnection();
String sSQL = "SELECT MDN FROM " + getTableName();
List<Object> list = new LinkedList<Object>();
try {
PreparedStatement stmt = con.prepareStatement(sSQL);
ResultSet rslt = stmt.executeQuery();
while (rslt.next()) {
Object oKey = rslt.getString(1);
list.add(oKey);
}
stmt.close();
} catch (SQLException e) {
throw ensureRuntimeException(e, "Iterator failed");
}
return list.iterator();
}
}