Hi Tom,
Thanks for the prompt reply. Here are the details:
User "dbuser" has CRUD permissions on the AEDB database. I connect to
the application using this "dbuser".
Here is my persistence.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
xmlns="http://java.sun.com/xml/ns/persistence";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd";>
<persistence-unit name="JPA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>com.sridhar.jpa.tutorial.Invoice</class>
<class>com.sridhar.jpa.tutorial.Order</class>
<class>com.sridhar.jpa.tutorial.Customer</class>
<class>com.sridhar.jpa.tutorial.Product</class>
<class>com.sridhar.jpa.tutorial.Person</class>
<properties>
<property name="javax.persistence.jdbc.driver"
value="com.sybase.jdbc3.jdbc.SybDriver"/>
<property name="javax.persistence.jdbc.url"
value="jdbc:sybase:Tds:barsr01b:5000/AEDB"/>
<property name="javax.persistence.jdbc.user"
value="dbuser"/>
<property name="javax.persistence.jdbc.password"
value="dbuser"/>
<!-- EclipseLink should create the database schema
automatically -->
<!-- property name="eclipselink.ddl-generation"
value="drop-and-create-tables" />
<property name="eclipselink.ddl-generation.output-mode"
value="both" /-->
<property name="eclipselink.logging.level" value="FINE"/>
<property name="eclipselink.session.customizer"
value="com.sridhar.jpa.waae.custom.JPASessionCustomizer"/>
<property name="eclipselink.jdbc.cache-statements"
value="true"/> </properties> </persistence-unit>
</persistence>
I am running this module as a standalone application and not using
JTA. I set my logging level as fine and I believe that is why we are
able to see all the logging associated with user "dbuser" and the
exceptions caught/thrown internally within eclipselink.
As I mentioned in the email, Sybase is a little different in its
behavior. When a connection is requested when Sybase is still coming
up and master being online, Sybase goes ahaead and connects with user
as "guest" thus giving a connection with "warnings". It does not throw
an exception.
In the case that I ran into, the retry code within
org.eclipse.persistence.internal.sessions.AbstractSession attempts to
retry the query using retryQuery function on a communication error and
when Sybase comes up partially (only recovering the master db), the
following code within
org.eclipse.persistence.sessions.DefaultConnector.class returns a
connection without even checking for the warnings on the connection.
try {
return
DriverManager.getConnection(this.getConnectionString(),
properties);
} catch (SQLException sqlException) {
The warning in this case would be:
"010DF: Attempt to set database at login failed. Error message:
Database 'AEDB' has not been recovered yet - please wait and try
again."
It is very easy to recreate the problem using my test case. You can
change getOrders to someSelect function.
Please let me know if you have additional questions.
I really appreciate your time.
Thanks for all your help.
Sridhar
--- On *Thu, 9/29/11, Tom Ware /<tom.ware@xxxxxxxxxx>/* wrote:
From: Tom Ware <tom.ware@xxxxxxxxxx>
Subject: Re: [eclipselink-dev] Sybase Connection logs in as guest in
case of Connection communication failure
To: "Dev mailing list for Eclipse Persistence Services"
<eclipselink-dev@xxxxxxxxxxx>
Date: Thursday, September 29, 2011, 11:32 AM
Hi Sridhar,
My first question is:
Where does the string "dbuser" come from. I can't find anywhere in
the EclipseLink code that could accidentally be providing that
string.
How are you setting up your database connectivity? What does the
persistence.xml for persistence unit "JPA" look like?
-Tom
sridhar baratam wrote:
> 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
<http://us.mc655.mail.yahoo.com/mc/compose?to=eclipselink-dev@xxxxxxxxxxx>
> https://dev.eclipse.org/mailman/listinfo/eclipselink-dev
_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
<http://us.mc655.mail.yahoo.com/mc/compose?to=eclipselink-dev@xxxxxxxxxxx>
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev
