Hi all,
the issue was not with the database nor with the jdbc driver but with the
implementation of call itself :
The implementation of execute does not close the connection
@Override
public boolean execute() {
try {
...
} catch (LockTimeoutException exception) {
throw exception;
} catch (PersistenceException exception) {
setRollbackOnly();
throw exception;
} catch (IllegalStateException e){
setRollbackOnly();
throw e;
} catch (RuntimeException exception) {
setRollbackOnly();
throw new PersistenceException(exception);
}
}
instead, using executeUpdate ensures that the connection is closed :
@Override
public int executeUpdate() {
try {
....
} catch (LockTimeoutException exception) {
throw exception;
} catch (PersistenceException e) {
setRollbackOnly();
throw e;
} catch (IllegalStateException e){
setRollbackOnly();
throw e;
} catch (RuntimeException exception) {
setRollbackOnly();
throw new PersistenceException(exception);
} finally {
close(); // Close the connection once we're done.
}
}
Hope this helps someone ... i've banged my head on the wall way too much time
on this.
Regards
On Apr 21 2022, at 5:30 pm, Marco Ferretti <[email protected]> wrote:
> Hi Brian,
> indeed: next steps are testing with alternative jpa implementations.
> Will keep you posted :D
>
> On Apr 21 2022, at 5:18 pm, Bryan Pendleton <[email protected]>
> wrote:
> > I'm still unclear as to what configuration settings Derby is using
> > during your test.
> >
> > Note that the default timeout setting for Derby is one minute:
> > https://db.apache.org/derby/docs/10.15/devguide/cdevconcepts16400.html
> >
> > If you are expecting a timeout after 10 seconds, you have to figure
> > out a way to set the Derby timeout configuration to 10 seconds.
> >
> > If your JPA approach is not able to do that (as Rick indicated, this
> > could be a bug in the JPA library you are using?), then you will have
> > to configure the Derby timeout setting using an alternate approach.
> >
> > There are various ways you can configure Derby, start here:
> > https://db.apache.org/derby/docs/10.15/devguide/cdevsetprop34818.html
> >
> > thanks,
> > bryan
> > On Thu, Apr 21, 2022 at 7:37 AM Marco Ferretti <[email protected]>
> > wrote:
> > >
> > > Hi all,
> > >
> > > another test another "success". I tried with an Oracle (XE) database and
> > > JDBC driver 8 :
> > >
> > > em.createStoredProcedureQuery("DBMS_SESSION.SLEEP(5)")
> > > .setHint("javax.persistence.query.timeout", 1)
> > > .execute();
> > >
> > > Apr 21, 2022 4:35:05 PM org.jboss.weld.bootstrap.WeldStartup <clinit>
> > > INFO: WELD-000900: 2.4.4 (Final)
> > > Apr 21, 2022 4:35:05 PM org.jboss.weld.bootstrap.WeldStartup
> > > startContainer
> > > INFO: WELD-000101: Transactional services not available. Injection of
> > > @Inject UserTransaction not available. Transactional observers will be
> > > invoked synchronously.
> > > WARNING: An illegal reflective access operation has occurred
> > > WARNING: Illegal reflective access by
> > > org.jboss.classfilewriter.ClassFile$1
> > > (file:/home/marco/.m2/repository/org/jboss/weld/se/weld-se/2.4.4.Final/weld-se-2.4.4.Final.jar)
> > > to method
> > > java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int)
> > > WARNING: Please consider reporting this to the maintainers of
> > > org.jboss.classfilewriter.ClassFile$1
> > > WARNING: Use --illegal-access=warn to enable warnings of further illegal
> > > reflective access operations
> > > WARNING: All illegal access operations will be denied in a future release
> > > Apr 21, 2022 4:35:06 PM org.jboss.weld.environment.se.WeldContainer
> > > fireContainerInitializedEvent
> > > INFO: WELD-ENV-002003: Weld SE container
> > > d725b64c-e888-4a45-845c-38cfe0b1bb9b initialized
> > > [EL Info]: 2022-04-21 16:35:06.4--ServerSession(1123236701)--EclipseLink,
> > > version: Eclipse Persistence Services - 2.7.3.v20180807-4be1041
> > > [EL Info]: connection: 2022-04-21
> > > 16:35:06.804--ServerSession(1123236701)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
> > > login successful
> > > [EL Warning]: 2022-04-21 16:35:07.491--UnitOfWork(808653065)--Exception
> > > [EclipseLink-4002] (Eclipse Persistence Services -
> > > 2.7.3.v20180807-4be1041):
> > > org.eclipse.persistence.exceptions.DatabaseException
> > > Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
> > > PLS-00801: internal error [22503]
> > > ORA-06550: line 1, column 7:
> > > PL/SQL: Statement ignored
> > >
> > > Error Code: 6550
> > > Call: BEGIN DBMS_SESSION.SLEEP(5)(); END;
> > > Query: ResultSetMappingQuery()
> > > [EL Info]: connection: 2022-04-21
> > > 16:35:07.497--ServerSession(1123236701)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
> > > logout successful
> > > Apr 21, 2022 4:35:07 PM org.jboss.weld.environment.se.WeldContainer
> > > shutdown
> > > INFO: WELD-ENV-002001: Weld SE container
> > > d725b64c-e888-4a45-845c-38cfe0b1bb9b shut down
> > >
> > >
> > >
> > > On Apr 20 2022, at 6:28 pm, Marco Ferretti <[email protected]>
> > > wrote:
> > >
> > > I didn’t because it works with Postgres jdbc. I’ll try with other
> > > databases/jdbc drivers not sure if that can help
> > >
> > >
> > > Il giorno mer 20 apr 2022 alle 18:08 Rick Hillegas
> > > <[email protected]> ha scritto:
> > >
> > > That suggests to me that the problem is not in the Derby layer. The
> > > problem is in JPA's support for Derby. JPA should be able to take
> > > advantage of java.sql.Statement.setQueryTimeout(). Have you brought this
> > > issue to the JPA community?
> > >
> > > On 4/20/22 7:52 AM, Marco Ferretti wrote:
> > > > Hi Rick,
> > > >
> > > > thanks for taking the time to reply.
> > > > I have looked at the link you provide: the method that sets the values
> > > > in persistence.xml should affect all queries attached to that
> > > > persistence unit; the second ("Setting the Query timeout on the single
> > > > Query") method is the one I am using, while the third option is, AFAIK,
> > > > out of scope in my case.
> > > >
> > > > Marco.
> > > >
> > > > On Apr 20 2022, at 4:46 pm, Rick Hillegas <[email protected]>
> > > > wrote:
> > > >> I'm not an expert on using JPA. The following link suggests that there
> > > >> is a way to configure query timeout in an xml-formatted JPA
> > > >> configuration file:
> > > >> http://www.mastertheboss.com/hibernate-jpa/jpa-configuration/3-ways-to-set-a-query-timeout-for-jpa-hibernate-applications/
> > > >>
> > > >> On 4/20/22 5:59 AM, Marco Ferretti wrote:
> > > >>> Ok I have an update.
> > > >>>
> > > >>> I have tested on PostgreSQL and I do get the timeout.
> > > >>> In order to create a simple case I have created a simple stored
> > > >>> procedure on pg :
> > > >>>
> > > >>> CREATE OR REPLACE PROCEDURE test_timeout("test" integer)
> > > >>> LANGUAGE SQL
> > > >>> AS $$
> > > >>> select count(*) from pg_sleep("test")
> > > >>> $$;
> > > >>>
> > > >>> and the call
> > > >>> em.createStoredProcedureQuery("test_timeout")
> > > >>> .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
> > > >>> .setParameter(1, 5)
> > > >>> .setHint("javax.persistence.query.timeout", 1)
> > > >>> .execute();
> > > >>>
> > > >>> actually throws the exception.
> > > >>> I have then created a simple Derby database (empty) in which I have
> > > >>> created my procedure
> > > >>> CREATE SCHEMA TEST;
> > > >>> CALL
> > > >>> SQLJ.INSTALL_JAR('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar',
> > > >>> 'TEST.test', 0);
> > > >>> CALL
> > > >>> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath',
> > > >>> 'TEST.test');
> > > >>>
> > > >>> DROP PROCEDURE APP.test_timeout;
> > > >>> CREATE PROCEDURE TEST.test_timeout (IN WAIT BIGINT)
> > > >>> PARAMETER STYLE JAVA
> > > >>> NO SQL
> > > >>> LANGUAGE JAVA
> > > >>> EXTERNAL NAME 'org.me.storedprocedure.Procedures.db_wait';
> > > >>>
> > > >>> Here's the super dummy procedure if you want to try:
> > > >>> public static void db_wait(long wait) throws Exception {
> > > >>> Thread.sleep(wait*1000);
> > > >>> }
> > > >>>
> > > >>> Running this code :
> > > >>> public boolean testStoredProcedure(EntityManager em ) throws
> > > >>> Exception {
> > > >>> em.createStoredProcedureQuery("test.test_timeout")
> > > >>> .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
> > > >>> .setParameter(1, 5)
> > > >>> .setHint("javax.persistence.query.timeout", 1)
> > > >>> .execute();
> > > >>> return false;
> > > >>> }
> > > >>>
> > > >>> Against Postgresql :
> > > >>> [EL Warning]: 2022-04-20
> > > >>> 14:52:29.152--UnitOfWork(392289808)--Exception [EclipseLink-4002]
> > > >>> (Eclipse Persistence Services - 2.7.3.v20180807-4be1041):
> > > >>> org.eclipse.persistence.exceptions.DatabaseException
> > > >>> Internal Exception: org.postgresql.util.PSQLException: ERROR:
> > > >>> canceling statement due to user request
> > > >>> Where: SQL statement "select count(*) from pg_sleep("test")"
> > > >>> PL/pgSQL function test.test_timeout(integer) line 5 at SQL statement
> > > >>> Error Code: 0
> > > >>> Call: SELECT * FROM test.test_timeout(?)
> > > >>> bind => [1 parameter bound]
> > > >>> Query: ResultSetMappingQuery()
> > > >>> javax.persistence.PersistenceException:Exception [EclipseLink-4002]
> > > >>> (Eclipse Persistence Services - 2.7.3.v20180807-4be1041):
> > > >>> org.eclipse.persistence.exceptions.DatabaseException
> > > >>> Internal Exception: org.postgresql.util.PSQLException: ERROR:
> > > >>> canceling statement due to user request
> > > >>> Where: SQL statement "select count(*) from pg_sleep("test")"
> > > >>> PL/pgSQL function test.test_timeout(integer) line 5 at SQL statement
> > > >>> Error Code: 0
> > > >>> Call: SELECT * FROM test.test_timeout(?)
> > > >>> bind => [1 parameter bound]
> > > >>> Query: ResultSetMappingQuery()
> > > >>> [EL Info]: connection: 2022-04-20
> > > >>> 14:52:29.162--ServerSession(1406848276)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
> > > >>> logout successful
> > > >>>
> > > >>> Against Derby (Embedded):
> > > >>> [EL Info]: 2022-04-20
> > > >>> 14:48:40.742--ServerSession(256346753)--EclipseLink, version: Eclipse
> > > >>> Persistence Services - 2.7.3.v20180807-4be1041
> > > >>> [EL Info]: connection: 2022-04-20
> > > >>> 14:48:41.028--ServerSession(256346753)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
> > > >>> login successful
> > > >>> [EL Info]: connection: 2022-04-20
> > > >>> 14:48:46.144--ServerSession(256346753)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
> > > >>> logout successful
> > > >>>
> > > >>> As you can see the call is cancelled after 1 millisecond when run
> > > >>> against postgresql while it is not while run against derby
> > > >>> The two jdbc drivers I used :
> > > >>> <dependency>
> > > >>> <groupId>org.apache.derby</groupId>
> > > >>> <artifactId>derby</artifactId>
> > > >>> <version>10.14.2.0</version>
> > > >>> </dependency>
> > > >>> <dependency>
> > > >>> <groupId>org.postgresql</groupId>
> > > >>> <artifactId>postgresql</artifactId>
> > > >>> <version>42.3.4</version>
> > > >>> </dependency>
> > > >>>
> > > >>> Am I hitting a derby/derby-jdbc limitation or am I missing some
> > > >>> configuration ?
> > > >>> Thanks in advance for any help you can provide
> > > >>>
> > > >>> On Apr 19 2022, at 11:57 pm, Marco Ferretti
> > > >>> <[email protected]> wrote:
> > > >>>> Hi Brian,
> > > >>>> Thanks for your reply and attempt to help.
> > > >>>>
> > > >>>> Here's what I do:
> > > >>>> Within the database (preparation of the test)
> > > >>>> CALL
> > > >>>> SQLJ.replace_jar('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar',
> > > >>>> 'APP.STORED_PROCS');
> > > >>>>
> > > >>>> CREATE PROCEDURE APP.P_MUI_EXPORT_PIANIFICAZIONE (IN ID_PROMOZIONE
> > > >>>> BIGINT , IN ID_COMPRATORE VARCHAR(255), IN CODICE_UTENTE VARCHAR(50)
> > > >>>> )
> > > >>>> PARAMETER STYLE JAVA
> > > >>>> MODIFIES SQL DATA
> > > >>>> LANGUAGE JAVA
> > > >>>> EXTERNAL NAME 'com.foo.Procedures.exportPianificazione';
> > > >>>>
> > > >>>> Here's the relevant parts of the (dummy) stored procedure:
> > > >>>> public static void exportPianificazione(long idPromozione, String
> > > >>>> idCompratori, String codiceUtente)
> > > >>>> throws DbPromoException {
> > > >>>> try (Connection conn =
> > > >>>> DriverManager.getConnection("jdbc:default:connection");) {
> > > >>>> new Utils().dummyExportPianificazione("APP", conn, idPromozione,
> > > >>>> idCompratori, codiceUtente);
> > > >>>> } catch (Exception e) {
> > > >>>> log.log(Level.SEVERE, "Error writing values in mui_check_testata",
> > > >>>> e);
> > > >>>> throw new DbPromoException("Error writing values in
> > > >>>> mui_check_testata : " + e.getMessage(), e);
> > > >>>> }
> > > >>>> }
> > > >>>>
> > > >>>> within the Utils class:
> > > >>>> public void dummyExportPianificazione(String schema, Connection
> > > >>>> conn, long idPromozione, String idCompratori,
> > > >>>> String codiceUtente) throws SQLException, InterruptedException {
> > > >>>> String query = "insert into "+schema+".MUI_CHECK_COMPRATORI (ID,
> > > >>>> ID_PROMOZIONE, ID_COMPRATORE, ESITO, CODICE_UTENTE_INSERIMENTO,
> > > >>>> CODICE_UTENTE_AGGIORNAMENTO, DATA_INSERIMENTO, DATA_AGGIORNAMENTO )
> > > >>>> values ("
> > > >>>> + "NEXT VALUE FOR MUI_CHECK_COMPRATORI_ID_SEQ, ?, ?, ?, ?, ?, ?, ?)";
> > > >>>> String delete = "delete from " + schema
> > > >>>> + ".MUI_CHECK_COMPRATORI where id_promozione = ? and id_compratore =
> > > >>>> ?";
> > > >>>> String esito=getEsito(); //random generation of a result
> > > >>>> String[] compratori = idCompratori.split(",");
> > > >>>> TimeUnit.SECONDS.wait(10);
> > > >>>> ....
> > > >>>> }
> > > >>>>
> > > >>>>
> > > >>>>
> > > >>>> In my application call :
> > > >>>> ...
> > > >>>> milliseconds = 1;
> > > >>>>
> > > >>>> ...
> > > >>>> getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE)
> > > >>>> .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
> > > >>>> .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
> > > >>>> .registerStoredProcedureParameter(3, String.class,
> > > >>>> ParameterMode.IN).setParameter(1, myPromo)
> > > >>>> .setParameter(2, idCompratori).setParameter(3, username)
> > > >>>> .setHint("javax.persistence.query.timeout", milliseconds)
> > > >>>> .execute();
> > > >>>> ....
> > > >>>>
> > > >>>> Activating the JPA logs (EclipseLink) to level FINEST I can see that
> > > >>>> it takes 10 seconds between the call to the stored procedure and the
> > > >>>> the next step of my application.
> > > >>>> I have also checked that the version of EclipseLink that I am using
> > > >>>> supports this hint and that the default unit of the hint is
> > > >>>> millisecons (but even if it was seconds, it should throw the timeout
> > > >>>> as 1<10).
> > > >>>>
> > > >>>> What is a real puzzle to me is the fact that I am not getting an
> > > >>>> exception when I call the stored procedure from the application thus
> > > >>>> my doubt: do I have to "activate" something in the jdbc
> > > >>>> driver/database in order to actually fire the timeout ?
> > > >>>> The other option is that I did not understand at all how this hint
> > > >>>> should work (which, at this point, would make sense)
> > > >>>>
> > > >>>> Thanks in advance
> > > >>>> On Apr 19 2022, at 10:05 pm, Bryan Pendleton
> > > >>>> <[email protected]> wrote:
> > > >>>>> I think that Marco is trying to *test* how his code handles a
> > > >>>>> timeout exception, but he doesn't know a way to force a timeout
> > > >>>>> exception to occur during his test.
> > > >>>>>
> > > >>>>> I don't know a trivial way to do this, either.
> > > >>>>>
> > > >>>>> I think you might have to write a more complex test program, for
> > > >>>>> example you could have two threads, and two transactions, and in
> > > >>>>> the first thread you could begin a transaction and update a record
> > > >>>>> BUT NOT COMMIT.
> > > >>>>>
> > > >>>>> Then, in the second thread, you could begin a transaction and call
> > > >>>>> your stored procedure to run a query against that record, but the
> > > >>>>> query should block because the record is updated by the first
> > > >>>>> transaction in the first thread.
> > > >>>>>
> > > >>>>> Then, after some time expires, you should get the timeout exception
> > > >>>>> in your stored procedure.
> > > >>>>>
> > > >>>>> thanks,
> > > >>>>>
> > > >>>>> bryan
> > > >>>>>
> > > >>>>>
> > > >>>>> On Tue, Apr 19, 2022 at 9:30 AM Marco Ferretti
> > > >>>>> <[email protected] (mailto:[email protected])> wrote:
> > > >>>>>> Hi Rick,
> > > >>>>>>
> > > >>>>>> thanks for taking the time to reply.
> > > >>>>>> I am not 100% sure what you mean. My application uses JPA and the
> > > >>>>>> Entity Manager is getting the connection from the container's
> > > >>>>>> datasource: I do not have (direct) access to the
> > > >>>>>> java.sql.Statement.
> > > >>>>>> What I (would like to) do is calling the a stored procedure in
> > > >>>>>> this way:
> > > >>>>>>
> > > >>>>>> getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE)
> > > >>>>>> .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
> > > >>>>>> .registerStoredProcedureParameter(2, String.class,
> > > >>>>>> ParameterMode.IN)
> > > >>>>>> .registerStoredProcedureParameter(3, String.class,
> > > >>>>>> ParameterMode.IN).setParameter(1, myPromo)
> > > >>>>>> .setParameter(2, idCompratori).setParameter(3, username)
> > > >>>>>> .setHint("javax.persistence.query.timeout", milliseconds)
> > > >>>>>> .execute();
> > > >>>>>>
> > > >>>>>> As far as the JPA API specification (and EclipseLink
> > > >>>>>> implementation) goes, the
> > > >>>>>> ".setHint("javax.persistence.query.timeout", milliseconds)" should
> > > >>>>>> be equivalent to what you suggest.
> > > >>>>>> The problem is that I cannot make the stored procedure call to
> > > >>>>>> throw a QueryTimeoutException even if my dummy procedure (in
> > > >>>>>> derby) waits for 10 seconds and I set the timeout to 1 millisecond.
> > > >>>>>> I am using, in my test environment, Apache Tomee (java 8) , derby
> > > >>>>>> client 10.14 and a docker image with a derby network server
> > > >>>>>> (https://github.com/az82/docker-derby/blob/master/Dockerfile
> > > >>>>>> (https://link.getmailspring.com/link/[email protected]/0?redirect=https%3A%2F%2Fgithub.com%2Faz82%2Fdocker-derby%2Fblob%2Fmaster%2FDockerfile&recipient=ZGVyYnktdXNlckBkYi5hcGFjaGUub3Jn))
> > > >>>>>> .
> > > >>>>>>
> > > >>>>>> As far as my knowledge goes, and it's not that far, the reasons I
> > > >>>>>> am not getting a timeout are :
> > > >>>>>> I am not correctly using the hint
> > > >>>>>>
> > > >>>>>> I am not correctly setting up the datasource
> > > >>>>>>
> > > >>>>>> There is no timeout because the stored procedure terminates within
> > > >>>>>> the given timeout.
> > > >>>>>>
> > > >>>>>>
> > > >>>>>> On point 3: in my (dummy) implementation of the stored procedure i
> > > >>>>>> do, before doing anything else, a TimeUnit.SECONDS.wait(10);
> > > >>>>>> On point 1 : I am lost. I think I am using the hint correctly (at
> > > >>>>>> least the API docs say so)
> > > >>>>>> On point 2: I am setting up the datasource with the minimum
> > > >>>>>> configuration possible :
> > > >>>>>> <Resource id="jdbc/myDatasource" type="DataSource"> JdbcDriver
> > > >>>>>> org.apache.derby.jdbc.ClientDriver JdbcUrl
> > > >>>>>> jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO
> > > >>>>>> Password dbpromo </Resource>
> > > >>>>>> Could it be that I am failing to activate something on the Derby
> > > >>>>>> side ?
> > > >>>>>>
> > > >>>>>> Thanks in advance
> > > >>>>>> Marco
> > > >>>>>>
> > > >>>>>> On Apr 19 2022, at 4:57 pm, Rick Hillegas <[email protected]
> > > >>>>>> (mailto:[email protected])> wrote:
> > > >>>>>>> java.sql.Statement.setQueryTimeout(int) should do the trick.
> > > >>>>>>>
> > > >>>>>>> On 4/19/22 3:30 AM, Marco Ferretti wrote:
> > > >>>>>>>> Hi all,
> > > >>>>>>>> I am trying to simulate a query timeout in a stored procedure by
> > > >>>>>>>> simply adding a delay in my (test) jar.
> > > >>>>>>>> I then am launching the stored procedure in my java code via JPA
> > > >>>>>>>> and try to set a timeout hint by adding
> > > >>>>>>>> .setHint("javax.persistence.query.timeout", milliseconds)
> > > >>>>>>>> but I am not able to register a timeout.
> > > >>>>>>>> I am wondering if there is some derby property or jdbc property
> > > >>>>>>>> I should use to activate such behavior. I have tried to google
> > > >>>>>>>> for it but I am having extremely bad results... but according to
> > > >>>>>>>> this
> > > >>>>>>>> (https://docs.oracle.com/cd/E25178_01/apirefs.1111/e13952/pagehelp/J2EEkodojdbcconfdescriptorDerbyDictionaryBeantitle.html)
> > > >>>>>>>> I have to activate it somehow.
> > > >>>>>>>>
> > > >>>>>>>> Does Derby supports query timeout at all? If so, can you please
> > > >>>>>>>> point me to some references ?
> > > >>>>>>>> Thanks in advance for any help,
> > > >>>>>>>> Marco
> > > >>>>>>>>
> > > >
> > >
> > > --
> > > Sent from Gmail Mobile
> >
>
