mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'"

I can not explain the following error message I am getting:

 EXC EZSQLException {Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'"}

I am using mormot2 for its ORM capabilities (without a central API server but as framework in workstations that perform db operations).
Two different workstations connect to the same Mariab server using Zeos
The table is external with primary key auto_increment and the two workstations read and insert records in this table through orm.AddOrUpdate(ormtable) (with IDvalue=0 for inserting)
Sometimes, the program needs to restarted as it continues to show this error upon inserting a new record .

Initially I though it could be a cache problem, but when retrieving records it shows all recent records with bigger primary key than the new it tries to use when inserting new record. It is just trying to insert a new record with a primary key that already exists.
The primary key that it tries to use in the sql query when inserting the record is increased by one in each try.

Any ideas?

Last edited by dcoun (2022-02-09 06:36:42)

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'"

I found the problem:
mormot.orm.sql, line 296
   /// by default, any INSERT will compute the new ID from an internal variable
    // - it is very fast and reliable, unless external IDs can be created
    // outside this engine
    // - you can set EngineAddUseSelectMaxID=true to execute a slower
    // 'select max(ID) from TableName' SQL statement before each EngineAdd()
    // - a lighter alternative may be to call EngineAddForceSelectMaxID only
    // when required, i.e. when the external DB has just been modified
    // by a third-party/legacy SQL process

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'"

Your main problem is that you don't have a service.
Don't let the workstations directly access the database, but create a service which will maintain the ID and have the DB connection.
It will be safer and also faster. Then the service will be able to grow up and do more than what you expect now.

Or use TSynUniqueIdentifier to generate the IDs in several points with no collision.
Or use a MariaDB sequence (there is no direct support of sequences in our ORM because it is not supported on all DB).
Because even EngineAddUseSelectMaxID may have troubles, and is subject to race condition if two instances are running it on the same time.

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: Message:"SQL Error: Duplicate entry '35669' for key 'PRIMARY'"

You have right, I am absolutely with you.
But for a 12 years old application still in use and learning mormot2 at the same time, I have to go with smaller steps: Refractor the code inside the application that will be the future service.
I am waiting also the mobile Delphi compatible part of mormot2 before splitting the service code.