Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Sybase Connection logs in as guest in case of Connection communication failure(Connection DelayBetweenConnectionAttempts)
Sybase Connection logs in as guest in case of Connection communication failure [message #729322] Sun, 25 September 2011 18:23
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
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
Previous Topic:Object: is not a known entity type.
Next Topic:desktop Swing application with EclipseLink
Goto Forum:
  


Current Time: Thu Aug 21 22:11:22 EDT 2014

Powered by FUDForum. Page generated in 0.02123 seconds