Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Conn

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]

Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Connection communication failure

From: sridhar baratam <sridhar_baratam@xxxxxxxxx>
Date: Thu, 29 Sep 2011 10:47:22 -0700 (PDT)
Delivered-to: eclipselink-dev@xxxxxxxxxxx
Domainkey-signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=AIJJOqn8Q+f/ZCPQtKobor4O1K30I09+q1s530BdtBAcVnAKQhKbrVDJxT57De/hpFYhRsWPSZl2yEKDWT6zY8QcCyNan8p37Sr7TQbvXzmeeuEM7nyfXwGvCZBstZm/Igy/VyVFgufR2r9e+L+8l5yQG6WdR2WwV11o3xstwPM=;
List-archive: <https://dev.eclipse.org/mailman/private/eclipselink-dev>
List-help: <mailto:eclipselink-dev-request@eclipse.org?subject=help>
List-subscribe: <https://dev.eclipse.org/mailman/listinfo/eclipselink-dev>, <mailto:eclipselink-dev-request@eclipse.org?subject=subscribe>
List-unsubscribe: <https://dev.eclipse.org/mailman/options/eclipselink-dev>, <mailto:eclipselink-dev-request@eclipse.org?subject=unsubscribe>

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"/>

  
   
        <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
> https://dev.eclipse.org/mailman/listinfo/eclipselink-dev
_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev

Follow-Ups:
- Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Connection communication failure
  - From: Tom Ware

References:
- Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Connection communication failure
  - From: Tom Ware

Prev by Date: Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Connection communication failure
Next by Date: [eclipselink-dev] Attention: planned cut-over to use Tycho for Build.
Previous by thread: Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Connection communication failure
Next by thread: Re: [eclipselink-dev] Sybase Connection logs in as guest in case of Connection communication failure
Index(es):
- Date
- Thread

Breadcrumbs