Re: [eclipselink-dev] questions while running JPA JUnit tests

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

Hi Tom,

> I am currently running tests on your patch. Let me know when you have
> the updates for pagination.

I have attached an updated patch to the bug.
It's the same as the previous patch, without the changes to 
DatabaseCall.java and DatabaseAccessor.java.

> --
> For your issue with Sequence, remind me what changes you have made? The
> initial places I would suggest to look for references are:
>
> org.eclipse.persistence.sequencing.TableSequence
> org.eclipse.persistence.internal.sessions.factories.model.sequencing.SequencingConfig
>
> org.eclipse.persistence.internal.sessions.factories.ObjectPersistenceRuntimeXMLProject

I have made changes to all classes that call one of the constructors of 
TableSequence that would default to its defaultTableName value.

As that did not help, I started temporarily renaming all occurrences of 
"SEQUENCE" with something reflecting the class name in the hope to find 
the culprit(s). No luck so far.
I have attached a diff file to the bug with the places I have changed.

> --
> For the locking issue, if your only issues in the Core SRG is in the
> AutoTableGeneratorBasicTestModel, I think we can consider those tests to
> be passing, lets not worry about those for now.

It is also the following Core tests:
- ComplexMultipleUnitOfWorkTest (RESPONS table)
- UnitOfWorkResumeOnFailureTest (RESPONS table)
- UnitOfWorkComplexRefreshTest (ADDRESS table)
- MultipleUnitOfWorkTest (EMPLOYEE table)
- UnitOfWorkResumeOnFailureTest (RESPONS table)
- ViolateObjectSpaceTest (EMPLOYEE table)
- NoValidationWithInitIdentityMaps (EMPLOYEE table)

All of these tables are also used in AutoTableGeneratorBasicTestModel.

> In the JPA tests, I think we will have to identify an initial set of
> tests as our target and then do some work on the test suites to ensure
> that they do not depend as heavily on DDL functions.
>
> Lets start by looking at
> org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite
>
>
> How is the behavior on that test suite? Do you see failures? Where do
> they start?

When I run the full test set, this suite fails in setup because table 
CMP3_DEPT is locked.
If I scroll up in the JUnit report I also find 
CallbackEventJUnitTestSuite.testSetup failing because of a lock on the 
same named table.
Before that it is CacheImplJUnitTest.testSetup.
Before that it is NamedNativeQueryJUnitTest.testSetup.
Before NamedNativeQueryJUnitTest there is only one failed test, 
testTransitionToDeferedFailure, which failed because it tried to update 
a sequence table with name "SEQUENCE".

When I run the suite from the test browser, I get:

TEST MODEL NAME: (JUnit test): EntityManagerJUnitTestSuite
###ERRORS###

Errors: (failures): 4
Fatal Errors: (errors): 45
Passed: 140
Total Tests: 189

The first 20 or so exception messages indicate the use of the same table 
name in the from clause of the main query as the subquery (the update 
all query limitation), but the locked issue starts at 
testCollectionAddNewObjectUpdate (table CMP3_EMP_PROJ).

> I think that if we can make ensure that all tests leave tables in the
> same state they started, we can make the majority of tests pass.

Are you suggesting we look for a solution where we can disable the 
drop/create processing on Symfoware?

I suppose I can easily try that by adding some flag to the models' 
replaceTables methods that will make sure it only tries to recreate the 
tables the first time.

> --
>
> On a side note, I have started a discussion about how we can lower the
> barrier required to include new database platforms in the shipped
> version of EclipseLink with some of the other commiters. Watch the dev
> mailing list for an initial proposal in the next week or so.

Thanks, looking forward to it!

Regards,
Dies


