Community
Participate
Working Groups
Hello Gurus, I am trying to test the behavior of DelayBetweenConnectionAttempts on a Sybase Connection communication failure 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() (any simple select query should serve the purpose) - 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 = 10; do { System.out.println("Continuing the loop for retry count:" + retryCount); 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) { retryCount++; System.out.println("Got an exception: " + e.getMessage()); 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("CLosing EM"); } System.out.println("Going to the top of the loop"); try { System.out.println("Sleeping for 5"); Thread.sleep(5000); } catch (InterruptedException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } continue; } //finally { // System.out.println("COming to finally block"); // if (em != null) // { // em.getTransaction().commit(); // em.close(); // em = null; // } //} return; } while (retryCount < masterRetry); } } ------------------------------------------------------------------------------- Here is the log information during the retry mechanims: ------------------------------------------------------------------------------- Continuing the loop for retry count:0 Trying to create EM [EL Info]: 2011-09-26 16:40:08.101--ServerSession(26093085)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.3.0.v20110604-r9504 [EL Fine]: 2011-09-26 16:40:08.398--Thread(Thread[main,5,main])--Detected Vendor platform: org.eclipse.persistence.platform.database.SybasePlatform [EL Config]: 2011-09-26 16:40:08.492--ServerSession(26093085)--Connection(7312507)--Thread(Thread[main,5,main])--connecting(DatabaseLogin( platform=>SybasePlatform user name=> "dbuser" datasource URL=> "jdbc:sybase:Tds:barsr01b:5000/AEDB" )) [EL Config]: 2011-09-26 16:40:11.429--ServerSession(26093085)--Connection(28677619)--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-26 16:40:11.601--ServerSession(26093085)--Thread(Thread[main,5,main])--file:/C:/Vignesh-SPOG/JPA/build/classes/_JPA login successful Got one EM [EL Fine]: 2011-09-26 16:40:12.351--ServerSession(26093085)--Connection(28677619)--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-26 16:40:12.398--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT CUST_ID, APPT, CITY, EMAIL, FIRST_NAME, LAST_NAME, STREET, LAST_UPDATED_TIME, ZIP_CODE FROM CUSTOMER WHERE (CUST_ID = ?) bind => [100] [EL Fine]: 2011-09-26 16:40:12.429--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT ORDER_ID, CUST_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME FROM ORDERS WHERE (CUST_ID = ?) bind => [100] [EL Fine]: 2011-09-26 16:40:12.445--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, ORDER_ID, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME FROM ORDER_INVOICE WHERE (ORDER_ID = ?) bind => [222] [EL Fine]: 2011-09-26 16:40:12.476--ServerSession(26093085)--Connection(28677619)--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 => [222] [EL Fine]: 2011-09-26 16:40:12.523--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT t1.ORDER_ID, t1.CUST_ID, t1.OREDER_DESC, t1.ORDER_DATE, t1.TOTAL_PRICE, t1.LAST_UPDATED_TIME FROM ORDER_DETAIL t0, ORDERS t1 WHERE ((t0.PROD_ID = ?) AND (t1.ORDER_ID = t0.ORDER_ID)) bind => [2010] [EL Fine]: 2011-09-26 16:40:12.538--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT CUST_ID, APPT, CITY, EMAIL, FIRST_NAME, LAST_NAME, STREET, LAST_UPDATED_TIME, ZIP_CODE FROM CUSTOMER WHERE (CUST_ID = ?) bind => [110] [EL Fine]: 2011-09-26 16:40:12.538--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT ORDER_ID, CUST_ID, OREDER_DESC, ORDER_DATE, TOTAL_PRICE, LAST_UPDATED_TIME FROM ORDERS WHERE (CUST_ID = ?) bind => [110] [EL Fine]: 2011-09-26 16:40:12.538--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, ORDER_ID, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME FROM ORDER_INVOICE WHERE (ORDER_ID = ?) bind => [333] [EL Fine]: 2011-09-26 16:40:12.554--ServerSession(26093085)--Connection(28677619)--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 => [333] [EL Fine]: 2011-09-26 16:40:12.57--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT t1.ORDER_ID, t1.CUST_ID, t1.OREDER_DESC, t1.ORDER_DATE, t1.TOTAL_PRICE, t1.LAST_UPDATED_TIME FROM ORDER_DETAIL t0, ORDERS t1 WHERE ((t0.PROD_ID = ?) AND (t1.ORDER_ID = t0.ORDER_ID)) bind => [2000] [EL Fine]: 2011-09-26 16:40:12.585--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT t1.ORDER_ID, t1.CUST_ID, t1.OREDER_DESC, t1.ORDER_DATE, t1.TOTAL_PRICE, t1.LAST_UPDATED_TIME FROM ORDER_DETAIL t0, ORDERS t1 WHERE ((t0.PROD_ID = ?) AND (t1.ORDER_ID = t0.ORDER_ID)) bind => [2020] [EL Fine]: 2011-09-26 16:40:12.601--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--SELECT INVOICE_ID, AMOUNT_DUE, DATE_CANCELLED, ORDER_ID, DATE_RAISED, DATE_SETTLED, LAST_UPDATED_TIME FROM ORDER_INVOICE WHERE (ORDER_ID = ?) bind => [111] [EL Fine]: 2011-09-26 16:40:12.616--ServerSession(26093085)--Connection(28677619)--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 Continuing the loop for retry count:0 Trying to create EM Got one EM [EL Fine]: 2011-09-26 16:40:22.648--ServerSession(26093085)--Connection(28677619)--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-26 16:40:22.648--ServerSession(26093085)--Thread(Thread[main,5,main])--SELECT 1 [EL Config]: 2011-09-26 16:40:22.648--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--disconnect [EL Info]: 2011-09-26 16:40:27.148--UnitOfWork(4388430)--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: com.sybase.jdbc3.jdbc.SybConnectionDeadException: JZ0C0: Connection is already closed. Error Code: 0 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-26 16:40:27.163--ServerSession(26093085)--Connection(21083958)--Thread(Thread[main,5,main])--connecting(DatabaseLogin( platform=>SybasePlatform user name=> "dbuser" datasource URL=> "jdbc:sybase:Tds:barsr01b:5000/AEDB" )) [EL Severe]: 2011-09-26 16:40:29.21--ServerSession(26093085)--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:42) 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-26 16:40:32.132--UnitOfWork(4388430)--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-26 16:40:42.132--ServerSession(26093085)--Connection(15613422)--Thread(Thread[main,5,main])--connecting(DatabaseLogin( platform=>SybasePlatform user name=> "dbuser" datasource URL=> "jdbc:sybase:Tds:barsr01b:5000/AEDB" )) [EL Config]: 2011-09-26 16:44:18.004--ServerSession(26093085)--Connection(4799946)--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-26 16:44:18.004--ServerSession(26093085)--Connection(4799946)--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-26 16:44:18.051--ServerSession(26093085)--Thread(Thread[main,5,main])--SELECT 1 [EL Warning]: 2011-09-26 16:44:18.066--UnitOfWork(4388430)--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). 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") CLosing EM Going to the top of the loop Sleeping for 5 Continuing the loop for retry count:1 Trying to create EM Got one EM [EL Fine]: 2011-09-26 16:44:23.066--ServerSession(26093085)--Connection(4799946)--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-26 16:44:23.066--ServerSession(26093085)--Thread(Thread[main,5,main])--SELECT 1 [EL Warning]: 2011-09-26 16:44:23.066--UnitOfWork(28626277)--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). 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") CLosing EM Going to the top of the loop Sleeping for 5 [EL Config]: 2011-09-26 16:45:08.394--ServerSession(26093085)--Connection(28677619)--Thread(Thread[main,5,main])--disconnect [EL Info]: 2011-09-26 16:45:08.394--ServerSession(26093085)--Thread(Thread[main,5,main])--file:/C:/Vignesh-SPOG/JPA/build/classes/_JPA logout successful [EL Config]: 2011-09-26 16:45:08.394--ServerSession(26093085)--Connection(4799946)--Thread(Thread[main,5,main])--disconnect ------------------------------------------------------------------------------------ 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. With that in mind, I put breakpoints in my code and was able to get the sqlwarning for the connection as follows: "010DF: Attempt to set database at login failed. Error message: Database 'AEDB' has not been recovered yet - please wait and try again." Upon looking at the code, I found this piece of code in org.eclipse.persistence.sessions.DefaultConnector.class try { return DriverManager.getConnection(this.getConnectionString(), properties); } catch (SQLException sqlException) { As you could see from the above, the connection is being returned without checking for warnings. In the case of Sybase, the code should not have honoured the connection and should have retried again until the db has been fully recovered. 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; } Unless this problem is fixed, I really cannot make any progress on the connection retry tests. I appreciate all the help. Thanks Sridhar
Created attachment 204394 [details] Sample Code to fix the Sybase connection as guest problem Please review the attachment for the sample fix provided that might help resolve the problem. The change needs to be made in the connect() method in the org.eclipse.persistence.sessions.DefaultConnector.class This fix is kind of generic in which it only checks for warnings coming from Sybase. Other database connections behavior will not be affected. I am not familar with the eclipseLink code and it should be reviewed by people knowledgable in this area.
Created attachment 204453 [details] updates to suggested patch I am attaching a version of this patch that is closer to the kind of fix we would consider including in EcipseLink. The thing that is missing, is that I believe the following part of the code is too general: if (warning != null) { connection.close(); We need to be able to narrow down the warning to the one/ones that happen in this particular situation (using error codes and other properties of the warning and not parsing the message - as it may be internationalized)
Hi Tom, This is the error message that we get when the application gets into an incorrect Sybase connection state with user as guest: "010DF: Attempt to set database at login failed. Error message: Database 'AEDB' has not been recovered yet - please wait and try again." In order to fix this particular warning/error, we can do the following: if ( (warning != null) && (warning.getSQLState().equalsIgnoreCase("010DF")) { connection.close(); Please let me know if you have additional questions. Thanks for your time, Sridhar
Won't the code you have provided result in issues if Warning "010DF" occurs and the cause is not "Database 'AEDB' has not been recovered yet". It looks like that exception could also be thrown for an incorrect URL. In that case, I think it is important that we proceed down that patch we are currently proceeding (i.e. catching the exception and trying to connect directly) That makes me wonder how Sybase expects us to recover connections. A google search indicate there is a 921 error associated with this. Is there any way to get that error code? http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@ebt-link;pt=39281?target=%25N%15_21571_START_RESTART_N%25
Hi Tom, You make up a valid point. It appears Sybase can throw this error code and the reason could be many and "Database not recovered" might be one of them. This is what the Sybase manual states: 010DF Attempt to set database at login failed. Error message:______. Description: jConnect cannot connect to the database specified in the connection URL. Action: Be sure the database name in the URL is correct. Also, if connecting to Adaptive Server Anywhere, use the SERVICENAME connection property to specify the database. http://manuals.sybase.com/onlinebooks/group-jcarc/jcg0520e/prjdbc/@Generic__BookTextView/9513 I am not sure how to capture the error code 921 from Sybase though. I could not get it on my env. I will research to see if I can find anything. Thanks Sridhar
Hi Tom, Just so you are aware of. I have opened an issue with Sybase for the JDBC driver which returns the sqlState as 010DF correctly but returns the associated ErrorCode always comes as Zero incorrectly. Here is a sample code provided to them going against a wrong database 'xxxxx': try { Class.forName("com.sybase.jdbc3.jdbc.SybDriver"); Connection conn=DriverManager.getConnection("jdbc:sybase:Tds:machineA:5000/xxxxx", "user", "password"); SQLWarning warning = conn.getWarnings(); while (warning != null) { System.out.println("warning: error code=" +warning.getErrorCode() + ", err msg: " + warning.getLocalizedMessage()); System.out.println("getStackTrace warning:" + warning.getStackTrace()); System.out.println("getLocalizedMessage:" + warning.getLocalizedMessage()); System.out.println("getSQLState:" + warning.getSQLState()); System.out.println("getMessage:" + warning.getMessage()); System.out.println("getCause:" + warning.getCause()); System.out.println("getClass:" + warning.getClass()); warning.printStackTrace(); warning = warning.getNextWarning(); } Statement stmt = conn.createStatement( ); ResultSet rs = stmt.executeQuery( "SELECT * FROM table"); while ( rs.next( ) ) { System.out.println( rs.getString( 2 ) ); } } catch ( Exception e ) { System.out.println( "An exception occurred: " + e.getLocalizedMessage() ); } Sybase did acknowledge this as a bug and are in the process of fixing the drivers. I will keep you posted when this is fixed and a patch is available for testing. Once we have this ready, I believe we can fix the issue at hand by adding an additional check on the Error code which might look as follows: if ( (warning != null) && (warning.getSQLState().equalsIgnoreCase("010DF")) && (warning.getErrorCode().equals("921")) { connection.close(); Thanks Sridhar
Thanks for all your work. Your changed code will probably have to go in a subclass of the current sybase platform. In the end, we will likely have two things to do. 1. Modify the framework to allow the behavior to be overriden -> like in the patch I provided 2. Add the new SybasePlatform subclass and include the new behavior That way we won't regress functionality for those not using the updated driver.
Setting target for now. When we have a fix from Sybase, we can investigate how best to include it. See the following page for the meanings of these fields: http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines Community: Please vote for this bug if it is important to you. Votes are one of the main criteria we use to determine which bugs to fix next.
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
