No such table SQL error when creating an index table after an operation which call session.FlushAsync

[aaronamm]

Hello everyone.

I have a Migrations.cs file which has the following code block in CreateAsync.

    public async Task<int> CreateAsync()
    {
        await CreateHomePageContentItemAsync();
        await _layerService.CreateHomepageLayerIfNotExistAsync(_conditionIdGenerator);
        await CreateMenuAsync();

        CreateContentItemByTermIndexTable();
        return 1;
    }

CreateContentItemByTermIndexTable method is for creating a new custom index table which store a record whenever there is a new content item associated to a term in a Taxonomy field.

From my understanding, the three methods before CreateContentItemByTermIndexTable should not create any index row to ContentItemByTermIndex table.
Therefore YesSql shouldn't does any operation on ContentItemByTermIndex table.

I know that _layerService.CreateHomepageLayerIfNotExistAsync(_conditionIdGenerator) called layerService.LoadLayersAsync() which called session.FlushAsync() under the hood.

There might be an updating index operation. However, I still can't reason myself why this happened because there is no insert operation to ContentItemByTermIndex table.

In addition, I have try to debug YesSql with Source Link but I couldn''t make it work because there is no debugging symbol in YesSql Nuget

Note The photo from other project which has Nuget debugging symbol (.snupkg).

This error is easy to fix, just move CreateContentItemByTermIndexTable to the top of CreateAsync method's body. However, I just only want to reason why we can't use it as the last method which should be okay because there is no an insert operation to ContentItemByTermIndex table.

Here is the link to the source code of the demo project.
orchard-issue-1 project

Exception stack trace:

Error while running migration version 0 for 'OrchardIssue1.Theme'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: ContentItemByTermIndex'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at YesSql.Commands.BatchCommand.ExecuteAsync(DbConnection connection, DbTransaction transaction, ISqlDialect dialect, ILogger logger)
at YesSql.Session.FlushAsync()
at YesSql.Session.FlushAsync()
at YesSql.Services.DefaultQuery.Query`1.FirstOrDefaultImpl()
at OrchardCore.Data.Documents.DocumentStore.GetOrCreateMutableAsync[T](Func`1 factoryAsync)
at OrchardCore.Documents.DocumentManager`1.GetOrCreateMutableAsync(Func`1 factoryAsync)
at OrchardIssue1.Theme.LayerServiceExtensions.CreateHomepageLayerIfNotExistAsync(ILayerService layerService, IConditionIdGenerator conditionIdGenerator) in /home/user/projects/orchard-issues/orchard-issue-1/src/Themes/OrchardIssue1.Theme/LayerSeviceExtensions.cs:line 18
at OrchardIssue1.Theme.Migrations.CreateAsync() in /home/user/projects/orchard-issues/orchard-issue-1/src/Themes/OrchardIssue1.Theme/Migrations.cs:line 66
at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId)

Thank you.

[MikeAlhayek]

I don't know why CreateHomePageContentItemAsync is async and CreateContentItemByTermIndexTable is not. Maybe they are doing more than creating an index?

I think you'll need to populate databases here in a deferred task to ensure that the tables exist file. Try something like this

    public async Task<int> CreateAsync()
    {
        await CreateHomePageContentItemAsync();
        CreateContentItemByTermIndexTable();

        ShellScope.AddDeferredTask(async scope =>
        {
               // user scope `scope.ServiceProvider.GetService` to resolve services you may need for the 2 methods
               var layerService = scope.ServiceProvider.GetRequiredService<ILayerService>();
               await layerService.CreateHomepageLayerIfNotExistAsync(_conditionIdGenerator);
               await CreateMenuAsync();
        });

        return 1;
    }

[aaronamm]

@MikeAlhayek thank you for your suggestion. I know that we have AddDeferredTask to use but I would like know the reason why I can't create an index table after an operation that does not insert any record to an index table.

Thanks.

[MikeAlhayek]

Your code is not all visible so I can comment much. But, the name CreateMenuAsync() to me means that you are creating menu content items via code. This will fail on a new setup if your module is loaded before OrchardCore.Menu since it create indexes for menu. So this would explain the error you are seeing.

When we import the content recipe, we execute the code using AddDeferredTask to allow all migrations to first run (create index...).

[aaronamm]

@MikeAlhayek here are the files of the demo project.
Migrations.cs
ContentItemByTermIndexProvider.cs

This will fail on a new setup if your module is loaded before OrchardCore.Menu since it create indexes for menu.

I still don't know why creating menu requires my custom index to exist because it will be inserted a new recorder when there is a term attached to a content item.

[MikeAlhayek]

The following code will fail on the first create because your code could be executed before the Autoroute indexes existed. Also, it could fail because the "HomePage" definition could be still missing.
var contentItem = await _contentManager.NewAsync("HomePage");
// We need to set DisplayText because it will be used to create a HTML page title.
// See src/OrchardCore.Modules/OrchardCore.Contents/Views/ContentsMetadata.cshtml
contentItem.DisplayText = "Home page";
var autoroutePart = contentItem.Alter(p =>
{
p.SetHomepage = true;
p.Path = "home"; // We need this because we can access this item even thought it is no longer a home page.
});

I still don't why creating menu requires my custom index to exists because it will be inserted a new recorder when there is a term attached to a content item.

Because your ContentItemByTermIndexProvider will be called every time a content item is created. So when you create menu, or HomePage from your migration, the provider will try to insert a record in ContentItemByTermIndex table.

Similar problem here. Both examples should be executed either using a recipe or within AddDeferredTask()

https://github.com/codesanook/orchard-issues/blob/258a87f50ebd182efc73bd04d714cb240ecd1963/orchard-issue-1/src/Themes/OrchardIssue1.Theme/Migrations.cs#L151-L164

