Hi

This may be very basic, but I have an entity with an auto generated Id, and a custom identificator that I want to follow the autogenerated id. My very old Mysql wont let me do this, so I've reverted to this strategy:

create entity save entity and read the autogenerated id write the autogenerated id to the other column save the entity again

When I do that, it works. On my machine. And on our test server, but not on the production server. Test and production have individual but "identical" databases on the same database server.

In production the second "save the entity" will cause a

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: Duplicate entry '10431-2022-01-21 15:37:29' for key 1. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> Devart.Data.MySql.MySqlException: Duplicate entry '10431-2022-01-21 15:37:29' for key 1
   at Devart.Data.MySql.a1.a()
   at Devart.Data.MySql.a1.h()
   at Devart.Data.MySql.m.a(r[]& A_0, Int32& A_1, Boolean A_2)
   at Devart.Data.MySql.m.a(Byte[] A_0, Int32 A_1, Boolean A_2, String A_3)
   at Devart.Data.MySql.p.e()
   at Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteActionQuery(IActionQuery queryToExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecuteElements(List`1 elementsToRun)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PersistQueue(List`1 queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass19_0.<SaveEntity>b__0()

The (relevant part) of the code:

                    var newCustomerProduct = new CustomerProductEntity
                    {
                        SubscriptionId = 0,
                        CustomerId = customerId,
                       ... omitted for readability ...
                    };

                    adapter.SaveEntity(newCustomerProduct, true);

                    // creating a new customer product equals creating a new subscription, so set the subscription id to the id of the original customer product

                    newCustomerProduct.SubscriptionId = newCustomerProduct.Id;
                    adapter.SaveEntity(newCustomerProduct, true);

Am I doing something conceptually wrong, code-wise - or should I be looking for other issues? How can this suddenly not work....

• LLBLGen 5.8 (5.8.1) RTM Adapter template
• Mysql 5.6
• Devart dotconnect for MySQL express 8.19.1866.0

/Jan

Edit: we use Devart dotconnect 8.19.1866.0

Edit2: and 5.8.1, not 5.8.0

More details:

I caught the ORMQueryExecutionException and now got:

Inner exception: Error Inner exception Duplicate entry '10434-2022-01-21 16:50:56' for key 1

Query executed: 
    Query: UPDATE `customerproducts` SET `subscriptionId`=@p1 WHERE ( `customerproducts`.`id` = @p2)
    Parameter: @p1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10434.
    Parameter: @p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10434.

How can that EVER result in a Duplicate Entry????

/Jan

Problem narrowed down to the fact that on the production database (and not the test or local database) there are defined an inserttrigger and an updatetrigger that will copy the inserted/updated/deleted row to a different database for analytical/history purposes. It appears to be that the trigger-execution may confuse the database so that the second SaveEntity() fails. Cant tell why.

I've tried with adding a transaction around the operations a la

startTransaction

create new entity save new entity with refetch save the id

SaveTransaction

fetch new entity based on Id saved update new entitys second attribute

Commit

in the hope that the "SaveTransaction" would help - it didn't.

I've also tried with creating a new adapter around the "create part" and a new adapter around the "fetch and update part". Also didn't work.

Turns out that the problem can be "worked around" by adding a sleep in c#

StartTransaction

• create new entity
• save new entity with refetch save the id

SaveTransaction

Thread.Sleep(1000)

• modify new entity, set entity.subscriptionId = entity.Id
• save the new entity again

Commit

apparently, the database need time to finish the trigger execution or else we get the "duplicate key". I really dont understand it. If the INSERT and the trigger-code runs in serial and only returns once done, a sleep should do no difference. If a sleep does do a difference, it must be because it runs asynchronously / in another thread, but then it must indicate that the db returns the INSERT operation as soon as the new Autogenerated id has been picked, i.e. when the row itself has been inserted and then runs the trigger afterwards letting it do whatever it wants. In that case, why would a second saveEntity in 10ms be any different from one in a 1000ms, the row is there.

Any advice? I cant handle the fact that we have a Thread.Sleep in our code for this... :-/

/Jan

I don't know how this is done specifically in MySQL, but in other databases, if a trigger is run due to an insert or update, it's run inside the transaction of the insert or update, so the isolation level is the same. You might want to look into isolation levels here, but as it's mysql 5.x, 'transaction' is something that's not 100% fail safe. It might be (but again, I'm not an expert on MySQL settings) that MySQL has settings or the trigger might have options, where it can be run inside the transaction that caused it to fire (so if the outer transaction completes, i.e. the insert, the trigger should also be completed).