Details
-
Suggestion
-
Resolution: Timed out
-
None
Description
NOTE: This suggestion is for Confluence Server. Using Confluence Cloud? See the corresponding suggestion.
When using SQL Server, currently the data type used for PERMTYPE, PERMGROUPNAME, PERMUSERNAME columns in the SPACEPERMISSIONS table is nvarchar(255).
This data type has a size of 510 bytes, which will cause a problem when creating an admin user for the first time during installation when using ODBC database driver:
com.atlassian.core.exception.InfrastructureException: Failed to create admin user at com.atlassian.confluence.setup.actions.SetupAdministrator.createAdmin(SetupAdministrator.java:102) caused by: com.atlassian.core.exception.InfrastructureException: com.atlassian.user.impl.RepositoryException: org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not insert: [com.atlassian.confluence.security.SpacePermission#65544]; bad SQL grammar []; nested exception is java.sql.SQLException: [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. at bucket.user.DefaultUserAccessor.addUser(DefaultUserAccessor.java:102)
The reason why it failed was due to the 3 columns indexed by sp_comp_idx: (PERMTYPE,PERMGROUPNAME,PERMUSERNAME) each has a size of 510 bytes.
When creating an admin for the first time, only two columns have values hence the size of the row that needs to be indexed is 510+510+0 = 1020 bytes, which is larger than the 900 bytes size limit set in SQL Server. Note that the third column is null hence it contains 0 bytes.
It appears that ODBC driver will try to add extra white space padding to the insert query:
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);
When JTDS driver is used, the installation works fine - because the insert query does not add the extra white space padding.
This will not happen if we have set the data type to something smaller so the index size will be smaller than 900 bytes.
Attachments
Issue Links
- relates to
-
CONFCLOUD-20446 Reduce data type size for PERMISSIONTYPE in SPACEPERMISSIONS
- Closed