scheduled tasks don't run on Oracle (or DB2, or probably PostgreSQL)

[Dick Davies <ra...@hellooperator.net>]

Hi,

I've just spent a fun afternoon trying to figure out why roller 4.0.1
isn't running any scheduled tasks
with our Oracle 10 database.

In a nutshell, the JPA config seems to assume that CURRENT_TIMESTAMP
is low precision
(i.e. hundredths of a second).

On databases that have microsecond resolution (Oracle, PostgreSQL, etc.) the
JPAThreadManagerImpl.registerLease() method quickly gets into a state where the

"TaskLock.updateClient&Timeacquired&Timeleased&LastRunByName&Timeacquired"
NamedUpdate never suceeds,

and so tasks never run.
(There was a similar issue with DB2 that resulted in us dumping that ,
so it's not all bad :) )

I turned on JPA debugging and on Oracle 10, we see

DEBUG 2008-11-20 15:30:00,064 CommonsLogFactory$LogAdapter:trace -
Executing query: [UPDATE TaskLock t SET t.clientId=?1, t.timeAquired=
CURRENT_TIMESTAMP, t.timeLeased= ?2, t.lastRun= ?3 WHERE t.name=?4 AND
t.timeAquired=?5 AND ?6 < CURRENT_TIMESTAMP] with parameters: {3=Thu
Aug 07 16:07:00 BST 2008, 2=PingQueueTask, 1=2008-08-07 16:37:00.07,
6=2008-11-20 15:30:00.0, 5=30, 4=devel-roller01}
DEBUG 2008-11-20 15:30:00,065 CommonsLogFactory$LogAdapter:trace - <t
28169327, conn 12589755> executing prepstmnt 11980159 UPDATE
roller_tasklock t0 SET timeleased = ?, client = ?, lastrun = ?,
timeacquired = CURRENT_TIMESTAMP WHERE (t0.name = ? AND
t0.timeacquired = ? AND ? < CURRENT_TIMESTAMP) [params=(int) 30,
(String) devel-roller01, (Timestamp) 2008-11-20 15:30:00.0, (String)
PingQueueTask, (Timestamp) 2008-08-07 16:07:00.07, (Timestamp)
2008-08-07 16:37:00.07]

TIMEACQUIRED at that time was '07-AUG-08 04.07.00.069896 PM', and
since Oracle checks down to the microsecond
when comparing timestamps, this means the WHERE clause never matches
and nothing updates.

The WHERE clause seems overly picky in any case -
the schema creation scripts declare the roller_tasklock.name column to
be unique, so 'WHERE to.name = ?' is enough
to find the right row, surely?

Another fix is to declare the timestamp columns as timestamp(2) in the Oracle
(+DB2 + PostgreSQL ? ) creation scripts. We're trying that now to see
if it helps.

I've filed this as https://issues.apache.org/roller/browse/ROL-1760 ,
if you need more information just ask.

Re: scheduled tasks don't run on Oracle (or DB2, or probably PostgreSQL)

[Dick Davies <ra...@hellooperator.net>]

On Thu, Nov 20, 2008 at 7:35 PM, Ron Peterson <rp...@mtholyoke.edu> wrote:
> 2008-11-20_11:24:52-0500 Dick Davies <ra...@hellooperator.net>:

>> In a nutshell, the JPA config seems to assume that CURRENT_TIMESTAMP
>> is low precision (i.e. hundredths of a second).
>
>> I've filed this as https://issues.apache.org/roller/browse/ROL-1760 ,

> I'll bet a bagel it's the same as this.
> https://issues.apache.org/roller/browse/ROL-1657
>
> I've been scratching my head about this myself the last couple of days.
> Thanks a _lot_ for the diagnosis.


No problem, I'm a PostgreSQL fanboy myself so if we can fix it for
both I'll be very happy :)

You can probably check if you like by turning off all but one of the
scheduled tasks and then
whacking up the debug level for JPA; try adding this to roller-custom.properties
(we also took the server out of load balance so the only DB activity
in the log was task related)

tasks.enabled=PingQueueTask
log4j.appender.roller.File=${catalina.base}/logs/roller-debug.log
log4j.category.org.apache.roller.weblogger.business.runnable=DEBUG
# generate an insane amount of JPA debugging
openjpa.Log=commons
log4j.category.openjpa.Tool=DEBUG
log4j.category.openjpa.Runtime=DEBUG
log4j.category.openjpa.Remote=DEBUG
log4j.category.openjpa.DataCache=DEBUG
log4j.category.openjpa.MetaData=DEBUG
log4j.category.openjpa.Enhance=DEBUG
log4j.category.openjpa.Query=DEBUG
log4j.category.openjpa.jdbc.SQL=DEBUG
log4j.category.openjpa.jdbc.JDBC=DEBUG
log4j.category.openjpa.jdbc.Schema=DEBUG

that's how we got the prepared statements that were being generated,
then it's a case of
grepping the source tree.

To clarify; we don't have a fix for this yet, we've tried altering
timestamp columns so they
are less fine grained but I'm not convinced that will fix things. May
resort to hacking the code
today (to take out the extra WHERE clauses), will let you all know how
that goes.

Though if Dave wants to fix this and release 4.0.1 this afternoon,
that'll be fine too :)

Re: scheduled tasks don't run on Oracle (or DB2, or probably PostgreSQL)

[Dave <sn...@gmail.com>]

On Thu, Nov 20, 2008 at 2:35 PM, Ron Peterson <rp...@mtholyoke.edu> wrote:
> 2008-11-20_11:24:52-0500 Dick Davies <ra...@hellooperator.net>:
>> Hi,
>>
>> I've just spent a fun afternoon trying to figure out why roller 4.0.1
>> isn't running any scheduled tasks
>> with our Oracle 10 database.
>>
>> In a nutshell, the JPA config seems to assume that CURRENT_TIMESTAMP
>> is low precision
>> (i.e. hundredths of a second).
>
>> I've filed this as https://issues.apache.org/roller/browse/ROL-1760 ,
>> if you need more information just ask.
>
> I'll bet a bagel it's the same as this.
>
> https://issues.apache.org/roller/browse/ROL-1657
>
> I've been scratching my head about this myself the last couple of days.
> Thanks a _lot_ for the diagnosis.

Yes! This is wonderful. Perhaps it's time to get serious about a 4.0.1
release now.

- Dave

Re: scheduled tasks don't run on Oracle (or DB2, or probably PostgreSQL)

[Ron Peterson <rp...@mtholyoke.edu>]

2008-11-20_11:24:52-0500 Dick Davies <ra...@hellooperator.net>:
> Hi,
> 
> I've just spent a fun afternoon trying to figure out why roller 4.0.1
> isn't running any scheduled tasks
> with our Oracle 10 database.
> 
> In a nutshell, the JPA config seems to assume that CURRENT_TIMESTAMP
> is low precision
> (i.e. hundredths of a second).

> I've filed this as https://issues.apache.org/roller/browse/ROL-1760 ,
> if you need more information just ask.

I'll bet a bagel it's the same as this.

https://issues.apache.org/roller/browse/ROL-1657

I've been scratching my head about this myself the last couple of days.
Thanks a _lot_ for the diagnosis.

-- 
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
-
I wish my computer would do what I want it to do - not what I tell it to do.