Indexing fails with timestamp conversion MySQL error

This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Restricted functions in Atlassian Cloud apps, the contents of this article cannot be applied to Atlassian Cloud applications.

Problem

The index is not working and newer contents are not indexed, even with a reindex from scratch.

Reindexing the instance or flushing the queue produces the following error in Atlassian-confluence.log:

2016-10-19 16:52:04,121 ERROR [Caesium-1-2] [impl.schedule.caesium.JobRunnerWrapper] runJob Scheduled job com.atlassian.confluence.plugins.confluence-edge-index:flushEdgeIndexQueueJob#flushEdgeIndexQueue failed to run
com.atlassian.bonnie.LuceneException: com.atlassian.confluence.api.service.exceptions.ServiceException: Failed to process entries
	at com.atlassian.bonnie.LuceneConnection.withBatchUpdate(LuceneConnection.java:526)
.
.
Caused by: com.atlassian.confluence.api.service.exceptions.ServiceException: Failed to process entries
.
.
Caused by: org.springframework.dao.TransientDataAccessResourceException: Hibernate operation: Could not execute query; SQL []; Cannot convert value '0000-00-00 00:00:00' from column X to TIMESTAMP.; nested exception is java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column X to TIMESTAMP.
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

Cause

The issue happens due to zero dates values "0000-00-00 00:00:00" that cannot be converted to timestamp from the JDBC.

Diagnosis

Environment

  • MySQL 5.5 and 5.6

Diagnostic Steps

  • The error does not show which table contains the zero dates. 
  • Perform a MySQL database dump of Confluence schema into a .sql file and open with a text editor that can handle giant files. (Edit pad for example)
  • Find in the file if there are occurrences of "0000-00-00 00:00:00" dates. If it does, this KB is valid for you.

Resolution

Since we have zero dates in the database, we must correct them and set a valid date. We must also update the mysql-connector jdbc driver to the latest version in Confluence.

  • Shutdown Confluence
  • Update the mysql jdbc driver in Confluence installing the latest driver available for your MySQL database
  • Perform a MySQL database dump using the command mysqldump to a .sql file. This file must be in plain text.

  • Find and Replace all occurrences of "0000-00-00 00:00:00" with your current date. For example "2016-10-20 11:00:00". Be careful to modify only the zero dates.

  • Save the file and import into MySQL database, to another schema.

  • Go to Confluence.cfg.xml file and modify the database connection string, replacing the schema name with the new one. Save the file.
  • Go to Confluence data directory and remove all contents of the index folder.
  • Restart Confluence and reindex the instance from scratch.

 

Last modified on Oct 20, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.