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

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

Hi Tom,

>   I think we need to figure out the details of what we are allowed to do 
> and what we are not allowed to do and then figure out where the test 
> framework causes issues with these assumptions.

I will try contacting the Symfoware developers tomorrow and see if they 
can answer per category you mentioned what the behavior is.
They've been busy and so far I haven't been able to get through to them.

> - Andrei Ilitchev send a link about temp tables in Symfoware that 
> contained some keywords like: "LOCAL TEMPORARY".  It is hard for us to 
> understand alot of the docs we find for symfoware in the internet since 
> they seem to be in Japanese. Is that keyword a valid one. It seems 

No, it's not.

> - Is it the first access to a temporary table that fails, or does the 
> first one succeed and a later one fails?

When I ran the test set it is the first access that failed.

> - TableSpace: Do all tables need a tablespace, or just temporary 
> tables?  Are you also getting an issue with our normal table creation?

All tables need a tablespace, but this setting can be omitted for normal 
tables. For temporary tables it is compulsory.

I don't see issues with normal table creation.

> - You mentioned earlier you tried to experiment with the connection pool 
> settings.  Remind me what your results are when you set the settings in 
> the test persistence xmls to have a MIN and MAX of 1.

I was still waiting for more information on how to change the settings. 
I had tried changing the hard-coded defaults in ConnectionPool, but did 
not notice any effect.
I just found various persistence.xml files in resource/, I'll try to 
find out what tests use which and run the tests with min/max 1 tomorrow.

Today I changed my test application to do the following:
1. create two connections, use one to create a table, close the connection.
2. use the other connection for DML (in a transaction, commit at the 
end), don't close the connection.
3. create a new connection to drop the table. This failed (table is 
exclusively locked). If I close the connection used for DML in 2. it 
does work.

