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.
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)
The issue happens due to zero dates values "0000-00-00 00:00:00" that cannot be converted to timestamp from the JDBC.
- MySQL 5.5 and 5.6
- 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.
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.