Best Way to implement Separate Tennant DB's with Shard Host DB #2057 | Support

Spospisil created 3 years ago

ABP Framework version: v4.4.3
UI type: MVC
DB provider: EF Core
Tiered (MVC) or Identity Server Separated (Angular): Tiered
Steps to reproduce the issue:"

We have a Host DB that has all the Identity Server/Sass and CMS related tables. Each tennat has their own DB as specified in the TenantConnectionStrings table but we do not want the Users table in the Tenant DB. In the main DB context class of my project I have specified this setup with the following code.

However, when I log in via the Web Application as a Tenant user I get an error indicating that there is 'no relation to the users table'. I have stepped through the code and the logic to log into and set everything accordinly is working fine and against the users table in the host db (which has all users across all tenants) and is verified by a successful login result in the code. But there is some other logic somewhere that seems to be looking for the Users table in the Tenant DB which is why I believe I'm getting the error.

Can you tell me the best way to implement the configuration of having all these core tables in the Host DB and only Tenant specific tables (our application tables) in the individual tenant DB's?

Thanks.

23 Answer(s)

0
Spospisil created 3 years ago
At the top of the DBContext class are the following class attributes which were put there by the generated ABP solution. With all the ABP module tables being in the one Host DB do I need to also put attribute lines in for each DBContext interface's from all the other modules as well?

[ReplaceDbContext(typeof(IIdentityProDbContext))] [ReplaceDbContext(typeof(ISaasDbContext))] [ConnectionStringName("Default")]
0

Spospisil created 3 years ago

By the way, when I log in as a Host user I do not get an error. It's only when I log in as a Tenant user.
0

Spospisil created 3 years ago

Any update for this?

liangshiwei created 3 years ago

Support Team Fullstack Developer

Hi, sorry I'm later.

Can you tell me the best way to implement the configuration of having all these core tables in the Host DB and only Tenant specific tables (our application tables) in the individual tenant DB's?

You can custom the connection string resolver to use host's connect string: https://docs.abp.io/en/abp/latest/Connection-Strings

For example:

[Dependency(ReplaceServices = true)]
[ExposeServices(typeof(IConnectionStringResolver))]
public class MyMultiTenantConnectionStringResolver : MultiTenantConnectionStringResolver
{
    private readonly ICurrentTenant _currentTenant;

    public MyMultiTenantConnectionStringResolver(
        IOptionsSnapshot<AbpDbConnectionOptions> options,
        ICurrentTenant currentTenant,
        IServiceProvider serviceProvider) : base(options, currentTenant, serviceProvider)
    {
        _currentTenant = currentTenant;
    }

    public override Task<string> ResolveAsync(string connectionStringName = null)
    {
        if (_currentTenant.IsAvailable)
        {

            if (connectionStringName == "Volo.Abp.Identity.EntityFrameworkCore.IIdentityProDbContext")
            {
                //.. return host's connection string
            }

            //Replace with the following code when 5.0 is released
            if (connectionStringName == AbpIdentityDbProperties.ConnectionStringName)
            {
                //.. return host's connection string
            }
        }

        //....
    }
}

0

Spospisil created 3 years ago

Thank you. That resolves my issue
0

Spospisil created 3 years ago

After looking at this deeper, what you suggested as the solution is not accurate. The 'connectionstringname' that is being used by the MultiTenantConnectionStringResolver are not the value you suggest, thus making it difficult to point each (if I choose to) module's DBContext to a particular connection string.

So to summarize I am trying to put all the ABP tables that store Identity, LanguageManagement, permissions, Auditing, users, etc into the Host's DB and just have tennat tables (application specific) in their own DB's for each tenant.

There is no clear direction in the documents or previous support tickets that outline this so an actual real world example of this being implemented using ABP would be much appreaciated.
0

liangshiwei created 2 years ago
Support Team Fullstack Developer

Hi,

Sorry, I forget one thing, the code you need to remove
0
Spospisil created 3 years ago
That resolved the issue with the code not finding the Users table but now I get an error on the "LanguageTexts" table as shown below. Just to clarify all the ABP base tables, including Saas, Identity server, users, etc are in a 'host' DB and the only tables in the 'tenant' DB are our application specific tables. So I want all things ABP module(pro and otherwise) to look at the host DB.

I tried adding the following code to resolve that connection string, but it did not work,.

case "Volo.Abp.LanguageManagement.EntityFrameworkCore.ILanguageManagementDbContext": using (_currentTenant.Change(null)) { connectionstring = await base.ResolveAsync(connectionStringName); } break;
0
liangshiwei created 3 years ago
Support Team Fullstack Developer
Hi,

We usually use constants for connection string names.

