search

dnnsoftware / Dnn.Platform

DNN (formerly DotNetNuke) is the leading open source web content management platform (CMS) in the Microsoft ecosystem.
https://dnncommunity.org/
MIT License
1.02k stars 749 forks source link

[Bug]: Unable to Install DNN 9.13.3 on Azure SQL #6028

Open mitchelsellers opened 4 months ago

mitchelsellers commented 4 months ago

Is there an existing issue for this?

What happened?

Attempting to Install DNN 9.13.3 into a clean Azure SQL database is failing starting on, or about, May 1st, 2024.

This has worked previously, without any issues, however, now it is not possible to install as there are abundant errors (See below)

Suspect issues with SQL Server compatibility with 2024 product releases on Azure.

Steps to reproduce?

  1. Setup a new database in SQL Azure
  2. Attempt to install DNN using the blank Azure database
  3. BOOM!

Current Behavior

Multiple SQL Failures at about 20% completion and no success

Expected Behavior

DNN should install and work

Relevant log output

The following error logs happen, no install is successful.

`
2024-05-08 04:04:17.697+00:00 [REDACTED][D:7][T:13][ERROR] DotNetNuke.Data.SqlDataProvider - System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@RemoveIncompatibleSchema".
Must declare the scalar variable "@Feature".
Must declare the scalar variable "@IsCurrentVersion".
Must declare the scalar variable "@Feature".
Must declare the scalar variable "@Feature".
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'EXEC'.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@Feature".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@Feature".
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@UserId".
Must declare the scalar variable "@UserId".
Must declare the scalar variable "@UserId".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@ApplicationId".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@NumTablesDeletedFrom".
The variable name '@TranStarted' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@UserName".
Must declare the scalar variable "@TablesToDeleteFrom".
Incorrect syntax near ')'.
Must declare the scalar variable "@NumTablesDeletedFrom".
Must declare the scalar variable "@TablesToDeleteFrom".
Incorrect syntax near ')'.
Must declare the scalar variable "@NumTablesDeletedFrom".
Must declare the scalar variable "@TablesToDeleteFrom".
Incorrect syntax near ')'.
Must declare the scalar variable "@NumTablesDeletedFrom".
Must declare the scalar variable "@TablesToDeleteFrom".
Incorrect syntax near ')'.
Must declare the scalar variable "@NumTablesDeletedFrom".
Must declare the scalar variable "@TablesToDeleteFrom".
Incorrect syntax near ')'.
Must declare the scalar variable "@NumTablesDeletedFrom".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@NumTablesDeletedFrom".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'ALTER'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near 'GO'.
The variable name '@command' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near the keyword 'IF'.
The variable name '@command' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near the keyword 'PRINT'.
Incorrect syntax near '----------------------------------------'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query)
   at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec)
ClientConnectionId:a3d7bbd1-fed2-4f73-8039-3961ec1b2d35
Error Number:102,State:1,Class:15
ClientConnectionId before routing:11d20368-d93e-4dbe-ba4c-ee582ab32fdf
Routing Destination:REDACTED,11001
2024-05-08 04:04:17.822+00:00 [REDACTED][D:7][T:13][ERROR] DotNetNuke.Data.SqlDataProvider - System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@ApplicationName".
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@UserName".
Must declare the scalar variable "@UserId".
Must declare the scalar variable "@UserName".
Must declare the scalar variable "@UserId".
Must declare the scalar variable "@UserId".
Must declare the scalar variable "@UniqueEmail".
Must declare the scalar variable "@Email".
Must declare the scalar variable "@CreateDate".
Must declare the scalar variable "@UserId".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@UpdateLastActivity".
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@CurrentTimeUtc".
Incorrect syntax near the keyword 'ELSE'.
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@UpdateLastActivity".
Must declare the scalar variable "@CurrentTimeUtc".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@Email".
Must declare the scalar variable "@ApplicationName".
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@IsLockedOut' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@UpdateLastLoginActivityDate".
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@CurrentTimeUtc".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@IsApproved' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@IsLockedOut' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@LastLockoutDate' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptWindowStart' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptWindowStart' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@ErrorCode' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@TranStarted' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@ApplicationName".
Must declare the scalar variable "@IsPasswordCorrect".
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@CurrentTimeUtc".
Incorrect syntax near the keyword 'ELSE'.
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@MaxInvalidPasswordAttempts".
Must declare the scalar variable "@CurrentTimeUtc".
Incorrect syntax near the keyword 'ELSE'.
Must declare the scalar variable "@UpdateLastLoginActivityDate".
A RETURN statement with a return value cannot be used in this context.
The label 'Cleanup' has already been declared. Label names must be unique within a query batch or stored procedure.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PasswordFormat' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@Password' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@IsLockedOut' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@LastLockoutDate' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptWindowStart' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptWindowStart' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@ErrorCode' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@TranStarted' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@ApplicationName".
Must declare the scalar variable "@PasswordAnswer".
Must declare the scalar variable "@PasswordAnswer".
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@CurrentTimeUtc".
Incorrect syntax near the keyword 'ELSE'.
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@MaxInvalidPasswordAttempts".
Must declare the scalar variable "@CurrentTimeUtc".
Incorrect syntax near the keyword 'ELSE'.
A RETURN statement with a return value cannot be used in this context.
The label 'Cleanup' has already been declared. Label names must be unique within a query batch or stored procedure.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@UserName".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@NewPassword".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@IsLockedOut' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@LastLockoutDate' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAttemptWindowStart' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptCount' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@FailedPasswordAnswerAttemptWindowStart' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@ErrorCode' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@TranStarted' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@UserName".
Must declare the scalar variable "@NewPassword".
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@CurrentTimeUtc".
Incorrect syntax near the keyword 'ELSE'.
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@MaxInvalidPasswordAttempts".
Must declare the scalar variable "@CurrentTimeUtc".
Must declare the scalar variable "@PasswordAnswer".
A RETURN statement with a return value cannot be used in this context.
The label 'Cleanup' has already been declared. Label names must be unique within a query batch or stored procedure.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@UserName".
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@ApplicationId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@UserName".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@UniqueEmail".
Must declare the scalar variable "@Email".
A RETURN statement with a return value cannot be used in this context.
The variable name '@TranStarted' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@Email".
A RETURN statement with a return value cannot be used in this context.
The label 'Cleanup' has already been declared. Label names must be unique within a query batch or stored procedure.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@UserName".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@NewPasswordQuestion".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@ApplicationId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
Must declare the scalar variable "@PageSize".
Must declare the scalar variable "@PageSize".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@MinutesSinceLastInActive".
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@ApplicationId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
The variable name '@PageLowerBound' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PageUpperBound' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@TotalRecords' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@PageSize".
Must declare the scalar variable "@PageSize".
There is already an object named '#PageIndexForUsers' in the database.
Must declare the scalar variable "@UserNameToMatch".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@ApplicationId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@ApplicationName".
A RETURN statement with a return value cannot be used in this context.
The variable name '@PageLowerBound' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PageUpperBound' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@TotalRecords' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@PageSize".
Must declare the scalar variable "@PageSize".
There is already an object named '#PageIndexForUsers' in the database.
Must declare the scalar variable "@EmailToMatch".
Incorrect syntax near the keyword 'ELSE'.
Must declare the scalar variable "@EmailToMatch".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'EXEC'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
The variable name '@command' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near the keyword 'PRINT'.
Incorrect syntax near '--------------------------------------------'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query)
   at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec)
ClientConnectionId:a3d7bbd1-fed2-4f73-8039-3961ec1b2d35
Error Number:102,State:1,Class:15
ClientConnectionId before routing:11d20368-d93e-4dbe-ba4c-ee582ab32fdf
Routing Destination:REDACTED,11001


### Anything else?

_No response_

### Affected Versions

9.13.3 (latest release)

### What browsers are you seeing the problem on?

_No response_

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct
mitchelsellers commented 4 months ago

I have tested this on 2 Azure Subscriptions, in 2 Azure Regions and was not able to install in either.

mitchelsellers commented 4 months ago

One other testing note we have tested: 9.13.0 and 9.12.0 and have the same result now. These too use to work, so I'm thinking Azure Compatability issues.

mitchelsellers commented 4 months ago

A bit more information on this specific issue. I was able to install DNN locally, then push it to azure SQL without issue.

davidjrh commented 4 months ago

Instead of fully installing DNN, I'm just creating an Azure SQL Database and installing the scripts available at: \Providers\DataProviders\SqlDataProvider, in this order:

  1. InstallCommon.sql
  2. InstallMembership.sql
  3. InstallProfile.sql
  4. InstallRoles.sql
  5. DotNetNuke.Schema.SqlDataProvider (replacing the {databaseOwner} and {objectQualifier} by "[dbo]." and "")
  6. DotNetNuke.Data.SqlDataProvider (replacing the {databaseOwner} and {objectQualifier} by "[dbo]." and "")

When I reach step 5, I get an error when creating the Users table (and I'm not seeing anything causing that error in the T-SQL script). I tried by changing the QUOTED_IDENTIFIER to ON before executing the script, but also get the same error. Curiously, I just created a new database and executed only that table creation, and works OK. WTF!

SET QUOTED_IDENTIFIER ON;
GO
image
davidjrh commented 4 months ago

Hi again,

Sounds weird, but I've tried again on different servers and the scripts (and the installation) works as expected. Not sure if it was a transient configuration issue that Microsoft fixed over the latest hours, but also tested the full installation on different datacenters and the installation finished successfully.

skramio commented 4 months ago

I am seeing this exact issue and log entry in Azure SQL (East US 2) with a new install of 9.13.3 today. I have had zero issues in the past few weeks doing upgrades, but still unable to do a fresh install.

As a temporary workaround, I am installing locally, then moving over to Azure.