Unreconciled Content Scheduled jobs shows Table 'confluence.contentproperties' doesn't exist

Environment

• Only happens in Linux and not Windows
• Tested with MySQL
• Oracle DB

Steps to reproduce

1. Install Confluence 5.10 with MySQL
2. Go to Confluence Administration >> Scheduled Jobs and run the "Synchronise Unreconciled Content".
3. You'll find these errors in the atlassian-confluence.log

   2016-06-21 00:00:00,114 ERROR [scheduler_Worker-3] [plugins.synchrony.job.ContentReconciliationJob] lambda$getDraftContentRequiringReconciliation$3 Error querying for content marked as requiring content reconciliation
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence.contentproperties' doesn't exist
   	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
   	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
   
   2016-06-28 00:00:00,231 ERROR [scheduler_Worker-6] [org.quartz.core.JobRunShell] run Job com.atlassian.confluence.plugins.confluence-collaborative-editor-plugin.contentReconciliationJob threw an unhandled Exception: 
   java.lang.NullPointerException
   	at com.atlassian.confluence.plugins.synchrony.job.ContentReconciliationJob.execute(ContentReconciliationJob.java:63)
   	at com.atlassian.confluence.plugin.descriptor.JobModuleDescriptor$DelegatingPluginJob.lambda$execute$0(JobModuleDescriptor.java:112)
   	at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:82)
   	at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:72)
   	at com.atlassian.confluence.plugin.descriptor.JobModuleDescriptor$DelegatingPluginJob.execute(JobModuleDescriptor.java:111)
   	at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
   	at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool.lambda$runInThread$0(ConfluenceQuartzThreadPool.java:16)
   	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
   
   

Cause

The ContentReconciliationJob.java which involves com.atlassian.confluence.plugins.confluence-collaborative-editor-plugin.contentReconciliationJob
code shows :

58:    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
59:
60:
61:
62:        List<Long> draftIds = getDraftContentRequiringReconciliation();
63:        log.info("Found {} items requiring content reconciliation", draftIds.size());
64:
65:        ThreadLocalCache.init(); //Needed for permissionManager.withExemption to work through all calls, including generating the JWT token without a user
66:
67:        draftIds.stream().forEach(draftId -> {
68:            permissionManager.withExemption(() ->
69:                   transactionTemplate.execute(() -> performReconciliationOnContent(draftId))
70:           );
71:
72:            ThreadLocalCache.flush();
73:        });
74:
75:        ThreadLocalCache.dispose();
76:
77:        log.info("Content reconciliation job finished");
78:    }

The problematic line can be found below where it try to find the draft directly from the contentproperties table:

80:    private List<Long> getDraftContentRequiringReconciliation() {
81:        final TransactionalExecutor transactionExecutor = transactionalExecutorFactory
82:                .createExecutor()
83:               .readOnly()
84:                .newTransaction();
85:
86:        return transactionExecutor.execute(connection -> {
87:            try {
88:                PreparedStatement preparedStatement = connection.prepareStatement("" +
89:                        "SELECT DISTINCT content.contentid, content.lastmoddate " +
90:                        "FROM contentproperties properties, content content " +
91:                        "WHERE content.contentid = properties.contentid " +
92:                        "AND properties.propertyname = 'sync-rev-source' " +
93:                        "AND properties.stringval = 'fallback' " +
94:                        "AND content.content_status = 'draft' " +
95:                        "ORDER BY content.lastmoddate " +96                        "LIMIT 1000");
96:                ResultSet resultSet = preparedStatement.executeQuery();
97:
98:               List<Long> draftIds = new ArrayList<>();
99:                while (resultSet.next()) {
100:                    draftIds.add(resultSet.getLong(1));
101:                }
102:                return draftIds;
103:            } catch (SQLException e) {
104:                log.error("Error querying for content marked as requiring content reconciliation", e);
105:                return null;
106:            }
107:        });
108:    }

The MySQL table in Linux is case sensitive, hence the CONTENTPROPERTIES table is in capital casing but the Reconciliation Job is querying the lower case table of contentproperties.

This error does not show in Windows because it is case insensitive.

Workaround

1. Disable the Synchronise Unreconciled Content Scheduled Jobs
2. Then, go to Confluence Administration >> Manage add-ons and search for Confluence Collaborative Editor Plugin under System filters and disable it.