Adding a long User Object Filter in a User Directory configuration results in "System Error"
Platform Notice: Data Center - This article applies to Atlassian products 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
When configuring a User Directory in Confluence, under User Schema Settings, if you enter a User Object Filter string longer than 4000 characters:
When trying to Save you get the error message: Oops - and error has occurred. System Error. Referer URL: https://<confluence-base-url>/plugins/servlet/embedded-crowd/configure/ldap/?directoryId=xxxx
Environment
Confluence 8.5.14 with MS SQL Server
Diagnosis
The following error is shown in the logs:
2024-10-14 08:44:50,023 ERROR [https-jsse-nio2-443-exec-3] [atlassian.plugin.servlet.ServletModuleContainerServlet] service Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement
-- url: /plugins/servlet/embedded-crowd/configure/ldap/ | userName: localadmin | referer: https://<confluence-base-url>/plugins/servlet/embedded-crowd/configure/ldap/?directoryId=xxxx | traceId: 31d215599332f95e
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement
...
Caused by: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement
...
Caused by: org.hibernate.exception.DataException: could not execute statement
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:473)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
... 435 more
Running SQL query:
SELECT column_name, data_type, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'cwd_directory_attribute';Shows a limit of 4000 characters for column attribute_value on table cwd_directory_attribute:
column_name data_type character_maximum_length
directory_id numeric NULL
attribute_name nvarchar 255
attribute_value nvarchar 4000Cause
The default character limit of 4000 is not big enough for some use cases.
This issue is tracked in bug:
CONFSERVER-98712 - Getting issue details... STATUS
Solution
Remove the 4000 characters limit with this SQL:
ALTER TABLE cwd_directory_attribute ALTER COLUMN attribute_value nvarchar (max);