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

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

 

最終更新日 2018 年 11 月 2 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.