LDAP directory fails to synchronise with Error occurred while refreshing the cache for directory Caused by: java.sql.SQLException: Field 'status' doesn't have a default value
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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 columnsthat 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.
StopJira.
Access the Jira database andDROPthe "cwd_synchronisation_status" table.
Confirmthat the table is successfully dropped.
StartJira.
Check the structure of the "cwd_synchronisation_status" table and make sure there is no "status" column.
Perform asynchronisationof the Active Directory.
Was this helpful?