Programatic created EJB calendar timer with SchedulerExpression and Start or End date failed on server restarted in EAP 7

Environment

• Red Hat JBoss Enterprise Application Platform (EAP)
  • 7.0
  • 6.4.x

Issue

• If a calendar timer is created programatical with a SchedulerExpression where start or end date is set a server restart will show the the following message. Why is the timestamp format wrong?

WARN  [org.jboss.as.ejb3.timer] (ServerService Thread Pool -- 63) WFLYEJB0161: Failed to reinstate timer 'ejb31-timer.ejb31-timer.SimpleScheduleSingletonTimerBean' (id=91be5b71-1ef3-4491-af81-2a7ace4c3a72) from its persistent state: java.sql.SQLException: Invalid conversion requested
	at oracle.jdbc.driver.CharCommonAccessor.getTimestamp(CharCommonAccessor.java:420)
	at oracle.jdbc.driver.T4CVarcharAccessor.getTimestamp(T4CVarcharAccessor.java:644)
	at oracle.jdbc.driver.GeneratedStatement.getTimestamp(GeneratedStatement.java:360)
	at oracle.jdbc.driver.GeneratedScrollableResultSet.getTimestamp(GeneratedScrollableResultSet.java:1008)
	at org.jboss.jca.adapters.jdbc.WrappedResultSet.getTimestamp(WrappedResultSet.java:2140)
	at org.jboss.as.ejb3.timerservice.persistence.database.DatabaseTimerPersistence.timerFromResult(DatabaseTimerPersistence.java:535)
	at org.jboss.as.ejb3.timerservice.persistence.database.DatabaseTimerPersistence.loadActiveTimers(DatabaseTimerPersistence.java:452)
	at org.jboss.as.ejb3.timerservice.TimerServiceImpl.getActivePersistentTimers(TimerServiceImpl.java:953)
	at org.jboss.as.ejb3.timerservice.TimerServiceImpl.restoreTimers(TimerServiceImpl.java:693)
	at org.jboss.as.ejb3.timerservice.TimerServiceImpl.activate(TimerServiceImpl.java:225)
	at org.jboss.as.ejb3.component.EJBComponent.start(EJBComponent.java:588)
	at org.jboss.as.ejb3.component.singleton.SingletonComponent.start(SingletonComponent.java:133)
	at org.jboss.as.ee.component.ComponentStartService$1.run(ComponentStartService.java:54)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
	at org.jboss.threads.JBossThread.run(JBossThread.java:320)
Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
	at java.sql.Timestamp.valueOf(Timestamp.java:237)
	at oracle.jdbc.driver.CharCommonAccessor.getTimestamp(CharCommonAccessor.java:415)
	... 18 more

Resolution

As workaround the columns can be converted to the "yyyy-mm-dd hh:mm:ss" format for each timer after creation.

The issue is tracked by Content from issues.jboss.org is not included.JBEAP-11954
and will be solve with a CP relases in the future.

The solution is to store the date's with a fixed format

Root Cause

The SchedulerExpression API contains start and end date as java.util.Date, the database table will store it as VARCHAR.
The Oracle database will convert that date according to the NLS_TIMESTAMP_FORMAT but try to read with the standard format "yyyy-mm-dd hh:mm:ss.fffffffff".
If the variable contains a different format the stored data can not be read to re install the timers if the server starts.
This can happen for a server restart or if a new server is started after the timer has been created.

Workaround

It is possible to change the SCHEDULE_EXPR_START_DATE or SCHEDULE_EXPR_END_DATE manually to the "yyyy-mm-dd hh:mm:ss" format after the timer was added, this can be done even if instances are running.

To prevent from adding dates with the wrong format the settings can be given to the connect String for Oracle it is
jdbc:datadirect:oracle://server1:1521;ServiceName=ORCL;InitializationString=ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.