Black Friday Sale!
Save up to 50% off any plans! Valid from 23 Nov to 30 Nov - Also valid for the existing customers
Open Closed

Issue with Multi-Tenancy Multiple Databases #9361


0
maharatha created

I am using ASPNET Zero .net core and angular to build an admin tool to control more than 2000 databases. All databases are in MySQL and I am basically creating one database per tenant. The host database and the tenant databases are in different servers.

My issue is I create multiple background jobs to handle certain type of things.

E.g. Let's say user clean up, so basically I have a job which looks for an email address in host databases and comb through each of the 2000 databases and delete the user from every database. The problem is while doing so I am ending up creating 2000 threads across the MySQL server and most of them goes to sleep. MYSql server then timesout and the whole database is crashing

namespace ABC.BackgroundJobs
{
    public class CreateUserToAllShowsJob : BackgroundJob<CreateUserInShowsJobArgs>, ITransientDependency
    {
        private readonly UserManager _userManager;
        private readonly IUserJobStateNotifier _userJobStateNotifier;
        private readonly IBackgroundJobEmailer _backgroundJobEmailer;
        private readonly MySQLUserManager _mySQLUserManager;

        private readonly IRepository<Tenant> _tenantRepository;
        private readonly IRepository<UserGroupOrganization> _usergrouporganizationsRepository;
        private readonly IRepository<UserAccount, long> _userAccountRepository;
        private readonly RoleManager _roleManager;
        private readonly IUnitOfWorkManager _unitOfWorkManager;
        private readonly IRepository<UserGroup> _usergroupsRepository;
        private readonly IRepository<Role> _roleRepository;
        private readonly IIocResolver _iIocResolver;
        private readonly IRepository<OrganizationUnit, long> _organizationUnitRepository;

        public CreateUserToAllShowsJob(IUserJobStateNotifier userJobStateNotifier,
                    IBackgroundJobEmailer backgroundJobEmailer,
                    MySQLUserManager mySQLUserManager,
                    UserManager userManager,
                    IRepository<Tenant> tenantRepository,
                    IRepository<UserGroupOrganization> usergrouporganizationsRepository,
                    IRepository<UserAccount, long> userAccountRepository,
                    RoleManager roleManager,
                    IUnitOfWorkManager unitOfWorkManager,
                    IRepository<UserGroup> usergroupsRepository,
                    IRepository<Role> roleRepository,
                    IIocResolver iIocResolver,
                    IRepository<OrganizationUnit, long> organizationUnitRepository
            )
        {
            _userManager = userManager;
            _userJobStateNotifier = userJobStateNotifier;
            _backgroundJobEmailer = backgroundJobEmailer;
            _mySQLUserManager = mySQLUserManager;
            _tenantRepository = tenantRepository;
            _usergrouporganizationsRepository = usergrouporganizationsRepository;
            _userAccountRepository = userAccountRepository;
            _roleManager = roleManager;
            _unitOfWorkManager = unitOfWorkManager;
            _usergroupsRepository = usergroupsRepository;
            _roleRepository = roleRepository;
            _iIocResolver = iIocResolver;
            _organizationUnitRepository = organizationUnitRepository;
        }

        [UnitOfWork]
        [Audited]
        public override void Execute(CreateUserInShowsJobArgs args)
        {
            var hostUser = _userManager.FindByIdAsync(args.ActionRequestedBy).Result;
            var newUser = _userManager.FindByIdAsync(args.√öserId).Result;
            try
            {
                AsyncHelper.RunSync(() => AddUserToAllShows(newUser, args.UserGroupIds, args.AssignedRoleNames, args.OrganizationIds, hostUser));
            }
            catch (Exception exception)
            {
                // sends notification
                _userJobStateNotifier.AddUserToAllShowsAsync(new BackgroundJobStatusDto()
                {
                    Message = exception.Message,
                    JobName = LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToAllShowsJob"),
                }, Abp.Notifications.NotificationSeverity.Error).Wait();

                // sends email
                if (!ReferenceEquals(hostUser, null) && !hostUser.EmailAddress.IsNullOrEmpty())
                {
                    // sends email                    
                    _backgroundJobEmailer.SendBackgroundJobFailInfo(new BackgroundJobEmailInput()
                    {
                        EmailAddress = hostUser.EmailAddress,
                        Message = exception.Message,
                        JobName = LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToAllShowsJob"),
                        JobFailedDateTime = DateTime.UtcNow
                    });
                }
                // logs the exception
                Logger.Error(exception.Message, exception);
                throw exception;
            }
        }

