Loading...

Details

Type: Bug
Resolution: Fixed
Priority: High
Fix Version/s: 8.0.0
Affects Version/s: 7.11.0, 7.11.2
Component/s: Server - Upgrade Manager
Labels:

Support reference count:
82
Symptom Severity:
Severity 3 - Minor
UIS:
72
Bug Fix Policy:
View Atlassian Server bug fix policy

Description

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

Install any older Confluence before 7.11.0
Confluence 6.15.10 were used in this example
Create the Confluence database name with a hyphen in it such as conf-1510
Complete the setup wizard of the older Confluence so it is up and running properly
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.

Attachments

Issue Links

is related to

CONFSERVER-66547 Confluence upgrade to 7.12.3 fails because of new column missing in cwd_group table (MS SQL Server)

Closed

CONFSERVER-65769 Upgrade task for Denormalised Space Permissions fails due to multiple prefixes(MSSQL)

Closed

relates to

CONFSERVER-69507 Upgrading to Confluence 7.13.0 will fail when the database name involves a hyphen in MySQL Database

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(6 mentioned in)