Error during importing data to a fresh Octopus Server

river_yang · 5 May 2016 23:24

Hi,

We’ve recently got an issue with Octopus while importing old data from another one.

Currently we have 2 Octopus environment: STG and PROD
Steps we have done for this issue:
1, Exporting all data from PROD to a folder
2, Copy folder to where STG Octopus server sits
3, Install fresh brand new Octopus server on STG Server
4, Import data from the folder which has been copied over from step 2.

Then we got following error message:
2016-05-05 13:14:08.8441 1 FATAL Error while executing SQL command: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2016-05-05 13:14:08.8441 1 ERROR ===============================================================================
The command being executed was:
INSERT INTO dbo.[Event] (RelatedDocumentIds, ProjectId, EnvironmentId, Category, UserId, Username, Occurred, Message, Id, Json) values (@RelatedDocumentIds, @ProjectId, @EnvironmentId, @Category, @UserId, @Username, @Occurred, @Message, @Id, @Json)
System.Exception: Error while executing SQL command: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The command being executed was:
INSERT INTO dbo.[Event] (RelatedDocumentIds, ProjectId, EnvironmentId, Category, UserId, Username, Occurred, Message, Id, Json) values (@RelatedDocumentIds, @ProjectId, @EnvironmentId, @Category, @UserId, @Username, @Occurred, @Message, @Id, @Json) —> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception: The wait operation timed out
— End of inner exception stack trace —
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 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.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Octopus.Core.RelationalStorage.TransientFaultHandling.IDbCommandExtensions.<>c__DisplayClass2_0.<ExecuteNonQueryWithRetry>b__0() in Y:\work\refs\tags\3.3.10\source\Octopus.Core\RelationalStorage\TransientFaultHandling\IDbCommandExtensions.cs:line 30 at Octopus.Shared.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) in Y:\work\refs\tags\3.3.10\source\Octopus.Shared\TransientFaultHandling\RetryPolicy.cs:line 215
at Octopus.Core.RelationalStorage.RelationalTransaction.Insert[TDocument](String tableName, TDocument instance, String customAssignedId) in Y:\work\refs\tags\3.3.10\source\Octopus.Core\RelationalStorage\RelationalTransaction.cs:line 131
— End of inner exception stack trace —
at Octopus.Core.RelationalStorage.RelationalTransaction.Insert[TDocument](String tableName, TDocument instance, String customAssignedId) in Y:\work\refs\tags\3.3.10\source\Octopus.Core\RelationalStorage\RelationalTransaction.cs:line 139
at Octopus.Core.RelationalStorage.RelationalTransaction.Insert[TDocument](TDocument instance, String customAssignedId) in Y:\work\refs\tags\3.3.10\source\Octopus.Core\RelationalStorage\RelationalTransaction.cs:line 101
at Octopus.Migrator.DocumentImporters.ConventionBasedImporter2.InsertNewDocument(ImportedDocument document, ImportContext context, Identity identity) in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\DocumentImporters\ConventionBasedImporter.cs:line 126 at Octopus.Migrator.DocumentImporters.ConventionBasedImporter2.Import(ImportedDocument document, ImportContext context) in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\DocumentImporters\ConventionBasedImporter.cs:line 103
at Octopus.Migrator.ImportProcess.ImportController.ConvertDocuments(ImportContext context) in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\ImportProcess\ImportController.cs:line 420
at Octopus.Migrator.ImportProcess.ImportController.ExecuteWithTimer(ImportContext ctx, String name, Action1 work) in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\ImportProcess\ImportController.cs:line 140 at Octopus.Migrator.ImportProcess.ImportController.ImportDocuments(IImportedDocumentSource backup) in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\ImportProcess\ImportController.cs:line 100 at Octopus.Migrator.ImportProcess.ImportController.Import(IImportedDocumentSource backup) in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\ImportProcess\ImportController.cs:line 61 at Octopus.Migrator.Commands.ImportFilesCommand.Start() in Y:\work\refs\tags\3.3.10\source\Octopus.Migrator\Commands\ImportFilesCommand.cs:line 90 at Octopus.Shared.Startup.AbstractCommand.Octopus.Shared.Startup.ICommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions) in Y:\work\refs\tags\3.3.10\source\Octopus.Shared\Startup\AbstractCommand.cs:line 57 at Octopus.Shared.Startup.ConsoleHost.Run(Action1 start, Action shutdown) in Y:\work\refs\tags\3.3.10\source\Octopus.Shared\Startup\ConsoleHost.cs:line 72
at Octopus.Shared.Startup.OctopusProgram.Run() in Y:\work\refs\tags\3.3.10\source\Octopus.Shared\Startup\OctopusProgram.cs:line 87