LanguageManagementDbProperties.ConnectionStringName is used for Language Management module:

case LanguageManagementDbProperties.ConnectionStringName: using (_currentTenant.Change(null)) { connectionstring = await base.ResolveAsync(connectionStringName); } break;
0

Spospisil created 3 years ago

Hi,

But it's been suggested here that I not use the connection name string, but rather use the full namespace and Interface class name to resolve this issue.

So how do I resolve the issue I'm getting as I've tried everything that's been suggested with no success.

liangshiwei created 3 years ago

Support Team Fullstack Developer

But it's been suggested here that I not use the connection name string, but rather use the full namespace and Interface class name to resolve this issue.

Sorry, I never recommend that you use full namespaces, you should always use constants.

It works for me.

[Dependency(ReplaceServices = true)]
[ExposeServices(typeof(IConnectionStringResolver))]
public class MyMultiTenantConnectionStringResolver : MultiTenantConnectionStringResolver
{
    private readonly ICurrentTenant _currentTenant;

    public MyMultiTenantConnectionStringResolver(
        IOptionsSnapshot<AbpDbConnectionOptions> options,
        ICurrentTenant currentTenant,
        IServiceProvider serviceProvider) : base(options, currentTenant, serviceProvider)
    {
        _currentTenant = currentTenant;
    }

    public override async Task<string> ResolveAsync(string connectionStringName = null)
    {
        if (_currentTenant.IsAvailable)
        {
            switch (connectionStringName)
            {
                case AbpPermissionManagementDbProperties.ConnectionStringName:
                    using (_currentTenant.Change(null))
                    {
                        var connectionString = await base.ResolveAsync(connectionStringName);
                        return connectionString;
                    }
                 //.........
            }
        }

        return await base.ResolveAsync(connectionStringName);
    }
}

0

Spospisil created 3 years ago

That is correct, you did not suggest that you did recomend that approach but if you read the entire thread it was suggested by someone else at Volo and did resolve that issue, so it appears that the connectionstring naming is not consistant so I will have to accomodate for that however, the issue is still not resolved with the LanguageTexts error I indicated I am getting which only happens when I log in as a Tenant user. Host user works fine. Please read thread to understand the distribution of our host vs tenant tables.

Thanks.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi,

Can you share your code and full logs?
0

Spospisil created 3 years ago

What specifically in the code do you need to see?
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

What specifically in the code do you need to see?

Your custom connection string resolver class code.

Spospisil created 3 years ago

[Dependency(ReplaceServices = true)]
[ExposeServices(typeof(IConnectionStringResolver))]
public class SWMultiTenantConnectionStringResolver : MultiTenantConnectionStringResolver
{
    private readonly ICurrentTenant _currentTenant;
    private readonly IServiceProvider _serviceProvider;

    public SWMultiTenantConnectionStringResolver(
        IOptionsSnapshot&lt;AbpDbConnectionOptions&gt; options,
        ICurrentTenant currentTenant,
        IServiceProvider serviceProvider) : base(options, currentTenant, serviceProvider)
    {
        _currentTenant = currentTenant;
        _serviceProvider = serviceProvider;
    }

    public override async Task&lt;string&gt; ResolveAsync(string connectionStringName = null)
    {
        string connectionstring = "";

        //No current tenant, fallback to default logic
        if (_currentTenant.Id == null)
        {
            return await base.ResolveAsync(connectionStringName);
        }


        if (_currentTenant.IsAvailable)
        {
            switch (connectionStringName)
            {
                case LanguageManagementDbProperties.ConnectionStringName:
                case AbpPermissionManagementDbProperties.ConnectionStringName:
                case FeatureManagementDbProperties.ConnectionStringName:
                    using (_currentTenant.Change(null))
                    {
                        connectionstring = await base.ResolveAsync(connectionStringName);
                    }
                    break;

                case "Volo.Abp.Identity.EntityFrameworkCore.IIdentityProDbContext":
                    using (_currentTenant.Change(null))
                    {
                        connectionstring = await base.ResolveAsync(connectionStringName);
                    }
                    break;

                case "Default":
                    using (_currentTenant.Change(null))
                    {
                        connectionstring = await base.ResolveAsync(connectionStringName);
                    }
                    break;

                default:
                    var tenant = await FindTenantConfigurationAsync(_currentTenant.Id.Value);

                    if (tenant == null || tenant.ConnectionStrings.IsNullOrEmpty())
                    {
                        //Tenant has not defined any connection string, fallback to default logic
                        return await base.ResolveAsync(connectionStringName);
                    }

                    connectionstring = tenant.ConnectionStrings.Default;
                    break;
            }
        }

        //Replace with the following code when 5.0 is released
        //if (connectionStringName == AbpIdentityDbProperties.ConnectionStringName)
        //{
        //    //.. return host's connection string
        //}
        return await Task.FromResult(connectionstring);
    }
}

