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.

Atlassian JIRA | Roy Hartono [Atlassian] | 7 years ago
tip
Click on the to mark the solution that helps you, Samebug will learn from it.
As a community member, you’ll be rewarded for you help.
  1. 0

    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_: {code} 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) {code} 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: {code} 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); {code} 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.

    Atlassian JIRA | 7 years ago | Roy Hartono [Atlassian]
    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.
  2. 0

    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_: {code} 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) {code} 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: {code} 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); {code} 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.

    Atlassian JIRA | 7 years ago | Roy Hartono [Atlassian]
    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.

    Root Cause Analysis

    1. 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()
    2. bucket.user
      DefaultUserAccessor.addUser
      1. bucket.user.DefaultUserAccessor.addUser(DefaultUserAccessor.java:102)
      1 frame