LDAP directory fails to synchronise with Error occurred while refreshing the cache for directory [ XXXXX ] ... Caused by: java.sql.SQLException: Field 'status' doesn't have a default value
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
LDAP directory fails to synchronize.
Environment
Jira 8.x.x
Diagnosis
- The following error appears in
atlassian-jira.log:
2022-03-20 11:24:55,451+0200 Caesium-1-3 ERROR ServiceRunner [c.a.crowd.directory.DbCachingDirectoryPoller] Error occurred while refreshing the cache for directory [ 10200 ].
com.querydsl.core.QueryException: Caught SQLException for insert into cwd_synchronisation_status (directory_id, sync_start, sync_status, status_parameters, id)
values (?, ?, ?, ?, ?)
...
Caused by: java.sql.SQLException: Field 'status' doesn't have a default value
...- Checking the definition of the table cwd_synchronisation_status, we see there are extra columns that should not be present (including the "status" column which makes the synchronization to fail):
`node_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`status` varchar(32) COLLATE utf8_bin NOT NULL,
`incremental_sync_error` mediumtext COLLATE utf8_bin,
`full_sync_error` mediumtext COLLATE utf8_bin,Example of query which can be used in PostgreSQL:
select column_name from information_schema.columns where table_name = 'cwd_synchronisation_status';Cause
The table cwd_synchronisation_status includes extra columns such as "status" which normally should not exist in a Jira instance.
The exact reasons are unknown, but one cause could be that another application was using at some point the same database (eg. Confluence).
Solution
Please take a full backup of your Jira database prior to proceeding with the following steps.
Please test the steps on a lower environment prior to performing them on your Production instance.
- Make sure no other application is using Jira's database.
- Stop Jira.
- Access the Jira database and DROP the "cwd_synchronisation_status" table.
- Confirm that the table is successfully dropped.
- Start Jira.
- Check the structure of the "cwd_synchronisation_status" table and make sure there is no "status" column.
- Perform a synchronisation of the Active Directory.