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
Any updates on this ?
-
0
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
