Hi Markus,
I have some more information for you about the issue you are seeing
with Delete queries. I just spoke to the person who implemented the
feature and he has dealt with some similar issues when working with MySQL.
TopLink is capable of using two strategies for these JPQL delete
queries. The first, the one you are seeing makes use of the subselect
you are having issues with. The second, the one that is used for MySQL
adds the use of temporary tables to the first strategy. It is possible
you will have to use the second strategy.
The MySQL4Platform contains an implementation of this strategy. To
see how it works, take a look at the method:
supportsGlobalTempTables
getCreateTempTableSqlPrefix
shouldAlwaysUseTempStorageForModifyAll
getCreateTempTableSqlPrefix
writeDeleteFromTargetTableUsingTempTableSql
writeUpdateOriginalFromTempTableSql
-Tom
Tom Ware wrote:
>Hi Markus,
>
> Could you please send me the changes you made to solve the issue with
>the table names that were too long for MaxDB. I will incorporate those
>changes the next time I do a check-in.
>
> Additional comments inline:
>
>Markus KARG wrote:
>
>
>
>>Tom,
>>
>>I have applied all the tips I received and now am stuck with some
>>problems that I hope you can clarify. Since you might need some insight
>>into the stack traces, I have attached the testresults.txt file.
>>
>>Here are my newly detected problems. I hope you might know a solution
>>for. :-)
>>
>>(1) As you know, my laptop is idle most of the time when running the
>>tests. As you told I have enabled a more detailed level of logging.
>>Would be great if you could have a look at the attached testresults.txt
>>file to find out why my machine's power is not used. I was a bit scared
>>by the fact that indeed the test is suspended. I expected lots of lines
>>pass before my eyes, but indeed two times it stops for long time!
>>
>>I noticed that it spent long time with doing nothing (no CPU / HD
>>activity, no RAM usage) after printing these lines:
>>
>> [junit] [TopLink Finer]:
>>ServerSession(9047389)--Thread(Thread[main,5,main])--client acquired
>> [junit] [TopLink Finer]:
>>ClientSession(15607307)--Connection(21048949)--Thread(Thread[main,5,main])--begin
>>transaction
>> [junit] [TopLink Fine]:
>>ClientSession(15607307)--Connection(21048949)--Thread(Thread[main,5,main])--DELETE
>>
>>
>>FROM CMP3_EMP_PROJ WHERE EXISTS(SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0,
>
>
>>CMP3_SALARY t1 WHERE ((t0.F_NAME = ?) AND (t1.EMP_ID = t0.EMP_ID)) AND
>>t0.EMP_ID = CMP3_EMP_PROJ.EMPLS_EMP_ID)
>> [junit] bind => [testRemoveFlushPersistContains]
>>
>>
>>
>>
>There are a number of places in the tests, when a query of this type
>runs. (A DELETE with this subquery). Are you just seeing it hang once,
>when the bind paramter is "testRemoveFlushPersistContains", or does it
>hang every time this type of SQL runs?
>
>I am wondering if this issue is related to the "Wayne Problem" referred
>to below. I see the SQL: t0.EMP_ID = CMP3_EMP_PROJ.EMPLS_EMP_ID, which
>I believe is an incarnation of this issue.
>
>
>
>>Also the system was idle a long time after writing that lines:
>>
>> [junit] [TopLink Finer]:
>>ServerSession(19171705)--Thread(Thread[main,5,main])--client acquired
>> [junit] [TopLink Finer]:
>>ClientSession(10967412)--Thread(Thread[main,5,main])--acquire unit of
>>work: 19697685
>> [junit] [TopLink Finer]:
>>ClientSession(10967412)--Connection(8662426)--Thread(Thread[main,5,main])--begin
>>transaction
>> [junit] [TopLink Fine]:
>>ClientSession(10967412)--Connection(8662426)--Thread(Thread[main,5,main])--DELETE
>>
>>
>>FROM CMP3_BOAT
>
>
>> [junit] [TopLink Fine]:
>>ClientSession(10967412)--Connection(8662426)--Thread(Thread[main,5,main])--DELETE
>>
>>
>>FROM CMP3_BICYCLE
>
>
>> [junit] [TopLink Fine]:
>>ClientSession(10967412)--Connection(8662426)--Thread(Thread[main,5,main])--DELETE
>>
>>
>>FROM CMP3_NONFUEL_VEH
>
>
>> [junit] [TopLink Fine]:
>>ClientSession(10967412)--Connection(8662426)--Thread(Thread[main,5,main])--DELETE
>>
>>
>>FROM CMP3_BUS
>
>
>> [junit] [TopLink Fine]:
>>ClientSession(10967412)--Connection(8662426)--Thread(Thread[main,5,main])--DELETE
>>
>>
>>FROM CMP3_SPORTS_CAR
>
>
>>I dropped the database, restarted my laptop, setup a new (clean)
>>database and run the test once more (which goes 46 Minutes, by the way,
>>about 40 of them doing nothing). It's reproducible. Always these two
>>steps are hanging.
>>
>>
>>I have no clue why it hangs, actually, and why it "somehow magically"
>>continues some minutes later. I thought it is some kind of database
>>deadlock, but in fact, it should be exactly the same time in both cases.
>>But it seems, the second hang is much, much longer than the first one.
>>
>>
>>
>>
>I am afraid I do not have a good idea why this is hanging on MaxDB
>(aside from my comment above). On my local Oracle run there is no
>perceptable slow down around those lines. I'll try to do a Derby run
>later to see if I see anything. Is there a chance that either MaxDB or
>the JDBC driver is having trouble with running these lines?
>
>
>
>>(2) Unknown SQL objects
>>
>>[-4005] (at 116): Unknown column name:PROJECTS_PROJ_ID
>>[-4020] (at 8): Unknown sequence name:PROJECT_SEQ
>>[-4020] (at 8): Unknown sequence name:ADDRESS_SEQ
>>[-4020] (at 8): Unknown sequence name:BUYER_SEQ
>>
>>As you know, I had to rename some tables and this might be a follow-up,
>>but I am not sure. So it would be great if you could commit the needed
>>changes to cvs HEAD and let me know when done, so I will remove my own
>>(maybe buggy) changes and use the cvs HEAD then.
>>
>>
>>
>>
>Just send your changes and I'll do my best to get them in soon.
>
>
>
>>On the other hand, some of those names I never touched (e. g.
>>"PROJECT_SEQ", "ADDRESS_SEQ" and "BUYER_SEQ"), so this cannot be the
>>sole cause of the problems. I think that there are more bugs in the
>>tests, especially the "Unkown sequence name" seems to point out missing
>>CREATE SEQUENCE commands. In fact I did not find code that creates those
>>sequences in the test cases (maybe I missed it). What do you think?
>>
>>
>>
>>
>These sequence tables should be created at test startup by the ddl
>generation feature. The persistence.xml property: <property
>name="toplink.ddl-generation" value="drop-and-create-tables"/> enables this.
>
>I see the following error while the ddl generation seems to be
>executing. Perhaps this error is getting in the way of the successful
>completion of the DDL generation:
>
>[TopLink Warning]:
>ServerSession(24934792)--Thread(Thread[main,5,main])--Exception
>[TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build
>ESSENTIALS_BUILD_NUMBER)):
>oracle.toplink.essentials.exceptions.DatabaseException
>Internal Exception: com.sap.dbtech.jdbc.exceptions.DatabaseException:
>[-3002] (at 219): Invalid datatypeError Code: -3002
>Call:CREATE TABLE CMP3_WRAPPER_TYPES (WT_ID INTEGER NOT NULL,
>DOUBLE_DATA DOUBLE PRECISION, BIGDECIMAL_DATA DOUBLE PRECISION,
>BOOLEAN_DATA BOOLEAN, BIGINTEGER_DATA DOUBLE PRECISION, LONG_DATA
>FIXED(20), CHARACTER_DATA CHAR UNICODE, STRING_DATA VARCHAR(255),
>INTEGER_DATA INTEGER, FLOAT_DATA FLOAT, SHORT_DATA BOOLEAN, BYTE_DATA
>BINARY(3), PRIMARY KEY (WT_ID))
>Query:DataModifyQuery()
>
>If we can narrow the issue down to this error, perhaps we can add a way
>to get more explicit errors is the DDL generation part of the test setup.
>
>
>
>>(3) "The Wayne Problem"
>>
>>As I wrote before, Wayne's solution was correct. Would be great if you
>>could commit the fix for the "Waybe Problem" into the cvs HEAD, since I
>>do not know how to workaround it. Since I got a lot of those [-4005] (at
>>116): Unknown column name:PROJECTS_PROJ_ID messages as you can see in
>>the attached file, this is really a blocker -- I have to skip lots of
>>those errors in the testresults.txt file to find my actual test failures.
>>
>>
>>
>>
>
>I have entered the following Defect in the GlassFish Issue tracker:
>
>https://glassfish.dev.java.net/issues/show_bug.cgi?id=1311
>
>I have marked in at a priority level that should put it close to the top
>of the stack of issues to be fixed.
>
>
>
>>(4) Don't use comments but syntax
>>
>>As Martin FOWLER pointed out in his great reference manual on
>>refactoring, whenever possible syntax constraints should be used instead
>>of constraints in comments. In the case of DatabasePlatform.java this
>>should be applied:
>>
>>/**
>>* ...
>>* Don't override this method.
>>* ...
>>*/
>>public boolean supportsTempTables() {...}
>>
>>should be changed to
>>
>>public final boolean supportsTempTable() {... }
>>
>>to enforce the constraint of not overriding the method (either it might
>>be overridden, or it shall not be overriden -- always be concise in your
>>code). Certainly this not only applies to this single place but to all
>>methods commented with "Don't override".
>>
>>
>>
>>
>Please feel free to enter an ENHANCEMENT in the GlassFish Issue tracker.
>
>-Tom
>
>
>
>>I could imagine that it is more clear to see where I need to spent more
>>time once I get tests from you and Marina that already contain fixes for
>>"The Wayne Problem" and for "The inactive transaction problem" (Marina
>>knows about). Maybe some of my problems are follow-ups of those and will
>>be gone once you and Marina contributed your fixes.
>>
>>
>>Thanks
>>Markus
>>
>>
>>
>>
>>
>
>
>
>