Failed to generate an XML backup due to Timestamp SQL Exception
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
The following appears in the atlassian-jira.log
2015-08-10 12:35:41,605 http-bio-8080-exec-10 ERROR xxxx xxxx xxx 217.110.62.222,127.0.0.1 /secure/admin/XmlBackup.jspa [web.action.util.XmlBackup] Exception occurred backing up: org.ofbiz.core.util.GeneralRuntimeException: Error creating GenericValue (SQL Exception while getting value: (Value '5969216153r
...can not be represented as java.sql.Timestamp))
at org.ofbiz.core.entity.EntityListIterator.next(EntityListIterator.java:253)
at com.atlassian.jira.ofbiz.DefaultOfBizListIterator.next(DefaultOfBizListIterator.java:140)
at com.atlassian.jira.ofbiz.WrappingOfBizListIterator.next(WrappingOfBizListIterator.java:80)
Diagnosis
Environment
- Applicable for MySQL
Cause
Some data are returning '0000-00-00 00:00' value but the database doesn't support it and throws this error message: "can not be represented as java.sql.Timestamp".
Resolution
Recommend to append the zeroDateTimeBehavior=convertToNull parameter into the JIRA database URL (via $JIRA_HOME/dbconfig.xml):
For example, your current JDBC URL is:
jdbc:mysql://localhost:3306/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDBIf you add the parameter, your JIRA JDBC URL will be like:
jdbc:mysql://localhost:3306/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB&zeroDateTimeBehavior=convertToNull