'index 'sp_comp_idx' exceeds the maximum length of 900 bytes' Using SQL Server
Platform Notice: Data Center Only - This article only 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
Symptoms
When using Microsoft SQL Server System DSN (Data Source Name) to connect to the database, with a database url looks like jdbc:odbc:YourDSNName, Confluence throws an error like:
1
2
3
4
5
2010-07-07 21:21:48,695 ERROR [http-7190-3] [sf.hibernate.util.JDBCExceptionReporter] logExceptions [Microsoft]
[ODBC SQL Server Driver][SQL Server]Operation failed. The index entry of length 1020 bytes for the index
'sp_comp_idx' exceeds the maximum length of 900 bytes.
-- referer: http://localhost:8080/setup/setupadministrator-start.action | url:
/setup/setupadministrator.action | userName: anonymous | action: setupadministrator
The SQL Server trace log records a query like:
1
2
3
insert into SPACEPERMISSIONS (SPACEID, PERMTYPE, PERMGROUPNAME, PERMUSERNAME, CREATOR, CREATIONDATE,
LASTMODIFIER, LASTMODDATE, PERMID) values (NULL,'USECONFLUENCE','confluence-users',NULL,NULL,
'2010-07-14 20:12:39:150',NULL,'2010-07-14 20:12:39:150',65537);
Cause
When using System DSN, the database connection is handled using ODBC driver. This driver will add extra white space padding in String data type (nvarchar). The columns that are indexed by the 'sp_comp_idx' have a total size larger than the default 900 bytes.
ℹ️ The 'sp_comp_idx' index indexes 3 columns: PERMTYPE, PERMGROUPNAME, PERMUSERNAME each of nvarchar type. The size of the row indexed is 510 + 510 + 0 = 1020 bytes. The last column is null hence it contains 0 bytes.
Resolution
Do not use DSN data source when connecting to your database.
Do not use an ODBC driver. Use JTDS instead.
Was this helpful?