'index 'sp_comp_idx' exceeds the maximum length of 900 bytes' Using SQL Server

Still need help?

The Atlassian Community is here for you.

Ask the community

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:

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:

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.
(info) 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.

Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.