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

Upgrading to Confluence 7.11.0 and above will fail when database name involve hyphen in MS SQL Server

XMLWordPrintable

      We don't plan to backport the fix for this bug to earlier Long Term Support versions

      The fix for this bug isn't suitable for backporting to a bug fix release for any previous LTS versions. This is often because the fix is considered too high risk to implement in an older version.

      The fix for this issue will be included in future Long Term Support versions.

      Issue Summary

      Upgrading to Confluence 7.11.0 and above will fail when database name involve hyphen in MS SQL Server

      Steps to Reproduce

      1. Install any older Confluence before 7.11.0
        Confluence 6.15.10 were used in this example
      2. Create the Confluence database name with a hyphen in it such as conf-1510
      3. Complete the setup wizard of the older Confluence so it is up and running properly
      4. Stop Confluence and upgrade to Confluence 7.11.0 (or any later version)

      Expected Results

      Confluence is upgraded successfully

      Actual Results

      Confluence upgrade fails with the error message below in atlassian-confluence.log:

      2021-05-06 10:07:48,158 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@4b048de6 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER dbo.denormalised_space_trigger
ON conf-1510.dbo.SPACES
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    if (dbo.space_function_for_denormalised_permissions() = 1)
        BEGIN
            INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from inserted;
            INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from deleted;
        END
END
]; SQL state [S0001]; error code [102]; Incorrect syntax near '-'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@4b048de6 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER dbo.denormalised_space_trigger
ON conf-1510.dbo.SPACES
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    if (dbo.space_function_for_denormalised_permissions() = 1)
        BEGIN
            INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from inserted;
            INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from deleted;
        END
END
]; SQL state [S0001]; error code [102]; Incorrect syntax near '-'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
	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.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER dbo.denormalised_space_trigger
ON conf-1510.dbo.SPACES
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    if (dbo.space_function_for_denormalised_permissions() = 1)
        BEGIN
            INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from inserted;
            INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from deleted;
        END
END
]; SQL state [S0001]; error code [102]; Incorrect syntax near '-'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	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:31)
	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)
	... 15 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
	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)
	... 26 more

      Note

      The atlassian-confluence.log will also be spammed rapidly with error messages like below and the main error above can easily get flooded away:

      2021-05-06 11:06:55,891 ERROR [perm-delta-cache-receiver] [gatekeeper.evaluator.cache.CacheUpdateReceiver] error Failed to initialize evaluator cache
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:228)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:388)
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'external_id'.
...

      For upgrading from pre-7.14 to recent ones, we can also expect following errors.

      2022-01-12 14:57:40,316 ERROR [Catalina-utility-1] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Invalid column name 'FINALIZED'.
.
2022-01-12 14:57:40,343 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.MigrateTrashDateUpgradeTask@48b3ba1f failed during the UPGRADE phase due to: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.MigrateTrashDateUpgradeTask@48b3ba1f failed during the UPGRADE phase due to: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'FINALIZED'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:260)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)

      This is due to missing column name in table CONFVERSION which is added from version 7.14

      Workaround

      Option 1

      Update the database/schema name and remove any periods/hyphens from the name and update the confluence.cfg.xml file with the new name. The following sample SQL, can be used for this.

      ALTER DATABASE "conf-7130" MODIFY NAME = "conf7130";

      If need be, ensure the user's access to this database again.

      Giving user 'confuser' full access to the database
      USE conf7130;GO
EXEC sp_addrolemember N'db_owner', N'confuser';

      Option 2

      Add new column external_id to the cwd_group table before starting upgrade (it's important to put your database and schema name in square brackets []):

      alter table [your_database_name].[your_schema_name].cwd_group add external_id nvarchar(255) 

      Note that this option is not scalable as it only helps to workaround one specific scenario where a new column is added into the cwd_group table. While Confluence upgrades don't often introduce additional columns in the database table but it can still happen when a new feature requires it.

      We recommend going with Option 1 as a better workaround instead. In the case if you've opted for Option 2 in the past, you can still go with Option 1 to rename the database and remove the hyphen in the database name to prevent this issue from happening again.

              bhyde Bradley Hyde
              btan@atlassian.com Damien Tan
              Votes:
              14 Vote for this issue
              Watchers:
              34 Start watching this issue

                Created:
                Updated:
                Resolved: