Database upgrade *failed* from 3.0.0 to 3.0.13

Legacy Uncategorised
1

Database upgrade failed: Deprecated feature ‘Multiple table hints without comma’ is not supported in this version of SQL Server.

Running on SQL Azure - 3.0.0 was fine. Any suggestions on how to fix this?

2015-08-15 09:18:58.3136 6 FATAL Unhandled AppDomain exception occurred: Database upgrade failed: Deprecated feature ‘Multiple table hints without comma’ is not supported in this version of SQL Server.
System.Exception: Database upgrade failed: Deprecated feature ‘Multiple table hints without comma’ is not supported in this version of SQL Server. —> System.Data.SqlClient.SqlException: Deprecated feature ‘Multiple table hints without comma’ is not supported in this version of SQL Server.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
— End of inner exception stack trace —
at Octopus.Core.Initialization.DatabaseUpgrader.Upgrade(IRelationalStore store) in Y:\work\refs\heads\master\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 36
at Octopus.Core.Initialization.StoreInitializer.Initialize() in Y:\work\refs\heads\master\source\Octopus.Core\Initialization\StoreInitializer.cs:line 22
at Octopus.Server.OctopusServerEngine.Start() in Y:\work\refs\heads\master\source\Octopus.Server\OctopusServerEngine.cs:line 49
at Octopus.Server.Commands.RunCommand.Start() in Y:\work\refs\heads\master\source\Octopus.Server\Commands\RunCommand.cs:line 37
at Octopus.Shared.Startup.AbstractCommand.Octopus.Shared.Startup.ICommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions) in Y:\work\refs\heads\master\source\Octopus.Shared\Startup\AbstractCommand.cs:line 57
at Octopus.Shared.Startup.WindowsServiceHost.<>c__DisplayClass1_0.b__0() in Y:\work\refs\heads\master\source\Octopus.Shared\Startup\WindowsServiceHost.cs:line 21
at Octopus.Shared.Startup.WindowsServiceAdapter.RunService() in Y:\work\refs\heads\master\source\Octopus.Shared\Startup\WindowsServiceAdapter.cs:line 59
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

2

Fixed - Octopus Deploy 3 migration only works on the Preview V12 SQL instances. Does not work on normal SQL Azure instances.

3

Hi Thomas,
Thanks for bringing this to our attention. We have been testing our builds on multiple SQL versions however perhaps this particular query fell through the gaps. I have added this issue to a new [GitHub issue #1908] (https://github.com/OctopusDeploy/Issues/issues/1908) to get this rectified. Keep an eye on this ticket since with our new release cadence it could be fixed any day now!
Thanks again,
Robert

4

Hi again Thomas,
We have tried to reproduce this based on scripts run up to 3.0.13 and so far have been unable to get this error. So that we can pinpoint the exact TSQL causing this could I please get you to provide the server logs (available where you installed the server instance e.g. C:\Octopus\Logs\OctopusServer.txt) surrounding this particular event.
SQL Azure definitely has its “peculiarities” as to what it considered valid TSQL or not, so hopefully we can get this identified quickly so that it works on all SQL Server Flavours.
Cheers,
Rob

5

I have the bacpac from the original db that caused the problem. Attached below (deleted the Data folder). From the log file:

2015-08-15 10:55:32.2931 6 INFO Executing SQL Server script ‘Octopus.Core.UpgradeScripts.Script0006 - Increase MaxLength of NuGetPackage PackageId.sql’
2015-08-15 10:55:33.8499 6 INFO Caution: Changing any part of an object name could break scripts and stored procedures.
2015-08-15 10:55:33.9376 6 INFO Executing SQL Server script ‘Octopus.Core.UpgradeScripts.Script0008 - Lengthen Ids in Releases.sql’
2015-08-15 10:55:34.0418 6 INFO SQL exception has occured in script: ‘Octopus.Core.UpgradeScripts.Script0008 - Lengthen Ids in Releases.sql’
2015-08-15 10:55:34.0418 6 ERROR Script block number: 2; Block line 2; Message:
2015-08-15 10:55:34.0557 6 ERROR System.Data.SqlClient.SqlException (0x80131904): Deprecated feature ‘Multiple table hints without comma’ is not supported in this version of SQL Server.

octopus-2015-8-15-9-9.bacpac (236 KB)

6

Hi Thomas,

Thanks for taking the time to get back to us! I’m going to take over from Rob on this ticket for now.

We did have a similar issue during the 3.0 pre release with Octopus.Core.UpgradeScripts.Script0008 - Lengthen Ids in Releases.sql but that was fixed in 3.0.1. We’d since set up some integration tests to protect us against shipping a build that didn’t cooperate with Azure SQL Database.

We currently test against Azure SQL Database V2 and V12. Which version does this script fail with?

Also can you confirm what exact build of the Octopus 3.0 pre-release you are upgrading from?

Thanks again for helping us zoom in on the root cause of the problem!
Mike

7

Definitely V2. Which is a bit weird since if it’s a depreciated feature - wouldn’t it still be depreciated in V12?

All I know is that was 3.0.0.X - so that would be a pre-release build. Do you record this in the database or log files somewhere?

8

Hi Thomas,

Regarding the deprecated feature, I would have to agree, it would seem strange. I’ve also found it hard to track down the source of the error in the first place.

Regarding the Octopus version, it turns out we would only write the version of Octopus to the Console, not to the log files. I’ve pushed a fix that will ship in 3.0.17 but that doesn’t help us in this instance.

In the meantime I’ll try that BACPAC on a V2 SQL Database server and see if I can replicate the issue.

Can I confirm: Are you blocked by this issue? I assume not because you’ve mentioned upgrading to V12 was a work around.

Thanks again for your help!
Mike

9

Hi Thomas,

I just realised the BACPAC has the data stripped out (which makes perfect sense) but I can’t use that to reproduce the issue because the SchemaVersions table is used to keep a journal of which scripts have been executed already.

If you’re up and running I’m inclined to write this off as an issue that will only be experienced by a few people who were running the 3.0 pre release on Azure SQL Database V2.

Hope that helps!
Mike

10

No worries. It’s not a blocker for me. Thanks for looking into it. I’ve attached the BACPAC with schemaversions intact just in case you get the itch :slight_smile:

octopus-2015-8-15-9-9-with-schema.bacpac (237 KB)

11

Thanks Thomas!

Happy Deployments!
Mike