Issue while saving the audit data into another database particularly during a Transaction scenario #712
Replies: 4 comments 3 replies
-
|
Could you share the code used to create the transaction or where the error occurs? Additionally, please provide the exception details, including the message and call stack trace |
Beta Was this translation helpful? Give feedback.
-
|
Since i can't directly mention some classes and method names i have modified them into general names audit config made in program .cs file: Audit.Core.Configuration.Setup().UseMySql(config =>
{
config.ConnectionString(GetDatabaseConnectionString(builder, "auditDb"));
});
var _options = new DbContextOptionsBuilder<AppAuditContext>()
.UseMySQL(GetDatabaseConnectionString(builder, "auditDb")).Options;
Audit.Core.Configuration.Setup()
.UseEntityFramework(ef => ef
.UseDbContext< AppAuditContext >(_options)
.AuditTypeMapper(t => typeof(AuditLog))
.AuditEntityAction<AuditLog>((ev, entry, entity) =>
{
AuditConfiguration.AddAudit(entry, entity);
})
.IgnoreMatchedProperties(true));Config Done with AuditConfiguration.AddAudit(entry, entity) method -> public static void AddAudit(EventEntry entry, AuditLog entity)
{
entity.Id = Guid.NewGuid();
entity.Entity = entry.Table;
entity.EntityId = entry.PrimaryKey.Select(x => new Guid(x.Value.ToString())).ToList();
entity.Action = entry.Action;
entity.ChangedAt = DateTime.UtcNow;
if (string.Equals(entity.Action, "Insert"))
{
entity.NewValue = entry.ColumnValues.Select(x => $"{x.Key} : {x.Value}");
entity.ChangedBy = entry.ColumnValues.Where(kvp => kvp.Key == "CreatedBy").Select(kvp => kvp.Value)?.FirstOrDefault()?.ToString();
}
else if (string.Equals(entity.Action, "Update"))
{
string? isActive = entry.ColumnValues.Where(kvp => kvp.Key == "IsActive").Select(kvp => kvp.Value)?.FirstOrDefault()?.ToString();
if (!string.IsNullOrEmpty(isActive) &&
bool.TryParse(isActive, out bool result) && result)
{
entity.NewValue = entry.Changes.Select(x => $"{x.ColumnName} : {x.NewValue}");
entity.OldValue = entry.Changes.Select(x => $"{x.ColumnName} : {x.OriginalValue}");
entity.ChangedBy = entry.ColumnValues.Where(kvp => kvp.Key == "UpdatedBy").Select(kvp => kvp.Value).FirstOrDefault().ToString();
}
else
{
entity.Action = "Delete";
entity.OldValue = entry.Changes.Select(x => $"{x.ColumnName} : {x.OriginalValue}");
entity.ChangedBy = entry.ColumnValues.Where(kvp => kvp.Key == "DeletedBy").Select(kvp => kvp.Value).FirstOrDefault().ToString();
}
}
// only for delete operation -
else
{
entity.OldValue = entity.OldValue = entry.Changes.Select(x => $"{x.ColumnName} : {x.OriginalValue}");
entity.ChangedBy = entry.ColumnValues.Where(kvp => kvp.Key == "DeletedBy").Select(kvp => kvp.Value).FirstOrDefault().ToString();
}
}Config Done in AppContext.cs file : public class AppContext: AuditDbContext, IAppContext{
// all the dbsets here
public AppContext (DbContextOptions<AppContext> options)
: base(options)
{
// injected the auditdataprovider here like a constructor injection by first configuring it in program file and adding that here.. see below
}
}Config Done in AppAuditContext.cs file : public class AppAuditContext : DbContext,IAppAuditContext
{
public AppAuditContext (DbContextOptions< AppAuditContext > options) : base(options)
{
}
public DbSet<AuditLog> AuditLogs { get; set; } // only single table in this db and it has a separate database created for audit purpose only
// onmodel configuration here
}Code where we get error : public async Task<xdto > Create(xdto newRecord, string createdBy)
{
await DeleteyrecordBy(newRecord.EId); // this method has savechangesasync once the record is deletd
// some logic
var newRequests = CreateRequest(newRecord, createdBy);
_appContext.[app_tablename].AddRange(newRequests);
// some logic here
if (something is true)
{
// getentity logic based on an Id
// internal updateMethod(with some params) // this method updated the entity fetched from the above getentity and this doeasn’t have any savechangesasync inside it and done inside the transactionscope
// some logic here
}
else
{
// same update method implementation for different entity
}
try
{
using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled); // Enable async flow
await _appContext.SaveChangesAsync().ConfigureAwait(true); // before configuring auditdataprovider this line calls the AddAudit method configured in the program class, suppose in this transaction if 2 entries are there at the end of processing the second entry it suddenly displays “multiple connections trying to use same transactions..” for now I could not give full error bcoz on configuring auditdataprovider in the program class and injecting that into our appContext class constructor the error is gone but the other issue is, it saves both the entries in the same row where sometimes we might want to filter the records based on entities.. in that case we won’t get proper record
scope.Complete();
// some logic here
}
catch (TransactionAbortedException ex)
{
throw new DbUpdateException(newRecord.EntityId.ToString(), ex);
}
return something;
}Auditdtaaprovider config to fix the error and to provide work around: //Audit.Core.Configuration.Setup().UseMySql(config =>
//{
// config.ConnectionString(GetDatabaseConnectionString(builder, "auditDb"));
//});
builder.Services.AddSingleton<AuditDataProvider>(new MySqlDataProvider(config => config
.ConnectionString(GetDatabaseConnectionString(builder, "auditDb"))
.TableName("AuditLogs")
.IdColumnName("Id")
.JsonColumnName("Data")
.CustomColumn("Id", ev => Guid.NewGuid().ToString())
.CustomColumn("EntityId", entry => string.Join(",",entry.GetEntityFrameworkEvent().Entries.SelectMany(x => x.PrimaryKey.Select(kv => new Guid(kv.Value.ToString()))).ToList()))
.CustomColumn("Entity", entry => string.Join(",", entry.GetEntityFrameworkEvent().Entries.Select(x => x.Table.ToString())))
.CustomColumn("Action", entry => string.Join(",",entry.GetEntityFrameworkEvent().Entries.Select(x => x.Action.ToString())))
.CustomColumn("ChangedAt", entry => DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"))
.CustomColumn("ChangedBy", entry => entry.Environment.UserName)
));
// these custom columns are bit difficult to manipulate according to the table schema we want
// When using audit data provider this add audit is not triggered.. but before adding audit data provider this Addaudit was executed..
var _options = new DbContextOptionsBuilder<AppAuditContext>()
.UseMySQL(GetDatabaseConnectionString(builder, "auditDb")).Options;
Audit.Core.Configuration.Setup()
.UseEntityFramework(ef => ef
.UseDbContext<AppAuditContext>(_options)
.AuditTypeMapper(t => typeof(AuditLog))
.AuditEntityAction<AuditLog>((ev, entry, entity) =>
{
AuditConfiguration.AddAudit(entry, entity);
})
.IgnoreMatchedProperties(true));In appcontext we inject this in constructor but it inserts the transaction inside a single row but not as individual row entries for each entity |
Beta Was this translation helpful? Give feedback.
-
|
It's still unclear to me what you're trying to achieve. I notice multiple calls to Are both databases hosted on the same server? I tried reproducing the issue with the following code, and it works as expected, but I'm running both databases (app and audit) on the same MS SQL Server, just in different databases: TransactionManager.ImplicitDistributedTransactions = true;
using var transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
ctx.Values.Add(entity);
await ctx.SaveChangesAsync();
transactionScope.Complete();builder.Services.AddDbContext<MyContext>(_ => _.UseSqlServer("data source=localhost;initial catalog=TestTran1;integrated security=true;Encrypt=False"));
builder.Services.AddDbContext<MyAuditContext>(_ => _.UseSqlServer("data source=localhost;initial catalog=TestTran2;integrated security=true;Encrypt=False"));
Audit.Core.Configuration.Setup()
.UseEntityFramework(d => d
.UseDbContext(ev => ev.GetEntityFrameworkEvent().GetDbContext().GetService<MyAuditContext>())
.AuditTypeMapper(t => typeof(AuditLog))
.AuditEntityAction<AuditLog>((ev, entry, auditLog) =>
{
auditLog.Table = entry.Table;
auditLog.ChangeType = entry.Action;
auditLog.Value = entry.ToJson();
})
.IgnoreMatchedProperties(true)); |
Beta Was this translation helpful? Give feedback.
-
|
{ } this the error I'm facing - Actually i'm trying to save the audits of my entities in different database dedicated for audit purpose. we have 2 different Db connection string : one is for appdb and another is for audit db.. so we created two dbcontexts class in our application |
Beta Was this translation helpful? Give feedback.
-
|
This seems to be a limitation for MySQL. Can you try with the suggestion in this SO answer? |
Beta Was this translation helpful? Give feedback.
-
|
I tried making it false but git the below error .ConnectionString(GetDatabaseConnectionString(builder, "auditDb")) |
Beta Was this translation helpful? Give feedback.
-
|
If you use the MySqlDataProvider, the audit logs will not be inserted within the same transaction as the application database. In this case, I recommend using the EFDataProvider but avoid creating a TransactionScope. This limitation lies within the MySQL server and is unrelated to this library. My understanding is that TransactionScope will function across different connections only if your MySQL server supports XA transactions, which are available exclusively with the InnoDB engine. |
Beta Was this translation helpful? Give feedback.
-
I have an Application Db and audit Db, i want to log the audit records of Application into this audit Db. But my configuration fails during a transaction scenario saying multiple connections can't access same transaction
In program.cs :
So changes made to make it work:
When AuditDataProvider is set in program class it did not work as expected, instead setting audit provider in the AppDbContext stores the whole record into the Db as a single row but I want them as individual rows.. tried many ways but still no luck!
Beta Was this translation helpful? Give feedback.
All reactions