Indexing fails with timestamp conversion MySQL error

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

本ナレッジベースはアトラシアンの サーバー プラットフォーム向けに記載されたものです。Atlassian Cloud との機能の違いにより、本記事の内容を Atlassian Cloud アプリケーションに適用することはできません。

問題

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.