I am trying to figure out which column, or columns, is tripping the error below. Something changed about the incoming data, fed by a 3rd party service and it is now causing failures when I try to save it to SQL.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.
A very simple flow:
Data structure is defined as:
[JsonObject(MemberSerialization.OptIn)]
public class RatDbAttributes
{
[JsonProperty]
[StringLength(50)]
public string block_chain { get; set; } // varchar(50)
[JsonProperty]
[StringLength(50)]
public string block_reduction { get; set; } // varchar(50)
[JsonProperty]
[StringLength(50)]
public string block_reward { get; set; } // varchar(50)
[JsonProperty]
public double block_time { get; set; } // decimal(28,6)
[JsonProperty]
[StringLength(100)]
public string consensus_method { get; set; } // varchar(100)
[JsonProperty]
public decimal decimals { get; set; } // decimal(28,6)
[JsonProperty]
[StringLength(50)]
public string difficulty_retarget { get; set; } // varchar(50)
[JsonProperty]
[StringLength(200)]
public string genesis_address { get; set; } // varchar(200)
[JsonProperty]
[StringLength(100)]
public string hash_algorithm { get; set; } // varchar(100)
[JsonProperty]
[StringLength(50)]
public string mineable { get; set; } // varchar(50)
[JsonProperty]
public long p2p_port { get; set; } // bigint
[JsonProperty]
public long rpc_port { get; set; } // bigint
[JsonProperty]
[StringLength(200)]
public string token_role { get; set; } // varchar(200)
[JsonProperty]
public decimal @float { get; set; } // decimal(28,6)
[JsonProperty]
public decimal minted { get; set; } // decimal(28,6)
[JsonProperty]
public decimal total_supply { get; set; } // decimal(28,6)
[JsonProperty]
public decimal max_supply { get; set; } // decimal(28,6)
[JsonProperty]
[StringLength(133)]
public string wallet { get; set; } // varchar(133)
[JsonProperty]
[NotMapped]
public double genesis_timestamp { get; set; } // see below
[JsonIgnore]
public DateTime Genesis_TimeStamp { get { return genesis_timestamp.ToDateTime(); } set { genesis_timestamp = value.ToEpoch(); } }
// Foregin Key Relationship (1-to-1) and Primary Key
[JsonIgnore]
public long TokenMasterId { get; set; }
[JsonIgnore]
[ForeignKey("TokenMasterId")]
public RatDbTokenMaster TokenMaster { get; set; } //foreign key to Parent
}
I've double checked the genesis_timestamp
and that is not the problem (converting double to datetime).
Sample incoming failing JSON:
{"block_chain":""
,"block_reduction":""
,"block_reward":"0"
,"block_time":0.0
,"consensus_method":""
,"decimals":0.0
,"difficulty_retarget":""
,"genesis_address":""
,"hash_algorithm":""
,"mineable":"False"
,"p2p_port":0
,"rpc_port":0
,"token_role":""
,"float":0.0
,"minted":0.0
,"total_supply":0.0
,"max_supply":0.0
,"wallet":""
,"genesis_timestamp":0.0
}
I created a fail-over iterative save for when a batch fails. Since I made my database connection "global" for this class I was inadvertantly running into a problem. I would add the recordset and try to save it (error pops), then try to iterate smaller and smaller batches to discover the offending record(s). The problem was I did not remove the offending recordset before iterating. Therefore, every iteration carried the error condition in the DB connection!
internal void IterateSave<TModel>(List<TModel> items) where TModel : class
{
using (LogContext.PushProperty("Data: Class", nameof(RatBaseCommandHandler)))
using (LogContext.PushProperty("Data: Method", nameof(IterateSave)))
using (LogContext.PushProperty("Data: Model", nameof(items)))
{
int max = items.Count;
int skip = 0;
int take = (max > 20) ? (max / 5) : 1;
int lastTake = take;
List<TModel> subItems = new List<TModel>();
while (skip <= max)
{
try
{
subItems = items.Skip(skip).Take(take).ToList();
Log.Verbose("Working {Max} | {Take} | {Skip}", max, take, skip);
skip += take;
_db.Set<TModel>().AddRange(subItems);
_db.SaveChanges();
}
catch (Exception ex)
{
/***** Was not removing the faulty record/recordset! *****/
_db.Set<TModel>().RemoveRange(subItems);
/***** Was not removing the faulty record/recordset! *****/
if (take == 1 && skip < max)
{
Log.Error(ex, "Error saving specific record in this data batch! {GuiltyRecord}", JsonConvert.SerializeObject(subItems));
if (skip >= max - 1)
{
depth--;
return;
}
}
else if (take > 1)
{
Log.Warning("Something is wrong saving this data batch! {RecordCount} Running a smaller batch to isolate.", take);
IterateSave(subItems);
}
}
}
}
}
With those 2 lines added (commented section in catch) the error literally popped right out!
Error saving specific record in this data batch! "[{\"block_chain\":\"Ethereum\",\"block_reduction\":\"\",\"block_reward\":\"0\",\"block_time\":0.0,\"consensus_method\":\"\",\"decimals\":18.0,\"difficulty_retarget\":\"\",\"genesis_address\":\"0x3520ba6a529b2504a28eebda47d255db73966694\",\"hash_algorithm\":\"\",\"mineable\":\"False\",\"p2p_port\":0,\"rpc_port\":0,\"token_role\":\"\",\"float\":0.0,\"minted\":60000000000000000000000000.0,\"total_supply\":60000000000000000000000000.0,\"max_supply\":60000000000000000000000000.0,\"wallet\":\"\",\"genesis_timestamp\":0.0}]" Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.
While C# can handle 60000000000000000000000000.0 in a DECIMAL data type, our SQL is defined at DECIMAL(28,6). Because of the 6 digit precision that only leaves space for a 10^22 value.
(It appears that SQL can now handle DECIMAL(38,6). Time to play with column definitions without losing production data.)