[
https://issues.apache.org/jira/browse/ROL-1760?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15030892#comment-15030892
]
Kohei Nozaki commented on ROL-1760:
-----------------------------------
If my understand is correct, This means databases that doesn't specify the
precision explicitly in the schema creation script (db2, derby, hsqldb and
mssql) are still affected by this problem...
Default precisions:
db2(timestamp): 6 (from
https://www-01.ibm.com/support/knowledgecenter/#!/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0005886.html?cp=SSEPGG_9.7.0%2F2-10-2-5-28
)
derby(timestamp): ? (I can see 3 from some experiment with {{ij}} but not
really sure)
hsqldb(timestamp): 6 (from http://hsqldb.org/doc/guide/sqlgeneral-chapt.html )
mssql(datetime): 3 (from
https://msdn.microsoft.com/ja-jp/library/ms187819(v=sql.120).aspx )
I think they should have explicit precision of {{3}} as well as other databases
to eliminate this problem completely.
> Scheduled tasks do not run on Oracle (or other DBs with high precision
> timestamps)
> ----------------------------------------------------------------------------------
>
> Key: ROL-1760
> URL: https://issues.apache.org/jira/browse/ROL-1760
> Project: Apache Roller
> Issue Type: Bug
> Components: Data Model & JPA Backend
> Affects Versions: 4.0
> Environment: Solaris 10, Oracle 10gR2, Roller 4.0.1
> Reporter: Dick Davies
> Assignee: David Johnson
> Fix For: 5.0
>
>
> Rollers JPA config seems to assume that CURRENT_TIMESTAMP is low precision
> (i.e. hundredths of a second).
> Specifically, I'm not sure the ORM config in TaskLock.orm.xml is correct.
> 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.
> Do you want a bug filed?
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
