Uncategorized SQLException, incorrect syntax near '-' encountered during installation to MS SQL database in Azure cloud
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Database problems were encountered during installation.
While installing Confluence on a virtual server in the Azure cloud, and using an MS SQL database, errors were encountered. During the setup, the test connection to the database was successful and some of the db tables were created, but during the process the error below is observed.
Environment
- Confluence 7.11.2
- Azure
- MS SQL Server
Diagnosis
The following message will appear in the logs:
"StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER confluenceschema.denormalised_space_trigger ON SQLDB01.confluenceschema.SPACES AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; if (confluenceschema.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 '-'."Cause
The Confluence database has dashes ("-") in the database's name; this is a requirement of MS SQL Server to not allow dashes in the db's name (not a Confluence requirement).
Microsoft: Database Identifiers states what is allowed:
- Letters as defined in the Unicode Standard 3.2.
- Decimal numbers
- The at sign (@), dollar sign ($), number sign (#), or underscore (_) are allowed
Solution
Do not use dashes ("-") in the name of the Confluence database.