Payara 5.2020 timer pool H2 vs Derby

Stf

unread,

Aug 25, 2020, 9:28:34 AM8/25/20

to Payara Forum

Hi!

We are currently looking into using Payara 5.2020 - However when migrating from Glassfish the TimerPool can not be used like before as the Derby version is not supported by Payara 5.2 (removed)

Can you please let me know what you would suggest?

Install (how?) Derby on Payara 5.2020

- or -

Use H2 DB and run the timer scripts (how?)

https://github.com/payara/Payara/issues/4091

?

Thanks!

Rainer Schamm

unread,

Aug 26, 2020, 11:26:13 AM8/26/20

to Payara Forum

I second this!

This is causing an upgrade nightmare scenario for us.

Please in future take more care not to remove perfectly valid functionality.

Could you please document in detail how we can restore Derby functionality !!!

Steve Millidge

unread,

Aug 26, 2020, 11:49:10 AM8/26/20

to Payara Forum

Even with GlassFish Derby should not have been used as the persistence layer for production systems using EJB timers. It was recommended to move to a multi-user database e.g. MySQL, PostGreSQL etc. From the above forum messages there's not much information to go on that describes the problems you are seeing moving to Payara so it is quite difficult for people to help.

Can you provide more detail about any issues you are experiencing?

Stf

unread,

Aug 26, 2020, 12:19:45 PM8/26/20

to Payara Forum

Thanks Steve.

We have been running GF with Derby for the TimerPool without a single problem for, well, for 10 years or so. PROD, yes.

I also considered this to be the default but I do not know that.

Anyways, if you can help us to resolve this issue now it would be great.

[#|2020-08-25T11:51:42.943+0200|INFO|Payara 5.2020|org.eclipse.persistence.session./file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App|_ThreadID=19;_ThreadName=RunLevelControllerThread-1598349051281;_TimeMillis=1598349102943;_LevelValue=800;|EclipseLink, version: Eclipse Persistence Services - 2.7.6.payara-p1|#]

[#|2020-08-25T11:51:44.348+0200|INFO|Payara 5.2020|org.eclipse.persistence.session./file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App.connection|_ThreadID=19;_ThreadName=RunLevelControllerThread-1598349051281;_TimeMillis=1598349104348;_LevelValue=800;|/file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App login successful|#]

[#|2020-08-25T11:51:44.426+0200|WARNING|Payara 5.2020|org.eclipse.persistence.session./file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App|_ThreadID=19;_ThreadName=RunLevelControllerThread-1598349051281;_TimeMillis=1598349104426;_LevelValue=900;|Local Exception Stack:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.6.payara-p1): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLException: java.lang.reflect.UndeclaredThrowableException

Error Code: 0

Call: SELECT "TIMERID", "APPLICATIONID", "BLOB", "CONTAINERID", "CREATIONTIMERAW", "INITIALEXPIRATIONRAW", "INTERVALDURATION", "LASTEXPIRATIONRAW", "OWNERID", "PKHASHCODE", "SCHEDULE", "STATE" FROM "EJB__TIMER__TBL" WHERE (("OWNERID" = ?) AND ("STATE" = ?))

bind => [2 parameters bound]

Query: ReadAllQuery(name="findTimersByOwnerAndState" referenceClass=TimerState sql="SELECT "TIMERID", "APPLICATIONID", "BLOB", "CONTAINERID", "CREATIONTIMERAW", "INITIALEXPIRATIONRAW", "INTERVALDURATION", "LASTEXPIRATIONRAW", "OWNERID", "PKHASHCODE", "SCHEDULE", "STATE" FROM "EJB__TIMER__TBL" WHERE (("OWNERID" = ?) AND ("STATE" = ?))")

at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342)

at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:691)

at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)

at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2099)

at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603)

at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:265)

at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)

at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)

at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:332)

at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:744)

at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2759)

[...]

at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.run(CurrentTaskFuture.java:1144)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)

at java.base/java.lang.Thread.run(Thread.java:834)

Caused by: java.sql.SQLException: java.lang.reflect.UndeclaredThrowableException

at com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40.prepareStatement(ProfiledConnectionWrapper40.java:247)

at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1595)

at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1544)

at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:806)

at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:628)

... 75 more

Caused by: java.lang.reflect.UndeclaredThrowableException

at com.sun.proxy.$Proxy397.prepareStatement(Unknown Source)

at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:609)

at com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40.prepareCachedStatement(ProfiledConnectionWrapper40.java:372)

at com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40.prepareCachedStatement(ProfiledConnectionWrapper40.java:66)

at com.sun.gjc.spi.ManagedConnectionImpl.prepareCachedStatement(ManagedConnectionImpl.java:1080)

