Problem

• Newly added content is not picked up by Confluence Search.
• The Recently Updated list is not showing new entries.
• New blog posts are not shown or displayed in the Blog Posts Macro
• When Lucene Debug log is enabled it shows Confluence tried Fetching index entries added in the future, something like this:
  
  or this:
   

• If the following appears in the atlassian-confluence.log, proceed to Alternative Cause and Resolution sections:

  2014-05-04 11:50:28,863 ERROR [scheduler_Worker-3] [org.quartz.core.ErrorLogger] schedulerError Unable to notify JobListener(s) of Job to be executed: (Job will NOT be executed!). trigger= DEFAULT.IndexQueueFlusher job= DEFAULT.IndexQueueFlusher
  org.quartz.SchedulerException: JobListener 'ScheduledJobListener' threw exception: Could not open Hibernate Session for transaction; nested exception is net.sf.hibernate.exception.GenericJDBCException: Cannot open connection [See nested exception: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is net.sf.hibernate.exception.GenericJDBCException: Cannot open connection]
  	at org.quartz.core.QuartzScheduler.notifyJobListenersToBeExecuted(QuartzScheduler.java:1908)
  	at org.quartz.core.JobRunShell.notifyListenersBeginning(JobRunShell.java:335)
  	at org.quartz.core.JobRunShell.run(JobRunShell.java:173)
  	at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool$1.run(ConfluenceQuartzThreadPool.java:20)
  	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
  Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is net.sf.hibernate.exception.GenericJDBCException: Cannot open connection
  	at org.springframework.orm.hibernate.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473)
  	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374)
  	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263)
  	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  	at com.sun.proxy.$Proxy104.jobToBeExecuted(Unknown Source)
  	at org.quartz.core.QuartzScheduler.notifyJobListenersToBeExecuted(QuartzScheduler.java:1906)
  	... 4 more
  Caused by: net.sf.hibernate.exception.GenericJDBCException: Cannot open connection
  	at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
  	at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
  	at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
  	at net.sf.hibernate.impl.BatcherImpl.convert(BatcherImpl.java:328)
  	at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:295)
  	at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3388)
  	at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3348)
  	at org.springframework.orm.hibernate.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:422)
  	... 11 more
  Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
  	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
  	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
  	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
  	at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:35)
  	at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:292)
  	... 14 more
  Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
  	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
  	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
  	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
  	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
  	... 17 more

Diagnosis

Check the <confluence data folder>/index/.timestamp file to see if it is dated in the future.

or

Check the table indexqueueentries with:

select * from indexqueueentries;

And verify if there is any date future from todays date or time.

Cause

Confluence will fetch and sort the last modified date from every content and put them in the index queue. It compares the last modified date against the current date. If you have one or more item in your content that is future dated this will cause the operation to fail. See also  CONF-21720 - Getting issue details... STATUS .

First Alternative: There's not enough database connection pool to be utilised for automatic (scheduled) index flushing.

Second Alternative: The vm hosting Confluence is in Pause mode.

Workaround

Flush the index queue or rebuild the index, as described in Content Index Administration.

Resolution

To resolve this problem,

1. Shutdown Confluence and make a database backup.

2. Find the future dated content in the CONTENT table and change it:

   select * from CONTENT where lastmoddate like '2010-10-01%';
   

   In our example the content was future dated to 2010-10-01. You need to change the query to your own based on your debug log file.

   Another tip to easily figure out this is to use current date/time function of database SQL query. For example, in MySQL you could run this query:

   SELECT * FROM CONTENT WHERE LASTMODDATE > CONCAT(CURRENT_DATE, ' ', CURRENT_TIME);

3. Change the lastmoddate value in your database:

   update CONTENT
   set lastmoddate = '2009-10-01 09:08:13.253'
   where contentid = <contentid>;
   

   Important: change the lastmoddate value to one that is appropriate to your case.
   <contentid> is contentid value returned from the first query.

   Another tip to easily change all the affected contents' lastmoddate is to use the date/time function as described in the above info box. For example, in MySQL you could run something like this:

   UPDATE CONTENT
   SET LASTMODDATE = CONCAT(CURRENT_DATE, ' ', CURRENT_TIME)
   WHERE LASTMODDATE > CONCAT(CURRENT_DATE, ' ', CURRENT_TIME);

4. Clear your INDEXQUEUEENTRIES table:

   delete from INDEXQUEUEENTRIES;
   

5. If you have plugin or edge directory under your <confluence home>/index directory, please back them up first to somewhere safe. Then delete your <confluence home>/index directory.
6. Restart Confluence and rebuild your index.
7. After Confluence has completed building it's index to 100%, try to add a new page and check if that gets indexed / not - confirm that the <confluence data folder>/index/.timestamp file is created with the right time.