0

Spospisil created 3 years ago

Here is the related log messages.

2021-11-17 08:34:38.671 -05:00 [INF] Request starting HTTP/1.1 GET https://localhost:44300/api/abp/multi-tenancy/tenants/by-id/3a00402b-022a-c209-bd72-28f9859e70b4?api-version=1.0 - - 2021-11-17 08:34:38.946 -05:00 [INF] Successfully validated the token. 2021-11-17 08:34:38.961 -05:00 [INF] Executing endpoint 'Pages.Abp.MultiTenancy.AbpTenantController.FindTenantByIdAsync (Volo.Abp.AspNetCore.Mvc.UI.MultiTenancy)' 2021-11-17 08:34:38.963 -05:00 [INF] Route matched with {area = "abp", action = "FindTenantById", controller = "AbpTenant", page = ""}. Executing controller action with signature System.Threading.Tasks.Task1[Volo.Abp.AspNetCore.Mvc.MultiTenancy.FindTenantResultDto] FindTenantByIdAsync(System.Guid) on controller Pages.Abp.MultiTenancy.AbpTenantController (Volo.Abp.AspNetCore.Mvc.UI.MultiTenancy). 2021-11-17 08:34:38.979 -05:00 [INF] Executing action method Pages.Abp.MultiTenancy.AbpTenantController.FindTenantByIdAsync (Volo.Abp.AspNetCore.Mvc.UI.MultiTenancy) - Validation state: "Valid" 2021-11-17 08:34:38.991 -05:00 [INF] Executed action method Pages.Abp.MultiTenancy.AbpTenantController.FindTenantByIdAsync (Volo.Abp.AspNetCore.Mvc.UI.MultiTenancy), returned result Microsoft.AspNetCore.Mvc.ObjectResult in 12.2466ms. 2021-11-17 08:34:38.993 -05:00 [INF] Executing ObjectResult, writing value of type 'Volo.Abp.AspNetCore.Mvc.MultiTenancy.FindTenantResultDto'. 2021-11-17 08:34:38.995 -05:00 [INF] Executed action Pages.Abp.MultiTenancy.AbpTenantController.FindTenantByIdAsync (Volo.Abp.AspNetCore.Mvc.UI.MultiTenancy) in 31.9413ms 2021-11-17 08:34:38.995 -05:00 [INF] Executed endpoint 'Pages.Abp.MultiTenancy.AbpTenantController.FindTenantByIdAsync (Volo.Abp.AspNetCore.Mvc.UI.MultiTenancy)' 2021-11-17 08:34:38.995 -05:00 [INF] Request finished HTTP/1.1 GET https://localhost:44300/api/abp/multi-tenancy/tenants/by-id/3a00402b-022a-c209-bd72-28f9859e70b4?api-version=1.0 - - - 200 - application/json;+charset=utf-8 324.9255ms 2021-11-17 08:34:39.030 -05:00 [INF] Request starting HTTP/1.1 GET https://localhost:44300/api/abp/application-configuration?api-version=1.0 - - 2021-11-17 08:34:39.031 -05:00 [INF] Successfully validated the token. 2021-11-17 08:34:39.035 -05:00 [INF] Executing endpoint 'Volo.Abp.AspNetCore.Mvc.ApplicationConfigurations.AbpApplicationConfigurationController.GetAsync (Volo.Abp.AspNetCore.Mvc)' 2021-11-17 08:34:39.035 -05:00 [INF] Route matched with {area = "abp", action = "Get", controller = "AbpApplicationConfiguration", page = ""}. Executing controller action with signature System.Threading.Tasks.Task1[Volo.Abp.AspNetCore.Mvc.ApplicationConfigurations.ApplicationConfigurationDto] GetAsync() on controller Volo.Abp.AspNetCore.Mvc.ApplicationConfigurations.AbpApplicationConfigurationController (Volo.Abp.AspNetCore.Mvc). 2021-11-17 08:34:39.036 -05:00 [INF] Executing action method Volo.Abp.AspNetCore.Mvc.ApplicationConfigurations.AbpApplicationConfigurationController.GetAsync (Volo.Abp.AspNetCore.Mvc) - Validation state: "Valid" 2021-11-17 08:34:39.057 -05:00 [INF] Entity Framework Core 5.0.12 initialized 'FeatureManagementDbContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: QuerySplittingBehavior=SplitQuery 2021-11-17 08:34:39.069 -05:00 [INF] Executed DbCommand (3ms) [Parameters=[@__providerName_0='?', @__providerKey_1='?'], CommandType='"Text"', CommandTimeout='30'] SELECT f."Id", f."Name", f."ProviderKey", f."ProviderName", f."Value" FROM "FeatureValues" AS f WHERE (f."ProviderName" = @__providerName_0) AND (f."ProviderKey" = @__providerKey_1) 2021-11-17 08:34:39.348 -05:00 [INF] Entity Framework Core 5.0.12 initialized 'LanguageManagementDbContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: QuerySplittingBehavior=SplitQuery 2021-11-17 08:34:39.427 -05:00 [ERR] Failed executing DbCommand (12ms) [Parameters=[@__ef_filter__p_0='?' (DbType = Boolean), @__ef_filter__CurrentTenantId_1='?' (DbType = Guid), @__resourceName_0='?', @__cultureName_1='?'], CommandType='"Text"', CommandTimeout='30'] SELECT l."Id", l."CreationTime", l."CreatorId", l."CultureName", l."LastModificationTime", l."LastModifierId", l."Name", l."ResourceName", l."TenantId", l."Value" FROM "LanguageTexts" AS l WHERE (@__ef_filter__p_0 OR (l."TenantId" = @__ef_filter__CurrentTenantId_1)) AND ((l."ResourceName" = @__resourceName_0) AND (l."CultureName" = @__cultureName_1)) 2021-11-17 08:34:39.458 -05:00 [ERR] An exception occurred while iterating over the results of a query for context type 'Volo.Abp.LanguageManagement.EntityFrameworkCore.LanguageManagementDbContext'. Npgsql.PostgresException (0x80004005): 42P01: relation "LanguageTexts" does not exist at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior) at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.Enumerator.MoveNext() Exception data: Severity: ERROR SqlState: 42P01 MessageText: relation "LanguageTexts" does not exist Position: 170 File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\parser\parse_relation.c Line: 1376 Routine: parserOpenTable Npgsql.PostgresException (0x80004005): 42P01: relation "LanguageTexts" does not exist at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior) at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Volo.Abp.LanguageManagement.EntityFrameworkCore.EfCoreLanguageTextRepository.GetList(String resourceName, String cultureName) in C:\Src\StructureWeb\ABP-Pro-Modules\language-management\src\Volo.Abp.LanguageManagement.EntityFrameworkCore\Volo\Abp\LanguageManagement\EntityFrameworkCore\EfCoreLanguageTextRepository.cs:line 27 at Castle.Proxies.Invocations.ILanguageTextRepository_GetList.InvokeMethodOnTarget() at Castle.DynamicProxy.AbstractInvocation.Proceed() at Castle.DynamicProxy.AbstractInvocation.ProceedInfo.Invoke() at Castle.DynamicProxy.AsyncInterceptorBase.ProceedSynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo) Exception data: Severity: ERROR SqlState: 42P01 MessageText: relation "LanguageTexts" does not exist Position: 170 File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\parser\parse_relation.c Line: 1376 Routine: parserOpenTable
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

