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

      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.

            [CONFSERVER-65134] Upgrading to Confluence 7.11.0 and above will fail when database name involve hyphen in MS SQL Server

            A fix for this issue is available in Confluence Server and Data Center 8.0.0.
            Upgrade now or check out the Release Notes to see what other issues are resolved.

            James Whitehead added a comment - A fix for this issue is available in Confluence Server and Data Center 8.0.0. Upgrade now or check out the Release Notes to see what other issues are resolved.

            Hi All,

            Resolving this issue requires upgrading an underlying core library, which is a breaking change. Breaking changes are only introduced in major version releases, such as Confluence 8.0.0.

            As a result, this change cannot be backported to older versions of Confluence.

            Thanks,
            James Ponting
            Engineering Manager - Confluence Data Center

            James Ponting added a comment - Hi All, Resolving this issue requires upgrading an underlying core library, which is a breaking change. Breaking changes are only introduced in major version releases, such as Confluence 8.0.0. As a result, this change cannot be backported to older versions of Confluence. Thanks, James Ponting Engineering Manager - Confluence Data Center

            Hi 2ce37c8cf3b1 , thanks for your comment. We will include this information in our testing notes and will double check that it's working with other symbols as well when we prepare a fix for this problem.

            Irina Tiapchenko added a comment - Hi 2ce37c8cf3b1 , thanks for your comment. We will include this information in our testing notes and will double check that it's working with other symbols as well when we prepare a fix for this problem.

            @Irina, contrary to what you've state. Our DB did in fact have an underscore in it's name and although MS SQL Server is fine with underscores, the issue is more on the Confluence side of the upgrade process... not MS SQL Server alone!

            So, the underscore does cause issue! 

            Samuel Leung added a comment - @Irina, contrary to what you've state. Our DB did in fact have an underscore in it's name and although MS SQL Server is fine with underscores, the issue is more on the Confluence side of the upgrade process... not MS SQL Server alone! So, the underscore does cause issue! 

            Hi a684c3486161 ,

            This problem only occurs in cases when database name has symbols such as hyphen or dots (I haven't seen problem with underscore before as it's a valid symbol in db name for MS SQL Server). If your database name doesn't have such symbol you will never see this error when new column is added to any table in Confluence.

            Workaround 2 will only help with this particular column. We already know that it's not working for versions after 7.14 as one more column have been added to CONFVERSION table as well. To be able to use Workaround 2 properly you will need to modify suggested script each time when new column is added to any table and add it to the table in the script. This solution is not scalable, so we recommend to everyone to use Workaround 1.

            Renaming the database is much better option and a permanent fix for this problem.

            Irina Tiapchenko added a comment - Hi a684c3486161 , This problem only occurs in cases when database name has symbols such as hyphen or dots (I haven't seen problem with underscore before as it's a valid symbol in db name for MS SQL Server). If your database name doesn't have such symbol you will never see this error when new column is added to any table in Confluence. Workaround 2 will only help with this particular column. We already know that it's not working for versions after 7.14 as one more column have been added to CONFVERSION table as well. To be able to use Workaround 2 properly you will need to modify suggested script each time when new column is added to any table and add it to the table in the script. This solution is not scalable, so we recommend to everyone to use Workaround 1. Renaming the database is much better option and a permanent fix for this problem.

            Hi,

            This bit us today, however our database name only had an underscore, not a hyphen so that was peculiar...

            Anyway, I ran workaround 1 and workaround 2, one of them did the trick and we are running now.

            Will Option 2 cause issues in the future as suggested above, or is that only if the database name has an underscore (which it doesn't anymore)?

            IT Infrastructure added a comment - Hi, This bit us today, however our database name only had an underscore, not a hyphen so that was peculiar... Anyway, I ran workaround 1 and workaround 2, one of them did the trick and we are running now. Will Option 2 cause issues in the future as suggested above, or is that only if the database name has an underscore (which it doesn't anymore)?

            SamLe added a comment -

            Be warned, if you used Option 2; subsequent upgrades (eg. 7.13.0 to 7.15.0) will fail even though you've added the external_id column. 
            However, to prevent/resolve this issue, follow these steps:

            1. (If you've already attempted an upgrade, then unfortunately you'll need to start by rolling back your version and database to whatever stable version you had before attempting the upgrade.)
            2. Run the following query to manually create the FINALIZED column
              ALTER TABLE [your_database_name].[your_schema_name].CONFVERSION ADD FINALIZED CHAR (1) NOT NULL DEFAULT 'N';
              
            1. After creating the column, you need to insert a value to the column as the column does not allow null value
              update CONFVERSION SET FINALIZED = 'N';
              
            1. Run the following query to manually create the ORIG_BUILD_NUMBER column
              ALTER TABLE [your_database_name].[your_schema_name].CONFZDU ADD ORIG_BUILD_NUMBER INT NOT NULL DEFAULT 0;
              

              Based on tests, there isn't really a need to add the default value 0 to the ORIG_BUILD_NUMBER

            1. Once the these columns are created, you can attempt/re-attempt the upgrade.

            SamLe added a comment - Be warned, if you used Option 2; subsequent upgrades (eg. 7.13.0 to 7.15.0) will fail even though you've added the external_id column.  However, to prevent/resolve this issue, follow these steps: (If you've already attempted an upgrade, then unfortunately you'll need to start by rolling back your version and database to whatever stable version you had before attempting the upgrade.) Run the following query to manually create the  FINALIZED  column ALTER TABLE [your_database_name].[your_schema_name].CONFVERSION ADD FINALIZED CHAR (1) NOT NULL DEFAULT 'N' ; After creating the column, you need to insert a value to the column as the column does not allow null value update CONFVERSION SET FINALIZED = 'N' ; Run the following query to manually create the  ORIG_BUILD_NUMBER  column ALTER TABLE [your_database_name].[your_schema_name].CONFZDU ADD ORIG_BUILD_NUMBER INT NOT NULL DEFAULT 0; Based on tests, there isn't really a need to add the default value 0 to the ORIG_BUILD_NUMBER Once the these columns are created, you can attempt/re-attempt the upgrade.

            Hi 0b69573696ac ,

            Yes, this workaround is approved by Atlassian.

            Irina Tiapchenko added a comment - Hi 0b69573696ac , Yes, this workaround is approved by Atlassian.

            Hi,

            Problem that trigger couldn't be created during upgrade was fixed in CONFSERVER-65769 and this fix is available in versions 7.12.3 and higher.

            But unfortunately even though we have fixed problem with trigger creation there is a problem with new column in cwd_group table which wasn't fixed yet - CONFSERVER-66547.

            This second problem couldn't be fixed right now because of the existing bug in Hibernate core - HHH-12106. This bug was fixed in Hibernate and Confluence team is actively working on Hibernate upgrade. So once Hibernate is upgraded we expect that CONFSERVER-66547 should also be solved.

            Meanwhile you could use one of those two workarounds to be able to proceed with Confluence upgrade:

            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.

            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) 

             

            Irina Tiapchenko added a comment - Hi, Problem that trigger couldn't be created during upgrade was fixed in CONFSERVER-65769 and this fix is available in versions 7.12.3 and higher. But unfortunately even though we have fixed problem with trigger creation there is a problem with new column in cwd_group table which wasn't fixed yet - CONFSERVER-66547 . This second problem couldn't be fixed right now because of the existing bug in Hibernate core - HHH-12106 . This bug was fixed in Hibernate and Confluence team is actively working on Hibernate upgrade. So once Hibernate is upgraded we expect that CONFSERVER-66547 should also be solved. Meanwhile you could use one of those two workarounds to be able to proceed with Confluence upgrade: 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. 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)   

            Does this column adding is fully validated and approved workaround by Atlassian ?

            In our case we cannot rename the database.

            serge calderara added a comment - Does this column adding is fully validated and approved workaround by Atlassian ? In our case we cannot rename the database.

              bhyde Bradley Hyde
              btan@atlassian.com Damien Tan
              Affected customers:
              14 This affects my team
              Watchers:
              34 Start watching this issue

                Created:
                Updated:
                Resolved: