Description Praba Vijayaratnam 2011-04-11 10:10:04 EDT

with EclipseLink 2.3.0.v20110331-r9192, following bug exists against Sybase 15.5 & 15.03 using Jconnect: 

-----TEST MODELS----------------
Following test models from eclipselink.core.test LRG failed with same error:
    
org.eclipse.persistence.testing.tests.unitofwork.UnitOfWorkClientSessionTestMode
org.eclipse.persistence.testing.tests.unitofwork.ProtectedUnitOfWorkTestModel
org.eclipse.persistence.testing.tests.unitofwork.UnitOfWorkIsolatedAlwaysTestModel
org.eclipse.persistence.testing.tests.unitofwork.UnitOfWorkSynchNewObjectsClientSessionTestModel
org.eclipse.persistence.testing.tests.unitofwork.UnitOfWorkPartitionedTestModel
org.eclipse.persistence.testing.tests.unitofwork.UnitOfWorkPartitionedIsolatedAlwaysTestModel

-----TEST CASES----------------
-MultipleUnitOfWorkTest(Employee: Bob Smith)
-ViolateObjectSpaceTest
-NoValidationWithInitIdentityMaps

------ EXCEPTION -------
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: The optimizer could
not find a unique index which it could use to perform an isolation level 0 scan
on table 'RESPONS'. Error Code: 313 Call: SELECT t0.DESCRIP FROM RESPONS t0
WHERE (t0.EMP_ID = ?) bind => [22552] Query:
DirectReadQuery(name="responsibilitiesList" sql="SELECT t0.DESCRIP FROM RESPONS
t0 WHERE (t0.EMP_ID = ?)")

Local Exception Stack:
Exception [EclipseLink-4002] :
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: The optimizer could
not find a unique index which it could use to perform an isolation level 0 scan
on table 'RESPONS'.

Error Code: 313
Call: SELECT t0.DESCRIP FROM RESPONS t0 WHERE (t0.EMP_ID = ?)
bind => [22552]
Query: DirectReadQuery(name="responsibilitiesList" sql="SELECT t0.DESCRIP FROM
RESPONS t0 WHERE (t0.EMP_ID = ?)")
at
org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:686)
at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:529)
at
org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
at
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:244)
at
org.eclipse.persistence.queries.DataReadQuery.executeNonCursor(DataReadQuery.java:188)
at
org.eclipse.persistence.queries.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:144)
at
org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:670)
at
org.eclipse.persistence.queries.DataReadQuery.execute(DataReadQuery.java:130)
at
org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:2322)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
at
org.eclipse.persistence.internal.indirection.QueryBasedValueHolder.instantiate(QueryBasedValueHolder.java:85)
at
org.eclipse.persistence.internal.indirection.QueryBasedValueHolder.instantiate(QueryBasedValueHolder.java:75)
at
org.eclipse.persistence.internal.indirection.DatabaseValueHolder.getValue(DatabaseValueHolder.java:83)
at
org.eclipse.persistence.internal.indirection.BasicIndirectionPolicy.getRealAttributeValueFromObject(BasicIndirectionPolicy.java:251)
at
org.eclipse.persistence.mappings.ForeignReferenceMapping.getRealAttributeValueFromAttribute(ForeignReferenceMapping.java:665)
at
org.eclipse.persistence.mappings.DatabaseMapping.getRealAttributeValueFromObject(DatabaseMapping.java:623)
at
org.eclipse.persistence.mappings.CollectionMapping.getRealCollectionAttributeValueFromObject(CollectionMapping.java:992)
at
org.eclipse.persistence.mappings.DirectCollectionMapping.compareObjects(DirectCollectionMapping.java:671)
at
org.eclipse.persistence.internal.descriptors.ObjectBuilder.compareObjects(ObjectBuilder.java:1586)
at
org.eclipse.persistence.internal.sessions.AbstractSession.compareObjects(AbstractSession.java:722)
at
org.eclipse.persistence.testing.framework.TestCase.compareObjects(TestCase.java:107)
at
org.eclipse.persistence.testing.tests.unitofwork.MultipleUnitOfWorkTest.setup(MultipleUnitOfWorkTest.java:114)
at org.eclipse.persistence.testing.framework.TestCase.setUp(TestCase.java:225)
at
org.eclipse.persistence.testing.framework.TestCase.execute(TestCase.java:140)
at
org.eclipse.persistence.testing.framework.TestCase.runBare(TestCase.java:263)
at
org.eclipse.persistence.testing.framework.TestExecutor.execute(TestExecutor.java:248)
at
org.eclipse.persistence.testing.framework.TestSuite.execute(TestSuite.java:75)
at
org.eclipse.persistence.testing.framework.TestCollection.run(TestCollection.java:313)
at
org.eclipse.persistence.testing.framework.TestExecutor.execute(TestExecutor.java:248)
at
org.eclipse.persistence.testing.framework.TestModel.execute(TestModel.java:208)
at
org.eclipse.persistence.testing.framework.TestCollection.run(TestCollection.java:313)
at
org.eclipse.persistence.testing.framework.TestExecutor.execute(TestExecutor.java:248)
at
org.eclipse.persistence.testing.framework.TestModel.execute(TestModel.java:208)
at
org.eclipse.persistence.testing.framework.TestCollection.run(TestCollection.java:313)
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: The optimizer could not find
a unique index which it could use to perform an isolation level 0 scan on table
'RESPONS'.

at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeQuery(Unknown Source)
at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:891)

--- 
org.eclipse.persistence.testing.framework.SybaseTransactionIsolationListener
class meant to fix this issue for Sybase DB versions 15 and up, but seems not
working for these 6 test models.

-----
Sybase documentation following explanation for possible cause:

Isolation level 0 (zero) allows transactions to read uncommitted data (dirty
reads). Since level 0 scans do not acquire locks, modifications by other
processes can cause rows being scanned to move. When this occurs, Adaptive
Server restarts the scan. To restart properly, the scan must use the same key
used to find the modified row, and start again from the next key in the index;
that is, the scan must be on a unique index. Error 313 occurs when a unique
index does not exist during a dirty read scan.
Action

To resolve the problem, create a unique index using one of these methods:

 * Drop your existing index. Create an index with the unique option or use
alter table add constraint with the unique option.
 * Create a new index using create index with the unique option or use alter
table add constraint with the unique option.
 * Add an IDENTITY column using alter table and the identity option.