roller-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kohei Nozaki (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (ROL-2063) NextLink appears in latest entry of permalink
Date Tue, 21 Apr 2015 12:30:59 GMT

    [ https://issues.apache.org/jira/browse/ROL-2063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14504867#comment-14504867
] 

Kohei Nozaki edited comment on ROL-2063 at 4/21/15 12:30 PM:
-------------------------------------------------------------

Glen, thank you for the feedback. I agree that extending precision is the best solution, if
we could update the schema.

If we take that way, looks like we need to introduce an another properties file for MySQL
5.6.x and later, due to its change of DATETIME rounding behavior. for MySQL 5.6.x we need
to specify precision explicitly because its the default datetime field (datetime(0)) will
be without decimal precision and cause the problem due to milliseconds will be rounded, but
it won't work with earlier MySQL as follows:

{noformat}
mysql> create table mytable(mycolumn datetime(3));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(3))' at line 1
mysql> create table mytable(mycolumn datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)
{noformat}



was (Author: xkylex):
Glen, thank you for the feedback. I agree that extending precision is the best solution, if
we could update the schema.

If we take thay way, looks like we need to introduce an another properties file for MySQL
5.6.x and later, due to its change of DATETIME rounding behavior. for MySQL 5.6.x we need
to specify precision explicitly because its the default datetime field (datetime(0)) will
be without decimal precision and cause the problem due to milliseconds will be rounded, but
it won't work with earlier MySQL as follows:

{noformat}
mysql> create table mytable(mycolumn datetime(3));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(3))' at line 1
mysql> create table mytable(mycolumn datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)
{noformat}


> 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_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)

Mime
View raw message