HI,

I tried the code, there is no any problem.

relation "LanguageTexts" does not exist

The table default name is AbpLanguageTexts, so I think the problem is unrelated to connection string resolver. Maybe it's a specific problem with your application, can you use ABP Suite to create a new project to reproduce and share to me? thanks. shiwei.liang@volosoft.com
0

Spospisil created 3 years ago

Please reference my original screenshot in terms of the table names.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi,

See: https://github.com/abpframework/abp/issues/8887

if you change DbTablePrefix, then module will fail). It is recommended to use the static properties to arrange these options (like AbpIdentityDbProperties.DbTablePrefix = "MyPrefix").
0

Spospisil created 3 years ago

Nope. This didn't work either. Same issue. Now the error says the below after additng theDbTablePrefix of 'CF'

Exception thrown: 'Npgsql.PostgresException' in Microsoft.EntityFrameworkCore.Relational.dll An exception of type 'Npgsql.PostgresException' occurred in Microsoft.EntityFrameworkCore.Relational.dll but was not handled in user code 42P01: relation "CFLanguageTexts" does not exist
0

Spospisil created 3 years ago

when I remove the 'DependsOn' attribute of 'LanguageManagement......Module' though I no longer get the error, however I will need to keep this in my module.
0
liangshiwei created 3 years ago
Support Team Fullstack Developer
Hi,

Can also you try and create&apply migration file:

builder.ConfigureLanuageManagement(options => {options.TablePrefix= "";});

We have remove ModelBuilderConfigurationOptions classes in the 5.0, but in the 4.4 you need to use both.

Have an answer to this question? Log in and write your answer.