at com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40.prepareStatement(ProfiledConnectionWrapper40.java:243)

... 79 more

Caused by: java.lang.reflect.InvocationTargetException

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566)

at com.sun.gjc.spi.JdbcObjectsFactory$1.invoke(JdbcObjectsFactory.java:143)

... 85 more

Caused by: org.h2.jdbc.JdbcSQLException: Table "EJB__TIMER__TBL" not found; SQL statement:

SELECT "TIMERID", "APPLICATIONID", "BLOB", "CONTAINERID", "CREATIONTIMERAW", "INITIALEXPIRATIONRAW", "INTERVALDURATION", "LASTEXPIRATIONRAW", "OWNERID", "PKHASHCODE", "SCHEDULE", "STATE" FROM "EJB__TIMER__TBL" WHERE (("OWNERID" = ?) AND ("STATE" = ?)) [42102-196]

Can you please let me know if H2 db will be a suitable replacement for this scenario, and if so, how I can make it work so Payara will find the timer tables once it is starting?

Thanks!!

Rainer Schamm

unread,

Aug 26, 2020, 1:02:48 PM8/26/20

to Payara Forum

I would, for now, just like to know all steps needed to get Derby working again. I gave it a quick shot by adding some of the derby jars to the domains/lib directory; but had no success.

Even better, if you could simply add Derby support back out of the box in the next release, then we can just skip this release for now.

(Also as far as production readiness is concerned, I would consider the difference between H2 and Derby, as much of a muchness)

On Wednesday, August 26, 2020 at 5:49:10 PM UTC+2 Steve Millidge wrote:

Ondro Mihályi

unread,

Aug 27, 2020, 7:53:41 AM8/27/20

to Payara Forum

Hi Rainer,

If you need to create EJB Timer or JBatch tables in the H2 DB (or another DB like PostgreSQL), you can use scripts in the Payara Server installation, in the directory apps/payara/payara5.201.1/glassfish/lib/install/databases.

I don't see a reason why you're complaining about removing Derby DB from Payara Server. The Derby DB project is not actively maintained, contains a lot of bugs and performs much worse than other similar databases. The Payara project decided to replace it with H2 DB, which is in all aspects much better than Derby. The Derby DB was deprecated since the first version of Payara 5 and we kept it for 2 years to allow enough time to migrate to H2. Derby was removed in Payara 5.201 and H2 became the only option.

We don't provide any guide or a single-step way to replace H2 with Derby DB as it was in the older versions and won't provide it in the future. However, you can add Derby DB into the server's classpath (usually in domains lib or lib/ext directory) and change all the JDBC connection pools to use it instead of H2 DB. It's the same procedure as with any other JDBC driver.

I hope you understand that we had very good reasons to replace Derby DB with H2 DB. If you'd like to have better support for Derby DB in future versions of Payara 5, you should consider signing up for Payara Enterprise, with which you can request custom enhancements for you. Of course it's not free, but production support is never free, it either costs you time or money.

Ondro

Ondro Mihályi

unread,

Aug 27, 2020, 7:56:32 AM8/27/20

to Payara Forum

Hi Stf,

Please try creating the tables in H2 using the script for H2 in Payara installation, in directory glassfish/lib/install/databases.

Ondro

Stf

unread,

Aug 27, 2020, 7:58:07 AM8/27/20

to Payara Forum

Ondro,

can you please advise how we have to configure Payara to run the scripts in: apps/payara/payara5.201.1/glassfish/lib/install/databases

The documentation is very poor on that part - and if you are removing default GF behaviour I would at least expect some information on a workaround to make EJB timers (@Schedule) work again as when Derby was in place.

Thank you.

Stf

unread,

Aug 27, 2020, 8:09:38 AM8/27/20

to Payara Forum

I think I can connect to a H2 database using:

java -cp /srv/payara5/h2db/bin/h2.jar org.h2.tools.Shell

Once I would connect to H2 we could use 'run script...' to run the mentioned script then.

But where is the H2 database located that I would run the script on?

Pretty worth documenting this... especially when introducing this kind of change!

Thanks.

Ondro Mihályi

unread,

Aug 27, 2020, 10:22:44 AM8/27/20

to Stf, Payara Forum

Hi Stf,

Please raise an issue on github, it's possible that it's just a bug and the tables should be automatically created by Payara Server on the default database. Even if not, it will remind us to document how to create the tables.

Payara Server is an opensource software and its documentation is provided for free, and thus not everything is perfect. We've documented everything for Payara Enterprise customers in a private knowledgebase but forgot to document it for Payara Community. We'd be greatly thankful if you or anybody else offers a pull request to the Payara documentation to improve it: https://github.com/payara/Payara-Community-Documentation

