[jira] [Comment Edited] (ROL-2095) Roller 510 -> 520 migration is incomplete for TIMESTAMPS

["David Johnson (JIRA)" <ji...@apache.org>]

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

David Johnson edited comment on ROL-2095 at 12/13/15 12:28 AM:
---------------------------------------------------------------

I did some investigation of this and I believe that Roller has been using datetime for MySQL since at least 2009.  My blog uses some timestamp types but that is probably because it is very very old (the database was created in 2002).

I used the SQL/DDL code below to migrate my two timestamp fields, but Roller still failed to work properly, but when I added the useFastDateParsing Roller started working for me.

alter table weblogentry add column updatetime_new datetime(3) not null;
update weblogentry set updatetime_new=updatetime where updatetime is not null;
alter table weblogentry change updatetime updatetime_old timestamp;
alter table weblogentry change updatetime_new updatetime datetime(3);

alter table weblog add column datecreated_new datetime(3) not null;
update weblog set datecreated_new=datecreated where datecreated is not null;
alter table weblog change datecreated datecreated_old timestamp;
alter table weblog change datecreated_new datecreated datetime(3);

I need to do more testing, but I think we will need to ask MySQL users to add that useFastDateParsing=false flag so that MySQL works with the new datetime(3) instead of datetime fields.



was (Author: djohnson):
I did some investigation of this and I believe that Roller has been using datetime for MySQL since at least 2009.  My blog uses some timestamp types but that is probably because it is very very old (the database was created in 2002).

I used the SQL/DDL code below to migrate my two timestamp fields, but Roller still failed to work properly, but when I added the useFastDateParsing Roller started working for me.

alter table weblogentry add column updatetime_new datetime(3) not null;
update weblogentry set updatetime_new=updatetime where updatetime is not null;
alter table weblogentry change updatetime updatetime_old timestamp;
alter table weblogentry change updatetime_new updatetime datetime(3);

alter table weblog add column datecreated_new datetime(3) not null;
update weblog set datecreated_new=datecreated where datecreated is not null;
alter table weblog change datecreated datecreated_old timestamp;
alter table weblog change datecreated_new datecreated datetime(3);


> Roller 510 -> 520 migration is incomplete for TIMESTAMPS
> --------------------------------------------------------
>
>                 Key: ROL-2095
>                 URL: https://issues.apache.org/jira/browse/ROL-2095
>             Project: Apache Roller
>          Issue Type: Bug
>            Reporter: David Johnson
>            Assignee: Roller Unassigned
>
> In SVN commit 1680531 we added to mysql.properties these two lines:
> TIMESTAMP_SQL_TYPE_NULL=datetime(3) NULL
> TIMESTAMP_SQL_TYPE=datetime(3)
> Those lines effectively changed the type of all timestamp columns in Roller from timestamp to datetime(3), but we offer no migration to convert timestamp data to datatime(3) format.
> This will cause Roller to fail to work with errors like this:
> [EL Warning]: 2015-12-06 16:09:42.61--UnitOfWork(659709738)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLException: Cannot convert value '2014-11-27 10:25:01.000' from column 8 to TIMESTAMP.
> Error Code: 0
> Call: SELECT id, about, isactive, allowcomments, analyticscode, blacklist, creator, datecreated, defaultallowcomments, defaultcommentdays, defaultplugins, editorpage, editortheme, emailaddress, emailcomments, enablebloggerapi, enablemultilang, displaycnt, handle, icon, lastmodified, locale, commentmod, name, showalllangs, tagline, timeZone, visible, bloggercatid FROM weblog WHERE (handle = ?)
> 	bind => [1 parameter bound]
> Query: ReadAllQuery(name="Weblog.getByHandle" referenceClass=Weblog sql="SELECT id, about, isactive, allowcomments, analyticscode, blacklist, creator, datecreated, defaultallowcomments, defaultcommentdays, defaultplugins, editorpage, editortheme, emailaddress, emailcomments, enablebloggerapi, enablemultilang, displaycnt, handle, icon, lastmodified, locale, commentmod, name, showalllangs, tagline, timeZone, visible, bloggercatid FROM weblog WHERE (handle = ?)")
> We should either revert the datetime(3) change or add a migration for all fields effected.



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