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

[Tom Ware <tom.ware@xxxxxxxxxx>]

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

Dies Koper wrote:
> Hi Tom,
>
> I tried to get more clarification regarding the behaviour for global 
> temporary tables, and the use of DROP statements when using connection 
> pooling (by JDBC driver or application server) with the Symfoware 
> developer.
>
> The reply was that the global temporary tables too can't be dropped 
> until the session is closed.
>
> Also, a session is closed by closing the connection so when using 
> connection pooling where you have no direct control over when a 
> connection is closed it could happen that a table that was 
> created/accessed can't be dropped. They acknowledge this disadvantage 
> and therefore do not recommend the use of DDL operations from JDBC 
> applications.
>
> Regards,
> Dies
>
>
> Dies Koper wrote:
> > Hi Tom,
> >
> > I just received a reply from the Symfoware team.
> > It's not a detailed reply regarding the behaviours of each category in 
> > your e-mail, but it does seem to explain the last lock errors I saw.
> >
> > In my question to them I had described that I saw lock errors in step 
> > 4 of the following sequence:
> >
> > 1. Obtain a connection
> > 2. (re)create tables (DROP TABLE, CREATE TABLE)
> > 3. start transaction (con.setAutoCommit())
> >      access tables (INSERT, UPDATE, DELETE)
> >    commit transaction (con.commit())
> > 4. recreate tables created at 2. (DROP TABLE, CREATE TABLE)
> > 5. access tables as in 3.
> >
> > My translation of his reply is that Symfoware has a concept of a 
> > "session", and resources accessed from within a session cannot be 
> > dropped, by design, until the session has been closed. He says that 
> > could be the reason an error occurred at step 4.
> > He adds that there is no effect with the other categories of 
> > statements (referring to the four categories in your e-mail below).
> >
> > Does this make sense to you?
> >
> > Regarding the hang I saw in getConnection, he asked more information, 
> > so that issue might be unrelated.
> >
> > Thanks,
> > Dies
> >
> >
> > Tom Ware wrote:
> > > Hi Dies,
> > >
> > >   Sorry not to have gotten back to you at the end of the week last 
> > > week - last week things were very busy around here.
> > >
> > >   I am hesitant to go down the "static session" path until we have 
> > > fully explored the transaction route.  Building a "static session" 
> > > into our schema generator in a reasonable way will be fairly involved.
> > >
> > >   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.
> > >
> > >   As I understand the information you have provided in the past, 
> > > there are several categories of statements that have different rules 
> > > in Symfoware:
> > >
> > > 1. CREATE TABLE
> > > 2. DROP TABLE
> > > 3. DML (INSERT, UPDATE, DELETE)
> > > 4. Temporary tables
> > >
> > >   For each of these there are several possible behaviors
> > >
> > > 1. No Effect on permissions for later statements
> > > 2. Transactional - If wrapped in a transaction, does not affect 
> > > permissions after transaction is committed.  Can exist in the same 
> > > transaction as other types of statments.
> > > 3. Transactional (exclusive) - same as above, but needs its own 
> > > transaction
> > > 4. Connection-Local - Later statements that affect the same table 
> > > must be in the same connection
> > > 5. Connection must be closed before later statement can affect the 
> > > same table
> > >
> > >   A definitive answer about what constraints we are trying to meet 
> > > for each of these scenarios will help us.  Is there a good way to get 
> > > that.  When we have a definitive answer, we can so some analysis of 
> > > the test issues you are seeing to see if there are easy ways we can 
> > > make the test framework behave in the way Symfoware expects.
> > >
> > >   A few other comments:
> > >
> > > - 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 counter-intuitive to allow something called a temporary table 
> > > and then not be allowed to create it and insert into in the same 
> > > transaction.
> > > - Is it the first access to a temporary table that fails, or does the 
> > > first one succeed and a later one fails?
> > >
> > > - TableSpace: Do all tables need a tablespace, or just temporary 
> > > tables?  Are you also getting an issue with our 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.
> > >
> > > - Update and Delete all statements are defined in the JPA 1.0 
> > > specification in the JPQL.  e.g. Delete from Employee e where 
> > > e.salary > 100000
> > > - The types of statements that require Temporary tables are fairly 
> > > limited. (Andrei can probably give a better comment, but I think they 
> > > are the same ones that we rewrite to use a subquery and that were 
> > > causing issues in your test because the subquery refers to the same 
> > > table as the outer query.)  If we log this issue as a limitation, I 
> > > do not think this would get in the way of certification.
> > >
> > > -Tom
> > >
> > > 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
>
> _______________________________________________
> eclipselink-dev mailing list
> eclipselink-dev@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-dev