I'll copy the essential info from the Payara Enterprise knowledge base for you, unfortunately I won't have time to add it to the public documentation any time soon.

To connect to the H2 DB Timer database and execute SQL scripts, you should:

Stop Payara Server if running
Navigate to /path/to/payara/payara5/h2db/bin, to find the h2db binaries
Execute java -jar h2.jar (this will start the H2 Admin UI server and open browser with the Admin UI URL)
Set JDBC URL to jdbc:h2:/path/to/payara/payara5/glassfish/domains/domain1/lib/databases/ejbtimer
Leave the other options as they are and log in, then execute your SQL
Then stop the h2.jar process

This will modify the database file used by the H2 server embedded in Payara Server.

I hope you find it useful.

All the best,

Ondro

št 27. 8. 2020 o 14:09 Stf <stefan....@keepitsafe.no> napísal(a):

This email, its contents and attachments contain information from J2 Global, Inc. and/or its affiliates which may be privileged, confidential or otherwise protected from disclosure. The information is intended to be for the addressee(s) only. If you are not an addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this email in error, please notify the sender by reply email and delete the original message and any copies.

--
You received this message because you are subscribed to the Google Groups "Payara Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to payara-forum...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/payara-forum/e3fc097a-6165-4e26-9656-7e47309169d0n%40googlegroups.com.

Rainer Schamm

unread,

Aug 27, 2020, 11:08:33 AM8/27/20

to Payara Forum

Hi

I apologize for my tone earlier on.

I also don't mind adding H2 support and that the new defaults are based on H2; I just think that there was no reason to remove Apache Derby support.

We have (also) NOT had a single issue in many years of production usage. Also, Derby has had yearly updates with the latest release being in Feb 2020.

I see no point trying to do a DB migration (with data, since I especially don't want to loose my batchdb data) to H2 which is also NOT recommended for production usage.

I will try and add the Derby jars like you suggested for now. Eventually we will migrate to MySQL, but that will be a bigger project.

Regards

Rainer

On Thursday, August 27, 2020 at 1:53:41 PM UTC+2 Ondro Mihályi wrote:

Stf

unread,

Aug 27, 2020, 11:33:27 AM8/27/20

to Payara Forum

Issue created now:

https://github.com/payara/Payara/issues/4857

Thanks for the support!

Stf

unread,

Aug 27, 2020, 11:40:00 AM8/27/20

to Payara Forum

PR created now.

https://github.com/payara/Payara-Community-Documentation/pull/62

HTH!

Stf

unread,

Aug 27, 2020, 11:47:12 AM8/27/20

to Payara Forum

Two more questions:

As the start domain is crashing, and so ends with a domain shutdown... does that mean that H2 for this domain is up and running and so can be accessed as described?

Does the default H2 db for domain1 have a default password? What is it? Is that described somewhere in the documentation?

Thanks.

Stf

unread,

Aug 27, 2020, 11:58:30 AM8/27/20

to Payara Forum

Still unsure if what I am doing is right... just let me know what you think...

This would be the command to run the script - which actually fails:

sql> runscript from '/srv/payara5/glassfish/lib/install/databases/ejbtimer_h2.sql';

Error: org.h2.jdbc.JdbcSQLException: The database is read only; SQL statement:

I have no clue on how to open the db in write mode...

URL jdbc:h2:/srv/payara5/glassfish/domains/domain1/lib/databases/ejbtimer;ACCESS_MODE_DATA=rws

Did not work..

Also, I keep username/pass for db login empty - which seems to work fine.

Correct?

Stf

unread,

Aug 27, 2020, 12:56:25 PM8/27/20

to Payara Forum

You need write permission on the file
jdbc:h2:/srv/payara5/glassfish/domains/domain1/lib/databases/ejbtimer

to be able to actually create the table.

Issue resolved for now.

Thanks for your help!!

[#|2020-08-27T18:53:51.777+0200|INFO|Payara 5.2020|org.eclipse.persistence.session./file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App|_ThreadID=27;_ThreadName=RunLevelControllerThread-1598547198981;_TimeMillis=1598547231777;_LevelValue=800;|EclipseLink, version: Eclipse Persistence Services - 2.7.6.payara-p1|#]

[#|2020-08-27T18:53:52.569+0200|INFO|Payara 5.2020|org.eclipse.persistence.session./file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App.connection|_ThreadID=27;_ThreadName=RunLevelControllerThread-1598547198981;_TimeMillis=1598547232569;_LevelValue=800;|/file:/srv/payara5/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App login successful|#]

Reply all

Reply to author

Forward