Can LLBLGen Runtime provide more helpful exception for errors related to MySQL fractional DateTime issues?

Posts   
 
    
safehome
User
Posts: 19
Joined: 23-Jul-2007
# Posted on: 03-Dec-2020 06:49:42   

If a DateTime contains fractional seconds, then adapter.SaveEntity fails with this error message: "During a save action an entity's update action failed. The entity which failed is enclosed." Exception thrown: 'SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException' in SD.LLBLGen.Pro.ORMSupportClasses.dll

Fails:

userProfile.RegisterDateTime = DateTime.Parse("11/9/2020 6:32:04.001 AM")
adapter.SaveEntity(userProfile);

Works:

userProfile.RegisterDateTime = DateTime.Parse("11/9/2020 6:32:04 AM")
adapter.SaveEntity(userProfile);

Here, I'm using MariaDB 10.5.7 and RegisterDateTime is a MySQL DateTime field (with its default of 0 precision, not 6). I tested against LLBLGen 5.7.1 and 5.6.2 runtimes and am using Devart version 8.17.1612.0.

With tracing enabled, I saw this query was being produced (which, if run by hand, 'works,' albeit with truncation): Method Enter: CreateUpdateDQ(4) Method Enter: CreateSingleTargetUpdateDQ(4) Generated Sql query: Query: UPDATE userprofile SET registerDateTime=@p1 WHERE ( userprofile.id = @p2) Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2020-11-09T06:32:04.0010000. Parameter: @p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 93420.

If we run the SQL directly, update userprofile set registerDateTime ='2020-11-09T06:32:04.0010000' WHERE id = 93420; then it works but produces this (much more helpful in my opinion) warning: Data truncated for column 'registerDateTime'

I've reviewed the following posts and understand that fractional seconds in MySQL are a mess. The irony here is I don't even need fractional seconds, plus it was easy to remove them once I became aware of the issue.

https://www.llblgen.com/tinyforum/Thread/22732/1 https://www.llblgen.com/tinyforum/Thread/26906/1 https://www.llblgen.com/tinyforum/Thread/22463/1 (I also tried adding IgnoreFractionalSeconds=true to the connection string, but that didn't help in this case.)

My goal in posting this is half to help others avoid going down the wrong track for "ORMConcurrencyException" errors and half to inquire if LLBLGen runtime could pass a more obvious exception message to the caller in such cases.

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Dec-2020 10:11:35   

The problem is I think that the update failed but didn't produce an exception so we don't know that the update failed because of an error/warning. All we see is that the update didn't succeed. Which can be e.g. due to a predicate value not matching etc. So if an update fails (meaning: returning 0 rows affected), we can only conclude a concurrency violation occurred: the row to update somehow didn't match with the predicates specified.

Normally, if there's an error with the update statement, we'd receive that as an exception and things would be reported as such, you wouldn't see a ConcurrencyException, but a query execution exception.

So I'm not sure what we can do at this point. Could you notify DevArt and ask if they are able to detect this and perhaps throw an exception? As the query fails and we don't have other ways to detect why the query failed.

Additionally, if the fraction has to be stripped off, we can do so (we can create a new DateTime value without the milliseconds), but that's a drastic measure that's going to be applied always so not something we'd like to do. You could also strip off the milliseconds btw. In a partial class of CommonEntityBase, you could override protected virtual void PreProcessValueToSet(IFieldInfo fieldInfoOfFieldToSet, ref object valueToSet)

Based on the IFieldInfo object (check the DataType property), you can determine if the field is of type DateTime. If so, you can then replace (as it's a struct!) valueToSet with a new DateTime without the milliseconds. This way setting the field with a DateTime with milliseconds/fraction would result in a value that doesn't have a fraction and you shouldn't run into this issue.

Frans Bouma | Lead developer LLBLGen Pro