Indexing fails with timestamp conversion MySQL error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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 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:
1
2
3
4
5
6
7
8
9
10
11
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.
Was this helpful?