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 knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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);