[ https://issues.apache.org/jira/browse/ROL-2063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14506958#comment-14506958 ]
Glen Mazza commented on ROL-2063: --------------------------------- 1. 5.2.0-SNAPSHOT will do, that or 5.1.3-SNAPSHOT, I've no strong preference. 2. That's fine, we can perhaps add a comment in the upgrade script that only those three databases will see changes, that comment will appear in the upgrade output. 3. If you can add a comment *here* about what manual things the pre-MySQL 5.6 folks need to do to fix their schema, if we get any questions on the mailing list we can refer them here. (Is is *not* necessary to code a fix to ROL-2063 for the pre-5.6 people, when they upgrade to 5.6 this problem will go away.) We can also leave a comment on the team blog about the new 5.6 limit. Again with 5.6 having been out since 2011, I don't see this as being much of a problem. > 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)