> Dies Koper wrote:
> > Wow!! First time a live response! :))
> > You must be at the office late :)
> >
> > Thanks. Yes, if you have some suggestions about the other two issues
> > that should really help me forward.
> >
> > Cheers,
> > Dies
> >
> >
> > On 16/12/2009 08:27, Tom Ware wrote:
> > > Hi Dies,
> > >
> > > In general, the other changes look good. I am hoping to run some testing
> > > tomorrow and also to take a look at some of the other questions from
> > > your email.
> > >
> > > -Tom
> > >
> > > Dies Koper wrote:
> > > > Hi James, Tom,
> > > >
> > > > Yes, I was wondering about that.
> > > > Okay, I'll drop the custom pagination support.
> > > >
> > > > Tom, have you been able to review/commit the other changes in the
> > > > patch?
> > > >
> > > > Thanks!
> > > > Dies
> > > >
> > > >
> > > > On 16/12/2009 02:42, James Sutherland wrote:
> > > > > Just an FYI Dies,
> > > > >
> > > > > I'm not sure the behavior on Symfoware, but on most databases the
> > > > > benefit in putting limit/offset in the SQL is for the offset, not the
> > > > > limit.
> > > > > For the limit EclipseLink uses the JDBC maxRows API by default, which
> > > > > seems to have equivalent or better performance on most databases I
> > > > > have tested.
> > > > > If Symfoware does not support the offset, there may be little point
> > > > > adding the custom pagination support.
> > > > >
> > > > >
> > > > > ----- Original Message -----
> > > > > From: tom.ware@xxxxxxxxxx
> > > > > To: eclipselink-dev@xxxxxxxxxxx
> > > > > Sent: Tuesday, December 15, 2009 10:32:18 AM GMT -05:00 US/Canada
> > > > > Eastern
> > > > > Subject: Re: [eclipselink-dev] questions while running JPA JUnit tests
> > > > >
> > > > > Hi Dies,
> > > > >
> > > > > To check this in, the setting will have to be completely split. i.e.
> > > > > remove
> > > > > the methods that affect both settings
> > > > > (setIgnoreFirstResultsMaxRowsSettings and
> > > > > shouldIgnoreFirstResultsMaxRowsSettings). Instead, have separate
> > > > > getters and
> > > > > setters for each setting, and make the appropriate changes to classes
> > > > > depending
> > > > > on those methods.
> > > > >
> > > > > -Tom
> > > > >
> > > > > Dies Koper wrote:
> > > > > > Hi Tom,
> > > > > >
> > > > > > > I have started looking at your patch. I have a quick question:
> > > > > > >
> > > > > > > - In DatabaseAccessor, what is the goal of splitting the
> > > > > > > ignoreFirstRowSettings and the ignoreMaxResultsSettings
> > > > > >
> > > > > > Symfoware supports LIMIT, but has no equivalent for OFFSET.
> > > > > >
> > > > > > Currently, the flag setIgnoreFirstRowMaxResultsSettings toggles
> > > > > > both, so
> > > > > > either both JDBC absolute and setMaxRows are called, or both are not.
> > > > > >
> > > > > > To support pagination using SQL I override printSQLSelectStatement
> > > > > > like
> > > > > > the other platforms do, but as I only have a LIMIT in the query I
> > > > > > need
> > > > > > EclipseLink to only call the JDBC absolute method when FirstRow is
> > > > > > set.
> > > > > >
> > > > > > So my implementation method looks like:
> > > > > >
> > > > > > public void printSQLSelectStatement(DatabaseCall call,
> > > > > > ExpressionSQLPrinter printer, SQLSelectStatement statement) {
> > > > > > int max = 0;
> > > > > > int firstRow = 0;
> > > > > >
> > > > > > statement.setMaximumAliasLength(getMaxFieldNameSize());
> > > > > > if (statement.getQuery() != null) {
> > > > > > max = statement.getQuery().getMaxRows();
> > > > > > firstRow = statement.getQuery().getFirstResult();
> > > > > > }
> > > > > >
> > > > > > // only MaxRows can be worked into the statement
> > > > > > if (max> 0&& this.shouldUseRownumFiltering()) {
> > > > > > statement.setUseUniqueFieldAliases(true);
> > > > > > call.setFields(statement.printSQL(printer));
> > > > > > printer.printString(" WITH OPTION LIMIT (");
> > > > > > printer.printParameter(DatabaseCall.MAXROW_FIELD);
> > > > > > printer.printString(")");
> > > > > >
> > > > > > call.setIgnoreMaxResultsSettings(true);
> > > > > > } else {
> > > > > > // use JDBC absolute and setMaxRows methods for pagination
> > > > > > super.printSQLSelectStatement(call, printer, statement);
> > > > > > }
> > > > > > }
> > > > > >
> > > > > > Thanks,
> > > > > > Dies
> > > > > >
> > > > > >
> > > > > > > Dies Koper wrote:
> > > > > > > > Hi Tom, all
> > > > > > > >
> > > > > > > > Congrats on the 2.0 release!
> > > > > > > >
> > > > > > > > I've been continuing running the test sets on the Symfoware
> > > > > > > > platform.
> > > > > > > > May I ask you for you help with the following three issues?
> > > > > > > >
> > > > > > > > 1. Review of patch
> > > > > > > >
> > > > > > > > I've added a patch with changes required for a number of tests to
> > > > > > > > run. Could you review them, and if okay, commit them?
> > > > > > > > https://bugs.eclipse.org/bugs/show_bug.cgi?id=288715
> > > > > > > >
> > > > > > > > 2. Where is table name "SEQUENCE" defined?
> > > > > > > >
> > > > > > > > I tried renaming all occurrences of the word "SEQUENCE" in the
> > > > > > > > code I
> > > > > > > > could find but still some tests try to create a sequence table with
> > > > > > > > table name SEQUENCE.
> > > > > > > > It starts with the 8th test in the JPA test set:
> > > > > > > > testTransitionToDeferedFailure
> > > > > > > >
> > > > > > > > which tries a UPDATE SEQUENCE (...).
> > > > > > > >
> > > > > > > > In the log I see even more:
> > > > > > > >
> > > > > > > > [junit] [EL Config]:
> > > > > > > > ServerSession(1006920425)--Thread(Thread[main,5,main])--The table
> > > > > > > > generator name defined within [class
> > > > > > > > org.eclipse.persistence.testing.models.jpa.inherited.Alpine] is
> > > > > > > > being
> > > > > > > > defaulted to: SEQUENCE.
> > > > > > > >
> > > > > > > > and
> > > > > > > >
> > > > > > > > [junit] [EL Fine]:
> > > > > > > > ServerSession(1006920425)--Connection(753758527)--Thread(Thread[main,5,main])--CREATE
> > > > > > > >
> > > > > > > >
> > > > > > > > TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT
> > > > > > > > DECIMAL(18),
> > > > > > > > PRIMARY KEY (SEQ_NAME))
> > > > > > > > [junit] java.sql.SQLException: [SymfoWARE ODBC Driver][SymfoWARE
> > > > > > > > [...]
> > > > > > > > [junit] at
> > > > > > > > org.eclipse.persistence.testing.tests.jpa.advanced.concurrency.LifecycleJUnitTest.testSetup(LifecycleJUnitTest.java:76)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > I believe these might be from the CONCURRENCYA, B and C tables, or
> > > > > > > > ConcurrencyA, B and C entities. Each of them has
> > > > > > > > fieldID.setIsIdentity(true) resp. @GeneratedValue specified but the
> > > > > > > > details of the sequence generators are omitted.
> > > > > > > >
> > > > > > > > I though it would default to what the platform's
> > > > > > > > createPlatformDefaultSequence() method returns but apparently not.
> > > > > > > >
> > > > > > > > Many tests fail because of this issue.
> > > > > > > >
> > > > > > > > 3. Table locks during DROP TABLE
> > > > > > > >
> > > > > > > > In the SymfowarePlatform wiki page you suggested that we should
> > > > > > > > start
> > > > > > > > by trying to get the Core SRG running first. Most are passing now.
> > > > > > > >
> > > > > > > > Only a few (the ReadAllTest tests, which rely on
> > > > > > > > AutoTableGeneratorBasicTestModel.java) failed I think because of
> > > > > > > > the
> > > > > > > > table lock issue.
> > > > > > > >
> > > > > > > > The error message is:
> > > > > > > >
> > > > > > > > Exception Description: 24 objects were read from the database, but
> > > > > > > > originially there were, 12.
> > > > > > > >
> > > > > > > > When I run it by itself through the test browser it passes without
> > > > > > > > problems.
> > > > > > > >
> > > > > > > > Some messages in the finest log let me believe this is because a
> > > > > > > > problem
> > > > > > > > occurred while recreating the tables: they should have been
> > > > > > > > dropped and
> > > > > > > > recreated, thereby clearing those old 12 object, but because of the
> > > > > > > > locking issue they were not.
> > > > > > > >
> > > > > > > > [junit] Internal Exception: java.sql.SQLException: [SymfoWARE ODBC
> > > > > > > > Driver][SymfoWARE Server] JYP3913E : Table "PHONE" being used
> > > > > > > > exclusively by another user.
> > > > > > > > [junit] Error Code: -3913
> > > > > > > > [junit] Call: drop table PHONE
> > > > > > > > [junit] Query: DataModifyQuery(sql="drop table PHONE")
> > > > > > > > [junit] at
> > > > > > > > org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > [..]
> > > > > > > > [junit] at
> > > > > > > > org.eclipse.persistence.testing.models.employee.relational.EmployeeSystem.dropTableConstraints(EmployeeSystem.java:86)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > [junit] at
> > > > > > > > org.eclipse.persistence.testing.tests.schemaframework.AutoTableGeneratorEmployeeSystem.createTables(AutoTableGeneratorEmployeeSystem.java:32)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > This table was created and accessed as follows:
> > > > > > > >
> > > > > > > > 1. drop (index) and table (outside transaction)
> > > > > > > > DROP TABLE PHONE
> > > > > > > >
> > > > > > > > 2. create table (and index) (in transaction), same connection
> > > > > > > > CREATE TABLE PHONE (EMP_ID NUMERIC(15) NOT NULL, TYPE VARCHAR(15)
> > > > > > > > NOT NULL, AREA_CODE VARCHAR(3), P_NUMBER VARCHAR(7), PRIMARY KEY
> > > > > > > > (EMP_ID, TYPE))
> > > > > > > >
> > > > > > > > 3. insert 30 rows (in single transaction), same connection
> > > > > > > > INSERT INTO PHONE (TYPE, AREA_CODE, P_NUMBER, EMP_ID) VALUES (?, ?,
> > > > > > > > ?, ?)
> > > > > > > > [junit] bind => [Home, 613, 5551234, 105]
> > > > > > > >
> > > > > > > > 4. drop (index) and table (outside transaction), different
> > > > > > > > connection
> > > > > > > > DROP TABLE PHONE
> > > > > > > > -> SQLException saying PHONE is locked.
> > > > > > > >
> > > > > > > > I can reproduce this in a simple JDBC application. If I close the
> > > > > > > > connection used in steps 1-3 before step 4 it works fine.
> > > > > > > >
> > > > > > > > I can see some session swapping is done in
> > > > > > > > AutoTableGeneratorBasicTestModel#addForcedRequiredSystems() so I
> > > > > > > > tried adding a reset() invocation at the start of its
> > > > > > > > addForcedRequiredSystems() method but that did not help.
> > > > > > > > How can I make it close the physical connection?
> > > > > > > > (You mentioned adding logic into TestSystem?)
> > > > > > > >
> > > > > > > > The lock issue occurs much more often in the JPA test set. The
> > > > > > > > first
> > > > > > > > failing test however is because of the reserved SEQUENCE keyword
> > > > > > > > used
> > > > > > > > as table name, so if you could help me in the right direction with
> > > > > > > > the above three issues I can try move forward with this test set
> > > > > > > > again.
> > > > > > > >
> > > > > > > > Thanks!
> > > > > > > > Dies
> > > > > > > >
> > > > > > > >
> > > > > > > > Tom Ware wrote:
> > > > > > > > > Hi Dies,
> > > > > > > > >
> > > > > > > > > FYI: I am working on integrating your initial patch. There are a
> > > > > > > > > couple of items I will not be integrating at the moment:
> > > > > > > > >
> > > > > > > > > 1. Specific references to SymfowarePlatform and
> > > > > > > > > isSymfowarePlatform(). These will be left until SymfowarePlatform
> > > > > > > > > becomes part of the full product
> > > > > > > > >
> > > > > > > > > 2. Changes to default SEQUENCE table name to "\"SEQUENCE\"" for
> > > > > > > > > our
> > > > > > > > > Sessions and project XML (in XMLSessionConfigProject,
> > > > > > > > > ObjectPersistenceRuntimeXMLProject and SequencingConfig). This is
> > > > > > > > > simply to big a backward compatibility risk since I cannot test
> > > > > > > > > these changes on every database platform that EclipseLink is
> > > > > > > > > run on
> > > > > > > > > by our customers. I am looking for some way of having this
> > > > > > > > > information stored on the DatabasePlatform, but at the moment,
> > > > > > > > > it is
> > > > > > > > > not looking good. Assuming I do not find a good solution,
> > > > > > > > > Symfoware
> > > > > > > > > users will simply have to explicitly set these items when using
> > > > > > > > > our
> > > > > > > > > proprietary configuration code (sessions.xml and
> > > > > > > > > deployment.xml) We
> > > > > > > > > will also have to address anywhere in the tests we choose to run
> > > > > > > > > where this is an issue.
> > > > > > > > >
> > > > > > > > > I am in the process of running testing on the other changes and
> > > > > > > > > I'll
> > > > > > > > > let you know when it gets checked in.
> > > > > > > > >
> > > > > > > > > -Tom
> > > > > > > > >
> > > > > > > > > Dies Koper wrote:
> > > > > > > > > > Hi Tom,
> > > > > > > > > >
> > > > > > > > > > I have added the outstanding issues with SQL keywords in
> > > > > > > > > > table/columns names, and maximum precision. The biggest open
> > > > > > > > > > issue
> > > > > > > > > > is of course the drop table restriction. I'm looking forward to
> > > > > > > > > > hearing what solution(s) you can come up with.
> > > > > > > > > >
> > > > > > > > > > Thanks!
> > > > > > > > > > Dies
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Tom Ware wrote:
> > > > > > > > > > > Hi Dies,
> > > > > > > > > > >
> > > > > > > > > > > I am moving some of the main points of this discussion to the
> > > > > > > > > > > wiki page so I can more easily keep track of where we are:
> > > > > > > > > > >
> > > > > > > > > > > http://wiki.eclipse.org/EclipseLink/Development/Incubator/Extensions/SymfowarePlatform#Open_Issues
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > I started by just adding some basics about the main issues that
> > > > > > > > > > > are getting in the way at the moment. I plan to add more detail
> > > > > > > > > > > next week. Please feel free to add other issues I have
> > > > > > > > > > > missed, or
> > > > > > > > > > > more detail.
> > > > > > > > > > >
> > > > > > > > > > > As for some of the other things you were wondering about:
> > > > > > > > > > >
> > > > > > > > > > > 1. Your initial patch (including sequencing fixes etc.). I hope
> > > > > > > > > > > to find some time to start integrating it next week
> > > > > > > > > > >
> > > > > > > > > > > 2. EclipseLink bug 286907 (not related to Symfoware platform)-
> > > > > > > > > > > this bug is in the queue and will be addressed in sequence with
> > > > > > > > > > > the other bugs. The fact that you have submitted a patch
> > > > > > > > > > > means it
> > > > > > > > > > > will potentially jump other bugs in the queue. At the moment
> > > > > > > > > > > I am
> > > > > > > > > > > going to focus on the Symfoware Platform issues with my free
> > > > > > > > > > > time,
> > > > > > > > > > > so I can't make any promises about when it will be included.
> > > > > > > > > > >
> > > > > > > > > > > 3. GlassFish bug 9179 (not related to Symfoware platform) - same
> > > > > > > > > > > thing as above, but additionally: we cannot legally make use of
> > > > > > > > > > > the submitted patch until it is attached to EclipseLink bug by
> > > > > > > > > > > someone with IP rights
> > > > > > > > > > >
> > > > > > > > > > > I'll get started looking for some solutions next week.
> > > > > > > > > > > -Tom