When cloning down an Umbraco Cloud project, building it and running it locally, it will not start, I get an error saying: SQLite does not support ALTER TABLE operations (full error below).
I've tested this on multiple machines, so it shouldn't be anything related to that.
The project is using these versions of Umbraco and Vendr
Umbraco 10.2.1 (started as v9.0.1)
Vendr 2.3.1 (started as v2.0.3)
I've tested it out with another project, which runs fine, and that is using
Umbraco 10.1.0 (started as v10.1.0)
Vendr 2.3.4 (started as v2.3.4)
Since SQLite was added for Umbraco 10, I'm wondering if it might be related to that the project started as a v9 project, and was then upgraded.
Not really sure on what to do to get the site up and running locally.
System.NotSupportedException: SQLite does not support ALTER TABLE operations. Instead you will have to:
1. Create a temp table.
2. Copy data from existing table into the temp table.
3. Delete the existing table.
4. Create a new table with the name of the table you're trying to alter, but with a new signature
5. Copy data from the temp table into the new table.
6. Delete the temp table.
at Umbraco.Cms.Infrastructure.Migrations.Expressions.Alter.Table.AlterTableBuilder.Do()
at Vendr.Umbraco.Migrations.V_1_0_0.CreateStoreTableConstraints.DoMigrate()
at Vendr.Umbraco.Migrations.VendrMigrationBase.Migrate()
at Umbraco.Cms.Infrastructure.Migrations.MigrationBase.Run()
at Umbraco.Cms.Infrastructure.Migrations.MigrationPlanExecutor.Execute(MigrationPlan plan, String fromState)
at Umbraco.Cms.Infrastructure.Migrations.Upgrade.Upgrader.Execute(IMigrationPlanExecutor migrationPlanExecutor, ICoreScopeProvider scopeProvider, IKeyValueService keyValueService)
at Umbraco.Cms.Infrastructure.Migrations.Upgrade.Upgrader.Execute(IMigrationPlanExecutor migrationPlanExecutor, IScopeProvider scopeProvider, IKeyValueService keyValueService)
at Vendr.Umbraco.Events.Notification.Handlers.RunVendrMigrations.Handle(UmbracoApplicationStartingNotification notification)
at Umbraco.Cms.Core.Events.NotificationHandlerWrapperImpl`1.<>c__DisplayClass0_0.<Handle>b__1(INotification theNotification)
at Umbraco.Cms.Core.Events.EventAggregator.PublishCore(IEnumerable`1 allHandlers, INotification notification)
at Umbraco.Cms.Core.Events.NotificationHandlerWrapperImpl`1.Handle(INotification notification, ServiceFactory serviceFactory, Action`2 publish)
at Umbraco.Cms.Core.Events.EventAggregator.PublishNotification(INotification notification)
at Umbraco.Cms.Core.Events.EventAggregator.PublishAsync[TNotification](TNotification notification, CancellationToken cancellationToken)
at Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken, Boolean isRestarting)
at Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken)
at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
at Program.Main(String[] args) in C:\Work\{ProjectPath}\Program.cs:line 9
I'm not quite sure what to suggest as to me it just sounds like the wrong SQLite version, so whether it's loading in a different version I'm not sure. Either that or Umbraco migrations didn't support SQLite alter table statements at that point, but then it's weird it would work on 10.1.0 and not 10.2.0.
Only other option I could suggest would be to upgrade to Vendr 3.0.0 where the persistence layer and migrations no longer use Umbraco's built in API and so there would be more control over fixing the issue if it's then still relevant.
You're right, I think that all pending changes must have been lost or discarded in git, because all packages had reverted back somehow🤷♂️
And after ensuring all packages are upgraded, the error is gone, and the sites starts up, so far so good👏
However, it does not finish with restoring , after a little while, I get an error saying:
`The source environment has thrown a Microsoft.Data.Sqlite.SqliteException with message: SQLite Error 6: 'database table is locked'.`
I can log into Umbraco, but no doctypes or template are there, the log gives me the full error
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 6: 'database table is locked'.
at Vendr.Infrastructure.Resiliency.PollyExecutionStrategyBase.Execute[TResult](Func`1 operation, Func`1 verifySucceeded)
at Vendr.Core.VendrUnitOfWorkProvider.Execute[T](IUnitOfWorkOptions options, Func`2 action)
at Vendr.Core.VendrUnitOfWorkProvider.Execute[T](Boolean autoComplete, Func`2 action)
at Vendr.Core.Services.CountryService.PerformGetAllCountryStates(Guid[] ids)
at Vendr.Core.Cache.FullDataSetEntityStatePolicyCache`2.DoGetAll(Func`2 performGetAll)
at Vendr.Core.Cache.FullDataSetEntityStatePolicyCache`2.Get(TId id, Func`2 performGet, Func`2 performGetAll)
at Vendr.Core.Services.CountryService.GetCountryState(Guid id)
at Vendr.Core.Services.CountryService.GetCountry(Guid id)
at Vendr.Core.Api.CoreVendrApi.GetCountry(Guid id)
at Vendr.Deploy.Connectors.ServiceConnectors.VendrCountryServiceConnector.GetEntity(Guid id)
at Vendr.Deploy.Connectors.ServiceConnectors.VendrEntityServiceConnectorBase`2.GetArtifact(GuidUdi udi)
at Umbraco.Deploy.Infrastructure.Connectors.ServiceConnectors.ServiceConnectorBase`3.Umbraco.Cms.Core.Deploy.IServiceConnector.GetArtifact(Udi udi)
at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.GetSignatures(IEnumerable`1 udis, CancellationToken token)
at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ReviewManifest(Guid sessionId, Manifest manifest)
at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ReviewManifestAsync(Guid sessionId, Manifest manifest, CancellationToken token)
at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.Proceed(List`1 artifacts, Manifest manifest)
at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsyncSub(IWorkContext context, List`1 resume, CancellationToken token)
at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsync(IWorkContext context, CancellationToken token)
When I debug into Vendr's code it seems to stem from the point we try to begin a transaction on the database. This ultimately times out with the table lock error. I've checked what I can and I can't see anything in our code that could be causing multiple connections on that table (only one request to connect to the DB is ever made, and that times out).
My guess then is that within Umbraco Deploy they already have a transaction open on the database and so us attempting to make our own connection to the DB is failing.
I'll need to check with Andy at HQ what happens inside deploy and see if he can suggest anything.
Unfortunately I don't have a work around at this moment in time.
To anyone else, a relatively simple workaround to the SQLite Error 6: 'database table is locked is to modify appsettings.Development.json and add this just after the Umbraco block.
SQLite does not support ALTER TABLE operations
When cloning down an Umbraco Cloud project, building it and running it locally, it will not start, I get an error saying: SQLite does not support ALTER TABLE operations (full error below).
I've tested this on multiple machines, so it shouldn't be anything related to that.
The project is using these versions of Umbraco and Vendr
I've tested it out with another project, which runs fine, and that is using
Since SQLite was added for Umbraco 10, I'm wondering if it might be related to that the project started as a v9 project, and was then upgraded.
Not really sure on what to do to get the site up and running locally.
Hmm, I think your SQLite version might be an old one as SQLite does support
ALTER TABLEfor adding columns which is what that migration does.I'd check what version of
Microsoft.Data.Sqliteyou have installed and see if can be upgraded.Microsoft.Data.Sqlite is v6.0.5 on both projects
What does your connection string in appsettings look like?
Again, on both projects
I'm not quite sure what to suggest as to me it just sounds like the wrong SQLite version, so whether it's loading in a different version I'm not sure. Either that or Umbraco migrations didn't support SQLite alter table statements at that point, but then it's weird it would work on 10.1.0 and not 10.2.0.
Only other option I could suggest would be to upgrade to Vendr 3.0.0 where the persistence layer and migrations no longer use Umbraco's built in API and so there would be more control over fixing the issue if it's then still relevant.
After upgrading Vendr to v3, the site still does not run, I get this error:
Yup, in v3 SQLite support is optional see here to add it https://vendr.net/docs/core/3.0.0/how-to-guides/configuring-sqlite-support/
Ok, but is the documentation complete? It seems something is missing or not right?
Do you have a
using Vendr.Extensions?UPDATE Actually, I think this suggests your code isn't fully updated. Have you updated Vendr to v3 in all your projects?
You're right, I think that all pending changes must have been lost or discarded in git, because all packages had reverted back somehow🤷♂️
And after ensuring all packages are upgraded, the error is gone, and the sites starts up, so far so good👏
However, it does not finish with restoring , after a little while, I get an error saying:
I can log into Umbraco, but no doctypes or template are there, the log gives me the full error
Hi Michael,
Did you find a solution for this issue?
I'm getting the similar one, seams an issue with Countries
Hi Benji
Unfortunately no, I'm still stuck with this issue.
@Matt Brailsford any insight to this and how we may resolve this issue?
Hmmm, I'm not quite sure. I guess I'll have to setup a cloud environment with a store and see if I can replicate it restoring locally.
How many countries are setup in your store?
Just 1 that was created United Kingdom
Ok, so yea, I can replicate this now.
I'll have to dig into it and see what's going on 👍
Great, thanks.
hopefully it's an easy fix
Ok, so it's not looking like a simple fix.
When I debug into Vendr's code it seems to stem from the point we try to begin a transaction on the database. This ultimately times out with the table lock error. I've checked what I can and I can't see anything in our code that could be causing multiple connections on that table (only one request to connect to the DB is ever made, and that times out).
My guess then is that within Umbraco Deploy they already have a transaction open on the database and so us attempting to make our own connection to the DB is failing.
I'll need to check with Andy at HQ what happens inside deploy and see if he can suggest anything.
Unfortunately I don't have a work around at this moment in time.
Damn, okay. Thanks for the quick update though.
@Matt Brailsford
Thanks for the speedy response 👍
I've created an issue here https://github.com/vendrhub/vendr/issues/394 mostly just for tracking, so I'll know as soon as there's a fix or workaround to it.
Perfect! Thanks Michael. I'll post further updates on the issue 👍
To anyone else, a relatively simple workaround to the SQLite Error 6: 'database table is locked is to modify
appsettings.Development.jsonand add this just after the Umbraco block.Then in
appsettings.Production.jsonandappsettings.Staging.jsonyou can set those to blank valuesThis will create a separate SQLite DB for Vendr locally, and the locking issue can be avoided.
Thanks to @Matt for that solution, which can be found here. https://github.com/vendrhub/vendr/issues/394#issuecomment-1301837359
is working on a reply...