Details
-
Bug
-
Resolution: Won't Fix
-
Low
-
None
-
7.11.0
-
None
-
11
-
Severity 2 - Major
-
43
-
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
- Install a fresh 7.2.x version of Confluence, using SQL Server 2016
- 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.