LDAP Filter Length cannot be greater than 4000 characters

Summary

While performing an LDAP user directory setup in Confluence, the LDAP Filter length (Group or User) can't be configured more than 4000 characters.

Environment

Confluence integrated with any External Directory

Diagnosis

When the LDAP filter length goes beyond the existing limit of 4000 characters, the below errors can be seen in the logs while configuring the user directory:

Cause

• The cwd_directory_attribute table column "attribute_value" has a character limit of 4000.

• Whenever a new Directory is setup in Confluence, the corresponding entry is created in the cwd_directory_attribute table.

• If the attribute_value is more than 4000 characters, it results in an SQL exception

Solution

There are two options to resolve the issue

• Option 1: Reduce the size of the LDAP filter, by making changes to the LDAP directory structure

• Option 2: Change the attribute_value column data type from nvarchar (4000) to TEXT . (applicable for MYSQL Database)

  1. Stop the Confluence.

  2. Check the table status first.

     1 select * from cwd_directory_attribute; 

  3. Create the backup of that table:

     1 SELECT * INTO cwd_directory_attribute_backup FROM cwd_directory_attribute;

  4. Check the backup table records:

     1 select * from cwd_directory_attribute_backup;

  5. Drop the original table:

     1 drop table cwd_directory_attribute; 

  6. Create the table again with the new Datatype:

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[cwd_directory_attribute](     [directory_id] [numeric](19, 0) NOT NULL,     [attribute_name] [nvarchar](255) NOT NULL,     [attribute_value] [text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING ON GO ALTER TABLE [dbo].[cwd_directory_attribute] ADD PRIMARY KEY CLUSTERED  (     [directory_id] ASC,     [attribute_name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE [dbo].[cwd_directory_attribute]  WITH CHECK ADD  CONSTRAINT [fk_directory_attribute] FOREIGN KEY([directory_id]) REFERENCES [dbo].[cwd_directory] ([id]) GO ALTER TABLE [dbo].[cwd_directory_attribute] CHECK CONSTRAINT [fk_directory_attribute] GO

  7. Reinsert the table records from the backup table:

     1 INSERT INTO cwd_directory_attribute SELECT * FROM cwd_directory_attribute_backup;

  8. Drop the backup table:

     1 drop table cwd_directory_attribute_backup

  9. Start the Confluence.

  10. Edit the user directory with a longer string.

  11. Test the user directory.

      ℹ️ The above SQLs are for the Microsoft SQL database. If you use a different database, please work with your DB admin to customize the queries accordingly