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 <bpendleton.de...@gmail.com> 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 <marco.ferre...@gmail.com> > 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 <marco.ferre...@gmail.com> 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 > > <rick.hille...@gmail.com> 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 <rick.hille...@gmail.com> 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 <marco.ferre...@gmail.com> > > >>> 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 > > >>>> <bpendleton.de...@gmail.com> 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 > > >>>>> <marco.ferre...@gmail.com (mailto:marco.ferre...@gmail.com)> 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/3174774e-c496-42ab-83b1-dfe1a95ae...@getmailspring.com/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 <rick.hille...@gmail.com > > >>>>>> (mailto:rick.hille...@gmail.com)> 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 >