[aaronamm]

The following code will fail on the first create because your code could be executed before the Autoroute indexes existed.

It failed because no such table: ContentItemByTermIndex' which is my custom index table not Autoroute indexes.

Also, it could fail because the "HomePage" definition could be still missing.

Here is the HomePage content type definition:
https://github.com/codesanook/orchard-issues/blob/develop/orchard-issue-1/src/Themes/OrchardIssue1.Theme/Migrations.cs#L170-L177

Because your ContentItemByTermIndexProvider will be called every time a content item is created.
So when you create menu, or HomePage from your migration, the provider will try to insert a record in ContentItemByTermIndex table.

I agree that ContentItemByTermIndexProvider will be called whenever there is a new content item but it will insert a new record to a custom index table when a content item that has
TaxonomyField only. Here is the code: https://github.com/codesanook/orchard-issues/blob/develop/orchard-issue-1/src/Themes/OrchardIssue1.Theme/Indexes/ContentItemByTermIndexProvider.cs#L98-L100

Thank you so much for your solution and it fix the issue for sure but I still don't think I get the answer.

I think in a meantime while waiting for the answer I will create an issue to YesSql to add debugging symbol in a Nuget package. This will help debugging with with Source Link and I can start debugging to YesSql.

Thank you so much.

[MikeAlhayek]

You can enable Mini Profiler module in OC. Also put a break point in your custom provider to see when it is called. I am not positive, but highly likely that the index is being used because it is registered. Since it is registered, it'll be called and yessql will want to validate the table existing before it uses it.

[aaronamm]

@MikeAlhayek

You can enable Mini Profiler module in OC.

Thanks for the tip, I will try.

Since it is registered, it'll be called and yessql will want to validate the table existing before it uses it.

I agree with this. It is possible that YesSql tried to sync indexes before query data in session.FlushAsync() and it checked all registered indexes must exist before inserting new record.
Just curiosity that it should effect only index that related to a new content item.
This behavior may be by designed.

[jtkech]

From memory so not fully sure ;)

Yes all registered index providers will be taken into account and the related tables "may" need to already exist. But the index tables are only created at the end of the shell scope executing the migrations, that's why when creating items through migrations we use a deferred task, as I remember we do the same when creating items through recipes.

That said what you can try in your index provider, just before the .Map(), is to add a .When() which allows to add a condition before using the provider, not sure it works but worth to try, for example

.When(contentItem => contentItem.Has<SomePart>())
.Map(contentItem =>

[aaronamm]

@JimBobSquarePants

the index tables are only created at the end of the shell scope executing the migrations, that's why when creating items through migrations we use a deferred task, as I remember we do the same when creating items through recipes.

I agree that using deferred task is the right way to insert a new content item in Migrations.cs.
However, I just want to inform you that I can move move CreateContentItemByTermIndexTable to the top of CreateAsync method's body and the migration will be applied without any issues.

[jtkech]

Okay, but in the initial order, as mentioned, did you try to use a .When() before the .Map()?

Just tried on my side, seems to work.

I reproduced the issue by creating an index provider without any related table and then by doing a fresh setup.

Then by adding the following .When() for testing, the setup succeeded.

            .When(contentItem => false)
            .Map(contentItem =>
            {
                return Task.FromResult<AutoroutePartIndex1>(null);
            });

So you could use a .When() checking for exemple if the item has a given part.

EDITED: YesSql save json document and is not aware of the composition of a content type and let the responsability of the index provider to return records or not and to be executed or not at all.

[aaronamm]

@jtkech I have updated ContentItemByTermIndexProvider.cs to have when filter and it works.
Here is then condition in when statement.

    context.For<ContentItemByTermIndex>()
    .When(contentItem =>
    {
        // Search for Taxonomy fields
        _fieldDefinitions = GetTaxonomyFieldsDefinitions(contentItem);
        return _fieldDefinitions.Any();
    })
 
    // GetTaxonomyFieldsDefinitions
    private IReadOnlyCollection<ContentPartFieldDefinition> GetTaxonomyFieldsDefinitions(ContentItem contentItem)
    {
        // Lazy initialization because of ISession cyclic dependency
        _contentDefinitionManager ??= _serviceProvider.GetRequiredService<IContentDefinitionManager>();

        // Search for Taxonomy fields
        var contentTypeDefinition = _contentDefinitionManager.GetTypeDefinition(contentItem.ContentType);

        // This can occur when content items become orphaned, particularly layer widgets when a layer is removed, before its widgets have been unpublished.
        if (contentTypeDefinition == null)
        {
            _ignoredTypes.Add(contentItem.ContentType);
            return Array.Empty<ContentPartFieldDefinition>();
        }

        var fieldDefinitions = contentTypeDefinition
            .Parts.SelectMany(x => x.PartDefinition.Fields.Where(f => f.FieldDefinition.Name == nameof(TaxonomyField)))
            .ToArray();

        // This type doesn't have any TaxonomyField, ignore it
        if (fieldDefinitions.Length == 0)
        {
            _ignoredTypes.Add(contentItem.ContentType);
            return Array.Empty<ContentPartFieldDefinition>();
        }

        return fieldDefinitions;
    }

Full source code => https://github.com/codesanook/orchard-issues/blob/develop/orchard-issue-1/src/Themes/OrchardIssue1.Theme/Indexes/ContentItemByTermIndexProvider.cs#L37-L43

This means whenever context.Map get called, it does matter of return value (null or new list of record), there will be an operation that required custom index table to be existed.

So always using when filter is the right way to avoid unnecessary operation.

I think I get the answer now.
Thanks, @jtkech and @MikeAlhayek

[JimBobSquarePants]