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

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

    XMLWordPrintable

Details

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              acarvalho@atlassian.com Adilson Carvalho
              9f7de485df51 Basar Beykoz
              Votes:
              1 Vote for this issue
              Watchers:
              17 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: