Hello Gurus,
I am trying to test the behavior of DelayBetweenConnectionAttempts on a
Sybase Connection communication behavior and running into problems. From
what I have experienced so far, when EclipseLink recovers from the
communication link failure, Sybase Connection gets logged in as user
"guest" as opposed to the original input user (which has the table
read/write access) thus resulting in "SybSQLException Table not found"
error.
My test case was relatively simple:
1. Write a sample program which does the following:
- Call GetOrders()
- Sleep for 10 seconds
- Call GetOrders() again.
While the program sleeps, stop the Sybase services and restart the
service. In the meantime, the program wakes up from sleep, determines a
ConnectionException and kicks in the Connection retry mechsnism using
the DelayBetweenConnectionAttempts variable. After a few attemps,
EclipseLink recovers but almost 50% of the times, connects to the
database as user guest thus resulting in a SybSQLException stating table
not found.
Here is my test module:
-----------------------
package com.sridhar.jpa.tutorial.test;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import javax.persistence.*;
import com.sridhar.jpa.tutorial.Order;
public class RetryTest {
public static void main(String[] args) {
EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("JPA");
getOrders(entityManagerFactory);
try {
System.out.println("Sleeping");
Thread.sleep(10000);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
getOrders(entityManagerFactory);
entityManagerFactory.close();
}
private static void getOrders(EntityManagerFactory entityManagerFactory) {
EntityManager em = null;
int retryCount = 0;
int masterRetry = 4;
do {
try {
System.out.println("Trying to create EM");
em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
System.out.println("Got one EM");
TypedQuery<Order> qry = em.createQuery("SELECT o FROM ORDERS o",
Order.class);
List<?> orderList = qry.getResultList();
System.out.println("Got query resultset");
Iterator<?> iter = orderList.iterator();
while (iter.hasNext()) {
Order order = (Order) iter.next();
System.out.println("order : " + order);
}
} catch (Exception e) {
System.out.println("Got an exception: " + e.getMessage());
retryCount++;
if (em != null)
{
java.sql.Connection connection = em.unwrap(java.sql.Connection.class);
try {
connection.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
em.close();
em = null;
}
System.out.println("Continuing the loop");
continue;
} finally {
if (em != null)
{
em.getTransaction().commit();
em.close();
em = null;
}
}
return;
} while (retryCount < masterRetry);
}
}
-------------------------------------------------------------------------------
Here is the log information during the retry mechanims:
-------------------------------------------------------
[EL Info]: 2011-09-23
16:51:29.072--ServerSession(29167060)--Thread(Thread[main,5,main])--EclipseLink,
version: Eclipse Persistence Services - 2.3.0.v20110604-r9504
[EL Fine]: 2011-09-23
16:51:29.354--Thread(Thread[main,5,main])--Detected Vendor platform:
org.eclipse.persistence.platform.database.SybasePlatform
[EL Config]: 2011-09-23
16:51:29.4--ServerSession(29167060)--Connection(27979955)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>SybasePlatform
user name=> "dbuser"
datasource URL=> "jdbc:sybase:Tds:barsr01b:5000/AEDB"
))
[EL Config]: 2011-09-23
16:51:29.463--ServerSession(29167060)--Connection(27296482)--Thread(Thread[main,5,main])--Connected:
jdbc:sybase:Tds:barsr01b:5000/AEDB
User: dbuser
Database: Adaptive Server Enterprise Version: Adaptive Server
Enterprise/15.0/EBF 12783 GA/P/NT (IX86)/Windows
2000/ase150/2158/32-bit/OPT/Fri Aug 19 18:42:50 2005
Driver: jConnect (TM) for JDBC (TM) Version: jConnect (TM) for
JDBC(TM)/6.05(Build 25773)/P/EBF12723/JDK14/Fri Aug 5 0:05:43 2005
[EL Info]: 2011-09-23
16:51:29.619--ServerSession(29167060)--Thread(Thread[main,5,main])--file:/C:/Vignesh-SPOG/JPA/build/classes/_JPA
login successful
Got one EM
[EL Fine]: 2011-09-23
16:51:30.354--ServerSession(29167060)--Connection(27296482)--Thread(Thread[main,5,main])--SELECT
t1.PROD_ID, t1.REGULAR_PRICE, t1.PROD_DESC, t1.PROD_NAME,
t1.LAST_UPDATED_TIME FROM ORDER_DETAIL t0, PRODUCT t1 WHERE
((t0.ORDER_ID = ?) AND (t1.PROD_ID = t0.PROD_ID))
bind => [111]
Got query resultset
order : orderId : 111 custId : 100 totPrice : 100.0 orderDesc : Internet
and phone orderDt : Wed Sep 07 00:00:00 EDT 2011 invoice : orderId : 111
invoiceId : 1 amtDue : 100.0 orderRaisedDt : Wed Sep 07 00:00:00 EDT
2011 orderSettledDt : Wed Sep 07 00:00:00 EDT 2011 orderCancelledDt :
null updatedTime : Wed Sep 07 00:00:00 EDT 2011
order : orderId : 222 custId : 100 totPrice : 15.0 orderDesc : Cable at
discounted price orderDt : Wed Sep 07 00:00:00 EDT 2011 invoice :
orderId : 222 invoiceId : 2 amtDue : 15.0 orderRaisedDt : Wed Sep 07
00:00:00 EDT 2011 orderSettledDt : Wed Sep 07 00:00:00 EDT 2011
orderCancelledDt : Wed Sep 07 00:00:00 EDT 2011 updatedTime : Wed Sep 07
00:00:00 EDT 2011
order : orderId : 333 custId : 110 totPrice : 99.0 orderDesc : 3 in one
offer orderDt : Wed Sep 07 00:00:00 EDT 2011 invoice : orderId : 333
invoiceId : 3 amtDue : 99.0 orderRaisedDt : Wed Sep 07 00:00:00 EDT 2011
orderSettledDt : Wed Sep 07 00:00:00 EDT 2011 orderCancelledDt : null
updatedTime : Wed Sep 07 00:00:00 EDT 2011
Sleeping
Trying to create EM
Got one EM
Call: SELECT ORDER_ID, CUST_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE,
LAST_UPDATED_TIME FROM ORDERS
Query: ReadAllQuery(referenceClass=Order sql="SELECT ORDER_ID, CUST_ID,
OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME FROM ORDERS").
[EL Config]: 2011-09-23
16:51:40.416--ServerSession(29167060)--Connection(31637242)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>SybasePlatform
user name=> "dbuser"
datasource URL=> "jdbc:sybase:Tds:barsr01b:5000/AEDB"
))
[EL Severe]: 2011-09-23
16:51:42.307--ServerSession(29167060)--Thread(Thread[main,5,main])--Local
Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: JZ006: Caught IOException:
java.net.ConnectException: Connection refused: connect
Error Code: 0
at
org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
at
org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
at
org.eclipse.persistence.sessions.DefaultConnector.connect(DefaultConnector.java:138)
at
org.eclipse.persistence.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:162)
at
org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.connectInternal(DatasourceAccessor.java:330)
at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.connectInternal(DatabaseAccessor.java:293)
at
org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.connect(DatasourceAccessor.java:418)
at
org.eclipse.persistence.sessions.server.ConnectionPool.buildConnection(ConnectionPool.java:216)
at
org.eclipse.persistence.sessions.server.ConnectionPool.acquireConnection(ConnectionPool.java:135)
at
org.eclipse.persistence.sessions.server.ServerSession.getAccessors(ServerSession.java:534)
at
org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:553)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646)
at
org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2592)
at
org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2551)
at
org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:418)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1097)
at
org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:829)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1056)
at
org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:390)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1144)
at
org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1501)
at
org.eclipse.persistence.internal.sessions.AbstractSession.retryQuery(AbstractSession.java:1571)
at
org.eclipse.persistence.sessions.server.ClientSession.retryQuery(ClientSession.java:593)
at
org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.retryQuery(UnitOfWorkImpl.java:5453)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1537)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1483)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1457)
at
org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
at
org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:742)
at com.sridhar.jpa.tutorial.test.RetryTest.getOrders(RetryTest.java:41)
at com.sridhar.jpa.tutorial.test.RetryTest.main(RetryTest.java:26)
Caused by: java.sql.SQLException: JZ006: Caught IOException:
java.net.ConnectException: Connection refused: connect
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseErrorCheckDead(Unknown Source)
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.login(Unknown Source)
at com.sybase.jdbc3.jdbc.SybConnection.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybConnection.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybConnection.<init>(Unknown Source)
at com.sybase.jdbc3.jdbc.SybConnection.<init>(Unknown Source)
at com.sybase.jdbc3.jdbc.SybDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at
org.eclipse.persistence.sessions.DefaultConnector.connect(DefaultConnector.java:98)
... 32 more
[EL Info]: 2011-09-23
16:51:42.307--UnitOfWork(1635615)--Thread(Thread[main,5,main])--Communication
failure detected when attempting to perform read query outside of a
transaction. Attempting to retry query. Error was: Exception
[EclipseLink-4002] (Eclipse Persistence Services -
2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: JZ006: Caught IOException:
java.net.ConnectException: Connection refused: connect
Error Code: 0
Query: ReadAllQuery(referenceClass=Order sql="SELECT ORDER_ID, CUST_ID,
OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME FROM ORDERS").
[EL Config]: 2011-09-23
16:51:52.322--ServerSession(29167060)--Connection(23257749)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>SybasePlatform
user name=> "dbuser"
datasource URL=> "jdbc:sybase:Tds:barsr01b:5000/AEDB"
))
[EL Config]: 2011-09-23
16:51:52.978--ServerSession(29167060)--Connection(8755816)--Thread(Thread[main,5,main])--Connected:
jdbc:sybase:Tds:barsr01b:5000/AEDB
User: guest
Database: Adaptive Server Enterprise Version: Adaptive Server
Enterprise/15.0/EBF 12783 GA/P/NT (IX86)/Windows
2000/ase150/2158/32-bit/OPT/Fri Aug 19 18:42:50 2005
Driver: jConnect (TM) for JDBC (TM) Version: jConnect (TM) for
JDBC(TM)/6.05(Build 25773)/P/EBF12723/JDK14/Fri Aug 5 0:05:43 2005
[EL Fine]: 2011-09-23
16:51:52.978--ServerSession(29167060)--Connection(8755816)--Thread(Thread[main,5,main])--SELECT
ORDER_ID, CUST_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE,
LAST_UPDATED_TIME FROM ORDERS
[EL Fine]: 2011-09-23
16:51:52.994--ServerSession(29167060)--Thread(Thread[main,5,main])--SELECT 1
[EL Warning]: 2011-09-23
16:51:52.994--UnitOfWork(1635615)--Thread(Thread[main,5,main])--Exception
[EclipseLink-4002] (Eclipse Persistence Services -
2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: ORDERS not
found. Specify owner.objectname or use sp_help to check whether the
object exists (sp_help may produce lots of output).
Error Code: 208
Call: SELECT ORDER_ID, CUST_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE,
LAST_UPDATED_TIME FROM ORDERS
Query: ReadAllQuery(referenceClass=Order sql="SELECT ORDER_ID, CUST_ID,
OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME FROM ORDERS")
Got an exception:
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: ORDERS not
found. Specify owner.objectname or use sp_help to check whether the
object exists (sp_help may produce lots of output).
------------------------------------------------------------------------------------
As you could see from the log file above, the connection recovers but as
user "guest" thus resulting in a failure. I even tried closing the
entity manager and that did not work. Later, I even tried to close the
connection as well but that did not help either.
It appears that Sybase allows a connection to be successful even when
the user specifies an invalid database like:
<property name="javax.persistence.jdbc.url"
value="jdbc:sybase:Tds:barsr01b:5000/XXXXXXXXXX"/>
In this case, Sybase logins in an user guest going against the master
database but gives SQL warning on the same. From what I can see,
EclipseLink should catch the SQL warnings and fail the connection.
Here is how one can get the SQLWarnings:
Properties props = new Properties();
props.setProperty("userName", user);
props.setProperty("password", pwd);
m_con = DriverManager.getConnection(url, user, pwd);
SQLWarning warning = m_con.getWarnings();
if (warning != null) {
logger.log(Level.SEVERE, "SQLWarning: ");
logger.log(Level.SEVERE, warning.getLocalizedMessage());
ErrorStack.addError(warning.getLocalizedMessage());
m_con.close();
return;
}
I appreciate all the help.
Thanks
Sridhar
------------------------------------------------------------------------
_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev
