Re: query timeout
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 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
> 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
> > 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 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
> > 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 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
Re: query timeout
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 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
> 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 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
> 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 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
> >>>
Re: query timeout
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
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 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 (mailto: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 > > (mailto: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
