Indexing fails with timestamp conversion MySQL error


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


プラットフォームについて: サーバーと Data Center のみ。この記事は、サーバーおよび Data Center プラットフォームのアトラシアン製品にのみ適用されます。


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


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


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