uSync 13 - Syncing vast amount of data

[Pekdon (Mathias Andreasen)]

So im in the process of syncing data between an old Umbraco 8 site, to a new Umbraco 13 one.

I got all the .config usync files from the v8 site and copied the files over to my v13 site.
However, im syncing up to 5000 media config files, The import fails. Im seeing this in the debug logs:

[22:22:14 WRN] MediaHandler: Import Failed : System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
   at Umbraco.Extensions.NPocoDatabaseExtensions.GetCurrentTransactionIsolationLevel(IDatabase database)
   at Umbraco.Cms.Infrastructure.Scoping.Scope.get_Database()
   at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.MediaRepository.MediaByGuidReadRepository.PerformGet(Guid id)
   at Umbraco.Cms.Core.Cache.DefaultRepositoryCachePolicy`2.Get(TId id, Func`2 performGet, Func`2 performGetAll)
   at Umbraco.Cms.Core.Services.MediaService.GetById(Guid key)
   at uSync.Core.Serialization.Serializers.ContentSerializerBase`1.FindItem(XElement node)
   at uSync.Core.Serialization.SyncSerializerRoot`1.Deserialize(XElement node, SyncSerializerOptions options)
   at uSync.BackOffice.SyncHandlers.SyncHandlerRoot`2.ImportElement(XElement node, String filename, HandlerSettings settings, uSyncImportOptions options)
[22:22:14 WRN] MediaHandler: Import Failed : System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

And stuff like this from the logviewer:

Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_cmsContentNu_umbracoContent_nodeId". The conflict occurred in database "ampirocms_prod", table "dbo.umbracoContent", column 'nodeId'.

The statement has been terminated.

   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)

   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)

   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

   at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)

   at NPoco.Database.InsertAsyncImp[T](PocoData pocoData, String tableName, String primaryKeyName, Boolean autoIncrement, T poco, Boolean sync)

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

ClientConnectionId:1f469f6e-66fb-4668-af35-20e465ee71f9

Error Number:547,State:0,Class:16

looking in the umbracoContent table I see no rows at all.

Im at my wits end, im in the final stages of upgrading a client site but im getting stuck on this issue over and over again. Im not even sure if the data i have is massive or not, but I know it’s 5000 media usync files and around 3000 content usync files.

I’ve tried the uSync.Exporter package, where I can create my own export packs, but for some reason ( i do not know why ) when exporting nodes im ONLY getting the media files, no content. This also makes me think im not getting all the media files im supposed to get.

So I did a clean export from my prod data, i started the umbraco 8 site locally, whilst connected to prod data and did a simple usync clean export, im confident I have all the data exported.

Now I just need to be able to import it into my v13 site.

I could try and import like 1000 media usync files at a time, but then Im afraid if lets say i have 5 usync files, A,B,C,D and E. and I only sync A-D. Whilst the E file is actually a media-folder that holds A-D. Will that ruin my sync?

Im very grateful for any input or insight into my issue. My client is anxious as this Upgrade from v8 to v13 has taken soooooooo long

[Pekdon (Mathias Andreasen)]

Maybe I can write my own little sync application that uses the uSync commands somehow to create my own little “batch” process, to take like 100 files at a time or something

[KevinJump]

Hi

Its sad this is causing you so much grief - but i will say not sure what those sql errors are but uSync doesn’t do any direct sql stuff its all via the umbraco api’s so if SQL is throwing errors that might be pointing to something else being odd as well (which i know won’t help).

If you have the files i would start with a clean (as in brand new) umbraco v13 local install using SqLite as the db and see what imports then. with that setup 3000 media items should not take that long to import and you will see errors.

Also worth noting for media its usually OK because the media types might not be complicated, but often you can’t just upgrade from v8 to v13 directly if you have data types that are not supported in use (e.g nupickers on v8 doesn’t have a directy comparison on v13, so it needs to be migrated, and other things too like archetype, leblender etc) not to suggest another tool but uSync.Migrations can help a little with that, but i don’t suspect it will just solve your problems here

I would note i am not an upgrade/migration expert here, i know there are lots of people doing lots of upgrades all the time who probibly have tips and tricks to share but.

I would be looking a splitting things up now - So on a clean install import the settings, datatypes, doctypes, media types, see if they look ok, do they actually work (can you create a media item, content item with them?

Then import the media, you can break this up on disk if you want, you can choose to import bits of the media at a time (by hand) the only thing to check is that you import the parent items first.

So if i was doing this a manual way i would find all the level “0” items all the level “1” items etc (the level an attribute at the top of each file), and make sure i import them first.

but to be honest, if we take the actual media files (the jpgs, etc) out of the picture and “just” import the media config (uSync/media) then 3000 media items into a local v13 install running sqlite shouldn’t take too long. (a few minutes)

at the end of that import all the media will be 'in" umbraco, but you won’t be able to see the images, becaus the media folder won’t have the media, but you can copy the media folder from v8 to the v13 site and they will be there.

Why the content isn’t exporting, is odd, because that again is how the export works (from uSync that’s bascially ContentService.GetAll If you think the export from the dashboard has got all the content you could do a similar thing to what you have done with the media, but this is where i would be supprised if it ‘just’ updated, only if there are no special packages or datatypes would that probibly work.

only once i knew the data was importing ok on a new clean umbraco site would i think about putting it on to the upgraded site with all the code etc. and also if have a v13 site with everything imported you can perform a clean export and get updated usync files that should “in theroy” be quicker and better to import onto your final site.

[Pekdon (Mathias Andreasen)]

I ended up perform a clean export on from my production environment whilst running the site locally. after that, i wrote a bash script that organized all the usync files (media + content) into level-directories.

after that, it got really fun, as I copied around 300-500 items at a time into my new environment, imported them, cleared the folder and copied over 300-500 more.
I have a lot of content and media on this site, so this entire process took my several hours, but I put on an audiobook and blasted through.

And the day after, I urged my client that we didn’t want to do this process again, so we went live. So I’m finally done with my upgrade from v8-v13 project.

I feel exhausted, but happy that this is behind me now.

I do love uSync, it’s pretty much a mandatory package to have, but it’s gotten me to think on how I can think and organize large input tasks in the future.

Thanks to @KevinJump for being available and responsive!