Until we can find out what Symfoware's rules are, if there is an easy 
way to make a session close a connection and create a new one, I could 
try that to at least run all the tests to check my platform class.
I could invoke that method where I currently begin and commit 
transactions (in SchemaManager#create/dropObject).

Thanks,
Dies


> Dies Koper wrote:
> > Hi Tom,
> >
> >  >   I am glad you are starting to get some success.
> >
> > I might have reached the limit of this success already.
> > I can do a full run again (no hangs), with a success rate of 57.90% 
> > (highest so far!), 56 failures and 495 errors.
> >
> > The main errors/failures I see are due to:
> > - table locked issue (still)
> > - temporary table issue
> >
> > I'm not sure why, but I see a certain table (for example CMP3_DEPT) 
> > being dropped/created and successfully used, then it tries to drop and 
> > recreate it, but the drop failed because the table's locked by another 
> > user. This exception then prevents other tables from being created, 
> > leading to other failures (like table not found).
> >
> >  From the log I can see the create and drop statements were executed 
> > in transactions. Even the DML to it seems to all use transactions.
> > I don't know if it means anything to you, but the logged numeric id of 
> > the connection when it failed was the same as when the table was 
> > successfully created earlier, but the id of the connection used for 
> > the DML was different.
> >
> > I'd like to try the other idea you had with a static session. Could 
> > you give more information?
> > I suppose I can swap it in in the locations where I now begin/commit 
> > the transactions?
> > Can this static session use (unpooled) connections that can be closed 
> > at "commit" time?
> >
> > > Did you take a look at any of the other calls to 
> > > shouldWriteToDatabase()?  Will they require transactions as well?
> >
> > Yes I did.
> > They don't require transactions for Symfoware because they relate to 
> > creating/dropping constraints using ALTER TABLE (a syntax Symfoware 
> > does not support) and to altering sequences (again, not supported by 
> > Symfoware).
> >
> > > > With global temporary tables implemented, creation of the table goes 
> > > > fine, but the following INSERT fails because the table is "locked". 
> > > > I'm not sure if I can (or should) put this table's create statement 
> > > > in a transaction, I suppose this whole query could already be 
> > > > running in a transaction.
> > >
> > > I am surprised that Delete All and Update All queries are not running 
> > > in transactions already.  Maybe we should try to isolate a sequence of 
> >
> > Note that a "Delete All" consists of the following statements (at 
> > least in the tests that I saw failing):
> >
> > 1. creation of the global table
> > 2. insert some rows
> > 3. select some rows
> > 4. drop the global table (or delete * from the table, depending on 
> > whether local temporary tables are used).
> >
> > All of this might be in one transaction, but the issue with Symfoware 
> > was that DDL and DML can't be in the same transaction, so 1. needs to 
> > run in a transaction and commit, then 2. and 3. in a new transaction, 
> > then 4. (even putting just 1. and 4. in transactions does not seem to 
> > help as 2. and 3. lock the table)
> > These statements are created and added to a Vector, and I'm not sure 
> > where and how these statements are executed (so I could add the 
> > demarcations between them). I am hoping Andrei will have a good 
> > suggestion. Or a better solution.
> >
> > > tests that cause the failure... (i.e. Does the first Delete all or 
> > > Update All fail?  Or, is the temporary table already created by 
> > > another Delete all or Update all?  Do we see the initial create 
> > > demarked by transactions?  Is there an error in the initial create 
> > > that causes issues?)
> >
> > The temporary table seems to have been created successfully. Here is 
> > the relevant part of the log:
> >
> >     [junit] [EL Finer]: 
> > ServerSession(14864562)--Thread(Thread[main,5,main])--client acquired
> >     [junit] [EL Finest]: 
> > UnitOfWork(31975400)--Thread(Thread[main,5,main])--Execute query 
> > DeleteAllQuery(referenceClass=Project sql="DELETE FROM TL_CMP3_PROJECT")
> >     [junit] [EL Finer]: 
> > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--begin 
> > transaction
> >     [junit] [EL Fine]: 
> > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--CREATE 
> > GLOBAL TEMPORARY TABLE TL_CMP3_PROJECT (PROJ_ID INTEGER NOT NULL, 
> > PROJ_TYPE VARCHAR(255), DESCRIP VARCHAR(255), PROJ_NAME VARCHAR(255), 
> > VERSION INTEGER, LEADER_ID INTEGER, PRIMARY KEY (PROJ_ID)) ON TESTDB01 10
> >     [junit] [EL Fine]: 
> > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--INSERT 
> > INTO TL_CMP3_PROJECT (PROJ_ID) SELECT PROJ_ID FROM CMP3_PROJECT WHERE 
> > (PROJ_NAME = testUpdateAllProjects)
> >     [junit] [EL Fine]: 
> > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--DELETE 
> > FROM TL_CMP3_PROJECT
> >     [junit] [EL Warning]: 
> > UnitOfWork(31975400)--Thread(Thread[main,5,main])--Local Exception Stack:
> >     [junit] Exception [EclipseLink-4002] (Eclipse Persistence Services 
> > - 2.0.0.qualifier): org.eclipse.persistence.exceptions.DatabaseException
> >     [junit] Internal Exception: java.sql.SQLException: [SymfoWARE ODBC 
> > Driver][SymfoWARE Server] JYP2091E Table "TL_CMP3_PROJECT" of schema 
> > "DEVELOP" being used exclusively by another user.
> >     [junit] Error Code: -2091
> >     [junit] Call: INSERT INTO TL_CMP3_PROJECT (PROJ_ID) SELECT PROJ_ID 
> > FROM CMP3_PROJECT WHERE (PROJ_NAME = 'testUpdateAllProjects')
> >     [junit] Query: DeleteAllQuery(referenceClass=Project sql="DELETE 
> > FROM TL_CMP3_PROJECT")
> >     [junit]     at 
> > org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) 
> >
> >
> > > > Another issue with Symfoware's temporary tables is that the table 
> > > > space name for the table must be specified at creation time. It does 
> > > > not default to the table space that creation of normal tables 
> > > > default to. So we'd need a way for the user to define this table space.
> > >
> > > There is a table passed into the writeCreateTempTableSql() method.  
> > > Can we use the table space from that table?
> >
> > I would like it to use that table's table space, but I don't know how 
> > to find out dynamically what table space that is using. (I am 
> > currently hard-coding it for now)
> > Symfoware V10 (coming out soon) has a new "DEFAULT TABLESPACE" option 
> > that I can specify. I'm not sure how it works, but there is also a new 
> > CREATE DEFAULT TABLESPACE DDL statement, that I suppose it works with.
> > So I could specify that and inform the user to prepare a default 
> > tablespace, but in that case I need to explain in the Wiki which kind 
> > of JPA functions require it.
> >
> > Also, except for the table space I need to specify the number of 
> > concurrent users that will use it. Is my understanding correct that 
> > these tables are used within a transaction, so they will never be 
> > accessed by multiple users?
> >
> > > > In what cases are  updateAll/deleteAll required exactly? Are they 
> > > > related to JPA 2.0 functions? Are they required for JPA 1.0 
> > > > functionality too?
> > >
> > > UpdateAll and DeleteAll are both JPA 1.0 features and covered in the 
> > > JPA 1.0 TCK.
> >
> > The spec does not use this terminology. Is it equivalent to an UPDATE 
> > or DELETE statement with no WHERE clause? Or an UPDATE/DELETE 
> > statement for an entity which has a particular relationship with other 
> > entities?
> >
> > > > As I see no clean solution I wonder what the impact is of making 
> > > > this a limitation for this platform for now.
> > >
> > > Have you looked into Andrei's suggestion of Local temporary tables.
> >
> > Yes, unfortunately it did not help.
> >
> > >  From an EclipseLink point of view, that is a possibility.  It is 
> > > just a matter of figuring out what that means to you from a TCK point 
> > > of view?
> >
> > No, more of a JPA1.0 /TLE point of view.
> > I've based this Symfoware platform class on the Symfoware platform my 
> > team developed for TLE. I cannot remember encountering this issue at 
> > the time. If there is a chance the same problem would occur on TLE 
> > (using JPA 1.0 functionality, not TLE/Toplink's additional stuff), I'd 
> > like to know the impact to Symfoware users of that implementation.
> >
> > But also from a JPA 1.0/2.0 (=TCK?) point of view: to what extend is 
> > the platform still usable without this function? If it only affects a 
> > certain type of update/delete queries, and the range of functions that 
> > is affected can be determined and clearly explained (from a user's 
> > point of view), then I'd like to consider giving up on this and focus 
> > on other functions.
> >
> > Unless it is a condition to ever get the Symfoware platform graduated 
> > from incubation (is it?), I'm not thinking of TCK certification at 
> > this time.
> >
> > > > If I no longer run into locking issues, what will the final solution 
> > > > look like? Can I add a method createObjectsInTransactions() to the 
> > > > DB platforms, defaulting to false of course, true for Symfoware, 
> > > > that begins/commits/rolls back transactions in the locations I 
> > > > described above?
> > >
> > > That is likely the best way to address this.  We could tweak the 
> > > method name a bit, and of course, check the other calls for 
> > > shouldWriteToDatabase() to see if they needed to be transactional as 
> > > well.
> >
> > Ah, you don't like the method name. ;)
> >
> > Well lucky for us the final solution will be different.
> > I won't propose createObjectsInStaticSession(). ;-p
> >
> > Thanks,
> > Dies
> >
> >
> > > > Dies Koper wrote:
> > > > > Hi Tom,
> > > > >
> > > > > I am trying to enclose DDL calls with transactions. Inside 
> > > > > schemaManager.createSequences() there are mixed DDL and DML (select 
> > > > > and inserts on the sequence table/object), so just I'm moving the 
> > > > > transaction calls deeper into the call stack.
> > > > >
> > > > > The locking error I get now is from the SELECT statement on table 
> > > > > CMP3_ENTITYB_SEQ in the following call in SchemaManager#createObject.
> > > > >
> > > > > databaseObjectDefinition.createOnDatabase(getSession());
> > > > >
> > > > > I did put a getSession().beginTransaction(); before this call, so 
> > > > > I'm not sure yet what the problem is. I'll investigate a bit more 
> > > > > and let you know.
> > > > >
> > > > > Thanks,
> > > > > Dies
> > > > >
> > > > >
> > > > > > The idea is that we would add calls that begin and commit 
> > > > > > transactions around table creation calls.
> > > > > >
> > > > > > Lets see if it works before we design the final solution:
> > > > > >
> > > > > > - Find the 
> > > > > > org.eclipse.persistence.tools.schemaframework.TableCreator.replaceTables(session, 
> > > > > > schemaManager) method
> > > > > > - Add transactional boundaries
> > > > > >
> > > > > >     public void replac