        private async Task AddUserToAllShows(User input, List<int> UserGroups, List<string> AssignedRoleNames, List<long> OrganizationUnits, User hostUser)
        {
            if (!ReferenceEquals(UserGroups, null) && UserGroups.Any())
            {
                var hostUserRoles = AssignedRoleNames.ToList();
                var tenants = await (from tenant in _tenantRepository.GetAll().Where(p => OrganizationUnits.Contains(p.OrganizationUnitId.Value))
                                     join usergrporg in _usergrouporganizationsRepository.GetAll().Where(p => UserGroups.Contains(p.UserGroupId)) on
                                     tenant.OrganizationUnitId equals usergrporg.OrganizationId
                                     join useracc in _userAccountRepository.GetAll().Where(p => p.UserName == input.UserName) on
                                     tenant.Id equals useracc.TenantId
                                     into userAccount
                                     from userAccounts in userAccount.DefaultIfEmpty()
                                     select new { tenantId = tenant.Id, userAccounts.UserName, tenant.OrganizationUnitId, tenant.TenancyName }).ToListAsync();

                var userGroupRoleIds = await _usergroupsRepository.GetAll().Where(p => UserGroups.Contains(p.Id))
                                .Select(p => p.RoleId.Value).Distinct().ToListAsync();

                var roleDisplayNames = new List<string>();
                if (userGroupRoleIds.Any())
                {
                    var roles = await _roleRepository.GetAll().Where(p => userGroupRoleIds.Contains(p.Id)).ToListAsync();
                    hostUserRoles = roles.Select(p => p.DisplayName).ToList();
                }
                else
                {
                    //from UI Role NAmes will passs, By using role name we are getting Displaynames
                    var roles = await _roleRepository.GetAll().Where(p => hostUserRoles.Contains(p.Name)).Select(p => p.DisplayName).ToListAsync();
                    hostUserRoles = roles;
                }
                foreach (var role in hostUserRoles)
                {
                    roleDisplayNames.Add(_roleManager.GetTrimmedRoleName(role));
                }
                var user = await _userManager.FindByNameAsync(input.UserName) ??
                                  await _userManager.FindByEmailAsync(input.EmailAddress);

                var orgIds = tenants.Where(p => p.UserName == null).Select(p => p.OrganizationUnitId);
                if (orgIds.Any())
                {
                    bool isException = false;
                    var orgNames = await _organizationUnitRepository.GetAll().Where(p => orgIds.Contains(p.Id)).Select(p => p.DisplayName).ToListAsync();
                    await _userJobStateNotifier.AddUserToAllShowsAsync(new BackgroundJobStatusDto()
                    {
                        Message = $"{ LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToAllShowsJob")}:" +
                     $"{string.Format(LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "Addtoshowsjobstarted"), input.UserName, string.Join(',', orgNames))}",
                    }, Abp.Notifications.NotificationSeverity.Info);


                    foreach (var tenant in tenants.Where(p => p.UserName == null).Distinct())
                    {
                        using (var uowForAdduser = _unitOfWorkManager.Begin(TransactionScopeOption.RequiresNew))
                        {
                            try
                            {
                                using (_unitOfWorkManager.Current.SetTenantId(tenant.tenantId))
                                {
                                    //Copy the details
                                    var userinputforTenant = new UserEditDto();
                                    userinputforTenant.Name = user.Name;
                                    userinputforTenant.Surname = user.Surname;
                                    userinputforTenant.UserName = user.UserName;
                                    userinputforTenant.EmailAddress = user.EmailAddress;
                                    userinputforTenant.PhoneNumber = user.PhoneNumber;
                                    userinputforTenant.Password = user.Password;
                                  

                                    var createUserinput = new CreateOrUpdateUserInput
                                    {
                                        User = userinputforTenant,
                                        SetRandomPassword = true,
                                        AssignedRoleNames = assignedRoleNames.ToArray(),
                                        OrganizationUnits = OrganizationUnits,
                                        SendActivationEmail = false,
                                        DbTemplates = new List<int>() { }
                                    };
                                    using (var _userAppService = _iIocResolver.ResolveAsDisposable<UserAppService>())
                                    {
                                        await _userAppService.Object.CreateInternalUserAsync(createUserinput);
                                    }
                                    //await CreateInternalUserAsync(createUserinput);
                                }
                                await _unitOfWorkManager.Current.SaveChangesAsync();
                                await _mySQLUserManager.GrantPrivilegeToUserForShow(new MySqlUserPrivilegeInput()
                                {
                                    TenantId = tenant.tenantId,
                                    UserName = input.UserName,
                                    Password = SimpleStringCipher.Instance.Decrypt(user.PasswordForMysqlUser)
                                });

                            }
                            catch (Exception exception)
                            {
                                isException = true;
                                var msg = $"{string.Format(LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToShowExceptionMessage"), tenant.TenancyName, orgNames)} {exception.Message}";
                                // sends notification
                                await _userJobStateNotifier.AddUserToAllShowsAsync(new BackgroundJobStatusDto()
                                {
                                    Message = msg,
                                }, Abp.Notifications.NotificationSeverity.Error);

                                // sends email
                                if (!hostUser.EmailAddress.IsNullOrEmpty())
                                {
                                    await _backgroundJobEmailer.SendBackgroundJobFailInfo(new BackgroundJobEmailInput()
                                    {
                                        EmailAddress = hostUser.EmailAddress,
                                        Message = msg,
                                        JobName = LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToAllShowsJob"),
                                        JobFailedDateTime = DateTime.UtcNow
                                    });
                                }
                                Logger.Error(msg, exception);
                            }
                            finally
                            {
                                await uowForAdduser.CompleteAsync();
                            }
                        }
                    }
                    if (isException)
                    {
                        throw new UserFriendlyException(string.Format(LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToAllShowsJobFailedReinitiateMesage"),
                            user.Name, string.Join(',', orgNames)));
                    }
                    await _userJobStateNotifier.AddUserToAllShowsAsync(new BackgroundJobStatusDto()
                    {
                        Message = $"{ LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "CreateUserToAllShowsJob")}:" +
                        $"{string.Format(LocalizationHelper.GetString(ABCConsts.LocalizationSourceName, "AddtoshowsjobCompletd"), input.UserName, string.Join(',', orgNames))}",
                    }, Abp.Notifications.NotificationSeverity.Info);
                }
            }
        }
    }
}

The code more or less looks like the above. Can you help me understand how to execute job which can safely go through each of the database and do something. Any example should help.


2 Answer(s)
  • 0
    maharatha created

    Any updates on this ?

  • 0
    maliming created
    Support Team

    hi

    UserTokenExpirationWorker will get the tenants info in the host, and then switch to the tenant to execute the deletion of tokens.

    It is compatible with hosts and tenants using the same database and different databases.

    I don't particularly understand your code, but you can refer to UserTokenExpirationWorker.

    If you have a lot of tenants and data, you can consider using sub-jobs in background jobs.

    https://github.com/aspnetboilerplate/aspnetboilerplate/blob/dev/src/Abp.ZeroCore/Authorization/Users/UserTokenExpirationWorker.cs#L40-L66