Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-61145

Upgrade task for Denormalised Space Permissions on Confluence 7.11.0 fail on MSSQL

    • Icon: Bug Bug
    • Resolution: Won't Fix
    • Icon: Low Low
    • None
    • 7.11.0
    • None

      Issue Summary

      During a Confluence upgrade to Confluence 7.11.0, when using SQL Server 2016, upgrade task for Denormalised SpacePermissions (DenormalisedSpacePermissionsUpgradeTask) fails with bad SQL grammar error.

      Environment

      • Confluence 7.2.0
      • SQL Server 2016 (13.00.2164)

      Steps to Reproduce

      1. Install a fresh 7.2.x version of Confluence, using SQL Server 2016
      2. Upgrade to 7.11.0

      Expected Results

      Confluence upgrade is successful.

      Actual Results

      The below exception is thrown in the atlassian-confluence.log file:

      2021-02-10 03:29:28,864 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.SplitIndexUpgradeTask] validate Split index validation task started.
      ...
      2021-02-10 03:29:28,970 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.SplitIndexUpgradeTask] validate Split index validation completed.
      2021-02-10 03:31:30,745 WARN [Catalina-utility-1] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
        ->[null]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #1805033217)
      2021-02-10 03:31:31,009 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@867e592 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [CREATE OR ALTER FUNCTION dbo.space_function_for_denormalised_permissions()
      RETURNS BIT
      AS
      BEGIN
          RETURN 0
      END]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.
      com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@867e592 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [CREATE OR ALTER FUNCTION dbo.space_function_for_denormalised_permissions()
      RETURNS BIT
      AS
      BEGIN
          RETURN 0
      END]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.
      	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:262)
      	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:218)
      	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:164)
      	at com.atlassian.confluence.plugin.PluginFrameworkContextListener.launchUpgrades(PluginFrameworkContextListener.java:122)
      	at com.atlassian.confluence.plugin.PluginFrameworkContextListener.contextInitialized(PluginFrameworkContextListener.java:82)
      	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4716)
      	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5177)
      	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
      	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)
      	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)
      	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
      	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      	at java.lang.Thread.run(Thread.java:748)
      Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE OR ALTER FUNCTION dbo.space_function_for_denormalised_permissions()
      RETURNS BIT
      AS
      BEGIN
          RETURN 0
      END]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.
      	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
      	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
      	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388)
      	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
      	at com.atlassian.confluence.security.denormalisedpermissions.impl.setup.sqlserver.BaseSqlServerDdlHelper.createTriggersAndFunctions(BaseSqlServerDdlHelper.java:29)
      	at com.atlassian.confluence.security.denormalisedpermissions.impl.setup.SqlServerDenormalisedPermissionsDdlOperations.createSpaceTriggersAndFunctions(SqlServerDenormalisedPermissionsDdlOperations.java:19)
      	at com.atlassian.confluence.security.denormalisedpermissions.impl.setup.DenormalisedPermissionsDdlExecutor.createSpaceTriggersAndFunctions(DenormalisedPermissionsDdlExecutor.java:241)
      	at com.atlassian.confluence.security.denormalisedpermissions.impl.setup.DenormalisedPermissionsDdlExecutor.lambda$createSpaceDatabaseObjects$0(DenormalisedPermissionsDdlExecutor.java:64)
      	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
      	at com.atlassian.confluence.security.denormalisedpermissions.impl.setup.DenormalisedPermissionsDdlExecutor.createSpaceDatabaseObjects(DenormalisedPermissionsDdlExecutor.java:62)
      	at com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask.doUpgrade(DenormalisedSpacePermissionsUpgradeTask.java:56)
      	at com.atlassian.confluence.upgrade.AbstractUpgradeManager$UpgradeStep$3.execute(AbstractUpgradeManager.java:720)
      	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeTask(AbstractUpgradeManager.java:276)
      	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:256)
      	... 16 more
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.
      	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:260)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:857)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:757)
      	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7342)
      	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2688)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:734)
      	at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
      	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:409)
      	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
      	... 27 more
      2021-02-10 03:31:31,009 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1 errors were encountered during upgrade:
      2021-02-10 03:31:31,010 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: StatementCallback; bad SQL grammar [CREATE OR ALTER FUNCTION dbo.space_function_for_denormalised_permissions()
      RETURNS BIT
      AS
      BEGIN
          RETURN 0
      END]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.
      

      After the upgrade tasks are done and Confluence starts, the below screen is displayed when trying to reach Confluence UI.

      Workaround

      Upgrade the database to MSSQL 2016 SP1 (or SP2, SP1 is the minimum supported version) or to MSSQL 2017
      If upgrading to a supported database is not a short term solution then rolling back the upgrade is otherwise highly recommended.

          Form Name

            [CONFSERVER-61145] Upgrade task for Denormalised Space Permissions on Confluence 7.11.0 fail on MSSQL

            Dear Customers,

            After reviewing this issue we have confirmed that this problem exists because the database version used (SQL Server 2016) does not implement the full set of functionality that Confluence 7.11.x requires to run correctly.

            The required database functionality is available in SQL Server 2016 SP 1 and on. We do understand that this is a huge inconvenience but we would encourage you to either roll back your Confluence upgrade or consider upgrading to a supported database.

            We have reviewed and updated our Supported Databases Platforms to highlight this information there. https://confluence.atlassian.com/doc/supported-platforms-207488198.html#SupportedPlatforms-Databases

            Our apologies for any inconvenience this issue may have caused.

            The Confluence Team

            Michael Andreacchio added a comment - Dear Customers, After reviewing this issue we have confirmed that this problem exists because the database version used (SQL Server 2016) does not implement the full set of functionality that Confluence 7.11.x requires to run correctly. The required database functionality is available in SQL Server 2016 SP 1 and on. We do understand that this is a huge inconvenience but we would encourage you to either roll back your Confluence upgrade or consider upgrading to a supported database. We have reviewed and updated our Supported Databases Platforms to highlight this information there. https://confluence.atlassian.com/doc/supported-platforms-207488198.html#SupportedPlatforms-Databases Our apologies for any inconvenience this issue may have caused. The Confluence Team

            Also affected our environment when upgrading from 7.9.3 with SQL Server 2014.

            Deleted Account (Inactive) added a comment - Also affected our environment when upgrading from 7.9.3 with SQL Server 2014.

              acarvalho@atlassian.com Adilson Carvalho (Inactive)
              9f7de485df51 Basar Beykoz
              Affected customers:
              1 This affects my team
              Watchers:
              17 Start watching this issue

                Created:
                Updated:
                Resolved: