Indexing fails with timestamp conversion MySQL error

問題

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 および 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.

ソリューション

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.

• 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.