During the time, we are pretty sure there is no network issue in between as we are having a ping monitor pinging each other during the data import all the time between SQL Server and STG Octopus Server.
The data folder size is about 900MB.

Can you please advise what to check next?

Thanks
River

river_yang · 5 May 2016 23:25

Sorry, the size of the data folder is about 2.44GB.

Thanks
River

Vanessa_Love · 6 May 2016 04:47

Hi River,

Thanks for getting in touch! Are you able to send me the migrator logs associated with this import. They should be found in the C:\Octopus\Logs file in on the target machine.
We constantly try to keep the SQL connection open, so it makes me wonder if there is a single query that is taking a while to execute.

That size is very large. We may have to think of an alternative strategy if you need all the history and audit data imported into the target destination.

Warm Regards,
Vanessa

river_yang · 6 May 2016 04:59

Hi Vanessa,

Thanks for your reply.
Here is the zipped log for you.

Thanks
River

OctopusMigrator.2016-05-05_10_46_34.3513.zip (2 MB)

Vanessa_Love · 6 May 2016 05:06

Hi River,

It does look like a straight connection loss. Your migrator is processing things very well as seen below before it just tries for a query and times out. It is short, only 2 minutes.

2016-05-05 13:12:56.4519      1 DEBUG    Created Event "clement" (Events-60515 -> Events-21917)
2016-05-05 13:12:56.4519      1 DEBUG    Converting Event Events-60516
2016-05-05 13:12:56.5457      1 DEBUG    Created Event "clement" (Events-60516 -> Events-21918)
2016-05-05 13:12:56.5457      1 DEBUG    Converting Event Events-60517
2016-05-05 13:12:56.6238      1 DEBUG    Created Event "clement" (Events-60517 -> Events-21919)
2016-05-05 13:12:56.6238      1 DEBUG    Converting Event Events-60518
2016-05-05 13:12:56.6863      1 DEBUG    Created Event "clement" (Events-60518 -> Events-21920)
2016-05-05 13:12:56.6863      1 DEBUG    Converting Event Events-60519
2016-05-05 13:14:05.8753      1 ERROR    An unexpected error occurred when processing document with source ID 'Events-60519': Error while executing SQL command: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Have you tried this multiple times with the same result or just the once? Do you need all the audit and event data on the prod machine?

Vanessa

river_yang · 6 May 2016 05:11

Thanks for the analysis.

I don’t need the audit and event data on the prod machine.
It’s odd that there is a timeout happening randomly.

We run this migration several times and got many errors, we resolved most of them, and only stuck here…

Do we have any option to skip those data to reduce the imported data?

Thanks
River

Vanessa_Love · 6 May 2016 05:17

Hi River,

Yes you can delete the contents of the Event directory prior to import and thus it will not import it. This is the same for really any part of the data you do not need on the destination server.

Vanessa

river_yang · 6 May 2016 05:23

Cool.

Let me try. Thanks!
River

river_yang · 6 May 2016 05:51

Hi Vanessa,

This is another error I saw after removed everything from “Events” folder.
Is this something related to the folder that I deleted?

Thanks
River

OctopusMigrator.2016-05-06_15_22_47.6336.zip (1 MB)

