Database Constraint Checker has mismatched definitions for expected index definitions

This bug report relates to warnings logged by the Database Constraint Checker which was introduced in Confluence 5.7.

Cause

This particular warning occurs because there is a column included in an index that Confluence was not expecting.

This warning has been encountered on these columns:

- spacepermissions.sp_comp_idx
- spacepermissions.sp_creator_idx
- spacepermissions.sp_lastmodifier_idx
- cwd_app_dir_group_mapping.idx_app_dir_group_mapping
- cwd_app_dir_group_mapping.idx_app_dir_group_group_dir
- cwd_app_dir_group_mapping.idx_app_dir_group_app
- content_perm_set.cps_permtype_idx
- bandana.band_cont_key_idx
- cwd_application.idx_app_active
- cwd_application.idx_app_type
- cwd_user_credential_record.idx_user_cred_record_user_id
- contentproperties.content_prop_str_idx
- contentproperties.content_prop_long_idx
- contentproperties.content_prop_name_idx
- contentproperties.content_prop_date_idx
- trackbacklinks.tbl_lastmodifier_idx
- trackbacklinks.tbl_creator_idx
- cwd_membership.idx_mem_dir_child
- cwd_membership.idx_mem_dir_parent
- cwd_membership.idx_mem_dir_child_user
- cwd_membership.idx_mem_dir_parent_child
- content_perm.cp_creator_idx
- content_perm.cp_lastmodifier_idx
- content_label.cl_owner_idx

These warnings appear in the logs:

2014-12-14 19:07:49,320 WARN [main] [core.persistence.schema.StartupSchemaChecker] handleWarnings Index [sp_comp_idx] of table [spacepermissions] has mismatched definitions for expected [Index spacepermissions.sp_comp_idx on [permtype, permgroupname] non-unique] and actual [Index spacepermissions.sp_comp_idx on [permtype, permgroupname, permusername] non-unique]

Impact

A composite index is an index created on multiple columns. When all the columns in an index are specified in a query (and in the same order as the index), there is no impact. However, if a query makes use of only a few of the columns, the index will be ignored, and a sequential search will be performed. In large databases, this can be a performance hit.

In some cases, indexes are created on combinations of columns that do see realistic use. For example, space permissions are queried by group name, or by username - but never by both at the same time. Thus an index on both the permgroupname and permusername columns would not provide any improvement, and could actually cause a decrease in performance in certain scenarios.

Resolution

To resolve these warnings, you will need to change the schema of your database to specify that each column should be nullable. The way you would achieve this varies across each database system, so refer to the official documentation for your database.

To resolve these warnings, you should drop the mentioned index, and re-create it. The syntax will depend on your particular database variety, but generally the following should work:

Postgres, Oracle, MSSQL:

DROP INDEX <index_name>;

MySQL:

ALTER TABLE <table_name> DROP INDEX <index_name>;

Next, allow Confluence to recreate the index. Add -Datlassian.forceSchemaUpdate=true to your JVM Arguments and restart Confluence. On the next restart, Confluence will recreate any indexes that are missing.

Confluence will note that the index has been dropped, with a similar warning message:

2015-03-23 10:54:21,559 WARN [localhost-startStop-1] [core.persistence.schema.StartupSchemaChecker] handleWarnings Database schema is inconsistent with expectations - %s warnings, see below
2015-03-23 10:54:21,560 WARN [localhost-startStop-1] [core.persistence.schema.StartupSchemaChecker] handleWarnings Index [sp_comp_idx] of table [spacepermissions] is missing; expected [Index spacepermissions.sp_comp_idx on [permtype, permgroupname] non-unique]

If the flag is enabled, Confluence will generate a recovery file, and then repair the schema. You can use your database tools to verify that the specified index has been re-created.

Please feel free to contact Support for help with this.