[jira] [Closed] (ROL-2063) NextLink appears in latest entry of permalink

["Kohei Nozaki (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/ROL-2063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kohei Nozaki closed ROL-2063.
-----------------------------
    Resolution: Fixed

Changes committed. from Roller 5.2.0, database creation script won't work on MySQL 5.5.x and prior.

Sending        app/pom.xml
Sending        app/src/main/java/org/apache/roller/weblogger/business/WeblogEntryManager.java
Sending        app/src/main/java/org/apache/roller/weblogger/business/jpa/JPAWeblogEntryManagerImpl.java
Sending        app/src/main/java/org/apache/roller/weblogger/business/startup/DatabaseInstaller.java
Sending        app/src/main/java/org/apache/roller/weblogger/ui/struts2/editor/EntryEdit.java
Adding         app/src/main/resources/sql/510-to-520-migration.vm
Sending        app/src/main/resources/sql/dbscripts.properties
Sending        app/src/main/resources/sql/macros.vm
Sending        app/src/main/resources/sql/mysql.properties
Sending        app/src/main/resources/sql/oracle.properties
Sending        app/src/main/resources/sql/postgresql.properties
Sending        assembly-release/pom.xml
Sending        assembly-release/sign-release.sh
Sending        it-selenium/pom.xml
Sending        pom.xml
Transmitting file data ...............
Committed revision 1680531.

> NextLink appears in latest entry of permalink
> ---------------------------------------------
>
>                 Key: ROL-2063
>                 URL: https://issues.apache.org/jira/browse/ROL-2063
>             Project: Apache Roller
>          Issue Type: Bug
>          Components: Data Model & JPA Backend
>    Affects Versions: 5.1.1
>         Environment: PostgreSQL 9.3.4
>            Reporter: Kohei Nozaki
>            Assignee: Roller Unassigned
>            Priority: Minor
>             Fix For: 5.1.2
>
>         Attachments: ROL-2063.patch, ROL-2063_alternative.patch, ROL-2063_automatic-schema-update.patch, ROL-2063_refresh.patch, ROL-2063_truncate.patch, ROL-2063_truncate_update1.patch, ROL-2063_update1.patch
>
>
> -With PostgreSQL-, NextLink shouldn't be appeared in latest entry of permalink, but sometimes it appears as the link to latest entry itself.
> The cause is precision of the column WEBLOGENTRY.PUBTIME in PostgreSQL. it is created with the type "timestamp(2) with time zone" as specified in postgresql.properties. the problem occurs as follows.
> 1. Someone posts an entry. following SQL was executed:
> 2015-02-23 17:07:26 JST LOG:  execute <unnamed>: INSERT INTO weblogentry (id, allowcomments, anchor, commentdays, content_src, content_type, creator, link, locale, pinnedtomain, plugins, pubtime, righttoleft, search_description, status, summary, text, title, updatetime, categoryid, websiteid) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21)
> 2015-02-23 17:07:26 JST DETAIL:  parameters: $1 = '71837e6f-735b-4c7e-b498-6b432accdcb2', $2 = 't', $3 = 'test', $4 = '0', $5 = NULL, $6 = NULL, $7 = 'kyle', $8 = NULL, $9 = 'en_US', $10 = 'f', $11 = '', $12 = '2015-02-23 17:07:26.548+09', $13 = 'f', $14 = '', $15 = 'PUBLISHED', $16 = '', $17 = 'test', $18 = 'test', $19 = '2015-02-23 17:07:26.551+09', $20 = 'fb216817-f8ea-46ec-aad9-35d9f222cac4', $21 = '78d076b8-19c2-4ee0-a766-2cdaf0b75d0d'
> 2. Someone visits the permalink of the entry just posted. JPAWeblogEntryManager#getNextEntry() is invoked and following SQL was executed and the query returns a row of current latest entry itself.
> 2015-02-23 17:07:34 JST LOG:  execute <unnamed>: SELECT id AS a1, allowcomments AS a2, anchor AS a3, commentdays AS a4, content_src AS a5, content_type AS a6, creator AS a7, link AS a8, lo
> cale AS a9, pinnedtomain AS a10, plugins AS a11, pubtime AS a12, righttoleft AS a13, search_description AS a14, status AS a15, summary AS a16, text AS a17, title AS a18, updatetime AS a19,
>  categoryid AS a20, websiteid AS a21 FROM weblogentry WHERE (((websiteid = $1) AND (status = $2)) AND (pubtime > $3)) ORDER BY pubtime ASC LIMIT $4 OFFSET $5
> 2015-02-23 17:07:34 JST DETAIL:  parameters: $1 = '78d076b8-19c2-4ee0-a766-2cdaf0b75d0d', $2 = 'PUBLISHED', $3 = '2015-02-23 17:07:26.548+09', $4 = '1', $5 = '0'
> It caused by the entry of PUBTIME is stored after rounded in the table as follows:
> roller2=# select title, pubtime FROM weblogentry WHERE (((websiteid = '78d076b8-19c2-4ee0-a766-2cdaf0b75d0d') AND (status = 'PUBLISHED')) AND (pubtime > '2015-02-23 17:07:26.548+09')) ORDER BY pubtime ASC LIMIT '1' OFFSET '0';
>  title |          pubtime          
> -------+---------------------------
>  test  | 2015-02-23 17:07:26.55+09
> (1 row)
> Roller compared '2015-02-23 17:07:26.548+09' against '2015-02-23 17:07:26.55+09' and recognized current latest entry itself as next entry while PUBTIME was rounded '.548' to '.55'. I don't know where Roller keeps '2015-02-23 17:07:26.548+09' but I guess that it saved in caches in Roller or EclipseLink because restarting of application server solves the problem temporarily.
> To solve the problem, I think changing precision of TIMESTAMP_SQL_TYPE_NULL and TIMESTAMP_SQL_TYPE in /resources/sql/postgresql.properties from 2 to 3 is reasonable because Roller uses milliseconds precision with java.util.Date, larger precision is unnecessary though.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)