-
Bug
-
Resolution: Fixed
-
Low
-
8.3.0, 8.5.3, 8.5.4, 8.7.2, 8.5.12
-
5
-
Severity 2 - Major
-
48
-
Issue Summary
Confluence fails to sync with Crowd with the error:
Caused by: org.postgresql.util.PSQLException: PreparedStatement can have at most 65,535 parameters. Please consider using arrays, or splitting the query in several ones, or using COPY.
This is reproducible on Data Center: yes
Steps to Reproduce
- Deploy Crowd with 35,000 users and 1 large group that has 35,000 members
- Deploy Confluence (Postgres) and sync with Crowd
- Confluence should now have the 35,000 users and 1 large group that has 35,000 members from Crowd
- Update Crowd so that the same large group above now has 10 members (instead of 35,000 members)
- Perform a sync in Confluence with Crowd
Expected Results
Confluence should successfully sync with Crowd.
Actual Results
Confluence fails to sync with Crowd 
atlassian-confluence-security.log will show the following message:
2024-02-01 21:39:32,521 INFO [Caesium-1-3] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache FULL synchronisation for directory [ 4128769 ] starting ... 2024-02-01 21:39:42,476 INFO [Caesium-1-3] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] removeUserMembershipsForGroup removed [ 0 ] user members from 'large-group' in [ 885ms ] 2024-02-01 21:39:42,478 ERROR [Caesium-1-3] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache Exception occured when performing full synchronization .. org.springframework.dao.DataIntegrityViolationException: could not prepare statement; SQL [select hibernatem0_.id as id1_35_0_, internalus1_.id as id1_39_1_, hibernatem0_.parent_id as parent_i2_35_0_, hibernatem0_.child_group_id as child_gr3_35_0_, hibernatem0_.child_user_id as child_us4_35_0_, internalus1_.user_name as user_nam2_39_1_, internalus1_.lower_user_name as lower_us3_39_1_, internalus1_.active as active4_39_1_, internalus1_.created_date as created_5_39_1_, internalus1_.updated_date as updated_6_39_1_, internalus1_.first_name as first_na7_39_1_, internalus1_.lower_first_name as lower_fi8_39_1_, internalus1_.last_name as last_nam9_39_1_, internalus1_.lower_last_name as lower_l10_39_1_, internalus1_.display_name as display11_39_1_, internalus1_.lower_display_name as lower_d12_39_1_, internalus1_.email_address as email_a13_39_1_, internalus1_.lower_email_address as lower_e14_39_1_, internalus1_.external_id as externa15_39_1_, internalus1_.directory_id as directo16_39_1_, internalus1_.credential as credent17_39_1_ from cwd_membership hibernatem0_ inner join cwd_user internalus1_ on hibernatem0_.child_user_id=internalus1_.id cross join cwd_group internalgr2_ where hibernatem0_.parent_id=internalgr2_.id and internalgr2_.directory_id=? and internalgr2_.lower_group_name=? and (internalus1_.lower_user_name in (? , ? , ? , ? , ? , ? , ? ... ? , ? , ?))]; nested exception is org.hibernate.exception.DataException: could not prepare statement at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:216) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:374) at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:320) at com.atlassian.confluence.impl.user.crowd.hibernate.HibernateMembershipDao.fetchExistingUserMemberships(HibernateMembershipDao.java:398) at com.atlassian.confluence.impl.user.crowd.hibernate.HibernateMembershipDao.removeUsersFromGroup(HibernateMembershipDao.java:379) at com.atlassian.confluence.impl.user.crowd.CachedCrowdMembershipDao.removeUsersFromGroup(CachedCrowdMembershipDao.java:146) .. at com.atlassian.crowd.directory.CachingDirectory.removeUsersFromGroup(CachingDirectory.java:145) at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.removeUserMembershipsFromGroupInBulk(DbCachingRemoteChangeOperations.java:756) at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.removeUserMembershipsForGroup(DbCachingRemoteChangeOperations.java:742) .. at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.syncUserMembersForGroup(DirectoryCacheImplUsingChangeOperations.java:114) at com.atlassian.crowd.directory.synchronisation.cache.AbstractCacheRefresher.synchroniseMemberships(AbstractCacheRefresher.java:211) at com.atlassian.crowd.directory.synchronisation.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:56) at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseAll(EventTokenChangedCacheRefresher.java:62) .. Caused by: org.postgresql.util.PSQLException: PreparedStatement can have at most 65,535 parameters. Please consider using arrays, or splitting the query in several ones, or using COPY. Given query has 131,074 parameters at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:102) at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:88) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1379) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1827) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:523) at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:327) at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176) ... 77 more
Workaround
The following is to be performed on the Confluence database.
- Identify the User directory ID for Crowd User Directory.
select * from cwd_directory;
- Take note of the directory ID for Crowd. e.g. 4128769 in our above example
- Identify the group name with the large number of users in the group from the atlassian-confluence-security.log file.
- e.g. In the above example, this is large-group:
2024-02-01 21:39:42,476 INFO [Caesium-1-3] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] removeUserMembershipsForGroup removed [ 0 ] user members from 'large-group' in [ 885ms ]
- e.g. In the above example, this is large-group:
- Shutdown Confluence
Backup the Confluence database- Run this SQL to verify the large count of users for the identifed group name
select count(*) from cwd_membership where parent_id in (select id from cwd_group where directory_id = <crowd_directory_id> and lower_group_name = '<group_name>'); e.g. select count(*) from cwd_membership where parent_id in (select id from cwd_group where directory_id = 4128769 and lower_group_name = 'large-group');
- Once a large count is confirmed, delete all the memberships on Confluence side for this specific group so that Confluence can re-sync in only the active memberships again from Crowd
delete from cwd_membership where parent_id in (select id from cwd_group where directory_id = <crowd_directory_id> and lower_group_name = '<group_name>'); e.g. delete from cwd_membership where parent_id in (select id from cwd_group where directory_id = 4128769 and lower_group_name = 'large-group');
- Start Confluence
- Re-attempt the sync with Crowd and it should succeed
[CONFSERVER-94244] Confluence sync with Crowd fails with the error "PreparedStatement can have at most 65,535 parameters"
We are now using Confluence Data Center 8.5.14 and this bug still exists. We encountered the issue yesterday, 11 September 2024. The workaround fixed the issue for now.
Upgraded from 8.5.6 to 8.5.7 on 30-Apr-2024 and our directory synchronization started failing due to this bug.
A fix for this issue is available in Confluence Server and Data Center 8.5.16.
Upgrade now or check out the Release Notes to see what other issues are resolved.