Vanessa_Love · 6 May 2016 10:41

Hi River,

Unfortunately no this is another error completely. We have some corrupted data around projects and channels due to a mix up when cloning.
It appears you have been bitten by the bug. I see that you are running 3.3.10. 3.3.11 has a script in it that fixes the bad data - hopefully it will fix this for you. Otherwise I can get you to run some queries and we might have to fix it manually (not the desired way to fix this).

Vanessa

river_yang · 8 May 2016 23:39

Thanks for the answer Vanessa.

I’ve upgraded this to the latest 3.3.11 version, and importing this now to see how we go this time.
We are going to use the latest version anyway, and we don’t really want any manual fix.

Thanks
River

river_yang · 9 May 2016 00:04

Hi Vanessa,

Lol, there you go. Another error after upgraded to the latest version…

River

From: River Yang (ITaaS)
Sent: Monday, 9 May 2016 9:40 AM
To: ‘Vanessa Love’ tender2+d5beecc63a@tenderapp.com
Subject: RE: Error during importing data to a fresh Octopus Server [Problems #45782]

Thanks for the answer Vanessa.

I’ve upgraded this to the latest 3.3.11 version, and importing this now to see how we go this time.
We are going to use the latest version anyway, and we don’t really want any manual fix.

Thanks
River

OctopusMigrator.2016-05-09_09_38_29.7757.zip (1 MB)

Vanessa_Love · 9 May 2016 02:27

Hi River,

You seem to have some projects that are referencing channels that no longer exist. Which is concerning.
Could you run both : http://youroctopus/api/projects/Projects-263/channels and http://youroctopus/api/projects/Projects-263/
Both will give a JSON result, if you could grab that for me it should help.

Vanessa

river_yang · 9 May 2016 04:00

Both returns:
{
“ErrorMessage”: “The resource ‘Projects-263’ was not found.”
}

River

Vanessa_Love · 9 May 2016 05:42

Hi River,

What about: http://youroctopus/api/channels/Channels-389

Vanessa

river_yang · 9 May 2016 05:48

Hi Vanessa,

Same again:

[Octoclippy is here to help!]
Not found
The resource ‘Channels-389’ was not found.
{
“ErrorMessage”: “The resource ‘Channels-389’ was not found.”
}

River

Vanessa_Love · 9 May 2016 11:59

Hi River,

To confirm you are running these on the source server?
Could you check your channels folder where your exported files are and see if there is a project listed there that you believe should not be?
I may need a zip of that directory only to figure out where that ID is coming from. You shouldn’t really have any sensitive data in there as it will just be defined channels. Not even step details will be in there.
I’ve made this thread private.

Vanessa

river_yang · 10 May 2016 00:08

Yes, I can see there is one folder (Project) that shouldn’t be there.

I’ve removed it and retried the process again.
I’ll let you know how it goes.

Thanks
River

river_yang · 10 May 2016 00:40

Hi Vanessa,

As I’ve already deleted projects that I think it shouldn’t be in the Channel folder, it still showing the same issue as attached.

I think we played too much about this repository, probably this could corrupt the data too? Should we export the data again and remove the events folder to import it again?

Thanks
River

From: River Yang (ITaaS)
Sent: Tuesday, 10 May 2016 10:08 AM
To: ‘Vanessa Love’ tender2+d5beecc63a@tenderapp.com
Subject: RE: Error during importing data to a fresh Octopus Server [Problems #45782]

Yes, I can see there is one folder (Project) that shouldn’t be there.

I’ve removed it and retried the process again.
I’ll let you know how it goes.

Thanks
River

OctopusMigrator.2016-05-10_10_07_32.7097.zip (1 MB)

Vanessa_Love · 10 May 2016 00:52

Hi River,

Yes absolutely. I do not understand why it would have a channel in the folder for a project that doesn’t exist. Please do another full export and see if that project still appears?

Thanks
Vanessa