[
https://issues.apache.org/jira/browse/ROL-2095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15055133#comment-15055133
]
David Johnson edited comment on ROL-2095 at 12/13/15 8:45 PM:
--------------------------------------------------------------
Yes, it would be nice to avoid use of {{useFastDateParsing=false}} by doing a
better migration.
Apparently just creating a new datetime(3) column and then using update to copy
timestamp format data into it does not work. I also tried using the MySQL
{{cast}} function to transform the data in a migration macro like this:
{code}
#macro(migrateMySqlDateTime $tableName $columnName)
-- migrate $tableName.$columnName from datetime to datetime(3)
alter table $tableName add column ${columnName}_new datetime(3) not null;
update $tableName set ${columnName}_new=cast($columnName as datetime(3)),
${tableName}.${columnName}=$columnName where ${columnName} is not null;
alter table $tableName change ${columnName} ${columnName}_old timestamp;
alter table $tableName change ${columnName}_new ${columnName} datetime(3);
#end
{code}
But that did not work either.
I think that new Roller users will not need the {{useFastDateParsing}} flag so
maybe this problem is not so bad. Only those with ancient databases need to
worry about it. We should mention it in the release notes of the next release.
was (Author: djohnson):
Yes, it would be nice to avoid use of {{useFastDateParsing=false}} by doing a
better migration.
Apparently just creating a new datetime(3) column and then using update to copy
timestamp format data into it does not work. I also tried using the MySQL
{{cast}} function to transform the data in a migration macro like this:
{{
#macro(migrateMySqlDateTime $tableName $columnName)
-- migrate $tableName.$columnName from datetime to datetime(3)
alter table $tableName add column ${columnName}_new datetime(3) not null;
update $tableName set ${columnName}_new=cast($columnName as datetime(3)),
${tableName}.${columnName}=$columnName where ${columnName} is not null;
alter table $tableName change ${columnName} ${columnName}_old timestamp;
alter table $tableName change ${columnName}_new ${columnName} datetime(3);
#end
}}
But that did not work either.
I think that new Roller users will not need the {{useFastDateParsing}} flag so
maybe this problem is not so bad. Only those with ancient databases need to
worry about it. We should mention it in the release notes of the next release.
> 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)
